| From: | "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com> |
|---|---|
| To: | 'Amit Kapila' <amit(dot)kapila16(at)gmail(dot)com> |
| Cc: | ls7777 <ls7777(at)yandex(dot)ru>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, "orlovmg(at)gmail(dot)com" <orlovmg(at)gmail(dot)com> |
| Subject: | RE: Patch for migration of the pg_commit_ts directory |
| Date: | 2026-02-23 04:41:30 |
| Message-ID: | OS9PR01MB12149F6E69BDF8ED6EF6F467AF577A@OS9PR01MB12149.jpnprd01.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Dear Amit,
> > Right, and code comments [a] should be also updated.
> >
>
> So, leaving aside update_delete, copying commit_ts could be helpful to
> detect some other conflicts. You may want to once test the same and
> show it here as part of use case establishment.
I confirmed that {update|delete}_origin_differs could be detected with the
following steps.
0.
Constructed pub-sub replication system. track_commit_timestamp=on was set on the
subscriber, and the table below was defined on both clusters
```
Table "public.employee"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
salary | integer | | |
Indexes:
"employee_pkey" PRIMARY KEY, btree (id)
```
1.
Inserted a tuple on the publisher
```
pub=# INSERT INTO employee VALUES (1, 100);
INSERT 0 1
```
2.
UPDATEd the replicated tuple on the subscriber. Confirmed that commit timestamps
were stored.
```
sub=# SELECT * FROM pg_last_committed_xact();
xid | timestamp | roident
-----+-------------------------------+---------
738 | 2026-02-23 13:17:19.263146+09 | 1
(1 row)
sub=# UPDATE employee SET salary = 10 WHERE id = 1;
UPDATE 1
sub=# SELECT * FROM pg_last_committed_xact();
xid | timestamp | roident
-----+-------------------------------+---------
739 | 2026-02-23 13:17:33.230773+09 | 0
(1 row)
```
3.
Ran pg_upgrade to upgrade the subscriber. The new cluster must also set
track_commit_timestamp to on.
4.
Confirmed commit timestamps could be migrated.
```
new=# SELECT * FROM pg_xact_commit_timestamp_origin('739');
timestamp | roident
-------------------------------+---------
2026-02-23 13:17:33.230773+09 | 0
(1 row)
```
5.
UPDATEd the tuple on the publisher.
```
pub=# UPDATE employee SET salary = 200 WHERE id = 1;
UPDATE 1
```
6.
update_origin_differs conflict was detected on the new subscriber.
```
LOG: conflict detected on relation "public.employee": conflict=update_origin_differs
DETAIL: Updating the row that was modified locally in transaction 739 at 2026-02-23 13:17:33.230773+09: local row (1, 10), remote row (1, 200), replica identity (id)=(1).
CONTEXT: processing remote data for replication origin "pg_16402" during message type "UPDATE" for replication target relation "public.employee" in transaction 745, finished at 0/018A4C6
```
One debatable point is whether we should include this in the TAP test. Personally
It's not necessary to simplify the test; either one is OK.
Not sure it is OK, but I created updated patches and considered a commit message.
0001 was not changed from the original, and 0002 addressed comments from you and
contained the commit message. For now I added my name as one of the author, but
OK to be listed as reviewer. Most of parts were written by Sergey.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
| Attachment | Content-Type | Size |
|---|---|---|
| v9-0001-Migration-of-the-pg_commit_ts-directory.patch | application/octet-stream | 8.1 KB |
| v9-0002-pg_upgrade-transfer-commit-timestamps-to-the-new-.patch | application/octet-stream | 4.6 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dilip Kumar | 2026-02-23 05:14:37 | Re: [PATCH] Support automatic sequence replication |
| Previous Message | Andreas Karlsson | 2026-02-23 02:28:57 | Re: refactor ExecInitPartitionInfo |