Re: Where art thou pg_clog?

Casey Duncan wrote:

> >>Interestingly I can manually vacuum that table in all of the
> >>databases on this machine without provoking the error.
> >
> >Except template0 I presume? Is this autovacuum running in template0
> >perchance? I note that 800 million transactions have passed since the
> >Xid in the error message was current.
>
> Wouldn't you know it! A little farther back up in the log file:
>
> 2007-02-15 14:20:48.480 PST LOG: autovacuum: processing database
> "template0"
> 2007-02-15 14:20:48.480 PST DEBUG: StartTransaction
> 2007-02-15 14:20:48.480 PST DEBUG: name: unnamed; blockState:
> DEFAULT; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1,
> children: <>
> 2007-02-15 14:20:48.481 PST DEBUG: autovacuum: VACUUM FREEZE whole
> database
> 2007-02-15 14:20:48.481 PST DEBUG: CommitTransaction
> 2007-02-15 14:20:48.481 PST DEBUG: name: unnamed; blockState:
> STARTED; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1,
> children: <>

This is a bug we fixed in 8.1.7. I suggest you update to the latest of
the 8.1 series, to get that fix among others.

To fix the problem, set pg_database.datallowconn=true for template0,
then connect to it and do a VACUUM FREEZE. Then set datallowconn=false
again.

> I'm curious how template0 got stomped on. Certainly nothing's been
> changing it. Of course it might just be some random bug so the fact
> it landed on a file for template0 could be completely arbitrary.

The problem is that all databases are vacuumed every so many
transactions, to avoid Xid wraparound problems; even non connectable
databases. The problem is that a bug in autovacuum caused that vacuum
operation to neglect using the FREEZE flag; this negligence makes it
leave non-permanent Xids in the tables, leading to the problem you're
seeing.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support