Re: tracking commit timestamps

On Wed, Nov 5, 2014 at 5:24 PM, Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi>
wrote:

> 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...
--
Michael