Re: BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed

Hi Yash, Rahul,

On Wed, 7 Oct 2026 at 14:45, Rahul <rahul(at)rhyadav(dot)dev> wrote:
>
> Hi Yash,
>
> Thanks for the detailed report and the scripts.
>
> I think this comes from the order of the steps at commit, in
> CommitTransaction() (src/backend/access/transam/xact.c):
>
> 1. RecordTransactionCommit() writes and flushes the commit record,
> and waits for synchronous standbys if there are any.
> 2. After that, ProcArrayEndTransaction() removes the transaction
> from the procarray, which is what makes it visible to new
> snapshots.
>
> A walsender can decode and send the transaction as soon as its commit
> record is flushed, so a fast client can receive it and take a new
> snapshot between steps 1 and 2. That snapshot still treats the
> transaction as in progress. This isn't specific to logical messages:
> any decoded transaction can briefly be invisible on the primary after
> the client has received it.
>
I am also able to reproduce the issue.
I was actually able to reproduce it by adding a breakpoint between
RecordTransactionCommit and ProcArrayEndTransaction, according to the
analysis by Rahul above.
I also came up with a perl script to consistently reproduce the issue
with the help of injection point and attached the same.

> I couldn't hit it on unmodified master here (macOS, about 210,000
> messages each with your settings and with 64 clients). To confirm the
> window, I added a 20 ms sleep just before ProcArrayEndTransaction() in
> a test build and ran your setup with 8 clients for 10 seconds:
>
> plain SELECT 3430 of 3456 reads stale
> SELECT ... FOR SHARE 0 of 3318
> wait until the xid is visible first 0 of 3608
>
> FOR SHARE works because it waits for the writer's row lock, which is
> released after step 2. It doesn't help for rows the transaction
> inserted, as there is nothing to lock yet.
>
Yes, a lock is taken on a transaction in EXCLUSIVE mode that is
performing an UPDATE on the relation.
And SELECT ... FOR SHARE try to take a ShareLock on the same
transaction which is conflicting.

postgres=# select * from pg_locks where transactionid = 667;
locktype | database | relation | page | tuple | virtualxid | t
ransactionid | classid | objid | objsubid | virtualtransaction | p
id | mode | granted | fastpath | waitstart

---------------+----------+----------+------+-------+------------+--
-------------+---------+-------+----------+--------------------+----
-----+---------------+---------+----------+-------------------------
---------
transactionid | | | | | |
667 | | | | 2/4 | 157
2437 | ExclusiveLock | t | f |
transactionid | | | | | |
667 | | | | 3/9 | 157
3112 | ShareLock | f | f | 2026-10-12 16:14:19.2200
45+05:30
(2 rows)
And this ExclusiveLock is released (after step 2) by
ResourceOwnerRelease, hence Stale read is not present in this case.

> A more general workaround is to put the writer's xid in the message
> and have the consumer wait until it is visible before reading:
>
> -- writer
> SELECT pg_logical_emit_message(true, 'outbox',
> ... || ':' || pg_current_xact_id());
>
> -- consumer, in READ COMMITTED, repeated until it returns true
> SELECT pg_visible_in_snapshot('<xid>'::xid8, pg_current_snapshot());
>
I agree this can be used for the workaround.

> I don't see a quick fix. Making decoding wait until the transaction
> is visible would deadlock when the walsender is a synchronous standby,
> because the committing backend waits for it in step 1, before the
> transaction becomes visible. The longer-term direction is making
> visibility follow WAL order, which Jeff Davis's "Commit Sequence
> Numbers and Visibility" thread on -hackers discusses [1]. This report
> is a concrete case for that discussion.
>
> [1] https://postgr.es/m/ea6ecdc74dbce67849526668a461bc4760241439.camel@j-davis.com
>
I have not reached a concrete solution yet, but attached the
deterministic reproducer for better understanding.

Thanks,
Shlok Kyal