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
