Re: could not access status of transaction pg_multixact issue

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(dot)klaver(at)aklaver(dot)com