Hi! Hm, why in commitfest it does not display the latest patch?
https://commitfest.postgresql.org/21/1953/ It does display correctly the latest e-mail, but not the link to the patch. :-( Mitar On Thu, Dec 27, 2018 at 11:51 PM Mitar <mmi...@gmail.com> wrote: > > Hi! > > One more version of the patch with slightly more deterministic tests. > > > Mitar > > On Thu, Dec 27, 2018 at 11:43 PM Mitar <mmi...@gmail.com> wrote: > > > > Hi! > > > > I have made an updated version of the patch, added tests and > > documentation changes. This is my view now a complete patch. Please > > provide any feedback or comments you might have for me to improve the > > patch. I will also add it to commitfest. > > > > 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. > > Triggers are not fired if you call REFRESH without CONCURRENTLY. This > > is based on some discussion on the mailing list because implementing > > it for without CONCURRENTLY would require us to add logic for firing > > triggers where there was none before (and is just an efficient heap > > swap). > > > > To be able to create a materialized view without data, specify > > triggers, and REFRESH CONCURRENTLY so that those triggers would be > > called also for initial data, I have tested and determined that there > > is no reason why REFRESH CONCURRENTLY could not be run on > > uninitialized materialized view. So I removed that check and things > > seem to just work. Including triggers being called for initial data. I > > think this makes REFRESH CONCURRENTLY have one less special case which > > is in general nicer. > > > > I have run tests and all old tests still succeed. I have added more > > tests for the new feature. > > > > I have run benchmark to evaluate the impact of me changing > > refresh_by_match_merge to do UPDATE instead of DELETE and INSERT for > > rows which were just updated. In fact it seems this improves > > performance slightly (4% in my benchmark, mean over 10 runs). I guess > > that this is because it is cheaper to just change one column's values > > (what benchmark is doing when changing rows) instead of removing and > > inserting the whole row. Because REFRESH MATERIALIZED VIEW > > CONCURRENTLY is meant to be used when not a lot of data has been > > changed anyway, I find this a pleasantly surprising additional > > improvement in this patch. I am attaching the benchmark script I have > > used. I compared the time of the final refresh query in the script. (I > > would love if pgbench could take a custom init script to run before > > the timed part of the script.) > > > > > > Mitar > > > > On Mon, Dec 24, 2018 at 12:59 PM Mitar <mmi...@gmail.com> wrote: > > > > > > Hi! > > > > > > Based on discussion about observing changes on an open query in a > > > reactive manner (to support reactive web applications) [1], I > > > identified that one critical feature is missing to fully implement > > > discussed design of having reactive queries be represented as > > > materialized views, and changes to these materialized views would then > > > be observed and pushed to the client through LISTEN/NOTIFY. > > > > > > This is my first time contributing to PostgreSQL, so I hope I am > > > starting this process well. > > > > > > I would like to propose that support for AFTER triggers are added to > > > materialized views. I experimented a bit and it seems this is mostly > > > just a question of enabling/exposing them. See attached patch. This > > > enabled me to add trigger to a material view which mostly worked. Here > > > are my findings. > > > > > > Running REFRESH MATERIALIZED VIEW CONCURRENTLY calls triggers. Both > > > per statement and per row. There are few improvements which could be > > > done: > > > > > > - 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. > > > - In current concurrently refresh logic those insert and remove > > > operations are made even if there are no changes to be done. Which > > > triggers a statement trigger unnecessary. A small improvement could be > > > to skip the statement in that case, but looking at the code this seems > > > maybe tricky because both each of inserts and deletions are done > > > inside one query each. > > > - Current concurrently refresh logic does never do updates on existing > > > rows. It would be nicer to have that so that triggers are more aligned > > > with real changes to the data. So current two queries could be changed > > > to three, each doing one of the insert, update, and delete. > > > > > > 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). > > > > > > [1] > > > https://www.postgresql.org/message-id/flat/CAKLmikP%2BPPB49z8rEEvRjFOD0D2DV72KdqYN7s9fjh9sM_32ZA%40mail.gmail.com > > > > > > > > > Mitar > > > > > > -- > > > http://mitar.tnode.com/ > > > https://twitter.com/mitar_m > > > > > > > > -- > > http://mitar.tnode.com/ > > https://twitter.com/mitar_m > > > > -- > http://mitar.tnode.com/ > https://twitter.com/mitar_m -- http://mitar.tnode.com/ https://twitter.com/mitar_m
