RE: Patch for migration of the pg_commit_ts directory

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

In response to

Responses

Browse pgsql-hackers by date

  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