Re: Please help - corrupt database

Thanks Tom. We recently added REINDEX DATABASE to satisfy a customer that swears that databases need to be routinely reindexed. In the customer's mind, this is especially true because we are not running sql server nor windows. I'll take the command out of our backup script and pass on the info that you provided.

Best regards,

Steve Ertel
859-534-4568
steve(dot)ertel(at)infimatic(dot)com

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, December 16, 2008 10:24 AM
To: Ertel, Steve
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Please help - corrupt database

"Ertel, Steve" <Steve(dot)Ertel(at)infimatic(dot)com> writes:
> From the log files you can see that the backup does a REINDEX DATABASE
> db_name While this reindex is running, several other queries are running against the database wrapped in a BEGIN/COMMIT transaction.
> After the transaction query was issued, the log file provides the
> message NOTICE: table "pg_class" was reindexed A few more queries run
> and then the log file provides the error message: ERROR: could not
> find pg_class tuple for index 2663

> Is there anything that we can do to recover from this issue?

REINDEX DATABASE during active database operations is just asking for trouble in 8.1 :-(. There are race conditions that can be triggered if another backend opens a reindexed index at the wrong time.

My advice is to stop doing the REINDEX DATABASE; I don't think that's necessary or appropriate as a routine maintenance operation. If you insist on doing it, update to a recent 8.2.x or 8.3.x release, where it should be reasonably safe.

regards, tom lane