Re: [HACKERS] Could not open file pg_multixact/offsets/ ERROR on 9.3.4
On Wed, Jun 4, 2014 at 5:10 PM, Andres Freund and...@2ndquadrant.com wrote: Looks like you're hitting the issue described in http://archives.postgresql.org/message-id/20140530121631.GE25431%40alap3.anarazel.de Aha, so the problem looks like this: - all multixact contents was purged (9.2 to 9.3 incompatibility) - the only offset was the file created by initidb during bootstrap - truncation code in vacuum assumed the page related to multixact it tried to truncate actually exists - the assumption was based on the fact that there exists a page earlier than the one it tried to truncate - due to the wrong assumption, there was an attempt to truncate a non-existing page, resulting in the error. Now that makes sense, thank you very much. Vacuum runs w/o issues once I removed that file. I think it should be safe to recommend removing the file for everyone with the same problem. The 2 cases where file is actually used are: - when the are no more then 2 pages of offsets ( and 0001) since the server creation - when the multixacts wrapped around and is not the first page. I think in both cases there will be no gaps in segments in offsets, so the problem won't be there in the first place. Cheers, -- Alexey Klyukin
Re: [HACKERS] Could not open file pg_multixact/offsets/ ERROR on 9.3.4
Hi, On 2014-06-04 16:59:10 +0200, Alexey Klyukin wrote: I've recently discovered a peculiar problem on one of our big databases (more than 1TB). The database has been upgraded from 9.2 to 9.3.4 (using hardlinks to speedup the process) on April 7th around 22:00 local time. When doing vacuum on any table, the system fails with the following error: ERROR: could not access status of transaction 2035987419 DETAIL: Could not open file pg_multixact/offsets/795A: No such file or directory. The erorr doesn't depend on the table being vacuumed, or even database, i.e: postgres=# create database x; CREATE DATABASE postgres=# \c x You are now connected to database x as user postgres. x=# create table test(); CREATE TABLE x=# vacuum test; ERROR: could not access status of transaction 2035987419 DETAIL: Could not open file pg_multixact/offsets/795A: No such file or directory. The content of pg_multixact/offsets is: pg_multixact$ ls -lR ./members: -rw--- 1 postgres postgres 8192 Apr 16 18:20 ./offsets: -rw--- 1 postgres postgres 8192 Apr 7 22:51 -rw--- 1 postgres postgres 262144 Apr 16 18:20 79A6 the select mutlixact from pg_database gives me: and the 2035987419 = 0x795AB3DB belongs to 795A segment. The file just contains a single page of all zeroes. Neither the 9.3.4 replica of this database, nor the original 9.2 cluster data directory contain this file. Looks like you're hitting the issue described in http://archives.postgresql.org/message-id/20140530121631.GE25431%40alap3.anarazel.de I'm tempted to just remove the file from master and restart the database, since it's effectively impossible to run vacuum now, but I'd like to understand what's happening first. Below is the result of pg_filedump for the master: Yes, that's fine in this specific case. Note that the segment isn't yused by anything between the oldest and newest multixact: Latest checkpoint's NextMultiXactId: 2040987419 Latest checkpoint's NextMultiOffset: 3 Latest checkpoint's oldestXID:1038291920 Latest checkpoint's oldestXID's DB: 16415 Latest checkpoint's oldestActiveXID: 1655189767 Latest checkpoint's oldestMultiXid: 2040987417 Latest checkpoint's oldestMulti's DB: 0 Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Could not open file pg_multixact/offsets/ ERROR on 9.3.4
Andres Freund wrote: On 2014-06-04 16:59:10 +0200, Alexey Klyukin wrote: I've recently discovered a peculiar problem on one of our big databases (more than 1TB). The database has been upgraded from 9.2 to 9.3.4 (using hardlinks to speedup the process) on April 7th around 22:00 local time. When doing vacuum on any table, the system fails with the following error: ERROR: could not access status of transaction 2035987419 DETAIL: Could not open file pg_multixact/offsets/795A: No such file or directory. Looks like you're hitting the issue described in http://archives.postgresql.org/message-id/20140530121631.GE25431%40alap3.anarazel.de Will look into this. Thanks. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
