On Tue, Apr 15, 2008 at 9:36 AM, Mikko Partio <
mpartio@gmail.com> wrote:
On Wed, Apr 9, 2008 at 4:47 PM, Mikko Partio <
mpartio@gmail.com> wrote:
Hello all
my struggle with the database continues (see earlier thread titled "too many trigger records found for relation xyz").
Today, I created yet another to table to the same database. Everything went ok, no errors or anything, but when I checked pg_tables -view I saw two tables with the same name. Instantly I queried pg_class and yes there was again two tables with same oid. I dropped the table before anything more serious could happen, but then postgres started to complain of "cache lookup failed for relation ...". I disconnected my psql session and tried to reconnect but failed to do so:
2008-04-09 16:39:25 EEST [18984]: [1-1] FATAL: could not open relation 1663/16386/544592: No such file or directory
Indeed, there is no such file in that directory. I'm guessing that file is connected to the table I just dropped. Now, is there anything to do to get the database back online? I can still connect to other databases in the same instance
The cure was to create file 1663/16386/54459 8K in size with dd. The file in question was in fact the oid index on pg_class -- I had issued a REINDEX on pg_class just a moment before and apparantly something went wrong and the system lost track of the index. There was also two entries in pg_index for index pg_class_oid_index. After I removed the other entry and reindexed pg_class and pg_index, everything seems to be working ok. All the symptoms indicate that perhaps a xid wraparound had happened, but there is no such warning in logs and age(datfrozenxid) went never higher than say 250,000,000. Does anybody have a clue what might have happene
And now it has happened again. A CLUSTER operation was done on a table succesfully, afterwards when trying to access the table I get the error
2008-04-17 13:05:30 EEST [8435]: [32-1] ERROR: could not open relation 1663/16386/359232: No such file or directory
Seems to me like VACUUM FULL, REINDEX and CLUSTER change the filename of a table and/or index and then fail to record the new name to system catalogues. Is this a known deficiency what can I do to stop this behaviour?
Regards
Mikko