Skip site navigation (1) Skip section navigation (2)

Re: Proposal: Commit timestamp

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal: Commit timestamp
Date: 2007-01-26 13:44:58
Message-ID: 45BA05DA.5070607@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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 #

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2007-01-26 13:53:32
Subject: HAVING push-down
Previous:From: Jan WieckDate: 2007-01-26 13:31:00
Subject: Re: Proposal: Snapshot cloning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group