Re: autovacuum: could not access status of transaction

Hello!

PostgreSQL 8.1.1 on x86_64-pc-linux-gnu

I've been running a server with autovacuum enabled for quite a while now (months) without problems. But recently the server slowed down and after investigation I found the following repeated error messsage in the log:

LOG: autovacuum: processing database "template0"
ERROR: could not access status of transaction 3541181801
DETAIL: could not open file "pg_clog/0D31": No such file or directory

I assume that the avac-process halts at this point which means no vacuum and/or analyze for the other databases? Which would explain the slowdown.

What is the best way to proceed with this? Stop the postmaster, create a zero-filled pg_clog/0D31 and restart?

Regards
Nichlas

On Tuesday 28 March 2006 14:21, Nichlas Löfdahl wrote:
> Hello!
>
> PostgreSQL 8.1.1 on x86_64-pc-linux-gnu
>
> I've been running a server with autovacuum enabled for quite a while now
> (months) without problems. But recently the server slowed down and after
> investigation I found the following repeated error messsage in the log:
>
> LOG: autovacuum: processing database "template0"
> ERROR: could not access status of transaction 3541181801
> DETAIL: could not open file "pg_clog/0D31": No such file or directory
>
> I assume that the avac-process halts at this point which means no vacuum
> and/or analyze for the other databases? Which would explain the slowdown.
>
> What is the best way to proceed with this? Stop the postmaster, create a
> zero-filled pg_clog/0D31 and restart?
>

Sorry, I am not really of any help with this, however I am finding it odd that
this message is occuring while trying to process template0. Have you fuddled
with your system catalogs to make template0 connectable? Else ISTM autovacuum
should never be trying to process template0. Anyone else want to weigh in
here?

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert Treat wrote:
> On Tuesday 28 March 2006 14:21, Nichlas Löfdahl wrote:

> > I've been running a server with autovacuum enabled for quite a while now
> > (months) without problems. But recently the server slowed down and after
> > investigation I found the following repeated error messsage in the log:
> >
> > LOG: autovacuum: processing database "template0"
> > ERROR: could not access status of transaction 3541181801
> > DETAIL: could not open file "pg_clog/0D31": No such file or directory
>
> Sorry, I am not really of any help with this, however I am finding it odd that
> this message is occuring while trying to process template0. Have you fuddled
> with your system catalogs to make template0 connectable? Else ISTM autovacuum
> should never be trying to process template0. Anyone else want to weigh in
> here?

Hmm ... all databases, including those marked not connectable, are
vacuumed eventually, even if it's only for doing a no-op VACUUM FREEZE.
Maybe the database had the datistemplate bit reset, then it was modified
and not frozen again before setting the datistemplate bit again.

While I don't see why anyone would modify template0, it's certainly
possible.

Another possibility is that there is data corruption, but I would think
that it would present in other ways as well.

> > I assume that the avac-process halts at this point which means no vacuum
> > and/or analyze for the other databases? Which would explain the slowdown.

Yup. Autovacuum would detect that template0 is the database in most
need of an autovacuum processing, then fail and die. Subsequent autovac
iterations would do the same.

> > What is the best way to proceed with this? Stop the postmaster, create a
> > zero-filled pg_clog/0D31 and restart?

Yeah, I'd do that and VACUUM FREEZE template0 right away. What files
are actually in pg_clog anyway?

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