Re: Proposal: Commit timestamp

On 1/26/2007 8:26 AM, Simon Riggs wrote:
> On Thu, 2007-01-25 at 18:16 -0500, Jan Wieck wrote:
>
>> 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.
>
> A transaction time table/log has other uses as well, so its fairly
> interesting to have this.
>
>> COMMIT [TRANSACTION] [WITH TIMESTAMP <timestamptz>];
>>
>> The extension is limited to superusers and will override the normally
>> generated commit timestamp.
>
> I don't think its acceptable to override the normal timestamp. That
> could lead to non monotonic time values which could screw up PITR. My
> view is that you still need PITR even when you are using replication,
> because the former provides recoverability and the latter provides
> availability.

Without that it is rendered useless for conflict resolution purposes.

The timestamp used does not necessarily have much to do with the real
time at commit. Although I'd like it to be as close as possible. This
timestamp marks the age of the new datum in an update. Since the
replication is asynchronous, the update on the remote systems will
happen later, but the timestamp recorded with that datum must be the
timestamp of the original transaction, not the current time when it is
replicated remotely. All we have to determine that is the xmin in the
rows tuple header, so that xmin must resolve to the original
transactions timestamp.

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 #