Hi!
One more version of the patch with slightly more deterministic tests.
Mitar
On Thu, Dec 27, 2018 at 11:43 PM Mitar <[email protected]> wrote:
>
> 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 <[email protected]> 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
--
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..944d608251 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 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 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 ORDER BY id 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..d150ce3824 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 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 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 ORDER BY id 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()
