Hey There,
I'm looking for some clarification around pg_visible_in_snapshot
function. As it seems to not be working the way I would expect or the way
the documentation is setup.
I've attempted this on pg13 and pg15 and am getting the same behaviour.
3 connections via psql 2 to the primary and 1 to the secondary
C1: primary
C2: primary
C3: secondary (setup using streaming replication with hot_standby_feedback
on)
Reproduction:
C1:
CREATE TABLE test (
id integer,
val text);
BEGIN TRANSACTION;
INSERT INTO test values (1, 'test 1');
SELECT pg_current_xact_id();
C3:
SELECT pg_visible_in_snapshot('<xact id from select
above>'::xid8,pg_current_snapshot()); -- returns false
SELECT pg_current_snapshot(); -- shows <xid from C1>:<xid from C1>:
C2
BEGIN TRANSACTION;
INSERT INTO test values (2, 'test 2');
SELECT pg_current_xact_id();
C3
SELECT pg_visible_in_snapshot('<xact id from C1 select
above>'::xid8,pg_current_snapshot()); -- returns false
SELECT pg_visible_in_snapshot('<xact id from C2 select
above>'::xid8,pg_current_snapshot()); -- returns false
SELECT pg_current_snapshot(); -- shows <xid from C1>:<xid from C1>:
C2:
COMMIT;
C3
SELECT pg_visible_in_snapshot('<xact id from C1 select
above>'::xid8,pg_current_snapshot()); -- returns true
SELECT pg_visible_in_snapshot('<xact id from C2 select
above>'::xid8,pg_current_snapshot()); -- returns true
SELECT pg_current_snapshot(); -- shows <xid from C1>:<xid from C2+1>:
This is where things don't make sense to me. I would expect
SELECT pg_visible_in_snapshot('<xact id from C1 select
above>'::xid8,pg_current_snapshot()); -- returns true
to return false as the transaction on C1 is still open and not commited.
The 1,test 1 record in the test table is not available on the secondary yet
however
pg_visible_in_snapshot is returning true for it's xactid.
I think this has to do with the pg_current_snapshot not showing the
transaction in the xip_list which appears to be empty on both C1 (in the
transaction) and C3 on the replica. However C2 pg_current_snapshot() does
show C1 xactid as in progress in the xip_list.
So I'm guessing from this that pg_visible_in_snapshot is not safe to use
between a primary and a secondary?
If anyone could provide any additional insight that would be amazing.
Thanks
