Thread: 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 0000
> ./offsets:
> -rw------- 1 postgres postgres   8192 Apr  7 22:51 0000
> -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 0000 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 0000 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 0000 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

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 Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services