Re: tracking commit timestamps

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:

> for a single system AIUI all we need to do is expose the LSN of
> each commit record and that will give you the exact and unique
> order in which transactions committed.
>
> This isn't a hypothetical feature either; if we had this,
> logical replication systems wouldn't have to try and fake this
> via batches. You could actually recreate exactly what data was
> visible at what time to all transactions, not just repeatable
> read ones (as long as you kept snapshot data as well, which isn't
> hard).

Well, that not entirely true for serializable transactions; there
are points in time where reading the committed state could cause a
transaction to roll back[1] -- either a writing transaction which
would make that visible state inconsistent with the later committed
state or the reading transaction if it views something which is not
(yet) consistent.

That's not to say that this feature is a bad idea; part of the
serializable implementation itself depends on being able to
accurately determine commit order, and this feature could allow
that to work more efficiently. I'm saying that, like hot standby,
a replicated database could not provide truly serializable
transactions (even read only ones) without something else in
addition to this. We've discussed various ways of doing that.
Perhaps the most promising is to include in the stream some
indication of which points in the transaction stream are safe for a
serializable transaction to read. If there's a way to implement
commit order recording such that a two-state flag could be
associated with each commit, I think it could be made to work for
serializable transactions.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1] https://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions