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
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index fb0de60a45..aacfa2338a 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -197,7 +197,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
* Triggers must be on tables or views, and there are additional
* relation-type-specific restrictions.
*/
- if (rel->rd_rel->relkind == RELKIND_RELATION)
+ if (rel->rd_rel->relkind == RELKIND_RELATION || rel->rd_rel->relkind == RELKIND_MATVIEW)
{
/* Tables can't have INSTEAD OF triggers */
if (stmt->timing != TRIGGER_TYPE_BEFORE &&
@@ -307,7 +307,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
else
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a table or view",
+ errmsg("\"%s\" is not a table, view, or materialized view",
RelationGetRelationName(rel))));
if (!allowSystemTableMods && IsSystemRelation(rel))
@@ -1513,11 +1513,12 @@ RemoveTriggerById(Oid trigOid)
if (rel->rd_rel->relkind != RELKIND_RELATION &&
rel->rd_rel->relkind != RELKIND_VIEW &&
+ rel->rd_rel->relkind != RELKIND_MATVIEW &&
rel->rd_rel->relkind != RELKIND_FOREIGN_TABLE &&
rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a table, view, or foreign table",
+ errmsg("\"%s\" is not a table, view, materialized view, or foreign table",
RelationGetRelationName(rel))));
if (!allowSystemTableMods && IsSystemRelation(rel))
@@ -1619,11 +1620,12 @@ RangeVarCallbackForRenameTrigger(const RangeVar *rv, Oid relid, Oid oldrelid,
/* only tables and views can have triggers */
if (form->relkind != RELKIND_RELATION && form->relkind != RELKIND_VIEW &&
+ form->relkind != RELKIND_MATVIEW &&
form->relkind != RELKIND_FOREIGN_TABLE &&
form->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("\"%s\" is not a table, view, or foreign table",
+ errmsg("\"%s\" is not a table, view, materialized view, or foreign table",
rv->relname)));
/* you must own the table to rename one of its triggers */
