Re: pg_visible_in_snapshot clarification - Mailing list pgsql-general

On Mon, Aug 28, 2023 at 8:34 PM Mike Roest <mike.roest@replicon.com> wrote:
> I think this has to do with the pg_current_snapshot not showing the transaction in the xip_list which appears to be
emptyon both C1 (in the transaction) and C3 on the replica.  However C2 pg_current_snapshot() does show C1 xactid as in
progressin the xip_list. 

Effectively there is something that I don't understand too.
I've reproduced the experiment, with two connections on the primary
and one on the standby (PostgreSQL 15).

First connection at the primary:

testdb=*> INSERT INTO t( t )
SELECT 'XID = ' || txid_current() || ' PID = ' || pg_backend_pid() ||
' SNAP = ' || pg_current_snapshot();
INSERT 0 1
testdb=*> SELECT * FROM t;
id |                  t
----+--------------------------------------
 8 | XID = 875 PID = 1151 SNAP = 875:875:
(1 row)


Meanwhile, second connection to the primary:

testdb=> BEGIN;
BEGIN
testdb=*> INSERT INTO t( t )
SELECT 'XID = ' || txid_current() || ' PID = ' || pg_backend_pid() ||
' SNAP = ' || pg_current_snapshot();
INSERT 0 1
testdb=*> SELECT * FROM t;
id |                  t
----+--------------------------------------
 9 | XID = 876 PID = 1200 SNAP = 875:875:
(1 row)

Meanwhile, third connection to the standby (physical replication with a slot):

% psql -U luca -h venkman -p 6432 testdb
psql (15.4 (Ubuntu 15.4-0ubuntu0.23.04.1), server 15.3)
Type "help" for help.

testdb=> SELECT pg_visible_in_snapshot( '875'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
------------------------+---------------------
f                      | 875:875:
(1 row)


So far so good, then commit the second connection (on the primary)
with xid 876, and on the standby:

testdb=> SELECT pg_visible_in_snapshot( '875'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
------------------------+---------------------
t                      | 875:877:

Then I rollback the first connection (xid 875) and again, on the standby:

testdb=> SELECT pg_visible_in_snapshot( '875'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
------------------------+---------------------
t                      | 877:877:


The latter result appears normal to me, since 875 is consolidated. But
why is 875 visible when 876 commits and 875 does not?
The same does not happen with only connections to the primary, that is
not involving the replica node. Reproducing the same experiment, the
third connections sees always a false against the first transaction
(not commit) before and after the commit of the second transactions:

testdb=> SELECT pg_visible_in_snapshot( '877'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
------------------------+---------------------
f                      | 877:879:877
(1 row)

-- second transacction 879 commits

testdb=> SELECT pg_visible_in_snapshot( '877'::xid8,
pg_current_snapshot() ), pg_current_snapshot();
pg_visible_in_snapshot | pg_current_snapshot
------------------------+---------------------
f                      | 877:880:877
(1 row)

What I see, however, is that the snapshot is different in the case of
local transacctions. I suspect that somehow the list of active
transactions is not propagated to the replica xip, that is therefore
forced to look into the commit status.
But I would like to get a better explanation.

Luca