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

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 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.

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 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

Regards,
Rahul Yadav