Re: Exposing the Xact commit order to the user

On 5/25/2010 4:50 PM, Simon Riggs wrote:
> On Tue, 2010-05-25 at 16:41 -0400, Jan Wieck wrote:
>> On 5/25/2010 12:03 PM, Simon Riggs wrote:
>> > On Sun, 2010-05-23 at 16:21 -0400, Jan Wieck wrote:
>> >
>> >> In some systems (data warehousing, replication), the order of commits is
>> >> important, since that is the order in which changes have become visible.
>> >> This information could theoretically be extracted from the WAL, but
>> >> scanning the entire WAL just to extract this tidbit of information would
>> >> be excruciatingly painful.
>> >
>> > I think it would be quite simple to read WAL. WALSender reads the WAL
>> > file after its been flushed, so it would be simple for it to read a blob
>> > of WAL and then extract the commit order from it.
>> >
>> > Overall though, it would be easier and more efficient to *add* info to
>> > WAL and then do all this processing *after* WAL has been transported
>> > elsewhere. Extracting info with DDL triggers, normal triggers, commit
>> > order and everything else seems like too much work to me. Every other
>> > RDBMS has moved away from trigger-based replication and we should give
>> > that serious consideration also.
>>
>> Reading the entire WAL just to find all COMMIT records, then go back to
>> the origin database to get the actual replication log you're looking for
>> is simpler and more efficient? I don't think so.
>
> Agreed, but I think I've not explained myself well enough.
>
> I proposed two completely separate ideas; the first one was this:
>
> If you must get commit order, get it from WAL on *origin*, using exact
> same code that current WALSender provides, plus some logic to read
> through the WAL records and extract commit/aborts. That seems much
> simpler than the proposal you outlined and as SR shows, its low latency
> as well since commits write to WAL. No need to generate event ticks
> either, just use XLogRecPtrs as WALSender already does.
>
> I see no problem with integrating that into core, technically or
> philosophically.
>

Which means that if I want to allow a consumer of that commit order data
to go offline for three days or so to replicate the 5 requested, low
volume tables, the origin needs to hang on to the entire WAL log from
all 100 other high volume tables?

Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin