Re: Proposal: Commit timestamp

On 1/25/2007 7:41 PM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>> On 1/25/2007 6:47 PM, Neil Conway wrote:
>>> Would this feature have any use beyond the specific project/algorithm
>>> you have in mind?
>
>> The tablelog project on pgfoundry currently uses the transactions start
>> time but would be very delighted to have the commit time available instead.
>
> BTW, it's not clear to me why you need a new log area for this. (We
> don't log transaction start time anywhere, so certainly tablelog's needs
> would not include it.) Commit timestamps are available from WAL commit
> records in a crash-and-restart scenario, so wouldn't that be enough?

First, I need the timestamp of the original transaction that caused the
data to change, which can be a remote or a local transaction. So the
timestamp currently recorded in the WAL commit record is useless and the
commit record has to be extended by one more timestamp.

Second, I don't think that an API scanning for WAL commit records by xid
would be efficient enough to satisfy the needs of a timestamp based
conflict resolution system, which would have to retrieve the timestamp
for every rows xmin that it is about to update in order to determine if
the old or the new values should be used.

Third, keeping the timestamp information in the WAL only would require
to keep the WAL segments around until they are older than the admin
chosen minimum freeze age. I hope you don't want to force that penalty
on everyone who intends to use multimaster replication.

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 #