Re: Feature: triggers on materialized views

From: Mitar <mmitar(at)gmail(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Feature: triggers on materialized views
Date: 2018-12-25 02:17:16
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers


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).


On Mon, Dec 24, 2018 at 4:13 PM Mitar <mmitar(at)gmail(dot)com> wrote:
> Hi!
> Thanks for reply!
> On Mon, Dec 24, 2018 at 2:20 PM David Fetter <david(at)fetter(dot)org> wrote:
> > You've got the right mailing list, a description of what you want, and
> > a PoC patch. You also got the patch in during the time between
> > Commitfests. You're doing great!
> Great!
> One thing I am unclear about is how it is determined if this is a
> viable feature to be eventually included. You gave me some suggestions
> to improve in my patch (adding tests and so on). Does this mean that
> the patch should be fully done before a decision is made?
> Also, the workflow is that I improve things, and resubmit a patch to
> the mailing list, for now?
> > > - Currently only insert and remove operations are done on the
> > > materialized view. This is because the current logic just removes
> > > changed rows and inserts new rows.
> >
> > What other operations might you want to support?
> Update. So if a row is changing, instead of doing a remove and insert,
> what currently is being done, I would prefer an update. Then UPDATE
> trigger operation would happen as well. Maybe the INSERT query could
> be changed to INSERT ... ON CONFLICT UPDATE query (not sure if this
> one does UPDATE trigger operation on conflict), and REMOVE changed to
> remove just rows which were really removed, but not only updated.
> > As far as you can tell, is this just an efficiency optimization, or
> > might it go to correctness of the behavior?
> It is just an optimization. Or maybe even just a surprise. Maybe a
> documentation addition could help here. In my use case I would loop
> over OLD and NEW REFERENCING TABLE so if they are empty, nothing would
> be done. But it is just surprising that DELETE trigger is called even
> when no rows are being deleted in the materialized view.
> > I'm not sure I understand the problem being described here. Do you see
> > these as useful to separate for some reason?
> So rows which are just updated currently get first DELETE trigger
> called and then INSERT. The issue is that if I am observing this
> behavior from outside, it makes it unclear when I see DELETE if this
> means really that a row has been deleted or it just means that later
> on an INSERT would happen. Now I have to wait for an eventual INSERT
> to determine that. But how long should I wait? It makes consuming
> these notifications tricky.
> If I just blindly respond to those notifications, this could introduce
> other problems. For example, if I have a reactive web application it
> could mean a visible flicker to the user. Instead of updating rendered
> row, I would first delete it and then later on re-insert it.
> > > Non-concurrent refresh does not trigger any trigger. But it seems
> > > all data to do so is there (previous table, new table), at least for
> > > the statement-level trigger. Row-level triggers could also be
> > > simulated probably (with TRUNCATE and INSERT triggers).
> >
> > Would it make more sense to fill in the missing implementations of NEW
> > and OLD for per-row triggers instead of adding another hack?
> You lost me here. But I agree, we should implement this fully, without
> hacks. I just do not know how exactly.
> Are you saying that we should support only row-level triggers, or that
> we should support both statement-level and row-level triggers, but
> just make sure we implement this properly? I think that my suggestion
> of using TRUNCATE and INSERT triggers is reasonable in the case of
> full refresh. This is what happens. If we would want to have
> DELETE/UPDATE/INSERT triggers, we would have to compute the diff like
> concurrent version has to do, which would defeat the difference
> between the two. But yes, all INSERT trigger calls should have NEW
> provided.
> So per-statement trigger would have TRUNCATE and INSERT called. And
> per-row trigger would have TRUNCATE and per-row INSERTs called.
> Mitar
> --


Attachment Content-Type Size
matview.patch text/x-patch 5.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Mitar 2018-12-25 02:20:01 Re: Feature: triggers on materialized views
Previous Message Tatsuro Yamada 2018-12-25 01:56:04 Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SET STATISTICS