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

From Ashutosh Bapat
Subject Re: Conflict Detection and Resolution
Date
Msg-id CAExHW5tXeEYDxg2WjV93ANeZY9ZVGEeUc-w9iL72q_LyY=eb9g@mail.gmail.com
Whole thread Raw
In response to Re: Conflict Detection and Resolution  (Nisha Moond <nisha.moond412@gmail.com>)
Responses Re: Conflict Detection and Resolution
List pgsql-hackers


On Thu, Jun 6, 2024 at 5:16 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
>

Here are more use cases of the "earliest_timestamp_wins" resolution method:
1) Applications where the record of first occurrence of an event is
important. For example, sensor based applications like earthquake
detection systems, capturing the first seismic wave's time is crucial.
2) Scheduling systems, like appointment booking, prioritize the
earliest request when handling concurrent ones.
3) In contexts where maintaining chronological order is important -
  a) Social media platforms display comments ensuring that the
earliest ones are visible first.
  b) Finance transaction processing systems rely on timestamps to
prioritize the processing of transactions, ensuring that the earliest
transaction is handled first

Thanks for sharing examples. However, these scenarios would be handled by the application and not during replication. What we are discussing here is the timestamp when a row was updated/inserted/deleted (or rather when the transaction that updated row committed/became visible) and not a DML on column which is of type timestamp. Some implementations use a hidden timestamp column but that's different from a user column which captures timestamp of (say) an event. The conflict resolution will be based on the timestamp when that column's value was recorded in the database which may be different from the value of the column itself.

If we use the transaction commit timestamp as basis for resolution, a transaction where multiple rows conflict may end up with different rows affected by that transaction being resolved differently. Say three transactions T1, T2 and T3 on separate origins with timestamps t1, t2, and t3 respectively changed rows r1, r2 and r2, r3 and r1, r4 respectively. Changes to r1 and r2 will conflict. Let's say T2 and T3 are applied first and then T1 is applied. If t2 < t1 < t3, r1 will end up with version of T3 and r2 will end up with version of T1 after applying all the three transactions. Would that introduce an inconsistency between r1 and r2?

--
Best Wishes,
Ashutosh Bapat

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Re: Add support to TLS 1.3 cipher suites and curves lists
Next
From: Robert Haas
Date:
Subject: Re: Things I don't like about \du's "Attributes" column