BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed
BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19733
Logged by: Yash Kumar Verma
Email address: yk.verma2000@gmail.com
PostgreSQL version: 18.6
Operating system: Linux 6.10.14 aarch64, Debian 13 (official postg
Description:
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.
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.
Full scripts and results:
https://github.com/YashKumarVerma/postgres-non-linear-reads-report
EXPECTED
Once pg_recvlogical has received the decoded message from transaction X,
a new snapshot taken after that point sees X as committed. The SELECT
should never return a version lower than the one in the message.
ACTUAL
One 30-second run per version, 32 pgbench clients rate-limited to 7000 tps:
16.15 210309 messages 28 stale reads
17.11 210321 messages 14 stale reads
18.6 210108 messages 21 stale reads
19beta4 209800 messages 13 stale reads
Sample output (every stale read is exactly one version behind):
STALE id=9 message_version=497 select_returned=496
STALE id=10 message_version=974 select_returned=973
STALE id=9 message_version=1072 select_returned=1071
A separate Go client (pgx + pglogrepl, pgoutput with messages 'true',
8 writers, 16000 messages per run, 3 runs per version) sees 12-38 stale
reads per run on 16, 17, 18 and 19beta4. The row becomes visible
0.1-5.4 ms after the message arrives. With that client, a single writer
never reproduced it, and reading with SELECT ... FOR SHARE instead of a
plain SELECT never reproduced it.
CONFIGURATION
Official postgres Docker image, default postgresql.conf, plus only
-c wal_level=logical. synchronous_commit = on, synchronous_standby_names
= '' (defaults). No standbys, no other replication clients.
STEPS TO REPRODUCE
docker run -d --name walrace -e POSTGRES_HOST_AUTH_METHOD=trust \
postgres:18 -c wal_level=logical
docker cp sql walrace:/sql # sql/ directory from the repo above
docker exec walrace /sql/repro.sh
--- setup.sql
DROP TABLE IF EXISTS txn;
CREATE TABLE txn (id bigint PRIMARY KEY, version bigint NOT NULL);
INSERT INTO txn SELECT g, 0 FROM generate_series(1, 64) g;
SELECT pg_drop_replication_slot('wal_race') FROM pg_replication_slots WHERE
slot_name = 'wal_race';
SELECT pg_create_logical_replication_slot('wal_race', 'test_decoding');
--- writer.sql (pgbench script; each client owns row client_id + 1)
BEGIN;
UPDATE txn SET version = version + 1 WHERE id = :client_id + 1 RETURNING
version \gset
SELECT pg_logical_emit_message(true, 'wal_race', (:client_id + 1) || ':' ||
:version);
COMMIT;
--- consumer.sh (each streamed message becomes a SELECT in one long-lived
--- psql session; it prints a row only when the version read is older)
pg_recvlogical -U postgres -d postgres -S wal_race --start -f - -F 0 \
| sed -un 's/^message: transactional: 1 prefix: wal_race, sz: [0-9]*
content:\([0-9]*\):\([0-9]*\)$/SELECT '"'"'STALE id=\1 message_version=\2
select_returned='"'"' || version FROM txn WHERE id = \1 AND version < \2;/p'
\
| psql -U postgres -d postgres -At
--- repro.sh
psql -U postgres -d postgres -q -f setup.sql >/dev/null
timeout 36 ./consumer.sh > stale.log 2>&1 &
sleep 1
pgbench -U postgres -d postgres -n -c 32 -j 8 -R 7000 -T 30 -f writer.sql |
grep "actually processed"
wait || true
cat stale.log
echo "stale_reads=$(grep -c STALE stale.log || true)"
pgbench is rate-limited because the single psql session in consumer.sh
cannot keep up with unthrottled pgbench. If psql falls behind, it reads
each row long after the message arrives, and nothing reproduces.
PLATFORM
Host: Apple M4 Pro, 24 GB RAM, macOS 27.0
Docker Desktop 28.3.2; VM: Linux 6.10.14-linuxkit aarch64, 12 CPUs, 8 GB RAM
glibc 2.41 (Debian 13)
Images: postgres:16, :17, :18, :19beta4, pulled 2026-09-29
Re: BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed
От:
Rahul <rahul@rhyadav.dev>
Дата:
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(''::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
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 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(''::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
Re: BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed
От:
Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Дата:
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(, pg_current_snapshot());
until it returns true (or poll pg_xact_status() 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