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
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 6514ffc6ae..936476a558 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -49,8 +49,8 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable>
<para>
<command>CREATE TRIGGER</command> creates a new trigger. The
- trigger will be associated with the specified table, view, or foreign table
- and will execute the specified
+ trigger will be associated with the specified table, view, materialized view,
+ or foreign table and will execute the specified
function <replaceable class="parameter">function_name</replaceable> when
certain operations are performed on that table.
</para>
@@ -92,6 +92,12 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable>
must be marked as <literal>FOR EACH STATEMENT</literal>.
</para>
+ <para>
+ <literal>AFTER</literal> triggers can be specified on materialized views and
+ are fired after every <command>REFRESH MATERIALIZED VIEW CONCURRENTLY</command>
+ on the materialized view.
+ </para>
+
<para>
In addition, triggers may be defined to fire for
<command>TRUNCATE</command>, though only
@@ -100,7 +106,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable>
<para>
The following table summarizes which types of triggers may be used on
- tables, views, and foreign tables:
+ tables, views, materialized views, and foreign tables:
</para>
<informaltable id="supported-trigger-types">
@@ -128,8 +134,8 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable>
<row>
<entry align="center" morerows="1"><literal>AFTER</literal></entry>
<entry align="center"><command>INSERT</command>/<command>UPDATE</command>/<command>DELETE</command></entry>
- <entry align="center">Tables and foreign tables</entry>
- <entry align="center">Tables, views, and foreign tables</entry>
+ <entry align="center">Tables, materialized views, and foreign tables</entry>
+ <entry align="center">Tables, views, materialized views, and foreign tables</entry>
</row>
<row>
<entry align="center"><command>TRUNCATE</command></entry>
@@ -276,8 +282,8 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
- The name (optionally schema-qualified) of the table, view, or foreign
- table the trigger is for.
+ The name (optionally schema-qualified) of the table, view, materialized view,
+ or foreign table the trigger is for.
</para>
</listitem>
</varlistentry>
@@ -520,6 +526,17 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
the ones that are fired.
</para>
+ <para>
+ Triggers on materialized views are fired only when
+ <command>REFRESH MATERIALIZED VIEW</command> is used with the
+ <literal>CONCURRENTLY</literal> option. Such refresh computes
+ a diff between old and new data and executes <command>INSERT</command>,
+ <command>UPDATE</command>, and <command>DELETE</command> queries to
+ update the materialized view to new data.
+ Refresh without this option refreshes all data at once and does not
+ fire any triggers.
+ </para>
+
<para>
Creating a row-level trigger on a partitioned table will cause identical
triggers to be created in all its existing partitions; and any partitions
@@ -730,6 +747,11 @@ CREATE TRIGGER paired_items_update
standard.
</para>
+ <para>
+ The ability to define triggers for materialized views is a PostgreSQL
+ extension of the SQL standard.
+ </para>
+
</refsect1>
<refsect1>
diff --git a/doc/src/sgml/ref/refresh_materialized_view.sgml b/doc/src/sgml/ref/refresh_materialized_view.sgml
index fd06f1fda1..b0c85cb3b9 100644
--- a/doc/src/sgml/ref/refresh_materialized_view.sgml
+++ b/doc/src/sgml/ref/refresh_materialized_view.sgml
@@ -66,10 +66,6 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
column names and includes all rows; that is, it must not index on any
expressions nor include a <literal>WHERE</literal> clause.
</para>
- <para>
- This option may not be used when the materialized view is not already
- populated.
- </para>
<para>
Even with this option only one <literal>REFRESH</literal> at a time may
run against any one materialized view.
@@ -100,6 +96,14 @@ REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="parameter">name</
to be ordered upon generation, you must use an <literal>ORDER BY</literal>
clause in the backing query.
</para>
+
+ <para>
+ Refresh with the <literal>CONCURRENTLY</literal> option fires
+ any <literal>INSERT</literal>, <literal>UPDATE</literal>, and
+ <literal>DELETE</literal> triggers defined on the materialized view.
+ Refresh without this option refreshes all data at once and does not
+ fire any triggers.
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index a171ebabf8..feaff32cc0 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -172,12 +172,6 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errmsg("\"%s\" is not a materialized view",
RelationGetRelationName(matviewRel))));
- /* Check that CONCURRENTLY is not specified if not populated. */
- if (concurrent && !RelationIsPopulated(matviewRel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("CONCURRENTLY cannot be used when the materialized view is not populated")));
-
/* Check that conflicting options have not been specified. */
if (concurrent && stmt->skipData)
ereport(ERROR,
@@ -565,9 +559,11 @@ make_temptable_name_n(char *tempname, int n)
* the old record (if matched) and the ROW from the new table as a single
* column of complex record type (if matched).
*
- * Once we have the diff table, we perform set-based DELETE and INSERT
+ * Once we have the diff table, we perform set-based DELETE, UPDATE, and INSERT
* operations against the materialized view, and discard both temporary
- * tables.
+ * tables. We do all of those operations so that any triggers called because
+ * of these operations represent reasonable calls one would expect to see when
+ * syncing the materialized view to new data.
*
* Everything from the generation of the new data to applying the differences
* takes place under cover of an ExclusiveLock, since it seems as though we
@@ -590,6 +586,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
bool foundUniqueIndex;
List *indexoidlist;
ListCell *indexoidscan;
+ AttrNumber relattno;
int16 relnatts;
Oid *opUsedForQual;
@@ -779,8 +776,9 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
Assert(foundUniqueIndex);
appendStringInfoString(&querybuf,
- " AND newdata OPERATOR(pg_catalog.*=) mv) "
+ ") "
"WHERE newdata IS NULL OR mv IS NULL "
+ "OR newdata OPERATOR(pg_catalog.*<>) mv "
"ORDER BY tid");
/* Create the temporary "diff" table. */
@@ -803,7 +801,7 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
OpenMatViewIncrementalMaintenance();
- /* Deletes must come before inserts; do them first. */
+ /* We do deletes first. */
resetStringInfo(&querybuf);
appendStringInfo(&querybuf,
"DELETE FROM %s mv WHERE ctid OPERATOR(pg_catalog.=) ANY "
@@ -814,7 +812,38 @@ refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
elog(ERROR, "SPI_exec failed: %s", querybuf.data);
- /* Inserts go last. */
+ /* Then we do updates. */
+ resetStringInfo(&querybuf);
+ appendStringInfo(&querybuf, "UPDATE %s mv SET (", matviewname);
+
+ for (relattno = 1; relattno <= relnatts; relattno++)
+ {
+ Form_pg_attribute attribute = TupleDescAttr(tupdesc, relattno - 1);
+ char *attributeName = NameStr(attribute->attname);
+
+ /* Ignore dropped */
+ if (attribute->attisdropped)
+ continue;
+
+ if (relattno == 1)
+ {
+ appendStringInfo(&querybuf, "%s", quote_identifier(attributeName));
+ }
+ else
+ {
+ appendStringInfo(&querybuf, ", %s", quote_identifier(attributeName));
+ }
+ }
+
+ appendStringInfo(&querybuf,
+ ") = ROW((diff.newdata).*) FROM %s diff "
+ "WHERE diff.tid IS NOT NULL AND diff.newdata IS NOT NULL "
+ "AND mv.ctid OPERATOR(pg_catalog.=) diff.tid",
+ diffname);
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_UPDATE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+
+ /* Inserts and updates go last. */
resetStringInfo(&querybuf);
appendStringInfo(&querybuf,
"INSERT INTO %s SELECT (diff.newdata).* "
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index fb0de60a45..8597def50a 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -208,6 +208,16 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
RelationGetRelationName(rel)),
errdetail("Tables cannot have INSTEAD OF triggers.")));
}
+ else if (rel->rd_rel->relkind == RELKIND_MATVIEW)
+ {
+ /* Materialized views can have only AFTER triggers */
+ if (stmt->timing != TRIGGER_TYPE_AFTER)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is a materialized view",
+ RelationGetRelationName(rel)),
+ errdetail("Materialized views can have only AFTER triggers.")));
+ }
else if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
/* Partitioned tables can't have INSTEAD OF triggers */
@@ -307,7 +317,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 +1523,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 +1630,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 */
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index 08cd4bea48..17f2029169 100644
--- a/src/test/regress/expected/matview.out
+++ b/src/test/regress/expected/matview.out
@@ -564,6 +564,104 @@ REFRESH MATERIALIZED VIEW mvtest_mv_foo;
REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo;
DROP OWNED BY regress_user_mvtest CASCADE;
DROP ROLE regress_user_mvtest;
+-- create a new test table
+CREATE TABLE mvtest_t2 (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
+INSERT INTO mvtest_t2 VALUES
+ (1, 'x', 2),
+ (2, 'x', 3),
+ (3, 'y', 5),
+ (4, 'y', 7),
+ (5, 'z', 11);
+-- define trigger functions
+CREATE OR REPLACE FUNCTION notify_changes() RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+message TEXT := TG_ARGV[0];
+BEGIN
+ IF (TG_OP = 'DELETE') THEN
+ RAISE NOTICE 'DELETE %', concat_ws(' ', message, (SELECT array_to_json(array_agg(old_table)) FROM old_table));
+ ELSIF (TG_OP = 'TRUNCATE') THEN
+ RAISE NOTICE 'TRUNCATE %', message;
+ ELSIF (TG_OP = 'UPDATE') THEN
+ RAISE NOTICE 'UPDATE %', concat_ws(' ', message, (SELECT array_to_json(array_agg(new_table)) FROM new_table), (SELECT array_to_json(array_agg(old_table)) FROM old_table));
+ ELSIF (TG_OP = 'INSERT') THEN
+ RAISE NOTICE 'INSERT %', concat_ws(' ', message, (SELECT array_to_json(array_agg(new_table)) FROM new_table));
+ END IF;
+RETURN NULL;
+END
+$$;
+CREATE OR REPLACE FUNCTION notify_changes_row() RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+message TEXT := TG_ARGV[0];
+BEGIN
+ IF (TG_OP = 'DELETE') THEN
+ RAISE NOTICE 'DELETE %', concat_ws(' ', message, row_to_json(OLD));
+ ELSIF (TG_OP = 'UPDATE') THEN
+ RAISE NOTICE 'UPDATE %', concat_ws(' ', message, row_to_json(NEW), row_to_json(OLD));
+ ELSIF (TG_OP = 'INSERT') THEN
+ RAISE NOTICE 'INSERT %', concat_ws(' ', message, row_to_json(NEW));
+ END IF;
+RETURN NULL;
+END
+$$;
+-- create materialized view WITH NO DATA
+CREATE MATERIALIZED VIEW mvtest_t2_no_data_view AS SELECT * FROM mvtest_t2 WITH NO DATA;
+CREATE UNIQUE INDEX mvtest_t2_no_data_view_id ON mvtest_t2_no_data_view (id);
+-- register triggers
+CREATE TRIGGER mvtest_t2_no_data_view_insert AFTER INSERT ON mvtest_t2_no_data_view REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_insert');
+CREATE TRIGGER mvtest_t2_no_data_view_update AFTER UPDATE ON mvtest_t2_no_data_view REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_update');
+CREATE TRIGGER mvtest_t2_no_data_view_delete AFTER DELETE ON mvtest_t2_no_data_view REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_delete');
+CREATE TRIGGER mvtest_t2_no_data_view_truncate AFTER TRUNCATE ON mvtest_t2_no_data_view FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_truncate');
+CREATE TRIGGER mvtest_t2_no_data_view_insert_row AFTER INSERT ON mvtest_t2_no_data_view FOR EACH ROW EXECUTE FUNCTION notify_changes_row('mvtest_t2_no_data_view_insert_row');
+CREATE TRIGGER mvtest_t2_no_data_view_update_row AFTER UPDATE ON mvtest_t2_no_data_view FOR EACH ROW EXECUTE FUNCTION notify_changes_row('mvtest_t2_no_data_view_update_row');
+CREATE TRIGGER mvtest_t2_no_data_view_delete_row AFTER DELETE ON mvtest_t2_no_data_view FOR EACH ROW EXECUTE FUNCTION notify_changes_row('mvtest_t2_no_data_view_delete_row');
+-- try to select from view without data, it should error
+SELECT * FROM mvtest_t2_no_data_view;
+ERROR: materialized view "mvtest_t2_no_data_view" has not been populated
+HINT: Use the REFRESH MATERIALIZED VIEW command.
+-- we should be able to do initial REFRESH MATERIALIZED VIEW CONCURRENTLY and triggers should be called
+REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_t2_no_data_view;
+NOTICE: DELETE mvtest_t2_no_data_view_delete
+NOTICE: UPDATE mvtest_t2_no_data_view_update
+NOTICE: INSERT mvtest_t2_no_data_view_insert_row {"id":2,"type":"x","amt":3}
+NOTICE: INSERT mvtest_t2_no_data_view_insert_row {"id":5,"type":"z","amt":11}
+NOTICE: INSERT mvtest_t2_no_data_view_insert_row {"id":4,"type":"y","amt":7}
+NOTICE: INSERT mvtest_t2_no_data_view_insert_row {"id":1,"type":"x","amt":2}
+NOTICE: INSERT mvtest_t2_no_data_view_insert_row {"id":3,"type":"y","amt":5}
+NOTICE: INSERT mvtest_t2_no_data_view_insert [{"id":2,"type":"x","amt":3},{"id":5,"type":"z","amt":11},{"id":4,"type":"y","amt":7},{"id":1,"type":"x","amt":2},{"id":3,"type":"y","amt":5}]
+-- now materialized view should have data
+SELECT * FROM mvtest_t2_no_data_view ORDER BY id;
+ id | type | amt
+----+------+-----
+ 1 | x | 2
+ 2 | x | 3
+ 3 | y | 5
+ 4 | y | 7
+ 5 | z | 11
+(5 rows)
+
+-- update the original table
+INSERT INTO mvtest_t2 VALUES (7, 'k', 10);
+DELETE FROM mvtest_t2 WHERE id=2;
+UPDATE mvtest_t2 SET amt=5 WHERE id=4;
+-- refresh
+REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_t2_no_data_view;
+NOTICE: DELETE mvtest_t2_no_data_view_delete_row {"id":2,"type":"x","amt":3}
+NOTICE: DELETE mvtest_t2_no_data_view_delete [{"id":2,"type":"x","amt":3}]
+NOTICE: UPDATE mvtest_t2_no_data_view_update_row {"id":4,"type":"y","amt":5} {"id":4,"type":"y","amt":7}
+NOTICE: UPDATE mvtest_t2_no_data_view_update [{"id":4,"type":"y","amt":5}] [{"id":4,"type":"y","amt":7}]
+NOTICE: INSERT mvtest_t2_no_data_view_insert_row {"id":7,"type":"k","amt":10}
+NOTICE: INSERT mvtest_t2_no_data_view_insert [{"id":7,"type":"k","amt":10}]
+-- materialized view should have updated data
+SELECT * FROM mvtest_t2_no_data_view ORDER BY id;
+ id | type | amt
+----+------+-----
+ 1 | x | 2
+ 3 | y | 5
+ 4 | y | 5
+ 5 | z | 11
+ 7 | k | 10
+(5 rows)
+
-- make sure that create WITH NO DATA works via SPI
BEGIN;
CREATE FUNCTION mvtest_func()
diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql
index d96175aa26..57db42f6cd 100644
--- a/src/test/regress/sql/matview.sql
+++ b/src/test/regress/sql/matview.sql
@@ -223,6 +223,80 @@ REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo;
DROP OWNED BY regress_user_mvtest CASCADE;
DROP ROLE regress_user_mvtest;
+-- create a new test table
+CREATE TABLE mvtest_t2 (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
+INSERT INTO mvtest_t2 VALUES
+ (1, 'x', 2),
+ (2, 'x', 3),
+ (3, 'y', 5),
+ (4, 'y', 7),
+ (5, 'z', 11);
+
+-- define trigger functions
+CREATE OR REPLACE FUNCTION notify_changes() RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+message TEXT := TG_ARGV[0];
+BEGIN
+ IF (TG_OP = 'DELETE') THEN
+ RAISE NOTICE 'DELETE %', concat_ws(' ', message, (SELECT array_to_json(array_agg(old_table)) FROM old_table));
+ ELSIF (TG_OP = 'TRUNCATE') THEN
+ RAISE NOTICE 'TRUNCATE %', message;
+ ELSIF (TG_OP = 'UPDATE') THEN
+ RAISE NOTICE 'UPDATE %', concat_ws(' ', message, (SELECT array_to_json(array_agg(new_table)) FROM new_table), (SELECT array_to_json(array_agg(old_table)) FROM old_table));
+ ELSIF (TG_OP = 'INSERT') THEN
+ RAISE NOTICE 'INSERT %', concat_ws(' ', message, (SELECT array_to_json(array_agg(new_table)) FROM new_table));
+ END IF;
+RETURN NULL;
+END
+$$;
+CREATE OR REPLACE FUNCTION notify_changes_row() RETURNS TRIGGER LANGUAGE plpgsql AS $$
+DECLARE
+message TEXT := TG_ARGV[0];
+BEGIN
+ IF (TG_OP = 'DELETE') THEN
+ RAISE NOTICE 'DELETE %', concat_ws(' ', message, row_to_json(OLD));
+ ELSIF (TG_OP = 'UPDATE') THEN
+ RAISE NOTICE 'UPDATE %', concat_ws(' ', message, row_to_json(NEW), row_to_json(OLD));
+ ELSIF (TG_OP = 'INSERT') THEN
+ RAISE NOTICE 'INSERT %', concat_ws(' ', message, row_to_json(NEW));
+ END IF;
+RETURN NULL;
+END
+$$;
+
+-- create materialized view WITH NO DATA
+CREATE MATERIALIZED VIEW mvtest_t2_no_data_view AS SELECT * FROM mvtest_t2 WITH NO DATA;
+CREATE UNIQUE INDEX mvtest_t2_no_data_view_id ON mvtest_t2_no_data_view (id);
+
+-- register triggers
+CREATE TRIGGER mvtest_t2_no_data_view_insert AFTER INSERT ON mvtest_t2_no_data_view REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_insert');
+CREATE TRIGGER mvtest_t2_no_data_view_update AFTER UPDATE ON mvtest_t2_no_data_view REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_update');
+CREATE TRIGGER mvtest_t2_no_data_view_delete AFTER DELETE ON mvtest_t2_no_data_view REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_delete');
+CREATE TRIGGER mvtest_t2_no_data_view_truncate AFTER TRUNCATE ON mvtest_t2_no_data_view FOR EACH STATEMENT EXECUTE FUNCTION notify_changes('mvtest_t2_no_data_view_truncate');
+CREATE TRIGGER mvtest_t2_no_data_view_insert_row AFTER INSERT ON mvtest_t2_no_data_view FOR EACH ROW EXECUTE FUNCTION notify_changes_row('mvtest_t2_no_data_view_insert_row');
+CREATE TRIGGER mvtest_t2_no_data_view_update_row AFTER UPDATE ON mvtest_t2_no_data_view FOR EACH ROW EXECUTE FUNCTION notify_changes_row('mvtest_t2_no_data_view_update_row');
+CREATE TRIGGER mvtest_t2_no_data_view_delete_row AFTER DELETE ON mvtest_t2_no_data_view FOR EACH ROW EXECUTE FUNCTION notify_changes_row('mvtest_t2_no_data_view_delete_row');
+
+-- try to select from view without data, it should error
+SELECT * FROM mvtest_t2_no_data_view;
+
+-- we should be able to do initial REFRESH MATERIALIZED VIEW CONCURRENTLY and triggers should be called
+REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_t2_no_data_view;
+
+-- now materialized view should have data
+SELECT * FROM mvtest_t2_no_data_view ORDER BY id;
+
+-- update the original table
+INSERT INTO mvtest_t2 VALUES (7, 'k', 10);
+DELETE FROM mvtest_t2 WHERE id=2;
+UPDATE mvtest_t2 SET amt=5 WHERE id=4;
+
+-- refresh
+REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_t2_no_data_view;
+
+-- materialized view should have updated data
+SELECT * FROM mvtest_t2_no_data_view ORDER BY id;
+
-- make sure that create WITH NO DATA works via SPI
BEGIN;
CREATE FUNCTION mvtest_func()
bench.sql
Description: application/sql
