G'day list. I've recently upgraded a number of servers from PostgreSQL 9.2.5 to 9.3.1 and have started getting the following errors every couple of hours along with some failed transactions. I have been unable to track down any sort of rhyme or reason for the errors yet, so I figured I'd check with the mailing list to see if I've potentially hit into a potential bug. The errors look like the following: ERROR could not access status of transaction 4179979 Could not open file "pg_subtrans/003F": No such file or directory. Indeed, pg_subtrans/003F does not exist on the file system. I have found similar errors mentioned on the list before a number of years ago with a thread starting here: http://www.postgresql.org/message-id/20111109150225.076c2f38@xxxxxxx A few details: - the new database cluster was created by dumping global settings using pg_dumpall and then dumping each database individually. Several of the databases within this cluster have PostGIS installed and I wanted to do a full PostGIS upgrade, which necessitated individual dumps. - this is the only database in a cluster of 14 databases that is exhibiting the problem and it is sporadic at best. This database is fairly write-intensive, and has been up since November 6th. In that time, we've had 17 such errors. - the pg_subtrans files mentioned in the log file do not exist on the file system. - I have rebuilt the cluster several times, as well as cleaned out the pg_statistic table and run `VACUUM FULL ANALYZE` on every database in case that was affecting it based on some similar threads back from the PostgreSQL 8.4 days, but there was no affect and the errors still occur. Anyone have any suggestions or ideas? Thankfully these are dev systems so I have some room to experiment and can post some more details as necessary. The following are the log file entries from one of the errors. Some details have been sanitized for privacy reasons, but the overall lines themselves are accurate. Nov 10 10:14:02 dev-server postgres[29835]: [4-1] user=dev,db=dev ERROR: could not access status of transaction 4179979 Nov 10 10:14:02 dev-server postgres[29835]: [4-2] user=dev,db=dev DETAIL: Could not open file "pg_subtrans/003F": No such file or directory. Nov 10 10:14:02 dev-server postgres[29835]: [4-3] user=dev,db=dev CONTEXT: SQL statement "SELECT 1 FROM ONLY "typhon"."collection_batches" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" Nov 10 10:14:02 dev-server postgres[29835]: [4-4] user=dev,db=dev STATEMENT: insert into raw (url, id) values ($1, $2) Nov 10 10:14:02 dev-server postgres[29839]: [4-1] user=dev,db=dev ERROR: could not access status of transaction 4179979 Nov 10 10:14:02 dev-server postgres[29839]: [4-2] user=dev,db=dev DETAIL: Could not open file "pg_subtrans/003F": No such file or directory. Nov 10 10:14:02 dev-server postgres[29839]: [4-3] user=dev,db=dev CONTEXT: SQL statement "SELECT 1 FROM ONLY "typhon"."collection_batches" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" Nov 10 10:14:02 dev-server postgres[29839]: [4-4] user=dev,db=dev STATEMENT: update listings set value_a=$1 where id=$2 Nov 10 10:14:02 dev-server postgres[29827]: [4-1] user=dev,db=dev ERROR: could not access status of transaction 4179979 Nov 10 10:14:02 dev-server postgres[29827]: [4-2] user=dev,db=dev DETAIL: Could not open file "pg_subtrans/003F": No such file or directory. Nov 10 10:14:02 dev-server postgres[29827]: [4-3] user=dev,db=dev CONTEXT: SQL statement "SELECT 1 FROM ONLY "typhon"."collection_batches" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x" I can provide additional details like my postgresql.conf if it would help, although it's fairly standard outside of tuning as provided by pgtune. Cheers and thanks, List. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
