Feature: triggers on materialized views - Mailing list pgsql-hackers

From Mitar
Subject Feature: triggers on materialized views
Date
Msg-id CAKLmikMN+0W79A7Wg4krK55cTLxb=TARdz2b=vqp19ao6AAZjw@mail.gmail.com
Whole thread Raw
Responses Re: Feature: triggers on materialized views
Re: Feature: triggers on materialized views
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: Move regression.diffs of pg_upgrade test suite
Next
From: Alexander Korotkov
Date:
Subject: Re: GIN predicate locking slows down valgrind isolationtests tremendously