On Mon, Mar 16, 2026 at 9:17 PM Amit Kapila <[email protected]> wrote: > > On Tue, Mar 17, 2026 at 2:34 AM Masahiko Sawada <[email protected]> wrote: > > > > I've reviewed this patch and have one question. > > > > The commit timestamp has not only the timestamp data but also > > replication origin id. The replication origin id assigned to the > > subscription on the new cluster is not necessarily the same as the > > ones assigned on the old cluster. Therefore, if there is mismatch, > > replication conflict 'update_origin_differs' could wrongly happen, > > even though the new subscriber starts logical replication from the > > same publisher. I've confirmed this behavior in the following > > scenario: > > > > 1. Setup publication. > > -- on publisher > > CREATE TABLE test (id int primary key, t text); > > CREATE PUBLICATION pub FOR TABLE test; > > > > 2. Setup subscription. > > -- on subscriber > > CREATE TABLE test (id int primary key, t text); > > CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION pub; > > CREATE SUBSCRIPTION a_dummy CONNECTION '...' PUBLICATION pub WITH > > (connect = false, enabled = false); > > > > -- confirm that mysub's origin id is 1 and a_dummy's origin id is 2. > > SELECT oid, subname FROM pg_subscription; > > oid | subname > > -------+--------- > > 16392 | mysub > > 16393 | a_dummy > > (2 rows) > > > > SELECT * FROM pg_replication_origin; > > roident | roname > > ---------+---------- > > 1 | pg_16392 > > 2 | pg_16393 > > (2 rows) > > > > 3. Insert a tuple to the test table and wait for it to be replicated. > > -- on publisher > > INSERT INTO test VALUES (1, 'replicated change'); > > > > The change is applied on the subscription with replication origin id 1: > > -- on subscriber > > SELECT * FROM test, pg_xact_commit_timestamp_origin(test.xmin); > > id | c | timestamp | roident > > ----+-------------------+-------------------------------+--------- > > 1 | replicated change | 2026-03-16 13:49:35.211316-07 | 1 > > (1 row) > > > > 4. Do pg_upgrade > > 5. Check the replication origins in the new subscriber > > > > SELECT oid, subname FROM pg_subscription; > > oid | subname > > -------+--------- > > 16402 | a_dummy > > 16403 | mysub > > (2 rows) > > > > SELECT * FROM pg_replication_origin; > > roident | roname > > ---------+---------- > > 1 | pg_16402 > > 2 | pg_16403 > > (2 rows) > > > > The replication origin 1 is now used by a_dummy subscription. > > > > 6. Update the tuple on the publisher > > -- on publisher > > UPDATE test SET c = 'change after upgrade' WHERE id = 1; > > > > Then I got the following LOG: > > > > LOG: conflict detected on relation "public.test": > > conflict=update_origin_differs > > 2026-03-16 13:53:41.992 PDT [1247879] DETAIL: Updating the row that > > was modified by a different origin "pg_16402" in transaction 760 at > > 2026-03-16 13:53:33.448002-07: local row (1, replicated change), > > remote row (1, change after upgrade), replica identity (id)=(1). > > 2026-03-16 13:53:41.992 PDT [1247879] CONTEXT: processing remote data > > for replication origin "pg_16403" during message type "UPDATE" for > > replication target relation "public.test" in transaction 664, finished > > at 0/017ED910 > > > > I think that this patch works fine only when users don't use the > > replication origins. > > > > But is there any value of this patch without replication origins? I > thought the value of this patch is to detect conflicts correctly after > upgrade. IIUC, in some cases, the commit time on subscriber could > still be later than the conflicting transaction from publisher in > which case timing data can also be used for last_write_win kind of > strategy but without origin that also won't be possible.
I don't have the specific example, but thought there might be use cases without logical replication, for example, pulling all the new records inserted within the last 30min to another system etc. Regards, -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
