Re: [HACKERS] Contrib: alternative MATERIALIZED VIEWs

On Mon, Jan 23, 2017 at 06:05:25PM -0600, Jim Nasby wrote:
> On 1/23/17 5:38 PM, Nico Williams wrote:
> >Attached is an alternative implementation of MATERIALIZED VIEWs.
> 
> Interesting. I don't see this being accepted into core because it's plpgsql
> and it depends on the user to track what criteria to use to apply the
> update. The second item is the biggest issue.

I myself said this is not properly integrated.  I do use this in an
actual system, but ideally if any of this is to be welcomed, then it'd
have to be properly integrated.  I don't see what's wrong with the use
of plpgsql as the MV system in PG uses SQL, but in a proper integration
a lot of this would be re-written in C (the use of SQL for the delta
computation and the updates of the history table would remain, but I
think too so would the triggers needed to update the history table when
the MV is updated directly).

As to the second issue...  Just the other day Kevin Grittner was
concerned about automatic MV updates because some of them can take too
long.  Now, PG could timeout MV updates, roll them back, and mark an MV
as requiring a refresh, but the user might still have something to say
about this: they really, really might want some updates to always happen
synchronously, and others always asynchronously.  I think it's really
necessary to give the user this sort of control.

> That said, I think this would be useful to some people as an extension. I
> suggest you put it on github (or equivalent) and upload it to
> http://pgxn.org.

Ah, I forgot to mention that it is on github here:

https://github.com/twosigma/postgresql-contrib

> In terms of community support, the next step is to get statement-level
> support for NEW and OLD, something I think Kevin has been working on.

Well, I think there's a lot to look at here.  That's partly why a
plpgsql-coded implementation is a good first step, IMO: it helps find
issues.  For me the need for a PK for MVs is fairly important; but the need
for deltas/history table is critical, and there is no reason not to have
it considering that deltas are produced internally during REFRESH ..
CONCURRENTLY.

Nico
--