Re: tracking commit timestamps - Mailing list pgsql-hackers

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
commitorder 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
understandthat.
 

>     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
timestampsin 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
sufficientrange...
 

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

Clearly trying to uniquely order commits across multiple systems is a far larger problem, and I'm not suggesting we
attemptthat. But for a single system AIUI all we need to do is expose the LSN of each commit record and that will give
youthe 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
thisvia batches. You could actually recreate exactly what data was visible at what time to all transactions, not just
repeatableread ones (as long as you kept snapshot data as well, which isn't hard).
 

As for how much data to keep, if you have a process that's doing something to record this information permanently all
itneeds to do is keep an old enough snapshot around. That's not that hard to do, even from user space.
 
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com