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.
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.
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).
As for how much data to keep, if you have a process that's doing something to
record this information permanently all it needs 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
