Re: Feature: triggers on materialized views - Mailing list pgsql-hackers

From Robert Haas
Subject Re: Feature: triggers on materialized views
Date
Msg-id CA+TgmobppmbtZ7PiSTmrWFsApxx1XxUbgw=TxkFW00=R2B7W1Q@mail.gmail.com
Whole thread Raw
In response to Re: Feature: triggers on materialized views  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-hackers
On Tue, Dec 25, 2018 at 10:05 PM Alvaro Herrera
<alvherre@2ndquadrant.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


pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: Connections hang indefinitely while taking a gin index's LWLockbuffer_content lock
Next
From: Robert Haas
Date:
Subject: Re: Feature: triggers on materialized views