Re: matview incremental maintenance

On 17 June 2013 15:41, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> While I have yet to look in detail at the mechanism for capturing
> the initial delta on the base tables, the two fairly obvious
> candidates are to stuff the before and after images into a
> tuplestore or temp table as base table changes are written,
> somewhere around the point that triggers would be fired, or to use
> the WAL stream in some way. The advantages of the former are that
> it would be hard to find a lower overhead way to capture the data,
> nor a more certain way to get exactly the right data. The latter,
> which Simon has been arguing is better than using triggers, would
> have the advantage of not directly slowing down a process writing
> to base tables, although for more eager modes transactions would
> need to block waiting for the data to flow through the walsender,
> be filtered and assembled as data of interest, and communicated
> back to the transaction somehow before it could proceed. Assuming
> that it can provide the changeset prior to the commit, and that it
> can include "before" images, it could work, but the timing sure
> seems dubious for the more eager modes.

It isn't unconditionally true statement to say "it would be hard to
find a lower overhead way to capture the data", since there is strong
experimental evidence from work on replication that shows that using
the WAL is very effective mechanism for changeset extraction.

There is nothing to say the changeset must occur through the
WalSender. That is just where it currently occurs, but it could easily
occur elsewhere, if the requirement existed. Similarly, changeset
extraction doesn't currently allow access to uncommitted rows, but it
could do so, if required. Before images of change could be provided by
direct access to prior versions via their tid, just as they are with
triggers.

There are other advantages to using WAL that you don't mention, such
as the avoidance of the need for the trigger queue to spill to disk,
avoidance of memory overhead for large transactions and avoidance of
random I/O.

ISTM that using WAL has to be properly considered as a viable option
which is why open discussion makes sense.

The timing of that discussion doesn't need to be immediate but
certainly it should happen before any options are precluded because of
the progress of other events. Let me me know when that's appropriate,
so we can discuss.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services