Re: Option to ensure monotonic timestamps

On Tue, Feb 20, 2018 at 9:51 AM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> 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
>

Hi Brent --

I haven't heard of temporal tables before, but I guess it's a feature
of SQL Server 2016. It sounds similar to some recent work in progress
to add "AS OF" to SELECT statements:
https://www.postgresql.org/message-id/78aadf6b-86d4-21b9-9c2a-51f1efb8a499@postgrespro.ru

Patrick