Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Started by Mihail Nikalayeuabout 1 year ago10 messageshackers
Jump to latest
#1Mihail Nikalayeu
mihailnikalayeu@gmail.com

Hello, everyone!

Initially this was discussed in ("[BUG?]
check_exclusion_or_unique_constraint false negative")[0]/messages/by-id/CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com, but as Amit
recommended [1]/messages/by-id/CAA4eK1LZxzORgAoDhix9MWrOqYOsNZuZLW2sTfGsJFM99yRgrg@mail.gmail.com I decided to write a dedicated email about it, since
the original thread is too deep and has some unrelated discussion.

Commitfest entry [2]https://commitfest.postgresql.org/patch/5151/ contains reproducers for most of the issues
described below - both injection_point-based and pgbench-based (to
ensure such things may happen in real situations).

------

Source of the problem: any scan using SnapshotDirty/SnapshotSelf may
miss some logical tuple (as if it does not exist) in case of a
parallel update.

It happens like this:
* A SnapshotDirty heap scan starts
* Some page X is already processed by the scan, but page Y is not yet
* A logical row is updated by a concurrent transaction: the new
version is placed into page X (already visited), the old version
located in page Y (not-yet-visited) is marked as deleted
* Our scan finally visits page Y and skips the tuple because of the
xmax of the committed transaction
* Both the first and the second version of the tuple are skipped

So, in the MVCC view, the row exists all the time (just in different
versions), but from the perspective of SnapshotDirty it does not exist
at all at that moment.

The same may happen in a BTREE scan, even in the case of a single
index page. This is because the btree scan copies all TIDs from an
index page before starting to process them.
If, after taking such a copy, a new tuple version TID is inserted into
that page, the scan will not see it. But the "cached" TID may be
marked as deleted by a committed transaction, resulting in zero tuples
being detected.

------

Minor consequence: check_exclusion_or_unique_constraint efficiency

It may not find a record in a UNIQUE index during INSERT ON CONFLICT
UPDATE. However, this is just a minor performance issue - retry logic
takes care of it anyway.

------

Major consequence: weird behavior of logical replication in case of
parallel updates on the subscriber.

Caused by the fact that RelationFindReplTupleByIndex (or less likely
RelationFindReplTupleSeq) may miss a target row to perform an action
on, resulting in a skipped action.

---case 1: LOST DELETE---

Scenario: race of DELETE on publisher with UPDATE on subscriber
Setup:
On publisher:
CREATE TABLE t(a int PRIMARY key, data text);
INSERT INTO t(a, data) VALUES (1,'value_from_publisher');
On subscriber:
CREATE TABLE t(a int PRIMARY key, data text);
CREATE INDEX data_index ON t(data); -- index is required
to prevent HOT
Commands:
On publisher:
DELETE FROM t WHERE a=1;
On subscriber:
UPDATE t SET data = 'value_from_subscriber' WHERE (a=1);
Results:
Expected:
Tuple is deleted on both subscriber and publisher.
Both DELETE-before-UPDATE and UPDATE-before-DELETE result in
the absence of the row in the end.
Actual:
Either as expected, or:
Tuple is deleted on publisher, but 'value_from_subscriber'
remains on subscriber.

---case 2: LOST UPDATE---

Scenario: UPDATE on publisher vs subscriber-columns only UPDATE on subscriber
Setup:
On publisher:
CREATE TABLE t(a int PRIMARY key, data text);
INSERT INTO t(a, data) VALUES (1,'initial_data');
On subscriber:
-- note additional subscriber-only column
CREATE TABLE t(a int PRIMARY key, data text, sub_only
varchar(40) DEFAULT 'default_data');
CREATE INDEX sub_only_index ON t(sub_only);
Commands:
On publisher:
UPDATE t SET data = 'update_from_publisher' WHERE (a=1);
On subscriber:
UPDATE t SET sub_only = 'update_from_subscriber' WHERE (a=1);
Results:
Expected:
On subscriber: tuple (a=1, data='update_from_publisher',
sub_only='update_from_subscriber').
Update on subscriber only affects the subscriber-only column,
so I expect publisher-driven columns are replicated.
Actual:
Either as expected, or:
Publisher update is lost, leaving (a=1, data='initial_data',
sub_only='update_from_subscriber') on subscriber.

------

Normal consequence:

For the same reasons, logical replication may provide invalid log
messages (and stats) about conflicts:

* correct: update_origin_differs, in logs: update_missing
* correct: update_origin_differs, in logs: update_deleted (providing
invalid deleted tuple data)
* correct: delete_origin_differs, in logs: delete_missing

This is not a critical issue, but invalid logs are something no one
wants to deal with, especially logs made to debug conflicts.
Also, in case of any conflict-resolution mechanics in the future, it
may be a critical flaw.

------

Proposed fix:

The patch in [2]https://commitfest.postgresql.org/patch/5151/ addresses the issue by rewriting
RelationFindReplTupleByIndex/RelationFindReplTupleSeq to use
GetLatestSnapshot for each attempt to find the target row.
Since it calls GetLatestSnapshot before table_tuple_lock anyway, no
performance regression is expected.

As a result:
* MVCC scan will not miss updated tuples, while DirtyScan may
* MVCC scan cannot see not-yet-committed new rows, while DirtyScan
can, however in both variants, table_tuple_lock will wait for the
updating transaction to commit before retrying the whole scan

In any case I think we should at least document such behavior.

Best regards,
Mikhail.

[0]: /messages/by-id/CANtu0oiktqQ2pwExoXqDpByXNCJa-KE5vQRodTRnmFHN_+qwHg@mail.gmail.com
[1]: /messages/by-id/CAA4eK1LZxzORgAoDhix9MWrOqYOsNZuZLW2sTfGsJFM99yRgrg@mail.gmail.com
[2]: https://commitfest.postgresql.org/patch/5151/

#2Mihail Nikalayeu
mihailnikalayeu@gmail.com
In reply to: Mihail Nikalayeu (#1)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hello, everyone!

Andres, added you in CC because I am referring to your words in
personal conversation.

Amit, I asked Andres about that situation and Andres said it is a bug
if we execute DirtySnapshot without holding a real one.
And this is how it is done in RelationFindReplTupleByIndex and
RelationFindReplTupleSeq now.

Best regards,
Mikhail.

#3Mihail Nikalayeu
mihailnikalayeu@gmail.com
In reply to: Mihail Nikalayeu (#2)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hello!

Issue is known for almost a year, so I propose to document it at least.

This is a draft of a documentation update.

Best regards,
Mikhail.

Attachments:

t52268_3
v1-0002-update-for-pg19.patchtext/plain; charset=US-ASCII; name=v1-0002-update-for-pg19.patchDownload+4-4
v1-0001-documentation-update-to-reflect-issue-described-i.patchtext/plain; charset=US-ASCII; name=v1-0001-documentation-update-to-reflect-issue-described-i.patchDownload+50-1
#4Mihail Nikalayeu
mihailnikalayeu@gmail.com
In reply to: Mihail Nikalayeu (#3)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hello, everyone!

Manu reviewed the fix in the original thread [1]/messages/by-id/179010806896.909920.249094834696255448@gmail.com Best regards, Mikhail. and pointed out a
behavior change. I'm replying here since this thread is about the
logical replication side.

The case is this: a local transaction inserts a row and remains open,
while an UPDATE for the same key arrives from the publisher. On
master, the dirty scan may see the uncommitted row, wait for the
inserter, and then apply the UPDATE after it commits. With the patch,
the row isn't visible, so we report update_missing immediately.

That behavior on master is racy, though. It only waits if the insert
has already made it into the index by the time the scan reaches it. If
the INSERT happens a little later, master reports update_missing as
well. In either case there is no committed row at the time the
replicated change is applied, which is different from the bug this
patch fixes, where the row exists throughout. I've updated the commit
message to make that distinction clear.

Manu also suggested doing another dirty scan when nothing is found, to
preserve the wait in more cases. That works, but it only makes the
race window smaller and reintroduces a dirty scan, so I left it out of
a fix intended to be back-patchable.

[1]: /messages/by-id/179010806896.909920.249094834696255448@gmail.com Best regards, Mikhail.
Best regards,
Mikhail.

Attachments:

t52268_4
v20-0001-Find-tuples-to-update-or-delete-during-apply-usi.patchapplication/x-patch; name=v20-0001-Find-tuples-to-update-or-delete-during-apply-usi.patchDownload+343-56
#5Manu
manuelreyesbravo@gmail.com
In reply to: Mihail Nikalayeu (#4)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hi Mikhail,

The patch [...] rewrites RelationFindReplTupleByIndex/
RelationFindReplTupleSeq to use GetLatestSnapshot for each attempt to
find the target row. Since it calls GetLatestSnapshot before
table_tuple_lock anyway, no performance regression is expected.

I reviewed v20 and reproduced the bug end to end on current master
(85adc584e2b), built with --enable-injection-points.

With v20 applied, 039_apply_search_concurrent_update passes. To confirm
the fix is what makes it pass, I reverted only the execReplication.c hunks
(back to the dirty snapshot), keeping the injection points and the test.
039 then fails exactly as the bug predicts (6 of 7):

- the DELETE conflict is logged as 'delete_missing' instead of
'delete_origin_differs', and the row is not deleted on the subscriber:
the publisher's DELETE is lost.
- the UPDATE conflict is logged as 'update_missing' instead of
'update_origin_differs', and the row keeps 'frompub' instead of
'frompubnew': the publisher's UPDATE is lost.
- the same happens under retain_dead_tuples.

So the subscriber's dirty-snapshot index scan misses the concurrently
updated row, the publisher's DELETE/UPDATE is dropped, and the conflict is
logged as *_missing instead of *_origin_differs. Reverting just the
snapshot change brings the bug back, which isolates the snapshot as the
root cause and v20 as the minimal fix.

No regression: the full subscription suite passes with v20, 41 files and
621 tests.

The fix itself reads well. GetLatestSnapshot() was already taken before
table_tuple_lock() on the found path, so moving it to the start of each
attempt is not an extra snapshot, and a fresh latest snapshot per retry is
the right thing. Leaving check_exclusion_or_unique_constraint() on the
dirty snapshot (with the new comment) also seems right, since the ON
CONFLICT retry covers that case and there is no data loss there.

Looks good to me. Happy to keep an eye on the next rev.

Regards,
Manu

#6Manu
manuelreyesbravo@gmail.com
In reply to: Mihail Nikalayeu (#4)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hi Mihail,

That behavior on master is racy, though. It only waits if the insert
has already made it into the index by the time the scan reaches it.
If the INSERT happens a little later, master reports update_missing as
well. In either case there is no committed row at the time the
replicated change is applied, which is different from the bug this
patch fixes, where the row exists throughout.

Agreed. The dirty scan only waits when it actually finds the in-progress
tuple, so on master the outcome already depends on whether the INSERT
reached the index before the scan reached it; the patch just makes the
"no committed row" outcome deterministic instead of racy. That is a
different situation from the one the patch fixes, where a committed row
is there throughout, and the updated commit message draws that line
clearly.

Manu also suggested doing another dirty scan when nothing is found, to
preserve the wait in more cases. That works, but it only makes the
race window smaller and reintroduces a dirty scan, so I left it out of
a fix intended to be back-patchable.

Makes sense -- it only narrows the window and brings a dirty scan back,
so leaving it out of a back-patchable fix is the right call.

The v20 code here is the same one I tested earlier, and the reproduction
still shows the fix holding. Looks good to me.

#7Hayato Kuroda (Fujitsu)
kuroda.hayato@fujitsu.com
In reply to: Mihail Nikalayeu (#4)
RE: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hi,

I re-visited this thread, sorry for quite late. I felt comments from senior
members are still needed to understand the initial intention, but let me share
few comments.

01.
```
retry:
found = false;
+ PushActiveSnapshot(GetLatestSnapshot());
```

Can you put a comment why we need to get the GetLatestSnapshot()? Per my
understanding it's for observing tuples which are updated by concurrent transactions.
I initially wondered it may break the transaction isolation level REPEATABLE READ
and the function should return false: but this can lose changes from the publisher.

02.
You told that this patch does not cause the performance isuse because
GetLatestSnapshot() had already been eventually called, but what if the
update_missing happens? Can the performance decrease in the extreme case which
the lots of XXX_missing happens?

Best regards,
Hayato Kuroda
FUJITSU LIMITED

#8Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Hayato Kuroda (Fujitsu) (#7)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Can you put a comment why we need to get the GetLatestSnapshot()? Per my
understanding it's for observing tuples which are updated by concurrent transactions.
I initially wondered it may break the transaction isolation level REPEATABLE READ
and the function should return false: but this can lose changes from the publisher.

+1. With default_transaction_isolation = SERIALIZABLE, the patch also
takes SIREAD locks and can cause the apply worker to fail with
serialization failures because of that. I am not 100% sure this is a
new issue or if it can happen in different ways, but it doesn't seem
intended or useful.

See the attached claude-generated test case.

Attachments:

041_apply_search_serializable.plapplication/octet-stream; name=041_apply_search_serializable.plDownload
#9Mihail Nikalayeu
mihailnikalayeu@gmail.com
In reply to: Zsolt Parragi (#8)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hello!

Hayato, Zsolt, thanks for the review!

Can you put a comment why we need to get the GetLatestSnapshot()? Per my
understanding it's for observing tuples which are updated by concurrent transactions.

Done. BTW, it is also GetLatestSnapshot in the current upstream.

You told that this patch does not cause the performance isuse because
GetLatestSnapshot() had already been eventually called, but what if the
update_missing happens? Can the performance decrease in the extreme case which
the lots of XXX_missing happens?

With the current snapshot-caching machinery, I think the impact is not
noticeable when compared to table fetches, logging, etc., done in that
path.
The whole scenario is non-normal also.

I initially wondered it may break the transaction isolation level REPEATABLE READ
and the function should return false: but this can lose changes from the publisher.
See the attached claude-generated test case.

Nice catch, thanks.

I think we can fix it in a way similar to
d774576f6f05f65e3a944eb509ef0620ea6b107e: just force READ COMMITTED
for the worker.
I don't think any other value makes sense per se.

Zsolt, I have added your test into the patch itself (a bit reworked).

Mikhail.

Attachments:

t52268_9
v21-0001-Find-tuples-to-update-or-delete-during-apply-usi.patchapplication/octet-stream; name=v21-0001-Find-tuples-to-update-or-delete-during-apply-usi.patchDownload+414-56
#10Manu
manuelreyesbravo@gmail.com
In reply to: Mihail Nikalayeu (#9)
Re: Logical replication: lost updates/deletes and invalid log messages caused by SnapshotDirty + concurrent updates

Hi Mikhail, Hayato, Zsolt,

v21 looks good to me. On master 4848fd6752a the subscription suite
passes, 039 still fails without the execReplication.c hunks, and
Zsolt's original 041 (index and sequential search) fails on v20 but
passes on v21 and on unpatched master, so the failure came with v20
and is not pre-existing. Thanks, Zsolt, for catching that.

On Wed, Oct 28, 2026 at 7:10 AM Hayato Kuroda (Fujitsu)
<kuroda.hayato@fujitsu.com> wrote:

Can the performance decrease in the extreme case which
the lots of XXX_missing happens?

Measured with -O2 builds, 10 interleaved pairs, conflicts logged as
by default. 1M one-row transactions that all miss apply in a median
4.28 s on master and 4.34 s with v21; paired ratio 1.006 (95% CI
0.988-1.024). With REPLICA IDENTITY FULL: 1.006 (1.000-1.011).
With a local writer committing ~128k/s on the subscriber: 1.009
(0.979-1.041). The only case outside the noise is 1M misses in a
single transaction: +1.9% (0.7-3.1%). Updates that find their row:
0.998 (0.994-1.002).

One behaviour change worth a line in the commit message if this is
back-patched: an ENABLE REPLICA or ALWAYS trigger on the subscriber
now runs at read committed whatever default_transaction_isolation says
(postgresql.conf, ALTER DATABASE or ALTER ROLE). At repeatable read
it now sees rows committed locally between two changes of the same
remote transaction; on master it did not.

Regards,
Manu