Re: Corrupt database? 8.1/FreeBSD6.0

Jeff Amiel <becauseimjeff(at)yahoo(dot)com> writes:
> Jan 13 08:27:26 prod-app-1 postgres[92257]: [30259-1] jboss 92257 ERROR: could not access status of transaction 2107200825
> Jan 13 08:27:26 prod-app-1 postgres[92257]: [30259-2] jboss 92257 DETAIL: could not open file "pg_clog/07D9": No such file or directory
> Jan 13 08:27:26 prod-app-1 postgres[92257]: [30259-3] jboss 92257 CONTEXT: SQL statement "DELETE FROM audit_metadata WHERE user_id <> -1"

> pg_clog dir looks like this:
> -rw------- 1 pgsql wheel 262144 Jan 13 05:41 07DA
> -rw------- 1 pgsql wheel 262144 Jan 13 08:06 07DB
> -rw------- 1 pgsql wheel 90112 Jan 13 08:51 07DC

> Now that table, audit_metadata, is a temporary table (when accessed by jboss as it is here). There is a 'rea'l table with the same name, but only used by batch processes that connect to postgres.

Really? Wow, *that's* an interesting thought. Is it likely that that
temp table could contain many-hour-old data?

This seems unrelated to your issue with autovacuum (which should never
touch a temp table, and certainly isn't going to find one in template0)
... but I suddenly fear that we've missed a fundamental point about
pg_clog truncation. And WAL wraparound for that matter. To wit, a
sufficiently long-lived temp table could contain old XIDs, and there's
no way for anyone except the owning backend to clean them out, or even
guarantee that they're marked committed.

Thoughts?

regards, tom lane