Re: tracking commit timestamps

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.