Message-ID: <54358537.4040906@aklaver.com>
Date: Wed, 08 Oct 2014 11:40:55 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.1.2
MIME-Version: 1.0
To: jim_yates <pg@wg5jim.net>, pgsql-sql@postgresql.org
Subject: Re: could not access status of transaction pg_multixact issue
References: <1412791213607-5822248.post@n5.nabble.com>
In-Reply-To: <1412791213607-5822248.post@n5.nabble.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 10/08/2014 11:00 AM, jim_yates wrote:
> I have this issue for 1 table.   Version is 9.3.5  upgraded from 9.2 with
> pg_upgrade a few months ago.
>
> This issues just started in the last couple of days.
>
> acustream=# SELECT count(*) from phyorg_charges_to_invoice;
> ERROR:  could not access status of transaction 267035
> DETAIL:  Could not open file "pg_multixact/members/10AD6": No such file or
> directory.
>
> This error happens when I try and select or vacuum the table.  Inserts still
> work.  I have a hot standby database and I can recover the data from there.
> Is there any work around for this?
>

http://www.postgresql.org/docs/9.3/interactive/release-9-3-5.html

E.1.2. Changes

     In pg_upgrade, remove pg_multixact files left behind by initdb 
(Bruce Momjian)

     If you used a pre-9.3.5 version of pg_upgrade to upgrade a database 
cluster to 9.3, it might have left behind a file 
$PGDATA/pg_multixact/offsets/0000 that should not be there and will 
eventually cause problems in VACUUM. However, in common cases this file 
is actually valid and must not be removed. To determine whether your 
installation has this problem, run this query as superuser, in any 
database of the cluster:

     WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
     SELECT EXISTS (SELECT * FROM list WHERE file = '0000') AND
            NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
            NOT EXISTS (SELECT * FROM list WHERE file = 'FFFF') AND
            EXISTS (SELECT * FROM list WHERE file != '0000')
            AS file_0000_removal_required;

     If this query returns t, manually remove the file 
$PGDATA/pg_multixact/offsets/0000. Do nothing if the query returns f.


-- 
Adrian Klaver
adrian.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql