Re: Proposal: Commit timestamp

On 1/26/2007 2:37 AM, Naz Gassiep wrote:
> I would be *very* concerned that system time is not a guaranteed
> monotonic entity. Surely a counter or other internally managed mechanism
> would be a better solution.

Such a counter has only "local" relevance. How do you plan to compare
the two separate counters on different machines to tell which
transaction happened last?

Even if the system clock isn't monotonically increasing, the described
increment system guarantees the timestamp used to appear so. Granted,
this system will not work too well on a platform that doesn't allow to
slew the system clock.

>
> Furthermore, what would be the ramifications of master and slave system
> times being out of sync?

The origin of a transaction must scan all tuples it updates and make
sure that the timestamp it uses for commit appears in the future with
respect to them.

>
> Finally what if system time is rolled forward a few minutes as part of a
> correction and there were transactions completed in that time? There is
> a change, albeit small, that two transactions will have the same
> timestamp. More importantly, this will throw all kinds of issues in when
> the slave sees transactions in the future. Even with regular NTP syncs,
> drift can cause a clock to be rolled forward a few milliseconds,
> possibly resulting in duplicate transaction IDs.
>
> In summary, I don't think the use of system time has any place in
> PostgreSQL's internal consistency mechanisms, it is too unreliable an
> environment property. Why can't a counter be used for this instead?

This is nothing used for PostgreSQL's consistency. It is a vehicle
intended to be used to synchronize the "last update wins" decision
process of an asynchronous multimaster system. If not with a timestamp,
how would you make sure that the replication processes of two different
nodes will come to the same conclusion as to which update was last?
Especially considering that the replication might take place hours after
the original transaction happened.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #