Re: Feature: triggers on materialized views

On 2018-Dec-25, Mitar wrote:

> On Tue, Dec 25, 2018 at 6:47 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > > I made another version of the patch. This one does UPDATEs for changed
> > > row instead of DELETE/INSERT.
> > >
> > > All existing regression tests are still passing (make check).
> >
> > Okay, but you don't add any for your new feature, which is not good.
> 
> Yes, I have not yet done that. I want first to also add calling
> triggers for non-concurrent refresh, but I would need a bit help there
> (what to call, example of maybe code which does something similar
> already).

Well, REFRESH CONCURRENTLY runs completely different than non-concurrent
REFRESH.  The former updates the existing data by observing the
differences with the previous data; the latter simply re-runs the query
and overwrites everything.  So if you simply enabled triggers on
non-concurrent refresh, you'd just see a bunch of inserts into a
throwaway data area (a transient relfilenode, we call it), then a swap
of the MV's relfilenode with the throwaway one.  I doubt it'd be useful.
But then I'm not clear *why* you would like to do a non-concurrent
refresh.  Maybe your situation would be best served by forbidding non-
concurrent refresh when the MV contains any triggers.

Alternatively, maybe reimplement non-concurrent refresh so that it works
identically to concurrent refresh (except with a stronger lock).  Not
sure if this implies any performance penalties.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services