Re: Conflict Detection and Resolution

On Thu, Jun 13, 2024 at 11:18 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> On 6/13/24 7:28 AM, Amit Kapila wrote:
> >>
> >> I feel that we should log all types of conflict in an uniform way. For
> >> example, with detect_conflict being true, the update_differ conflict
> >> is reported as "conflict %s detected on relation "%s"", whereas
> >> concurrent inserts with the same key is reported as "duplicate key
> >> value violates unique constraint "%s"", which could confuse users.
> >> Ideally, I think that we log such conflict detection details (table
> >> name, column name, conflict type, etc) to somewhere (e.g. a table or
> >> server logs) so that the users can resolve them manually.
> >>
> >
> > It is good to think if there is a value in providing in
> > pg_conflicts_history kind of table which will have details of
> > conflicts that occurred and then we can extend it to have resolutions.
> > I feel we can anyway LOG the conflicts by default. Updating a separate
> > table with conflicts should be done by default or with a knob is a
> > point to consider.
>
> +1 for logging conflicts uniformly, but I would +100 to exposing the log
> in a way that's easy for the user to query (whether it's a system view
> or a stat table). Arguably, I'd say that would be the most important
> feature to come out of this effort.
>

We can have both the system view and a stats table. The system view
could have some sort of cumulative stats data like how many times a
particular conflict had occurred and the table would provide detailed
information about the conflict. The one challenge I see in providing a
table is in its cleanup mechanism. We could prove a partitioned table
such that users can truncate/drop the not needed partitions or provide
a non-partitioned table where users can delete the old data in which
case they generate a work for auto vacuum.

> Removing how conflicts are resolved, users want to know exactly what row
> had a conflict, and users from other database systems that have dealt
> with these issues will have tooling to be able to review and analyze if
> a conflicts occur. This data is typically stored in a queryable table,
> with data retained for N days. When you add in automatic conflict
> resolution, users then want to have a record of how the conflict was
> resolved, in case they need to manually update it.
>
> Having this data in a table also gives the user opportunity to
> understand conflict stats (e.g. conflict rates) and potentially identify
> portions of the application and other parts of the system to optimize.
> It also makes it easier to import to downstream systems that may perform
> further analysis on conflict resolution, or alarm if a conflict rate
> exceeds a certain threshold.
>

Agreed those are good use cases to store conflict history.

--
With Regards,
Amit Kapila.