Thread: Option to ensure monotonic timestamps

Brent Kerby <blkerby@gmail.com> writes:
> What I propose is that we could add a boolean configuration option, say
> 'ensure_monotonic_timestamps', that enables the following behavior: when
> GetCurrentTimestamp is called (
> https://doxygen.postgresql.org/backend_2utils_2adt_2timestamp_8c.html#a9822cdf3fd41b15851c0c18ddc80143c),
> before it returns it checks if `result` is less than what was returned last
> time (if any) that GetCurrentTimestamp was called, and if so it returns the
> result from the previous call (after logging a warning), otherwise it
> proceeds as normal. In its simplest form, this could be accomplished by
> adding a global variable lastGetCurrentTimestamp that stores the result of
> the previous call.

The "global" variable would actually need to be cluster-wide, ie in shared
memory, which would imply contention and the need for locks.  I think the
overhead of this would be mighty high, and the return pretty low.

It's also worth pointing out that if you don't trust the kernel clock,
simply clamping to the last returned value isn't likely to be terribly
satisfactory.  What if $idiotsysadmin steps the clock back an hour?
We've had actual problems of that sort, for example with the stats
collector going AWOL for awhile because it thought it'd already written a
sufficiently new stats file.  There's now an explicit check for clock-
went-backwards in pgstat_recv_inquiry, which will be broken in that sort
of scenario if you cause GetCurrentTimestamp to do clamping internally.

On the whole, the alternative of solving the problem at the kernel level
seems preferable to me.

            regards, tom lane

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

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

Hi,

On 2018-02-20 12:32:22 -0500, Tom Lane wrote:
> The "global" variable would actually need to be cluster-wide, ie in shared
> memory, which would imply contention and the need for locks.  I think the
> overhead of this would be mighty high, and the return pretty low.

I think if we wanted to go for something like this (which I doubt), we'd
have that global variable as an atomic 64bit variable in shmem, and
*only* use it for stuff where the ordering actually matters. I.e. not
for transaction start times etc...


> It's also worth pointing out that if you don't trust the kernel clock,
> simply clamping to the last returned value isn't likely to be terribly
> satisfactory.  What if $idiotsysadmin steps the clock back an hour?
> We've had actual problems of that sort, for example with the stats
> collector going AWOL for awhile because it thought it'd already written a
> sufficiently new stats file.  There's now an explicit check for clock-
> went-backwards in pgstat_recv_inquiry, which will be broken in that sort
> of scenario if you cause GetCurrentTimestamp to do clamping internally.

I guess you could hack something together with CLOCK_MONOTONIC or such,
but brrrr.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2018-02-20 12:32:22 -0500, Tom Lane wrote:
>> The "global" variable would actually need to be cluster-wide, ie in shared
>> memory, which would imply contention and the need for locks.  I think the
>> overhead of this would be mighty high, and the return pretty low.

> I think if we wanted to go for something like this (which I doubt), we'd
> have that global variable as an atomic 64bit variable in shmem, and
> *only* use it for stuff where the ordering actually matters. I.e. not
> for transaction start times etc...

Then you've got problems with figuring out which usages "matter" and which
don't, and being sure you don't ever compare timestamps from the two
different sources.  Seems mighty fragile to me, and reminiscent of the
replication problems that forced us to drop support for float timestamps.

In any case I'd not much like a system that mostly reported in system
clock time except transaction commit timestamps are on some other
timescale.

But really, the killer point here is your upthread comment that even if
GetCurrentTimestamp were guaranteed to return monotonic time, that would
not guarantee that commit timestamps match physical commit order, which
was the OP's goal.  At least not unless we read the clock while holding
WALWriteLock, which I'm pretty sure everyone will say Ain't Happening.

I think your not-very-explicit suggestion that he should work in
commit LSNs, not timestamps at all, is a far superior answer.

            regards, tom lane