Re: tracking commit timestamps

On Wed, Oct 23, 2013 at 3:46 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Hi,
>
> There has been some interest in keeping track of timestamp of
> transaction commits. This patch implements that.

Some of the use cases, I could think of are
1. Is it for usecases such that if user want to read all data of table
where transaction commit_ts <= '2012-04-04 09:30:00'?
2. for replication systems, may be the middleware can use it to replay
transactions in some remote system.
3. Is there any use of this feature in logical-rep/change data extraction?

> There are some seemingly curious choices here. First, this module can
> be disabled, and in fact it's turned off by default. At startup, we
> verify whether it's enabled, and create the necessary SLRU segments if
> so. And if the server is started with this disabled, we set the oldest
> value we know about to avoid trying to read the commit TS of
> transactions of which we didn't keep record. The ability to turn this
> off is there to avoid imposing the overhead on systems that don't need
> this feature.
>
> Another thing of note is that we allow for some extra data alongside the
> timestamp proper. This might be useful for a replication system that
> wants to keep track of the origin node ID of a committed transaction,
> for example. Exactly what will we do with the bit space we have is
> unclear, so I have kept it generic and called it "commit extra data".

"commit extra data" can be LSN of commit log record, but I think it
will also depend on how someone wants to use this feature.
To suggest for storing LSN, I had referred information at below page
which describes about similar information for each transaction.
http://technet.microsoft.com/en-us/library/cc645959.aspx

> This offers the chance for outside modules to set the commit TS of a
> transaction; there is support for WAL-logging such values. But the core
> user of the feature (RecordTransactionCommit) doesn't use it, because
> xact.c's WAL logging itself is enough.

I have one question for the case when commits is set from
RecordTransactionCommit().

*** 1118,1123 **** RecordTransactionCommit(void)
--- 1119,1132 ----
}

/*
+ * We don't need to log the commit timestamp separately since the commit
+ * record logged above has all the necessary action to set the timestamp
+ * again.
+ */
+ TransactionTreeSetCommitTimestamp(xid, nchildren, children,
+ xactStopTimestamp, 0, false);
+

Here for CLOG, we are doing Xlogflush before writing to Clog page, but
Committs writes timestamp before XlogFlush().
Won't that create problem for synchronous commit as Checkpoint can
takecare of flushing Xlog for relation pages before flush of page,
but for Clog/Committs RecordTransactionCommit() should take care of doing it.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com