On Mon, Dec 24, 2018 at 12:59:43PM -0800, Mitar 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.
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! > 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. About that. When there's a change (or possible change) in user-visible behavior, it should come with regression tests, which it would make sense to add to src/tests/regress/matview.sql along with the corresponding changes to src/tests/regress/expected/matview.out > 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. You'd want at least one test for each of those new features. > 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. What other operations might you want to support? > - 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. As far as you can tell, is this just an efficiency optimization, or might it go to correctness of the behavior? > - 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. I'm not sure I understand the problem being described here. Do you see these as useful to separate for some reason? > 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? Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
