Обсуждение: could not access status of transaction 0
Hi all! 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 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). The Server itself is a Dual AMD Opteron box running Debian Sarge/AMD64 with 64Bit Kernel and Userspace. PostgreSQL version is 8.0.5. 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. Stefan
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