Re: Option to ensure monotonic timestamps

Right, I'm talking about temporal tables in the sense of the SQL:2011
standard. I know there's a Postgres extension temporal_tables by Vlad
Arkhipov (https://github.com/arkhipov/temporal_tables/) that approximates
this. There's also a way of doing it using only triggers written in
pgplsql, by Paolo Chiodi (
https://www.nearform.com/blog/time-travel-with-postgresql-on-amazon-rds/).
In these solutions, however, as well as in the SQL Server 2016 and many
other implementations, the transaction start time (as opposed to commit
time) is used as the time at which the data is considered to have changed,
which does not ensure consistency of the historical data: for instance, you
can end up with a situation where, when viewed "AS OF" certain time points,
the database will appear to have had non-unique primary keys and broken
foreign key references (e.g., see
https://dba.stackexchange.com/questions/143241/why-do-temporal-tables-log-the-begin-time-of-the-transaction/198204#198204
).

I wasn't aware of that recent work. The "AS OF" syntax seems useful,
although if I understand it correctly it doesn't provide the full power of
the temporal tables. With a full implementation of temporal tables, for
each temporal table there's a corresponding history table that can be
directly accessed by queries, making it possible for instance to see a list
of all changes that have affected rows satisfying certain conditions, or to
see the data "AS OF" not just constant times but "AS OF" some variable time
given by a column in another table that is being joined with (The ability
to do this is important in my application).

I agree with Tom's points and don't think that what I originally proposed
is a very good solution, but it still makes me uncomfortable to trust
blindly in the kernel clock when the integrity of the data hangs in the
balance. How about the following alternative proposal?: Instead of trying
to enforce monotonicity of all Postgres-generated timestamps, we look only
at the commit timestamps, and if at the time that we are about to commit we
detect that a violation occurs, instead of clamping (which I agree is ugly)
we abort the transaction with an error. And this should happen only if a
configuration option, say 'monotonic_commit_timestamp', is enabled. With
this approach, we only need to keep track of the previous commit timestamp,
which is already being done if "track_commit_timestamp" is enabled (which
should probably be a prerequisite for enabling
'monotonic_commit_timestamp'), so that should impose minimal overhead -- no
need for any additional locking or including anything more in the WAL,
right?

On Tue, Feb 20, 2018 at 11:09 AM, Patrick Krecker <pkrecker(at)gmail(dot)com>
wrote:

> 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(at)postgrespro(dot)ru
>
> Patrick
>