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 <mmi...@gmail.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.eisentr...@2ndquadrant.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 > >
