Re: tracking commit timestamps

On 2014-11-05 10:23:15 -0600, Jim Nasby wrote:
>
>
> On 11/5/14, 6:10 AM, Michael Paquier wrote:
> > 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..
>
> Anssi's point is that you can't use xmin because it can change, but I think anyone working with this feature would understand that.
>
> > 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...

> Except that commit time is not guaranteed unique *even on a single
> system*. That's my whole point. If we're going to bother with all the
> commit time machinery it seems really silly to provide a way to
> uniquely order every commit.

Well. I think that's the misunderstanding here. That's absolutely not
what committs is supposed to be used for. For the replication stream
you'd hopefully use logical decoding. That gives you the transaction
data exactly in commit order.

> Clearly trying to uniquely order commits across multiple systems is a
> far larger problem, and I'm not suggesting we attempt that. But 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.

I don't think that's something you should attempt. That's what logical
decoding is for. Hence I see little point in exposing the LSN that way.

Where I think committs is useful is a method for analyzing and resolving
conflicts between multiple systems. In that case you likely can't use
the LSN for anything as it won't be very meaningful. If you get
conflicts below the accuracy of the timestamps you better use another
deterministic method of resolving them - BDR e.g. compares the system
identifier, timeline id, database oid, and a user defined name. While
enforcing that those aren't the same between systems.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services