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