Re: Exposing the Xact commit order to the user - Mailing list pgsql-hackers

On Fri, Jun 4, 2010 at 2:32 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> I find the skeptical attitude on this thread altogether unwarranted.
> Jan made his case and, at least IMHO, presented it pretty clearly.

Just to be clear I think the idea of exposing commit order is a
no-brainer.  The specific interface is what I was questioning.

A function which takes a starting xid and a number of transactions to
return seems very tied to one particular application. I could easily
see other systems such as a multi-master system instead only wanting
to compare two transactions to find out which committed first. Or
non-replication applications where you have an LSN and want to know
whether a given transaction had committed by that time.

So one possible interface would be to do something like
xids_committed_between(lsn_start, lsn_end) -- and yes, possibly with
an optional argument to limit the number or records returned.

So you could do:

old := select pg_current_xlog_location();
while (1)
{ sleep 60s; new := select pg_current_xlog_location() process(select xids_committed_between(old,new)) old := new
}

This might be more useful for PITR recovery for example where you want
to find out what transactions committed between now and some known
point of corruption.

I could also see it being useful to have a function
pg_xlog_location_of_commit(xid). That would let you run recovery until
a particular transaction committed or test whether your replica is
caught up to a particular commit. It could be useful for monitoring
Hot Standby slaves.


>  He
> then answered, multiple times, numerous questions which were already
> addressed in the original email, as well as various others.

I think I did miss some of the original description. That might have
caused some of the difficulty as I was asking questions about
something he assumed he had already answered.

>  I think
> we should be very careful about assuming that we understand
> replication and its needs better than someone who has spent many years
> developing one of the major PostgreSQL replication solutions.

Well the flip side of that is that we want an interface that's useful
for more than just one replication system. This is something basic
enough that I think it will be useful for more than just replication
if we design it generally enough. It should be useful for
backup/restore processes and monitoring as well as various forms of
replication including master-slave trigger based systems but also
including PITR-based replication, log-parsing systems, multi-master
trigger based systems, 2PC-based systems, etc.



--
greg