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
