On Thu, Jun 13, 2024 at 11:18 PM Jonathan S. Katz <jk...@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.