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-07T06:59:59Z
Lists: pgsql-hackers
Dear Hou,

While playing with the 0003 patch (the patch may not be ready), I found that
when the insert_exists event occurred, both apply_error_count and insert_exists_count
was counted.

```
-- insert a tuple on the subscriber
subscriber =# INSERT INTO tab VALUES (1);

-- insert the same tuple on the publisher, which causes insert_exists conflict
publisher =# INSERT INTO tab VALUES (1);

-- after some time...
subscriber =# SELECT * FROM pg_stat_subscription_stats;
-[ RECORD 1 ]--------+------
subid                | 16389
subname              | sub
apply_error_count    | 16
sync_error_count     | 0
insert_exists_count  | 16
update_differ_count  | 0
update_exists_count  | 0
update_missing_count | 0
delete_differ_count  | 0
delete_missing_count | 0
stats_reset          |
```

Not tested, but I think this could also happen for the update_exists_count case,
or sync_error_count may be counted when the tablesync worker detects the conflict.

IIUC, the reason is that pgstat_report_subscription_error() is called in the
PG_CATCH part in start_apply() even after ReportApplyConflict(ERROR) is called.

What do you think of the current behavior? I wouldn't say I like that the same
phenomenon is counted as several events. E.g., in the case of vacuum, the entry
seemed to be separated based on the process by backends or autovacuum.
I feel the spec is unfamiliar in that only insert_exists and update_exists are
counted duplicated with the apply_error_count.

An easy fix is to introduce a global variable which is turned on when the conflict
is found.

Thought?

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.