ERROR: cache lookup failed for relation #######

What would cause an error such as "ERROR:  cache lookup failed for relation #######"?  This happens from time to time (a lot lately) and occurs when trying to insert data from a CSV file.  I can then later go back and try importing the same CSV file data and it will be succesful.  So there is nothing wrong with the file or the tables.  This is really frustrating because we have had a large number of insert problems.

I am using Perl PgPP.pm to connect to the Postgres 7.4 database.
mock5cAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gheistCommented:
Can I have full version of database and some idea of underlying OS?
Cause most likely is power failure or unclean process shutdown.
REINDEX or VACUUM usually helps.
0
mock5cAuthor Commented:
OS: debian 4.0
Postgres version 7.4.19

I have a cron job that vacuum analyzes every night and it does a vacuum full once a week.

I have manually reindexed select tables numerous times.
0
gheistCommented:
It looks like some integrity problem with (system) data e.g table deleted and index still present, or because of power failure.

pg_dumpall walks around my mind...

if you can recall power failures or oom over the life of system then you should spend some effort to make PostgeeSQL always sync data and filesystem to do full jounaling (or get into better power environment with more memory)
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

earth man2Commented:
If you do a lot of inserts, sometimes it is best to set AUTOCOMMIT ON and drop index es on that table and recreate them after you've finished.

If you need to worry about transactional integrity then insert into an identical table then copy the data from that table into the correct table.
0
mock5cAuthor Commented:
For the CSV file imports, there are anywhere from 27 - 4000 inserts per CSV file.  So yes, there are a lot of inserts.  I am using transactions so I can rollback in case a bad line of data in the CSV file is encountered (out of range, illegal value, etc.).  I think the idea of inserting into an identical table and then copying would be a solution to that.  Or I could have the program check for file integrity before doing any inserts and then do inserts without transactions.
0
gheistCommented:
This is not the cause of referential integrity problem. In theory bad data should be rejected on attempt to insert and your mechanism inserting by batches is balance between locating input error and optimally filling DB.

This might be bug in previous version etc. There is no elegant way to fix system tables damaged this way.
0
gheistCommented:
Both gave tips on issue... A liitle feedback after
0
earth man2Commented:
He say
"I think the idea of inserting into an identical table and then copying would be a solution to that"
If he no say where he is at with problem how can we assist further?

The author is using an old version of PostgreSQL and maybe upgrading to the latest version will solve this issue.
0
mock5cAuthor Commented:
I'm apologize for abandoning this message.  I haven't checked back in awhile.  It seems that the fix for my problem was to change the cron job that was installed along with Postgres.  It was running a vacuum analyze every 3 or 4 hours and it's a very large database.  I think that was locking the tables.  Ever since I've changed this cron job to run only once a day in the middle of the night, I have only seen this error a few times.
0
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.