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
