Re: Feature: triggers on materialized views

On Tue, Dec 25, 2018 at 10:05 PM Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> 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.

Sorry to jump in late, but all of this sounds very strange to me.
It's possible for either concurrent or non-concurrent refresh to be
faster, depending on the circumstances; for example, if a concurrent
refresh would end up deleting all the rows and inserting them again, I
think that could be slower than just blowing all the data away and
starting over. So disabling non-concurrent refresh sounds like a bad
idea. For the same reason, reimplementing it to work like a
concurrent refresh also sounds like a bad idea.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company