Re: Option to ensure monotonic timestamps - Mailing list pgsql-hackers

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.