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

Hi,

On Wed, Sep 30, 2026 at 04:30:43AM +0000, PG Bug reporting form wrote:
> A transaction updates a row, calls pg_logical_emit_message(true, ...) with
> the row's new version, and commits. A client streaming the slot with
> pg_recvlogical receives the message and then runs a SELECT on that row
> from a separate ordinary session (READ COMMITTED, autocommit, same server).
> Occasionally that SELECT returns the row version from before the update.
>
> So a transaction's changes can be invisible to a new snapshot taken after
> the client has received that transaction's decoded output.

Indeed, the commit WAL record can already be decoded while the transaction still
appears as running in the procarray, as described by this comment in
RecordTransactionCommit():

"
* Note that at this stage we have marked clog, but still show as running
* in the procarray and continue to hold locks.
"

The same "race" has already been discussed, like for example in [1].

> We hit this in production, where a service uses transactional logical
> messages as an outbox. The consumer re-reads the row when it gets the
> message and sometimes reads stale data. Our current workaround is to sleep
> 100 ms before the read.

I did not look at what it would take to close this race but want to say that
relying on a hardcoded sleep as workaround looks fragile. Maybe a better
workaround would be to also put the top level XID in the message (can get it with
pg_current_xact_id()) and then have the consumer poll:

SELECT pg_visible_in_snapshot(<the xid>, pg_current_snapshot());

until it returns true (or poll pg_xact_status(<the xid>) while it returns
"in progress").

Each poll and the subsequent row read should be separate READ COMMITTED
statements.

[1]: https://postgr.es/m/CAL9smLBPrRLj7uwUrxtVT7wdkzJd%2Bj%3DCB%2BSRepOpS0hgtP8dog%40mail.gmail.com

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com