could not access status of transaction 0

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:

ERROR: could not access status of transaction 0
DETAIL: could not create file
/var/databases/postgres/data/pg_subtrans/57DA<<: File exists

and seems to be generated in backend/access/transam/slru.c
it looks like we got those(with changing filenames) about 5 times during
the last 2 months mostly during low-utilisation times (on this
particular database - not on the server itself).

Hm ... can you check exactly what set of filenames exists in
pg_subtrans/ when this happens? Is it always referencing pg_subtrans/,
or are there similar complains about pg_clog/ ?

regards, tom lane

Tom Lane wrote:

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:

ERROR: could not access status of transaction 0
DETAIL: could not create file
/var/databases/postgres/data/pg_subtrans/57DA<<: File exists

and seems to be generated in backend/access/transam/slru.c
it looks like we got those(with changing filenames) about 5 times during
the last 2 months mostly during low-utilisation times (on this
particular database - not on the server itself).

Hm ... can you check exactly what set of filenames exists in
pg_subtrans/ when this happens? Is it always referencing pg_subtrans/,
or are there similar complains about pg_clog/ ?

it always complains about pg_subtrans/ , as for finding out what files
are in that directory when that happens - I will try to put something in
place that monitors the direcory.
However it might take a while until we get a result out of this since I
cannot reproduce this issue at will and it only happens rarely :-(

Stefan

Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:

We seem to be getting this error (in german) once in a while on a rather
complex database:

FEHLER: konnte auf den Status von Transaktion 0 nicht zugreifen
DETAIL: kann Datei >>/var/databases/postgres/data/pg_subtrans/57DA<<
nicht erstellen: Die Datei existiert bereits

which roughly translates to

ERROR: could not access status of transaction 0
DETAIL: could not create file
/var/databases/postgres/data/pg_subtrans/57DA<<: File exists

I think we've finally identified the reason for this:
http://archives.postgresql.org/pgsql-committers/2006-01/msg00287.php

It might be interesting to note that we use slony to replicate a few
tables of this database to multiple slaves and according to our logs it
always was the slony-user connected to the database that triggered this
error.

AFAICT Slony is blameless; the triggering condition is a very high
transaction rate (2000 or more transactions between checkpoints)
plus the bad luck to hit a fairly narrow race-condition window.

regards, tom lane