On Tue, May 23, 2017 at 10:50 PM, Dmitriy Sarafannikov <dsarafanni...@yandex.ru> wrote: > Hi hackers, > > We have some problems on our production with hint bits and frozen tuples. > More and more following errors began to appear on master after switchover: > ERROR: 58P01: could not access status of transaction 1952523525 > DETAIL: Could not open file "pg_clog/0746": No such file or directory. > LOCATION: SlruReportIOError, slru.c:896 > > We investigated the problem with pageinspect and found the tuples that are > the cause: > > xdb311g(master)=# select * from mytable where ctid = '(4,21)'; > ERROR: 58P01: could not access status of transaction 1951521353 > DETAIL: Could not open file "pg_clog/0745": No such file or directory. > LOCATION: SlruReportIOError, slru.c:896 > > But the same query successfully executed on replica. > > We found some difference in hint bits between master and replica: > > xdb311g(master)=# SELECT t_xmin, t_infomask::bit(32) & X'0300'::int::bit(32) > FROM heap_page_items(get_raw_page(‘mytable',4)) where lp=21; > -[ RECORD 1 ]------------------------------ > t_xmin | 1951521353 > ?column? | 00000000000000000000000000000000 > > old master, now replica: > xdb311e(replica)=# SELECT t_xmin, t_infomask::bit(32) & X'0300'::int::bit(32) > FROM heap_page_items(get_raw_page(‘mytable',4)) where lp=21; > -[ RECORD 1 ]------------------------------ > t_xmin | 1951521353 > ?column? | 00000000000000000000001100000000 > > X’0300’ = HEAP_XMIN_FROZEN according to > > #define HEAP_XMIN_COMMITTED 0x0100 /* t_xmin committed */ > #define HEAP_XMIN_INVALID 0x0200 /* t_xmin invalid/aborted */ > #define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID) > > xdb311g(master)=# select relfrozenxid from pg_class where relname = ‘mytable'; > relfrozenxid > -------------- > 2266835605 > (1 row) > > This tuple must be frozen but there are no set bits HEAP_XMIN_COMMITTED and > HEAP_XMIN_INVALID on master > > Another interesting thing that LSN of this page on master and replica are not > the same: > xdb311g(master)=# select lsn from page_header(get_raw_page(‘mytable',4)); > lsn > --------------- > 8092/6A26DD08 > (1 row) > > xdb311e(replica)=# select lsn from page_header(get_raw_page(‘mytable',4)); > lsn > --------------- > 838D/C4A0D280 > (1 row) > > And LSN on replica is greater that LSN on master (838D/C4A0D280 > > 8092/6A26DD08) > How can this be possible? >
Yeah, I think this is quite suspicious. This seems to indicate that not all WAL records are replicated before the switchover. What is the value of "synchronous_commit" you are using? I think you somehow need to ensure before switchover that all the WAL is replicated to ensure this is not a setup problem. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
