Re: Corrupt database? 8.1/FreeBSD6.0

Jeff Amiel <becauseimjeff(at)yahoo(dot)com> writes:
> "PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) 3.4.4 [FreeBSD] 20050518"

> Jan 2 03:05:04 prod-app-1 postgres[8524]: [4-1] 8524 LOG: autovacuum: processing database "template0"
> Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-1] 8524 ERROR: could not access status of transaction 1924023481
> Jan 2 03:05:05 prod-app-1 postgres[8524]: [5-2] 8524 DETAIL: could not open file "pg_clog/072A": No such file or
> directory

> ls -l /db/pg_clog/
> total 984
> -rw------- 1 pgsql wheel 262144 Jan 11 09:55 07CF
> -rw------- 1 pgsql wheel 262144 Jan 11 13:45 07D0
> -rw------- 1 pgsql wheel 262144 Jan 11 17:00 07D1
> -rw------- 1 pgsql wheel 172032 Jan 11 20:39 07D2

So apparently there's some row in template0 that didn't get marked
committed before the pg_clog segment for it went away. Given 8.1's
rather schizophrenic view of whether it can modify template0 or not,
this is not too surprising, but I thought we'd put in some defense
against this happening. Alvaro?

Jeff, had you changed your autovac settings recently? Given that
autovac seems to be trying to hit template0 every few minutes, it's
hard to see how the tuple got missed for long enough to be a problem.

regards, tom lane