Postgres Pro
Downloads
Home   >   mailing lists

Thread: ERROR: could not access status of transaction 283479860

ERROR: could not access status of transaction 283479860

From
"Pweaver (Paul Weaver)"
Date:
We started getting the following error on some transactions on our database (when against particular rows run against the table).


PGError: ERROR: could not access status of transaction 283479860 DETAIL: Could not open file "pg_multixact/members/4D6D": No such file or directory.

We don't know what happen or how to recover. Any advise?

We are running 
 PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit


pg_controldata  .

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           6049974226855122843
Database cluster state:               in production
pg_control last modified:             Wed 01 Apr 2015 05:53:33 PM EDT
Latest checkpoint location:           6D77/6D729948
Prior checkpoint location:            6D77/6BBC9038
Latest checkpoint's REDO location:    6D77/6BE0F0E0
Latest checkpoint's REDO WAL file:    0000000100006D770000006B
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/2713180814
Latest checkpoint's NextOID:          1709773115
Latest checkpoint's NextMultiXactId:  318596530
Latest checkpoint's NextMultiOffset:  2059555326
Latest checkpoint's oldestXID:        1746597315
Latest checkpoint's oldestXID's DB:   16415
Latest checkpoint's oldestActiveXID:  2713180814
Latest checkpoint's oldestMultiXid:   54760341
Latest checkpoint's oldestMulti's DB: 16415
Time of latest checkpoint:            Wed 01 Apr 2015 05:50:01 PM EDT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current max_connections setting:      600
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

--
Thank You,

Re: ERROR: could not access status of transaction 283479860

From
Alvaro Herrera
Date:
Pweaver (Paul Weaver) wrote:
> We started getting the following error on some transactions on our database
> (when against particular rows run against the table).
>
>
> PGError: ERROR: could not access status of transaction 283479860 DETAIL:
> Could not open file "pg_multixact/members/4D6D": No such file or directory.
>
> We don't know what happen or how to recover. Any advise?

This looks like you overran pg_multixact/members files by wrapping
around the 32 bit counter (NextMultiOffset in your pg_controldata
output); a cleanup process later removed files that were still needed.
This is a Postgres bug, I fear.  Recovery is a bit complicated.  I see
that your report was made 5 days ago -- have you had any progress on
getting things fixed since then?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: ERROR: could not access status of transaction 283479860

From
"Pweaver (Paul Weaver)"
Date:
We figured that out. We were afraid that there was corrupt data due the counter overflow so we reverted to a backup right before the overflow. We submitted a bug this morning (bug #12990) to the Postgres bugs mailing list which has more information and questions.

On Mon, Apr 6, 2015 at 2:17 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Pweaver (Paul Weaver) wrote:
> We started getting the following error on some transactions on our database
> (when against particular rows run against the table).
>
>
> PGError: ERROR: could not access status of transaction 283479860 DETAIL:
> Could not open file "pg_multixact/members/4D6D": No such file or directory.
>
> We don't know what happen or how to recover. Any advise?

This looks like you overran pg_multixact/members files by wrapping
around the 32 bit counter (NextMultiOffset in your pg_controldata
output); a cleanup process later removed files that were still needed.
This is a Postgres bug, I fear.  Recovery is a bit complicated.  I see
that your report was made 5 days ago -- have you had any progress on
getting things fixed since then?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



--
Thank You,