Re: Conflict Detection and Resolution

On Thu, Sep 19, 2024 at 5:43 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> >
>
> I was reviewing the CONFLICT RESOLVER (insert_exists='apply_remote')
> and found that one conflict remains unresolved in the following
> scenario:

Thanks for the review and testing.

> Pub:
> CREATE TABLE circles(c1 CIRCLE, c2 text, EXCLUDE USING gist (c1 WITH &&));
> CREATE PUBLICATION pub1 for table circles;
>
> Sub:
> CREATE TABLE circles(c1 CIRCLE, c2 text, EXCLUDE USING gist (c1 WITH &&))
> insert into circles values('<(0,0), 5>', 'sub');
> CREATE SUBSCRIPTION ... PUBLICATION pub1 CONFLICT RESOLVER
> (insert_exists='apply_remote');
>
> The following conflict is not detected and resolved with remote tuple data:
> Pub:
> INSERT INTO circles VALUES('<(0,0), 5>', 'pub');
>
> 2024-09-19 17:32:36.637 IST [31463] 31463 LOG: conflict detected on
> relation "public.t1": conflict=insert_exists, Resolution=apply_remote.
> 2024-09-19 17:32:36.637 IST [31463] 31463 DETAIL: Key already
> exists in unique index "t1_pkey", modified in transaction 742,
> applying the remote changes.
> Key (c1)=(1); existing local tuple (1, sub); remote tuple (1, pub).
> 2024-09-19 17:32:36.637 IST [31463] 31463 CONTEXT: processing
> remote data for replication origin "pg_16398" during message type
> "INSERT" for replication target relation "public.t1" in transaction
> 744, finished at 0/1528E88
> ........
> 2024-09-19 17:32:44.653 IST [31463] 31463 ERROR: conflicting key
> value violates exclusion constraint "circles_c1_excl"
> 2024-09-19 17:32:44.653 IST [31463] 31463 DETAIL: Key
> (c1)=(<(0,0),5>) conflicts with existing key (c1)=(<(0,0),5>).
> ........

We don't support conflict detection for exclusion constraints yet.
Please see the similar issue raised in the conflict-detection thread
and the responses at [1] and [2]. Also see the docs at [3].

[1]: https://www.postgresql.org/message-id/TYAPR01MB569224262F44875973FAF344F5B22%40TYAPR01MB5692.jpnprd01.prod.outlook.com
[2]: https://www.postgresql.org/message-id/CAA4eK1KwqAUGDV3trUZf4hkrUYO3yzwjmBqYtoyFAPMFXpHy3g%40mail.gmail.com
[3]: https://www.postgresql.org/docs/devel/logical-replication-conflicts.html
<See this in doc: Note that there are other conflict scenarios, such
as exclusion constraint violations. Currently, we do not provide
additional details for them in the log.>

thanks
Shveta