RE: Conflict detection and logging in logical replication

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
Cc: shveta malik <shveta.malik@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, Dilip Kumar <dilipbalaut@gmail.com>, Jan Wieck <jan@wi3ck.info>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>
Date: 2024-08-01T08:56:13Z
Lists: pgsql-hackers
Dear Hou,

Let me contribute the great feature. I read only the 0001 patch and here are initial comments.

01. logical-replication.sgml

track_commit_timestamp must be specified only on the subscriber, but it is not clarified.
Can you write down that?

02. logical-replication.sgml

I felt that the ordering of {exists, differ,missing} should be fixed, but not done.
For update "differ" is listerd after the "missing", but for delete, "differ"
locates before the "missing". The inconsistency exists on souce code as well.

03. conflict.h

The copyright seems wrong. 2012 is not needed.

04. general

According to the documentation [1], there is another constraint "exclude", which
can cause another type of conflict. But this pattern cannot be logged in detail.
I tested below workload as an example.

=====
publisher=# create table tab (a int, EXCLUDE (a WITH =));
publisher=# create publication pub for all tables;

subscriber=# create table tab (a int, EXCLUDE (a WITH =));
subscriber=# create subscription sub...;
subscriber=# insert into tab values (1);

publisher=# insert into tab values (1);

-> Got conflict with below log lines:
```
ERROR:  conflicting key value violates exclusion constraint "tab_a_excl"
DETAIL:  Key (a)=(1) conflicts with existing key (a)=(1).
CONTEXT:  processing remote data for replication origin "pg_16389" during message type "INSERT"
for replication target relation "public.tab" in transaction 740, finished at 0/1543940
```
=====

Can we support the type of conflict?

[1]: https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Commits

  1. Rename the conflict types for the origin differ cases.

  2. Doc: explain the log format of logical replication conflicts.

  3. Log the conflicts while applying changes in logical replication.

  4. Avoid duplicate table scans for cross-partition updates during logical replication.