Re: Conflict Detection and Resolution - Mailing list pgsql-hackers

On Thu, Aug 22, 2024 at 8:15 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Wed, Aug 21, 2024 at 4:08 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> >
> > The patches have been rebased on the latest pgHead following the merge
> > of the conflict detection patch [1].
>
> Thanks for working on patches.
>
> Summarizing the issues which need some suggestions/thoughts.
>
> 1)
> For subscription based resolvers, currently the syntax implemented is:
>
> 1a)
> CREATE SUBSCRIPTION <subname>
> CONNECTION <conninfo> PUBLICATION <pubname>
> CONFLICT RESOLVER
>     (conflict_type1 = resolver1, conflict_type2 = resolver2,
> conflict_type3 = resolver3,...);
>
> 1b)
> ALTER SUBSCRIPTION <subname> CONFLICT RESOLVER
>     (conflict_type1 = resolver1, conflict_type2 = resolver2,
> conflict_type3 = resolver3,...);
>
> Earlier the syntax suggested in [1] was:
> CREATE SUBSCRIPTION <subname> CONNECTION <conninfo> PUBLICATION <pubname>
> CONFLICT RESOLVER 'conflict_resolver1' FOR 'conflict_type1',
> CONFLICT RESOLVER 'conflict_resolver2' FOR 'conflict_type2';
>
> I think the currently implemented syntax  is good as it has less
> repetition, unless others think otherwise.
>
> ~~
>
> 2)
> For subscription based resolvers, do we need a RESET command to reset
> resolvers to default? Any one of below or both?
>
> 2a) reset all at once:
>  ALTER SUBSCRIPTION <name> RESET CONFLICT RESOLVERS
>
> 2b) reset one at a time:
>  ALTER SUBSCRIPTION <name> RESET CONFLICT RESOLVER for 'conflict_type';
>
> The issue I see here is, to implement 1a and 1b, we have introduced
> the  'RESOLVER' keyword. If we want to implement 2a, we will have to
> introduce the 'RESOLVERS' keyword as well. But we can come up with
> some alternative syntax if we plan to implement these. Thoughts?
>

Hi Shveta,

I felt it would be better to keep the syntax similar to the existing
INSERT ... ON CONFLICT [1].

I'd suggest a syntax like this:

... ON CONFLICT ['conflict_type'] DO { 'conflict_action' | DEFAULT }

~~~

e.g.

To configure conflict resolvers for the SUBSCRIPTION:

CREATE SUBSCRIPTION subname CONNECTION coninfo PUBLICATION pubname
ON CONFLICT 'conflict_type1' DO 'conflict_action1',
ON CONFLICT 'conflict_type2' DO 'conflict_action2';

Likewise, for ALTER:

ALTER SUBSCRIPTION <subname>
ON CONFLICT 'conflict_type1' DO 'conflict_action1',
ON CONFLICT 'conflict_type2' DO 'conflict_action2';

To RESET all at once:

ALTER SUBSCRIPTION <subname>
ON CONFLICT DO DEFAULT;

And, to RESET one at a time:

ALTER SUBSCRIPTION <subname>
ON CONFLICT 'conflict_type1' DO DEFAULT;

~~~

Although your list format "('conflict_type1' = 'conflict_action1',
'conflict_type2' = 'conflict_action2')"  is clear and without
repetition, I predict this terse style could end up being troublesome
because it does not offer much flexibility for whatever the future
might hold for CDR.

e.g. ability to handle the conflict with a user-defined resolver
e.g. ability to handle the conflict conditionally (e.g. with a WHERE clause...)
e.g. ability to handle all conflicts with a common resolver
etc.

~~~~

Advantages of my suggestion:
- Close to existing SQL syntax
- No loss of clarity by removing the word "RESOLVER"
- No requirement for new keyword/s
- The commands now read more like English
- Offers more flexibility for any unknown future requirements
- The setup (via create subscription) and the alter/reset all look the same.

======
[1] https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

Kind Regards,
Peter Smith.
Fujitsu Australia