Re: Proposal: Commit timestamp

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.

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

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?

- Naz.

Jan Wieck wrote:
> For a future multimaster replication system, I will need a couple of 
> features in the PostgreSQL server itself. I will submit separate 
> proposals per feature so that discussions can be kept focused on one 
> feature per thread.
>
> For conflict resolution purposes in an asynchronous multimaster 
> system, the "last update" definition often comes into play. For this 
> to work, the system must provide a monotonically increasing timestamp 
> taken at the commit of a transaction. During replication, the 
> replication process must be able to provide the remote nodes timestamp 
> so that the replicated data will be "as of the time it was written on 
> the remote node", and not the current local time of the replica, which 
> is by definition of "asynchronous" later.
>
> To provide this data, I would like to add another "log" directory, 
> pg_tslog. The files in this directory will be similar to the clog, but 
> contain arrays of timestamptz values. On commit, the current system 
> time will be taken. As long as this time is lower or equal to the last 
> taken time in this PostgreSQL instance, the value will be increased by 
> one microsecond. The resulting time will be added to the commit WAL 
> record and written into the pg_tslog file.
>
> If a per database configurable tslog_priority is given, the timestamp 
> will be truncated to milliseconds and the increment logic is done on 
> milliseconds. The priority is added to the timestamp. This guarantees 
> that no two timestamps for commits will ever be exactly identical, 
> even across different servers.
>
> The COMMIT syntax will get extended to
>
>     COMMIT [TRANSACTION] [WITH TIMESTAMP <timestamptz>];
>
> The extension is limited to superusers and will override the normally 
> generated commit timestamp. This will be used to give the replicating 
> transaction on the replica the exact same timestamp it got on the 
> originating master node.
>
> The pg_tslog segments will be purged like the clog segments, after all 
> transactions belonging to them have been stamped frozen. A frozen xid 
> by definition has a timestamp of epoch. To ensure a system using this 
> timestamp feature has enough time to perform its work, a new GUC 
> variable defining an interval will prevent vacuum from freezing xid's 
> that are younger than that.
>
> A function get_commit_timestamp(xid) returning timpstamptz will return 
> the commit time of a transaction as recorded by this feature.
>
>
> Comments, changes, additions?
>
> Jan
>