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.