Re: Option to ensure monotonic timestamps

The issue is that presence of timestamps is fundamental to the
functionality of temporal tables. The users need to.be able to make queries
on temporal tables in terms of timestamps; LSNs won't mean anything to
them. It would be an option to implement the temporal tables using LSNs
under the hood, but then it is still required to construct a monotonic
mapping between LSNs and timestamps in order for it to be usable. The most
natural method for constructing such a mapping would be to use the stored
commit timestamps; if these are monotonic, then that works, but we gain
little by storing the LSNs, since they will just be converted to timestamps
anyway when they're used. But if the timestamps aren't monotonic, then
we're faced with the same problem as before; we could try to patch up the
non-monotonicity in the mapping after-the-fact, using clamping or possibly
some more sophisticated method, but this is inefficient and could get ugly
fast. It would seem preferable to just ensure that the timestamps are
monotonic to begin with. And based on your observations of why we shouldn't
try to enforce this on every application of GetCurrentTimestamp, I think
maybe it would be cleaner to just enforce this on commit timestamps (and
only if enabled by a configuration option, of course), since this is all
that is needed and should be simpler and less expensive. And if a violation
occurs, we can just abort the transaction with an error, rather than
clamping the timestamp. This way we don't end up with an ugly scenario like
you pointed out, where we have one set of timestamps that are clamped
(i.e., for commit timestamps) and others that are not; and also this way,
if the $idiotsysadmin sets the clock back an hour, then we get errors
immediately instead of a whole hour of temporal table history being messed
up.

On Tue, Feb 20, 2018 at 11:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andres Freund <andres(at)anarazel(dot)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
>