Re: Conflict Detection and Resolution

On Mon, Aug 26, 2024 at 7:28 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Thu, Aug 22, 2024 at 8:15 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Wed, Aug 21, 2024 at 4:08 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)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;
>

Thanks for the suggestion. The idea looks good to me. But we need to
once check the complexity involved in its implementation in gram.y.
Initial analysis says that it will need something like 'action' which
we have for ALTER TABLE command ([1]) to have these multiple
subcommands implemented. For INSERT case, it is a just a subclause but
for create/alter sub we hill have it multiple times under one command.
Let us review.

Also I would like to know opinion of others on this.

[1]: https://www.postgresql.org/docs/current/sql-altertable.html

>
> 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