RE: Collect statistics about conflicts in logical replication

On Thursday, August 29, 2024 8:31 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:

Hi,

> I tried an experiment where I deliberately violated a primary key during initial
> table synchronization.
>
> For example:
...
> test_sub=# 2024-08-29 09:53:57.245 AEST [4345] LOG: logical replication
> apply worker for subscription "sub1" has started
> 2024-08-29 09:53:57.258 AEST [4347] LOG: logical replication table
> synchronization worker for subscription "sub1", table "t1" has started
> 2024-08-29 09:53:57.311 AEST [4347] ERROR: duplicate key value violates
> unique constraint "t1_pkey"
> 2024-08-29 09:53:57.311 AEST [4347] DETAIL: Key (a)=(1) already exists.
> 2024-08-29 09:53:57.311 AEST [4347] CONTEXT: COPY t1, line 1
> ~~~
>
> Notice how after a while there were multiple (15) 'sync_error_count' recorded.
>
> According to the docs: 'insert_exists' happens when "Inserting a row that
> violates a NOT DEFERRABLE unique constraint.". So why are there not the
> same number of 'insert_exists_count' recorded in pg_stat_subscription_stats?

Because this error was caused by COPY instead of an INSERT (e.g., CONTEXT: COPY
t1, line 1), so this is as expected. The doc of conflict counts(
insert_exists_count) has already mentioned that it counts the conflict only *during the
application of changes* which is clear to me that it doesn't count the ones in
initial table synchronization. See the existing apply_error_count where we also
has similar wording(e.g. "an error occurred while applying changes").

Best Regards,
Hou zj