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
listWHERE 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