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(at)cryptonector(dot)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
--