From: | Mike Roest <mike(dot)roest(at)replicon(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | pg_visible_in_snapshot clarification |
Date: | 2023-08-28 18:34:43 |
Message-ID: | CAE7ByhjsN4SkdQsk6Z2M+zJQi7NsmS7SjNW9acG1Sk0wco0s+g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Stange | 2023-08-28 20:06:21 | auto vacuum question |
Previous Message | David G. Johnston | 2023-08-28 14:44:09 | Re: ident auth does not works as usual |