Re: Idea: GSoC - Query Rewrite with Materialized Views

On 3/3/15 3:34 PM, David Fetter wrote:
> On Tue, Mar 03, 2015 at 05:49:06PM -0300, Alvaro Herrera wrote:
>> Jim Nasby wrote:
>>
>>> FWIW, what I would find most useful at this point is a way to get
>>> the equivalent of an AFTER STATEMENT trigger that provided all
>>> changed rows in a MV as the result of a statement.
>>
>> Ah, like
>> https://www.postgresql.org/message-id/1402790204.65037.YahooMailNeo%40web122301.mail.ne1.yahoo.com
>
> Yes, very much like that.

Actually, I was talking about the next step beyond that. I don't want
what changed in a single table; I want what changed *in the source of
the entire MV*. Kevin has a whitepaper that describes how to do this in
set notation; theoretically this is a matter of converting that to SQL.
IIRC this needs the deltas and current (or maybe NEW and OLD) for every
table in the MV. So one way you could model this is a function that
accepts a bunch of NEW and OLD recordsets.

Theoretically you could actually drive that with per-row triggers, but
the performance would presumably suck. Next best thing would be
providing NEW and OLD for AFTER STATEMENT triggers (what Kevin was
talking about in that email). Though, if you're driving this at a
statement level that means you can't actually reference the MV in a
statement that's performing DML on any of the dependent tables.

As you can see, this is all pretty involved. Doing just a small part of
this would make for a good GSoC project. AFTER STATEMENT NEW and OLD
might be a good project; I don't know how much more work Kevin's stuff
needs. But there's much greater value in creating something that would
take the definition for a MV and turn that into appropriate delta logic.
That would be the basis for detecting if a MV was stale (beyond just the
gross level check of were any of the tables involved touched), and is
what is needed to do *any* kind of incremental update.

That logic doesn't have to be driven by triggers. For example, you could
have PgQ or similar capture all DML on all tables for a MV and feed that
data to the delta logic on an async incremental basis. It's pretty easy
for an end user to setup PgQ or similar but doing the delta logic is
tightly coupled to the MV definition, which would be very hard for an
end user to deal with.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com