Re: Option to ensure monotonic timestamps

Hi,

Leaving Tom's concerns aside:

On 2018-02-19 13:42:31 -0700, Brent Kerby wrote:
> Hi, I'm new to Postgres hacking, and I'm interested in the possibility of a
> new feature to make it possible to ensure that Postgres-generated
> timestamps never decrease even if the system clock may step backwards. My
> use case is that I'm implementing a form of temporal tables based on
> transaction commit timestamps (as returned by pg_xact_commit_timestamp),
> and to ensure the integrity of the system I need to know that the ordering
> of the commit timestamps will always be consistent with the order in which
> the transactions actually committed.

The acquiration of the commit timestamp and the actual visibility of the
commit will not necessarily be sufficient for many things. A backend can
theoretically sleep for an hour between

static TransactionId
RecordTransactionCommit(void)
{
...
SetCurrentTransactionStopTimestamp();
/* here */
XactLogCommitRecord(xactStopTimestamp,
nchildren, children, nrels, rels,
nmsgs, invalMessages,
RelcacheInitFileInval, forceSyncCommit,
MyXactFlags,
InvalidTransactionId /* plain commit */ );
}

static void
CommitTransaction(void)
{
...
/*
* We need to mark our XIDs as committed in pg_xact. This is where we
* durably commit.
*/
latestXid = RecordTransactionCommit();

/* here */

/*
* Let others know about no transaction in progress by me. Note that this
* must be done _before_ releasing locks we hold and _after_
* RecordTransactionCommit.
*/
ProcArrayEndTransaction(MyProc, latestXid);

whether that affects your approach I do not know.

> Any thoughts?

Why are you looking to do something timestamp based in the first place?
It's a bit hard to give good advice without further information...

Greetings,

Andres Freund