Re: Feature: triggers on materialized views

Dear,

You can try https://github.com/ntqvinh/PgMvIncrementalUpdate to generate
triggers in C for incremental updates of matviews.

For asynchronous updates, the tool does generate the triggers for
collecting updated/inserted/deleted rows and then the codes for doing
incremental updating as well.

Tks and best regards,

Vinh

On Sat, Jan 5, 2019 at 5:10 AM Mitar <mmitar(at)gmail(dot)com> wrote:

> Hi!
>
> I am new to contributing to PostgreSQL and this is my first time
> having patches in commit fest, so I am not sure about details of the
> process here, but I assume that replying and discuss the patch during
> this period is one of the actives, so I am replying to the comment. If
> I should wait or something like that, please advise.
>
> On Fri, Jan 4, 2019 at 3:23 AM Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> > > A summary of the patch: This patch enables adding AFTER triggers (both
> > > ROW and STATEMENT) on materialized views. They are fired when doing
> > > REFRESH MATERIALIZED VIEW CONCURRENTLY for rows which have changed.
> >
> > What bothers me about this patch is that it subtly changes what a
> > trigger means. It currently means, say, INSERT was executed on this
> > table. You are expanding that to mean, a row was inserted into this
> > table -- somehow.
>
> Aren't almost all statements these days generated by some sort of
> automatic logic? Which generates those INSERTs and then you get
> triggers on them? I am not sure where is this big difference in your
> view coming from? Triggers are not defined as "user-made INSERT was
> executed on this table". I think it has always been defined as "INSERT
> modified this table", no matter where this insert came from (from
> user, from some other trigger, by backup process). I mean, this is the
> beauty of declarative programming. You define it once and you do not
> care who triggers it.
>
> Materialized views are anyway just built-in implementation of tables +
> triggers to rerun the query. You could reconstruct them manually. And
> why would not triggers be called if tables is being modified through
> INSERTs? So if PostgreSQL has such a feature, why make it limited and
> artificially make it less powerful? It is literally not possible to
> have triggers only because there is "if trigger on a materialized
> view, throw an exception".
>
> > Triggers should generally refer to user-facing commands
>
> So triggers on table A are not run when some other trigger from table
> B decides to insert data into table A? Not true. I think triggers have
> never cared who and where an INSERT came from. They just trigger. From
> user, from another trigger, or from some built-in PostgreSQL procedure
> called REFRESH.
>
> > Could you not make a trigger on REFRESH itself?
>
> If you mean if I could simulate this somehow before or after I call
> REFRESH, then not really. I would not have access to previous state of
> the table to compute the diff anymore. Moreover, I would have to
> recompute the diff again, when REFRESH already did it once.
>
> I could implement materialized views myself using regular tables and
> triggers. And then have triggers after change on that table. But this
> sounds very sad.
>
> Or, are you saying that we should introduce a whole new type of of
> trigger, REFRESH trigger, which would be valid only on materialized
> views, and get OLD and NEW relations for previous and old state? I
> think this could be an option, but it would require much more work,
> and more changes to API. Is this what community would prefer?
>
> > This is also a problem, because it would allow bypassing the trigger
> > accidentally.
>
> Sure, this is why it is useful to explain that CONCURRENT REFRESH uses
> INSERT/UPDATE/DELETE and this is why you get triggers, and REFRESH
> does not (but it is faster). I explained this in documentation.
>
> But yes, this is downside. I checked the idea of calling row-level
> triggers after regular REFRESH, but it seems it will introduce a lot
> of overhead and special handling. I tried implementing it as TRUNCATE
> + INSERTS instead of heap swap and it is 2x slower.
>
> > Moreover, consider that there could be updatable materialized views,
> > just like there are updatable normal views. And there could be triggers
> > on those updatable materialized views. Those would look similar but
> > work quite differently from what you are proposing here.
>
> Hm, not really. I would claim they would behave exactly the same.
> AFTER trigger on INSERT on a materialized view would trigger for rows
> which have changed through user updating materialized view directly,
> or by calling CONCURRENT REFRESH which inserted a row. In both cases
> the same trigger would run because materialized view had a row
> inserted. Pretty nice.
>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
>
>