MIME-Version: 1.0
References: <19360-1952ab7afd799f70@postgresql.org>
In-Reply-To: <19360-1952ab7afd799f70@postgresql.org>
From: vignesh C <vignesh21@gmail.com>
Date: Mon, 29 Dec 2025 16:26:01 +0530
Message-ID: <CALDaNm3Y6Y4Mub6QC8fZKnNy5jZspELQYCoQF_FL2Zwzweu=og@mail.gmail.com>
Subject: Re: BUG #19360: Bug Report: Logical Replication initial sync fails
 with "conflict=update_origin_differs" PG12 toPG18
To: mostafaa.hasanzadeh@gmail.com, pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CALDaNm3Y6Y4Mub6QC8fZKnNy5jZspELQYCoQF_FL2Zwzweu%3Dog%40mail.gmail.com>
Precedence: bulk

On Mon, 22 Dec 2025 at 19:00, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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.

This can occur in the following scenario: commit timestamp tracking is
enabled on the subscriber; the same table exists on both publisher and
subscriber; a publication is created on the publisher with initial
data; and a subscription is created on the subscriber with origin =
none. During the initial table synchronization, the row is inserted
using a tablesync replication origin, which is dropped once
synchronization completes. If the row is updated on the publisher
after the initial sync, the apply worker attempts to update a row that
was inserted using a different replication origin(tablesync origin),
resulting in an origin mismatch.

The conflict is logged and logical replication continues normally. No
crash occurs, and the log entry is informational rather than
indicative of a failure. These messages can be safely ignored for now.

We are currently evaluating possible improvements to handle this
scenario more gracefully and to avoid reporting these conflicts in the
future.

Regards,
Vignesh