Re: Option to ensure monotonic timestamps
| От | Brent Kerby |
|---|---|
| Тема | Re: Option to ensure monotonic timestamps |
| Дата | |
| Msg-id | CAH8WVsj54D40+sT5V_uefxsKq3oKOvdxXW5HNf7CSUxEcGt1-w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Option to ensure monotonic timestamps (Patrick Krecker <pkrecker@gmail.com>) |
| Список | pgsql-hackers |
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@gmail.com> wrote:
Hi Brent --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
>
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
В списке pgsql-hackers по дате отправления: