Re: Exposing the Xact commit order to the user

On Wed, May 26, 2010 at 11:43 AM, Steve Singer <ssinger(at)ca(dot)afilias(dot)info> wrote:
> Jan Wieck wrote:
>
>> Since the actual row level change information and other event data is
>> found inside of regular tables, identified by TXID and sequence number, I am
>> pretty sure I want that data in a server-side query. What you are proposing
>> is to read the xid's and timestamps with an external process, that now
>> forcibly needs to reside on the DB server itself (neither Londiste nor Slony
>> have that requirement as of today), then bring it back into the DB at least
>> inside the WHERE clause of a query.
>
>
> It depends on how you approach the problem.
>
> If you had a process that could scan WAL files (or a platform/version
> independent representation of these WAL files) you could run that process on
> any server (the origin server, a replica, or some third server with the
> software installed).  Where you run it involves making trade-offs on the
> costs of storing transferring and processing the files and would ideally be
> configurable.
>
> You could then have a process that transfers all of the data logged by the
> triggers to the replicas as soon as it is committed.  Basically saying 'copy
> any rows in sl_log from the origin to the replica that we haven't already
> sent to that replica'
>
> You could then move the work of figuring out the commit order onto the
> replica where you would combine the output of the WAL scanning process with
> the transaction data that has been copied to the replica.

I'm sure it's possible to make this work however you want to do it,
but I don't really see what advantage Greg Stark's proposal has over
Jan's original proposal. Recording the commits in one extra place at
commit time is practically free, especially compared to the overall
cost of replication. Rescanning the WAL seems likely to be much more
expensive and potentially introduces more failure paths.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company