On 11/05/2014 11:23 AM, Jim Nasby wrote:
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.
+1 for this.
It isn't just 'replication' systems that have a need for getting the
commit order of transactions on a single system. I have a application
(not slony) where we want to query a table but order the output based on
the transaction commit order of when the insert into the table was done
(think of a queue). I'm not replicating the output but passing the data
to other applications for further processing. If I just had the commit
timestamp I would need to put in some other condition to break ties in a
consistent way. I think being able to get an ordering by commit LSN is
what I really want in this case not the timestamp.
Logical decoding is one solution to this (that I was considering) but
being able to do something like
select * FROM event_log order by commit_id would be a lot simpler.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
