On Tue, Feb 20, 2018 at 9:51 AM, Andres Freund <and...@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-51f1efb8a...@postgrespro.ru Patrick