Re: Option to ensure monotonic timestamps

On Tue, Feb 20, 2018 at 9:51 AM, Andres Freund <andres@anarazel.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