Re: delta relations in AFTER triggers

On Fri, Jan 20, 2017 at 01:37:33PM -0600, Kevin Grittner wrote:
> On Thu, Jan 19, 2017 at 4:14 PM, Nico Williams <nico@cryptonector.com> wrote:
> 
> Reviews welcome!

I will review.

> There is currently plenty of room for pseudo-MV implementations,
> and may be for a while.  It's a good indication of the need for the
> feature in core.  An implementation in the guts of core can have
> advantages that nothing else can, of course.  For example, for
> eager application of the deltas, nothing will be able to beat
> capturing tuples already in RAM and being looked at for possible
> trigger firing into a RAM-with-spill-to-disk tuplestore.

BTW, automatic updates of certain types of MVs should be easy: add
constraints based on NEW/OLD rows from synthetic triggers to the
underlying query.  However, there is a bug in the query planner that
prevents this from being very fast.  At some point I want to tackle that
bug.  Basically, the planner does not notice that a table source in a
join has a lookup key sufficiently well-specified by those additional
constraints that it should be the first table source in the outermost
loop.

> I don't have time to review what you've done right now, but will
> save that link to look at later, if you give permission to borrow
> from it (with proper attribution, of course) if there is something
> that can advance what I'm doing.  If such permission is not
> forthcoming, I will probably avoid looking at it, to avoid any
> possible copyright issues.

Our intention is to contribute this.  We're willing to sign reasonable
contribution agreements.

I'd appreciate a review, for sure.  Thanks!

> > Incidentally, it's really nice that PG has some "higher order" SQL
> > features that make this sort of thing easier.  In particular, here, row
> > values and record types, and being able to refer to a table as a column
> > of the table's record type.
> 
> Yeah, I found that quite handy in developing the REFRESH feature,
> and expect to be using it in incremental maintenance.

Indeed, I *copied* the pattern.  However, I didn't have to generate
SELECT statements that include column names, as I was able to just
compare table source row values.  There's a gotcha w.r.t. NULL columns,
but it affects the built-in REFRESH as well, IIRC.  The commentary in
our implementation discusses that in more detail.

Nico
--