Обсуждение: BUG #19360: Bug Report: Logical Replication initial sync fails with "conflict=update_origin_differs" PG12 toPG18
BUG #19360: Bug Report: Logical Replication initial sync fails with "conflict=update_origin_differs" PG12 toPG18
От
PG Bug reporting form
Дата:
The following bug has been logged on the website:
Bug reference: 19360
Logged by: Mostafa Hassanzadeh
Email address: mostafaa.hasanzadeh@gmail.com
PostgreSQL version: 18.1
Operating system: Ubuntu 24.04
Description:
Description: I am encountering a persistent issue during the initial
synchronization (Logical Replication) migrating from PostgreSQL 12 (Source)
to PostgreSQL 18 (Target/Devel).
Despite ensuring a clean state (truncated tables, disabled triggers, dropped
indexes), the replication fails immediately after the initial COPY phase
when it tries to apply concurrent updates from WAL. The error indicates an
origin conflict, even though origin is set to none.
It appears that the rows inserted during the initial COPY process in PG18
are not being treated correctly regarding their origin status, causing a
conflict when the Apply Worker tries to update these rows with incoming WAL
entries.
Environment:
Publisher: PostgreSQL 12
Subscriber: PostgreSQL 18 (Development/Beta version)
OS: Linux (Kernel > 5.10)
Setup: High-volume data migration (~100GB tables)
Steps to Reproduce:
Publisher (PG12): Create a publication for tables with moderate write
traffic.
Subscriber (PG18):
DISABLE TRIGGER ALL on target tables.
TRUNCATE target tables.
Create a subscription with:
SQL
CREATE SUBSCRIPTION sub_name
CONNECTION '...'
PUBLICATION pub_name
WITH (copy_data = true, origin = 'none', binary = false);
Observation:
The COPY phase starts and writes data to the disk.
As soon as COPY finishes and the worker switches to streaming to
catch up, it crashes with the following error.
Error Log:
LOG: conflict detected on relation "public.player":
conflict=update_origin_differs
DETAIL: Updating the row that was modified by a non-existent origin in
transaction [TXID] at [TIMESTAMP].
Existing local row (...); remote row (...); replica identity (id)=(...).
CONTEXT: processing remote data for replication origin "pg_..." during
message type "UPDATE" ...
Analysis: I have verified that:
There are no other active subscriptions writing to the target database.
All triggers and foreign keys are disabled on the subscriber.
The issue persists even after multiple cleanups (DROP SUBSCRIPTION /
TRUNCATE).
Suspected Cause: It seems there is an incompatibility or regression in
PostgreSQL 18's logical replication handling. Specifically, tuples inserted
via the initial COPY protocol (from a PG12 source) might be tagged with a
local or null origin in a way that conflicts with the conflict_resolver or
origin checking logic in PG18, even when origin = 'none' is explicitly
configured.
I suspect the COPY process does not correctly set the tuple origin state
that the WAL apply worker expects, leading it to believe the row was
modified locally by a third party.
Hi,
WITH (copy_data = true, origin = 'none', binary = false);
There is the following note in the documentation about the setting of copy_data = true
and origin = 'none'.
```
When using a subscription parameter combination of copy_data = true and origin = NONE,
the initial sync table data is copied directly from the publisher, meaning that knowledge of the
true origin of that data is not possible. If the publisher also has subscriptions then the copied
table data might have originated from further upstream. This scenario is detected and a
WARNING is logged to the user, but the warning is only an indication of a potential problem;
it is the user's responsibility to make the necessary checks to ensure the copied data origins
are really as wanted or not.
```
Kindly check if this is happening in your case.
Observation:
The COPY phase starts and writes data to the disk.
As soon as COPY finishes and the worker switches to streaming to
catch up, it crashes with the following error.
Error Log:
LOG: conflict detected on relation "public.player":
conflict=update_origin_differs
DETAIL: Updating the row that was modified by a non-existent origin in
transaction [TXID] at [TIMESTAMP].
Existing local row (...); remote row (...); replica identity (id)=(...).
CONTEXT: processing remote data for replication origin "pg_..." during
message type "UPDATE" ...
This does not seem like an error and the apply operation can proceed
successfully even after logging this. Can you please check if there is
another message with ERROR/FATAL/PANIC log level in the logs?
Thank you,
Rahila Syed