Re: Feature: triggers on materialized views

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