On Tue, 2014-11-04 at 23:43 -0600, Jim Nasby wrote:
> I'm worried about 2 commits in the same microsecond on the same > system, not on 2 different systems. Or, put another way, if we're > going to expose this I think it should also provide a guaranteed > unique commit ordering for a single cluster. Presumably, this > shouldn't be that hard since we do know the exact order in which > things committed.
Addition of LSN when the timestamps for two transactions are exactly same isn't enough. There isn't any guarantee that a later commit gets a later timestamp than an earlier commit.
True if WAL record ID is not globally consistent. Two-level commit ordering can be done with (timestamp or LSN, nodeID). At equal timestamp, we could say as well that the node with the lowest systemID wins for example. That's not something
In addition, I wonder if this feature would be misused. Record transaction ids to a table to find out commit order (use case could be storing historical row versions for example). Do a dump and restore on another cluster, and all the transaction ids are completely meaningless to the system.
I think you are forgetting the fact to be able to take a consistent dump using an exported snapshot. In this case the commit order may not be that meaningless..
Having the ability to record commit order into an audit table would be extremely welcome, but as is, this feature doesn't provide it.
That's something that can actually be achieved with this feature if the SQL interface is able to query all the timestamps in a xid range with for example a background worker that tracks this data periodically. Now the thing is as well: how much timestamp history do we want to keep? The patch truncating SLRU files with frozenID may cover a sufficient range... --