Re: Where art thou pg_clog?

On Feb 15, 2007, at 2:44 PM, Alvaro Herrera wrote:

> Casey Duncan wrote:
>>
>> On Feb 15, 2007, at 1:46 PM, Alvaro Herrera wrote:
>>> [..]
>>> Can you relate it to autovacuum?
>>
>> Maybe. Here's what I get when I crank up the logging to debug4:
>>
>> 2007-02-15 14:20:48.771 PST DEBUG: StartTransaction
>> 2007-02-15 14:20:48.771 PST DEBUG: name: unnamed; blockState:
>> DEFAULT; state: INPROGR, xid/subid/cid: 3429052708/1/0, nestlvl: 1,
>> children: <>
>> 2007-02-15 14:20:48.771 PST DEBUG: vacuuming
>> "pg_catalog.pg_statistic"
>> 2007-02-15 14:20:48.771 PST ERROR: could not access status of
>> transaction 2565134864
>> 2007-02-15 14:20:48.772 PST DETAIL: could not open file "pg_clog/
>> 098E": No such file or directory
>> 2007-02-15 14:20:48.772 PST DEBUG: proc_exit(0)
>> 2007-02-15 14:20:48.772 PST DEBUG: shmem_exit(0)
>> 2007-02-15 14:20:48.773 PST DEBUG: exit(0)
>> 2007-02-15 14:20:48.775 PST DEBUG: reaping dead processes
>>
>> does that imply that it is the pg_statistic table that is hosed?
>>
>> 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: <>

fwiw, I did a cluster-wide vacuum on 1/20/2007. Not sure if that has
any impact on anything, just thought I'd throw it out there.

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.
Anyhow it does seem curious to me.

-Casey