From: Yugo Nagata <nagata@sraoss.co.jp>
Subject: [PATCH v37 10/11] Add regression tests for Incremental View Maintenance
Date: Fri, 29 May 2026 18:31:23 +0900
---
src/test/isolation/isolation_schedule | 9 +
.../regress/expected/incremental_matview.out | 1100 +++++++++++++++++
src/test/regress/parallel_schedule | 1 +
src/test/regress/sql/incremental_matview.sql | 580 +++++++++
4 files changed, 1690 insertions(+)
create mode 100644 src/test/regress/expected/incremental_matview.out
create mode 100644 src/test/regress/sql/incremental_matview.sql
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_scheduleindex 15c33fad4c5..111fd465207 100644--- a/src/test/isolation/isolation_schedule+++ b/src/test/isolation/isolation_schedule@@ -127,3 +127,12 @@ test: matview-write-skew
test: lock-nowait
test: for-portion-of
test: ddl-dependency-locking
+test: ivm-create-insert+test: ivm-create-insert2+test: ivm-create-insert3+test: ivm-insert-insert+test: ivm-insert-insert2+test: ivm-insert-insert3+test: ivm-refresh-insert+test: ivm-refresh-insert2+test: ivm-refresh-insert3diff --git a/src/test/regress/expected/incremental_matview.out b/src/test/regress/expected/incremental_matview.outnew file mode 100644index 00000000000..f215aa88e48--- /dev/null+++ b/src/test/regress/expected/incremental_matview.out@@ -0,0 +1,1100 @@+-- create a table to use as a basis for views and materialized views in various combinations+CREATE TABLE mv_base_a (x int, i int, y int, j int);+CREATE TABLE mv_base_b (x int, i int, y int, k int);+-- test for base tables with dropped columns+ALTER TABLE mv_base_a DROP COLUMN x;+ALTER TABLE mv_base_a DROP COLUMN y;+ALTER TABLE mv_base_b DROP COLUMN x;+ALTER TABLE mv_base_b DROP COLUMN y;+INSERT INTO mv_base_a VALUES+ (1,10),+ (2,20),+ (3,30),+ (4,40),+ (5,50);+INSERT INTO mv_base_b VALUES+ (1,101),+ (2,102),+ (3,103),+ (4,104);+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ERROR: materialized view "mv_ivm_1" has not been populated+HINT: Use the REFRESH MATERIALIZED VIEW command.+REFRESH MATERIALIZED VIEW mv_ivm_1;+NOTICE: could not create an index on materialized view "mv_ivm_1" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ i | j | k +---+----+-----+ 1 | 10 | 101+ 2 | 20 | 102+ 3 | 30 | 103+ 4 | 40 | 104+(4 rows)++-- REFRESH WITH NO DATA+BEGIN;+CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$+ BEGIN+ RETURN NULL;+ END+$$ language plpgsql;+CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT+ON mv_base_a FROM mv_ivm_1 FOR EACH ROW+EXECUTE PROCEDURE dummy_ivm_trigger_func();+SELECT COUNT(*)+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;+ count +-------+ 17+(1 row)++REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA;+SELECT COUNT(*)+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;+ count +-------+ 1+(1 row)++ROLLBACK;+-- immediate maintenance+BEGIN;+INSERT INTO mv_base_b VALUES(5,105);+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ i | j | k +---+----+-----+ 1 | 10 | 101+ 2 | 20 | 102+ 3 | 30 | 103+ 4 | 40 | 104+ 5 | 50 | 105+(5 rows)++UPDATE mv_base_a SET j = 0 WHERE i = 1;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ i | j | k +---+----+-----+ 1 | 0 | 101+ 2 | 20 | 102+ 3 | 30 | 103+ 4 | 40 | 104+ 5 | 50 | 105+(5 rows)++DELETE FROM mv_base_b WHERE (i,k) = (5,105);+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ i | j | k +---+----+-----+ 1 | 0 | 101+ 2 | 20 | 102+ 3 | 30 | 103+ 4 | 40 | 104+(4 rows)++ROLLBACK;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ i | j | k +---+----+-----+ 1 | 10 | 101+ 2 | 20 | 102+ 3 | 30 | 103+ 4 | 40 | 104+(4 rows)++-- rename of IVM columns+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a;+NOTICE: created index "mv_ivm_rename_index" on materialized view "mv_ivm_rename"+ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx;+ERROR: IVM column can not be renamed+DROP MATERIALIZED VIEW mv_ivm_rename;+-- unique index on IVM columns+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a;+NOTICE: created index "mv_ivm_unique_index" on materialized view "mv_ivm_unique"+CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__);+ERROR: unique index creation on IVM columns is not supported+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__));+ERROR: unique index creation on IVM columns is not supported+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1));+ERROR: unique index creation on IVM columns is not supported+DROP MATERIALIZED VIEW mv_ivm_unique;+-- test for renaming column name to camel style+BEGIN;+ALTER TABLE mv_base_a RENAME i TO "I";+ALTER TABLE mv_base_a RENAME j TO "J";+UPDATE mv_base_a SET "J" = 0 WHERE "I" = 1;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ i | j | k +---+----+-----+ 1 | 0 | 101+ 2 | 20 | 102+ 3 | 30 | 103+ 4 | 40 | 104+(4 rows)++ROLLBACK;+-- TRUNCATE a base table in join views+BEGIN;+TRUNCATE mv_base_a;+SELECT * FROM mv_ivm_1;+ i | j | k +---+---+---+(0 rows)++ROLLBACK;+BEGIN;+TRUNCATE mv_base_b;+SELECT * FROM mv_ivm_1;+ i | j | k +---+---+---+(0 rows)++ROLLBACK;+-- some query syntax+BEGIN;+CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql'+ AS 'SELECT 1' IMMUTABLE;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func();+NOTICE: could not create an index on materialized view "mv_ivm_func" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1;+NOTICE: could not create an index on materialized view "mv_ivm_no_tbl" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+ROLLBACK;+-- result of materialized view have DISTINCT clause or the duplicate result.+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a;+NOTICE: could not create an index on materialized view "mv_ivm_duplicate" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a;+NOTICE: created index "mv_ivm_distinct_index" on materialized view "mv_ivm_distinct"+INSERT INTO mv_base_a VALUES(6,20);+SELECT * FROM mv_ivm_duplicate ORDER BY 1;+ j +----+ 10+ 20+ 20+ 30+ 40+ 50+(6 rows)++SELECT * FROM mv_ivm_distinct ORDER BY 1;+ j +----+ 10+ 20+ 30+ 40+ 50+(5 rows)++DELETE FROM mv_base_a WHERE (i,j) = (2,20);+SELECT * FROM mv_ivm_duplicate ORDER BY 1;+ j +----+ 10+ 20+ 30+ 40+ 50+(5 rows)++SELECT * FROM mv_ivm_distinct ORDER BY 1;+ j +----+ 10+ 20+ 30+ 40+ 50+(5 rows)++ROLLBACK;+-- support SUM(), COUNT() and AVG() aggregate functions+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+ i | sum | count | avg +---+-----+-------+---------------------+ 1 | 10 | 1 | 10.0000000000000000+ 2 | 20 | 1 | 20.0000000000000000+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++INSERT INTO mv_base_a VALUES(2,100);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+ i | sum | count | avg +---+-----+-------+---------------------+ 1 | 10 | 1 | 10.0000000000000000+ 2 | 120 | 2 | 60.0000000000000000+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+ i | sum | count | avg +---+-----+-------+----------------------+ 1 | 10 | 1 | 10.0000000000000000+ 2 | 220 | 2 | 110.0000000000000000+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++DELETE FROM mv_base_a WHERE (i,j) = (2,200);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+ i | sum | count | avg +---+-----+-------+---------------------+ 1 | 10 | 1 | 10.0000000000000000+ 2 | 20 | 1 | 20.0000000000000000+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++ROLLBACK;+-- support COUNT(*) aggregate function+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;+ i | sum | count +---+-----+-------+ 1 | 10 | 1+ 2 | 20 | 1+ 3 | 30 | 1+ 4 | 40 | 1+ 5 | 50 | 1+(5 rows)++INSERT INTO mv_base_a VALUES(2,100);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;+ i | sum | count +---+-----+-------+ 1 | 10 | 1+ 2 | 120 | 2+ 3 | 30 | 1+ 4 | 40 | 1+ 5 | 50 | 1+(5 rows)++ROLLBACK;+-- TRUNCATE a base table in aggregate views+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"+TRUNCATE mv_base_a;+SELECT sum, count FROM mv_ivm_agg;+ sum | count +-----+-------+(0 rows)++SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;+ i | sum | count +---+-----+-------+(0 rows)++ROLLBACK;+-- support aggregate functions without GROUP clause+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;+NOTICE: could not create an index on materialized view "mv_ivm_group" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv_ivm_group ORDER BY 1;+ sum | count | avg +-----+-------+---------------------+ 150 | 5 | 30.0000000000000000+(1 row)++INSERT INTO mv_base_a VALUES(6,60);+SELECT * FROM mv_ivm_group ORDER BY 1;+ sum | count | avg +-----+-------+---------------------+ 210 | 6 | 35.0000000000000000+(1 row)++DELETE FROM mv_base_a;+SELECT * FROM mv_ivm_group ORDER BY 1;+ sum | count | avg +-----+-------+-----+ | 0 | +(1 row)++ROLLBACK;+-- TRUNCATE a base table in aggregate views without GROUP clause+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;+NOTICE: could not create an index on materialized view "mv_ivm_group" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+TRUNCATE mv_base_a;+SELECT sum, count, avg FROM mv_ivm_group;+ sum | count | avg +-----+-------+-----+ | 0 | +(1 row)++SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;+ sum | count | avg +-----+-------+-----+ | 0 | +(1 row)++ROLLBACK;+-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect.+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i;+NOTICE: created index "mv_ivm_avg_bug_index" on materialized view "mv_ivm_avg_bug"+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;+ i | sum | count | avg +---+-----+-------+---------------------+ 1 | 10 | 1 | 10.0000000000000000+ 2 | 20 | 1 | 20.0000000000000000+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++INSERT INTO mv_base_a VALUES+ (1,0),+ (1,0),+ (2,30),+ (2,30);+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;+ i | sum | count | avg +---+-----+-------+---------------------+ 1 | 10 | 3 | 3.3333333333333333+ 2 | 80 | 3 | 26.6666666666666667+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++DELETE FROM mv_base_a WHERE (i,j) = (1,0);+DELETE FROM mv_base_a WHERE (i,j) = (2,30);+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;+ i | sum | count | avg +---+-----+-------+---------------------+ 1 | 10 | 1 | 10.0000000000000000+ 2 | 20 | 1 | 20.0000000000000000+ 3 | 30 | 1 | 30.0000000000000000+ 4 | 40 | 1 | 40.0000000000000000+ 5 | 50 | 1 | 50.0000000000000000+(5 rows)++ROLLBACK;+-- support MIN(), MAX() aggregate functions+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT i, MIN(j), MAX(j) FROM mv_base_a GROUP BY i;+NOTICE: created index "mv_ivm_min_max_index" on materialized view "mv_ivm_min_max"+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;+ i | min | max +---+-----+-----+ 1 | 10 | 10+ 2 | 20 | 20+ 3 | 30 | 30+ 4 | 40 | 40+ 5 | 50 | 50+(5 rows)++INSERT INTO mv_base_a VALUES+ (1,11), (1,12),+ (2,21), (2,22),+ (3,31), (3,32),+ (4,41), (4,42),+ (5,51), (5,52);+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;+ i | min | max +---+-----+-----+ 1 | 10 | 12+ 2 | 20 | 22+ 3 | 30 | 32+ 4 | 40 | 42+ 5 | 50 | 52+(5 rows)++DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32));+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;+ i | min | max +---+-----+-----+ 1 | 11 | 12+ 2 | 20 | 22+ 3 | 30 | 31+ 4 | 40 | 42+ 5 | 50 | 52+(5 rows)++ROLLBACK;+-- support MIN(), MAX() aggregate functions without GROUP clause+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT MIN(j), MAX(j) FROM mv_base_a;+NOTICE: could not create an index on materialized view "mv_ivm_min_max" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv_ivm_min_max;+ min | max +-----+-----+ 10 | 50+(1 row)++INSERT INTO mv_base_a VALUES+ (0,0), (6,60), (7,70);+SELECT * FROM mv_ivm_min_max;+ min | max +-----+-----+ 0 | 70+(1 row)++DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70));+SELECT * FROM mv_ivm_min_max;+ min | max +-----+-----+ 10 | 60+(1 row)++DELETE FROM mv_base_a;+SELECT * FROM mv_ivm_min_max;+ min | max +-----+-----+ | +(1 row)++ROLLBACK;+-- Test MIN/MAX after search_path change+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min AS SELECT MIN(j) FROM mv_base_a;+NOTICE: could not create an index on materialized view "mv_ivm_min" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv_ivm_min;+ min +-----+ 10+(1 row)++CREATE SCHEMA myschema;+GRANT ALL ON SCHEMA myschema TO public;+CREATE TABLE myschema.mv_base_a (j int);+INSERT INTO myschema.mv_base_a VALUES (1);+DELETE FROM mv_base_a WHERE (i,j) = (1,10);+SELECT * FROM mv_ivm_min;+ min +-----+ 20+(1 row)++SET search_path TO myschema,public,pg_catalog;+DELETE FROM public.mv_base_a WHERE (i,j) = (2,20);+SELECT * FROM mv_ivm_min;+ min +-----+ 30+(1 row)++ROLLBACK;+-- aggregate views with column names specified+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"+INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);+UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);+DELETE FROM mv_base_a WHERE (i,j) = (3,30);+SELECT * FROM mv_ivm_agg ORDER BY 1,2;+ a | sum +---+------+ 1 | 110+ 2 | 2200+ 3 | 300+ 4 | 40+ 5 | 50+(5 rows)++ROLLBACK;+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;+NOTICE: created index "mv_ivm_agg_index" on materialized view "mv_ivm_agg"+INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);+UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);+DELETE FROM mv_base_a WHERE (i,j) = (3,30);+SELECT * FROM mv_ivm_agg ORDER BY 1,2;+ a | b +---+------+ 1 | 110+ 2 | 2200+ 3 | 300+ 4 | 40+ 5 | 50+(5 rows)++ROLLBACK;+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b,c) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;+ERROR: too many column names were specified+ROLLBACK;+-- support self join view and multiple change on the same table+BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);+CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS+ SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i;+NOTICE: could not create an index on materialized view "mv_self" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv_self ORDER BY v1;+ v1 | v2 +----+----+ 10 | 10+ 20 | 20+ 30 | 30+(3 rows)++INSERT INTO base_t VALUES (4,40);+DELETE FROM base_t WHERE i = 1;+UPDATE base_t SET v = v*10 WHERE i=2;+SELECT * FROM mv_self ORDER BY v1;+ v1 | v2 +-----+-----+ 30 | 30+ 40 | 40+ 200 | 200+(3 rows)++WITH+ ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1),+ ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1),+ upd_t AS (UPDATE base_t SET v = v + 100 RETURNING 1),+ dlt_t AS (DELETE FROM base_t WHERE i IN (4,5) RETURNING 1)+SELECT NULL;+ ?column? +----------+ +(1 row)++SELECT * FROM mv_self ORDER BY v1;+ v1 | v2 +-----+-----+ 50 | 50+ 60 | 60+ 130 | 130+ 300 | 300+(4 rows)++--- with sub-transactions+SAVEPOINT p1;+INSERT INTO base_t VALUES (7,70);+RELEASE SAVEPOINT p1;+INSERT INTO base_t VALUES (7,77);+SELECT * FROM mv_self ORDER BY v1, v2;+ v1 | v2 +-----+-----+ 50 | 50+ 60 | 60+ 70 | 70+ 70 | 77+ 77 | 70+ 77 | 77+ 130 | 130+ 300 | 300+(8 rows)++ROLLBACK;+-- support simultaneous table changes+BEGIN;+CREATE TABLE base_r (i int, v int);+CREATE TABLE base_s (i int, v int);+INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30);+INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300);+CREATE INCREMENTAL MATERIALIZED VIEW mv(v1, v2) AS+ SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i);+NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv ORDER BY v1;+ v1 | v2 +----+-----+ 10 | 100+ 20 | 200+ 30 | 300+(3 rows)++WITH+ ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1),+ ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1),+ ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1),+ upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1),+ dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1)+SELECT NULL;+ ?column? +----------+ +(1 row)++SELECT * FROM mv ORDER BY v1;+ v1 | v2 +------+-----+ 10 | 100+ 11 | 100+ 1020 | 200+ 1020 | 222+(4 rows)++ROLLBACK;+-- support foreign reference constraints+BEGIN;+CREATE TABLE ri1 (i int PRIMARY KEY);+CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int);+INSERT INTO ri1 VALUES (1),(2),(3);+INSERT INTO ri2 VALUES (1),(2),(3);+CREATE INCREMENTAL MATERIALIZED VIEW mv_ri(i1, i2) AS+ SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i);+NOTICE: created index "mv_ri_index" on materialized view "mv_ri"+SELECT * FROM mv_ri ORDER BY i1;+ i1 | i2 +----+----+ 1 | 1+ 2 | 2+ 3 | 3+(3 rows)++UPDATE ri1 SET i=10 where i=1;+DELETE FROM ri1 WHERE i=2;+SELECT * FROM mv_ri ORDER BY i2;+ i1 | i2 +----+----+ 3 | 3+ 10 | 10+(2 rows)++ROLLBACK;+-- views including NULL+BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (1,10),(2, NULL);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;+NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv ORDER BY i;+ i | v +---+----+ 1 | 10+ 2 | +(2 rows)++UPDATE base_t SET v = 20 WHERE i = 2;+SELECT * FROM mv ORDER BY i;+ i | v +---+----+ 1 | 10+ 2 | 20+(2 rows)++ROLLBACK;+BEGIN;+CREATE TABLE base_t (i int);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;+NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT * FROM mv ORDER BY i;+ i +---+(0 rows)++INSERT INTO base_t VALUES (1),(NULL);+SELECT * FROM mv ORDER BY i;+ i +---+ 1+ +(2 rows)++ROLLBACK;+BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i;+NOTICE: created index "mv_index" on materialized view "mv"+SELECT * FROM mv ORDER BY i;+ i | sum +---+-----+ 1 | 30+ | 3+(2 rows)++UPDATE base_t SET v = v * 10;+SELECT * FROM mv ORDER BY i;+ i | sum +---+-----+ 1 | 300+ | 30+(2 rows)++ROLLBACK;+BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, min(v), max(v) FROM base_t GROUP BY i;+NOTICE: created index "mv_index" on materialized view "mv"+SELECT * FROM mv ORDER BY i;+ i | min | max +---+-----+-----+ | 1 | 5+(1 row)++DELETE FROM base_t WHERE v = 1;+SELECT * FROM mv ORDER BY i;+ i | min | max +---+-----+-----+ | 2 | 5+(1 row)++DELETE FROM base_t WHERE v = 3;+SELECT * FROM mv ORDER BY i;+ i | min | max +---+-----+-----+ | 2 | 5+(1 row)++DELETE FROM base_t WHERE v = 5;+SELECT * FROM mv ORDER BY i;+ i | min | max +---+-----+-----+ | 2 | 4+(1 row)++ROLLBACK;+-- IMMV containing user defined type+BEGIN;+CREATE TYPE mytype;+CREATE FUNCTION mytype_in(cstring)+ RETURNS mytype AS 'int4in'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+NOTICE: return type mytype is only a shell+CREATE FUNCTION mytype_out(mytype)+ RETURNS cstring AS 'int4out'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+NOTICE: argument type mytype is only a shell+LINE 1: CREATE FUNCTION mytype_out(mytype)+ ^+CREATE TYPE mytype (+ LIKE = int4,+ INPUT = mytype_in,+ OUTPUT = mytype_out+);+CREATE FUNCTION mytype_eq(mytype, mytype)+ RETURNS bool AS 'int4eq'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE FUNCTION mytype_lt(mytype, mytype)+ RETURNS bool AS 'int4lt'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE FUNCTION mytype_cmp(mytype, mytype)+ RETURNS integer AS 'btint4cmp'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE OPERATOR = (+ leftarg = mytype, rightarg = mytype,+ procedure = mytype_eq);+CREATE OPERATOR < (+ leftarg = mytype, rightarg = mytype,+ procedure = mytype_lt);+CREATE OPERATOR CLASS mytype_ops+ DEFAULT FOR TYPE mytype USING btree AS+ OPERATOR 1 <,+ OPERATOR 3 = ,+ FUNCTION 1 mytype_cmp(mytype,mytype);+CREATE TABLE t_mytype (x mytype);+CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS+ SELECT * FROM t_mytype;+NOTICE: could not create an index on materialized view "mv_mytype" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+INSERT INTO t_mytype VALUES ('1'::mytype);+SELECT * FROM mv_mytype;+ x +---+ 1+(1 row)++ROLLBACK;+-- outer join is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i;+ERROR: OUTER JOIN is not supported on incrementally maintainable materialized view+-- CTE is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv AS+ WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i;+ERROR: CTE is not supported on incrementally maintainable materialized view+-- contain system column+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a;+ERROR: system column is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';+ERROR: system column is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a;+ERROR: system column is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a;+ERROR: system column is not supported on incrementally maintainable materialized view+-- contain subquery+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );+ERROR: subquery is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i;+ERROR: subquery is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;+ERROR: subquery is not supported on incrementally maintainable materialized view+-- contain ORDER BY+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k;+ERROR: ORDER BY clause is not supported on incrementally maintainable materialized view+-- contain HAVING+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5;+ERROR: HAVING clause is not supported on incrementally maintainable materialized view+-- contain view or materialized view+CREATE VIEW b_view AS SELECT i,k FROM mv_base_b;+CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i;+ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i;+ERROR: VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i;+ERROR: subquery is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5;+ERROR: subquery is not supported on incrementally maintainable materialized view+-- contain mutable functions+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;+ERROR: mutable function is not supported on incrementally maintainable materialized view+HINT: functions must be marked IMMUTABLE+-- LIMIT/OFFSET is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5;+ERROR: LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view+-- DISTINCT ON is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;+ERROR: DISTINCT ON is not supported on incrementally maintainable materialized view+-- TABLESAMPLE clause is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50);+ERROR: TABLESAMPLE clause is not supported on incrementally maintainable materialized view+-- window functions are not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a;+ERROR: window functions are not supported on incrementally maintainable materialized view+-- aggregate function with some options is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a;+ERROR: aggregate function with FILTER clause is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm18 AS SELECT COUNT(DISTINCT i) FROM mv_base_a;+ERROR: aggregate function with DISTINCT arguments is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a;+ERROR: aggregate function with ORDER clause is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),());+ERROR: GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view+-- inheritance parent is not supported+BEGIN;+CREATE TABLE parent (i int, v int);+CREATE TABLE child_a(options text) INHERITS(parent);+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm21 AS SELECT * FROM parent;+ERROR: inheritance parent is not supported on incrementally maintainable materialized view+ROLLBACK;+-- UNION statement is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;;+ERROR: UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view+-- empty target list is not allowed with IVM+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm25 AS SELECT FROM mv_base_a;+ERROR: empty target list is not supported on incrementally maintainable materialized view+-- FOR UPDATE/SHARE is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE;+ERROR: FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view+-- tartget list cannot contain ivm column that start with '__ivm'+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a;+ERROR: column name __ivm_count__ is not supported on incrementally maintainable materialized view+-- expressions specified in GROUP BY must appear in the target list.+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;+ERROR: GROUP BY expression not appearing in select list is not supported on incrementally maintainable materialized view+-- experssions containing an aggregate is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a;+ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a;+ERROR: expression containing an aggregate in it is not supported on incrementally maintainable materialized view+-- VALUES is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1);+ERROR: VALUES is not supported on incrementally maintainable materialized view+-- views containing base tables with Row Level Security+DROP USER IF EXISTS regress_ivm_admin;+NOTICE: role "regress_ivm_admin" does not exist, skipping+DROP USER IF EXISTS regress_ivm_user;+NOTICE: role "regress_ivm_user" does not exist, skipping+CREATE USER regress_ivm_admin;+CREATE USER regress_ivm_user;+--- create a table with RLS+SET SESSION AUTHORIZATION regress_ivm_admin;+CREATE TABLE rls_tbl(id int, data text, owner name);+INSERT INTO rls_tbl VALUES+ (1,'foo','regress_ivm_user'),+ (2,'bar','postgres');+CREATE TABLE num_tbl(id int, num text);+INSERT INTO num_tbl VALUES+ (1,'one'),+ (2,'two'),+ (3,'three'),+ (4,'four'),+ (5,'five'),+ (6,'six');+--- Users can access only their own rows+CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user);+ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;+GRANT ALL on rls_tbl TO PUBLIC;+GRANT ALL on num_tbl TO PUBLIC;+--- create a view owned by regress_ivm_user+SET SESSION AUTHORIZATION regress_ivm_user;+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls AS SELECT * FROM rls_tbl;+NOTICE: could not create an index on materialized view "ivm_rls" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;+ id | data | owner +----+------+------------------+ 1 | foo | regress_ivm_user+(1 row)++RESET SESSION AUTHORIZATION;+--- inserts rows owned by different users+INSERT INTO rls_tbl VALUES+ (3,'baz','regress_ivm_user'),+ (4,'qux','postgres');+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;+ id | data | owner +----+------+------------------+ 1 | foo | regress_ivm_user+ 3 | baz | regress_ivm_user+(2 rows)++--- combination of diffent kinds of commands+WITH+ i AS (INSERT INTO rls_tbl VALUES(5,'quux','postgres'), (6,'corge','regress_ivm_user')),+ u AS (UPDATE rls_tbl SET owner = 'postgres' WHERE id = 1),+ u2 AS (UPDATE rls_tbl SET owner = 'regress_ivm_user' WHERE id = 2)+SELECT;+--+(1 row)++SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;+ id | data | owner +----+-------+------------------+ 2 | bar | regress_ivm_user+ 3 | baz | regress_ivm_user+ 6 | corge | regress_ivm_user+(3 rows)++---+SET SESSION AUTHORIZATION regress_ivm_user;+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls2 AS SELECT * FROM rls_tbl JOIN num_tbl USING(id);+NOTICE: could not create an index on materialized view "ivm_rls2" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+RESET SESSION AUTHORIZATION;+WITH+ x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)),+ y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4))+SELECT;+--+(1 row)++SELECT * FROM ivm_rls2 ORDER BY 1,2,3;+ id | data | owner | num +----+-------+------------------+---------+ 2 | bar | regress_ivm_user | two+ 3 | baz_2 | regress_ivm_user | three_2+ 6 | corge | regress_ivm_user | six+(3 rows)++-- trigger updating the same table+BEGIN;+CREATE TABLE tbl_update_same (i int);+CREATE FUNCTION func_update_same() RETURNS TRIGGER AS+ $$ BEGIN UPDATE tbl_update_same SET i = i + 1; RETURN NEW; END; $$+ LANGUAGE plpgsql;+CREATE TRIGGER trig_update_same AFTER INSERT ON tbl_update_same FOR EACH ROW+ EXECUTE FUNCTION func_update_same();+CREATE INCREMENTAL MATERIALIZED VIEW mv_update_same AS SELECT * FROM tbl_update_same;+NOTICE: could not create an index on materialized view "mv_update_same" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+INSERT INTO tbl_update_same VALUES (1);+SELECT * FROM mv_update_same;+ i +---+ 2+(1 row)++-- self-referential FKs+CREATE TABLE tbl_self_ref (a int primary key,+ b int references tbl_self_ref(a) ON UPDATE CASCADE);+INSERT INTO tbl_self_ref VALUES (1, null), (2, 1), (3, 2);+CREATE INCREMENTAL MATERIALIZED VIEW mv_self_ref AS SELECT * FROM tbl_self_ref;+NOTICE: created index "mv_self_ref_index" on materialized view "mv_self_ref"+UPDATE tbl_self_ref set a = a + 10;+SELECT * FROM mv_self_ref ORDER BY a;+ a | b +----+----+ 11 | + 12 | 11+ 13 | 12+(3 rows)++ROLLBACK;+-- automatic index creation+BEGIN;+CREATE TABLE base_a (i int primary key, j int);+CREATE TABLE base_b (i int primary key, j int);+--- group by: create an index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx1 AS SELECT i, sum(j) FROM base_a GROUP BY i;+NOTICE: created index "mv_idx1_index" on materialized view "mv_idx1"+--- distinct: create an index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx2 AS SELECT DISTINCT j FROM base_a;+NOTICE: created index "mv_idx2_index" on materialized view "mv_idx2"+--- with all pkey columns: create an index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx3(i_a, i_b) AS SELECT a.i, b.i FROM base_a a, base_b b;+NOTICE: created index "mv_idx3_index" on materialized view "mv_idx3"+--- missing some pkey columns: no index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx4 AS SELECT j FROM base_a;+NOTICE: could not create an index on materialized view "mv_idx4" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx5 AS SELECT a.i, b.j FROM base_a a, base_b b;+NOTICE: could not create an index on materialized view "mv_idx5" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+--- with set-returning function: no index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx6 AS SELECT i FROM base_a, generate_series(1,10);+NOTICE: could not create an index on materialized view "mv_idx6" automatically+DETAIL: This target list does not have all the primary key columns, or this view does not contain GROUP BY or DISTINCT clause.+HINT: Create an index on the materialized view for efficient incremental maintenance.+ROLLBACK;+-- type that doesn't have default operator class for access method btree+BEGIN;+CREATE TABLE table_json (j json);+CREATE INCREMENTAL MATERIALIZED VIEW mv_json AS SELECT * from table_json;+ERROR: data type json has no default operator class for access method "btree"+ROLLBACK;+-- cleanup+DROP TABLE rls_tbl CASCADE;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to materialized view ivm_rls+drop cascades to materialized view ivm_rls2+DROP TABLE num_tbl CASCADE;+DROP USER regress_ivm_user;+DROP USER regress_ivm_admin;+DROP TABLE mv_base_b CASCADE;+NOTICE: drop cascades to 3 other objects+DETAIL: drop cascades to materialized view mv_ivm_1+drop cascades to view b_view+drop cascades to materialized view b_mview+DROP TABLE mv_base_a CASCADE;diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex 8fa0a6c47fb..7caca5f64fd 100644--- a/src/test/regress/parallel_schedule+++ b/src/test/regress/parallel_schedule@@ -77,6 +77,7 @@ test: brin_bloom brin_multi
# Another group of parallel tests
# ----------
test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions nls sysviews tsrf tid tidscan tidrangescan collate.utf8 collate.icu.utf8 incremental_sort create_role without_overlaps generated_virtual
+test: incremental_matview
# collate.linux.utf8 and collate.icu.utf8 tests cannot be run in parallel with each other
# psql depends on create_am
diff --git a/src/test/regress/sql/incremental_matview.sql b/src/test/regress/sql/incremental_matview.sqlnew file mode 100644index 00000000000..2d55c393d6f--- /dev/null+++ b/src/test/regress/sql/incremental_matview.sql@@ -0,0 +1,580 @@+-- create a table to use as a basis for views and materialized views in various combinations+CREATE TABLE mv_base_a (x int, i int, y int, j int);+CREATE TABLE mv_base_b (x int, i int, y int, k int);+-- test for base tables with dropped columns+ALTER TABLE mv_base_a DROP COLUMN x;+ALTER TABLE mv_base_a DROP COLUMN y;+ALTER TABLE mv_base_b DROP COLUMN x;+ALTER TABLE mv_base_b DROP COLUMN y;+INSERT INTO mv_base_a VALUES+ (1,10),+ (2,20),+ (3,30),+ (4,40),+ (5,50);+INSERT INTO mv_base_b VALUES+ (1,101),+ (2,102),+ (3,103),+ (4,104);++CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_1 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) WITH NO DATA;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+REFRESH MATERIALIZED VIEW mv_ivm_1;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;++-- REFRESH WITH NO DATA+BEGIN;+CREATE FUNCTION dummy_ivm_trigger_func() RETURNS TRIGGER AS $$+ BEGIN+ RETURN NULL;+ END+$$ language plpgsql;++CREATE CONSTRAINT TRIGGER dummy_ivm_trigger AFTER INSERT+ON mv_base_a FROM mv_ivm_1 FOR EACH ROW+EXECUTE PROCEDURE dummy_ivm_trigger_func();++SELECT COUNT(*)+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;++REFRESH MATERIALIZED VIEW mv_ivm_1 WITH NO DATA;++SELECT COUNT(*)+FROM pg_depend pd INNER JOIN pg_trigger pt ON pd.objid = pt.oid+WHERE pd.classid = 'pg_trigger'::regclass AND pd.refobjid = 'mv_ivm_1'::regclass;+ROLLBACK;++-- immediate maintenance+BEGIN;+INSERT INTO mv_base_b VALUES(5,105);+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+UPDATE mv_base_a SET j = 0 WHERE i = 1;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+DELETE FROM mv_base_b WHERE (i,k) = (5,105);+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ROLLBACK;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;++-- rename of IVM columns+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_rename AS SELECT DISTINCT * FROM mv_base_a;+ALTER MATERIALIZED VIEW mv_ivm_rename RENAME COLUMN __ivm_count__ TO xxx;+DROP MATERIALIZED VIEW mv_ivm_rename;++-- unique index on IVM columns+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_unique AS SELECT DISTINCT * FROM mv_base_a;+CREATE UNIQUE INDEX ON mv_ivm_unique(__ivm_count__);+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__));+CREATE UNIQUE INDEX ON mv_ivm_unique((__ivm_count__ + 1));+DROP MATERIALIZED VIEW mv_ivm_unique;++-- test for renaming column name to camel style+BEGIN;+ALTER TABLE mv_base_a RENAME i TO "I";+ALTER TABLE mv_base_a RENAME j TO "J";+UPDATE mv_base_a SET "J" = 0 WHERE "I" = 1;+SELECT * FROM mv_ivm_1 ORDER BY 1,2,3;+ROLLBACK;+++-- TRUNCATE a base table in join views+BEGIN;+TRUNCATE mv_base_a;+SELECT * FROM mv_ivm_1;+ROLLBACK;++BEGIN;+TRUNCATE mv_base_b;+SELECT * FROM mv_ivm_1;+ROLLBACK;++-- some query syntax+BEGIN;+CREATE FUNCTION ivm_func() RETURNS int LANGUAGE 'sql'+ AS 'SELECT 1' IMMUTABLE;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_func AS SELECT * FROM ivm_func();+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_no_tbl AS SELECT 1;+ROLLBACK;++-- result of materialized view have DISTINCT clause or the duplicate result.+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_duplicate AS SELECT j FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_distinct AS SELECT DISTINCT j FROM mv_base_a;+INSERT INTO mv_base_a VALUES(6,20);+SELECT * FROM mv_ivm_duplicate ORDER BY 1;+SELECT * FROM mv_ivm_distinct ORDER BY 1;+DELETE FROM mv_base_a WHERE (i,j) = (2,20);+SELECT * FROM mv_ivm_duplicate ORDER BY 1;+SELECT * FROM mv_ivm_distinct ORDER BY 1;+ROLLBACK;++-- support SUM(), COUNT() and AVG() aggregate functions+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+INSERT INTO mv_base_a VALUES(2,100);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+UPDATE mv_base_a SET j = 200 WHERE (i,j) = (2,100);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+DELETE FROM mv_base_a WHERE (i,j) = (2,200);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3,4;+ROLLBACK;++-- support COUNT(*) aggregate function+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;+INSERT INTO mv_base_a VALUES(2,100);+SELECT * FROM mv_ivm_agg ORDER BY 1,2,3;+ROLLBACK;++-- TRUNCATE a base table in aggregate views+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg AS SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;+TRUNCATE mv_base_a;+SELECT sum, count FROM mv_ivm_agg;+SELECT i, SUM(j), COUNT(*) FROM mv_base_a GROUP BY i;+ROLLBACK;++-- support aggregate functions without GROUP clause+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;+SELECT * FROM mv_ivm_group ORDER BY 1;+INSERT INTO mv_base_a VALUES(6,60);+SELECT * FROM mv_ivm_group ORDER BY 1;+DELETE FROM mv_base_a;+SELECT * FROM mv_ivm_group ORDER BY 1;+ROLLBACK;++-- TRUNCATE a base table in aggregate views without GROUP clause+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_group AS SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;+TRUNCATE mv_base_a;+SELECT sum, count, avg FROM mv_ivm_group;+SELECT SUM(j), COUNT(j), AVG(j) FROM mv_base_a;+ROLLBACK;++-- resolved issue: When use AVG() function and values is indivisible, result of AVG() is incorrect.+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_avg_bug AS SELECT i, SUM(j), COUNT(j), AVG(j) FROM mv_base_A GROUP BY i;+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;+INSERT INTO mv_base_a VALUES+ (1,0),+ (1,0),+ (2,30),+ (2,30);+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;+DELETE FROM mv_base_a WHERE (i,j) = (1,0);+DELETE FROM mv_base_a WHERE (i,j) = (2,30);+SELECT * FROM mv_ivm_avg_bug ORDER BY 1,2,3;+ROLLBACK;++-- support MIN(), MAX() aggregate functions+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT i, MIN(j), MAX(j) FROM mv_base_a GROUP BY i;+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;+INSERT INTO mv_base_a VALUES+ (1,11), (1,12),+ (2,21), (2,22),+ (3,31), (3,32),+ (4,41), (4,42),+ (5,51), (5,52);+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;+DELETE FROM mv_base_a WHERE (i,j) IN ((1,10), (2,21), (3,32));+SELECT * FROM mv_ivm_min_max ORDER BY 1,2,3;+ROLLBACK;++-- support MIN(), MAX() aggregate functions without GROUP clause+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min_max AS SELECT MIN(j), MAX(j) FROM mv_base_a;+SELECT * FROM mv_ivm_min_max;+INSERT INTO mv_base_a VALUES+ (0,0), (6,60), (7,70);+SELECT * FROM mv_ivm_min_max;+DELETE FROM mv_base_a WHERE (i,j) IN ((0,0), (7,70));+SELECT * FROM mv_ivm_min_max;+DELETE FROM mv_base_a;+SELECT * FROM mv_ivm_min_max;+ROLLBACK;++-- Test MIN/MAX after search_path change+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_min AS SELECT MIN(j) FROM mv_base_a;+SELECT * FROM mv_ivm_min;++CREATE SCHEMA myschema;+GRANT ALL ON SCHEMA myschema TO public;+CREATE TABLE myschema.mv_base_a (j int);+INSERT INTO myschema.mv_base_a VALUES (1);++DELETE FROM mv_base_a WHERE (i,j) = (1,10);+SELECT * FROM mv_ivm_min;++SET search_path TO myschema,public,pg_catalog;+DELETE FROM public.mv_base_a WHERE (i,j) = (2,20);+SELECT * FROM mv_ivm_min;+ROLLBACK;++-- aggregate views with column names specified+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;+INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);+UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);+DELETE FROM mv_base_a WHERE (i,j) = (3,30);+SELECT * FROM mv_ivm_agg ORDER BY 1,2;+ROLLBACK;+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;+INSERT INTO mv_base_a VALUES (1,100), (2,200), (3,300);+UPDATE mv_base_a SET j = 2000 WHERE (i,j) = (2,20);+DELETE FROM mv_base_a WHERE (i,j) = (3,30);+SELECT * FROM mv_ivm_agg ORDER BY 1,2;+ROLLBACK;+BEGIN;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_agg(a,b,c) AS SELECT i, SUM(j) FROM mv_base_a GROUP BY i;+ROLLBACK;++-- support self join view and multiple change on the same table+BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (1, 10), (2, 20), (3, 30);+CREATE INCREMENTAL MATERIALIZED VIEW mv_self(v1, v2) AS+ SELECT t1.v, t2.v FROM base_t AS t1 JOIN base_t AS t2 ON t1.i = t2.i;+SELECT * FROM mv_self ORDER BY v1;+INSERT INTO base_t VALUES (4,40);+DELETE FROM base_t WHERE i = 1;+UPDATE base_t SET v = v*10 WHERE i=2;+SELECT * FROM mv_self ORDER BY v1;+WITH+ ins_t1 AS (INSERT INTO base_t VALUES (5,50) RETURNING 1),+ ins_t2 AS (INSERT INTO base_t VALUES (6,60) RETURNING 1),+ upd_t AS (UPDATE base_t SET v = v + 100 RETURNING 1),+ dlt_t AS (DELETE FROM base_t WHERE i IN (4,5) RETURNING 1)+SELECT NULL;+SELECT * FROM mv_self ORDER BY v1;++--- with sub-transactions+SAVEPOINT p1;+INSERT INTO base_t VALUES (7,70);+RELEASE SAVEPOINT p1;+INSERT INTO base_t VALUES (7,77);+SELECT * FROM mv_self ORDER BY v1, v2;++ROLLBACK;++-- support simultaneous table changes+BEGIN;+CREATE TABLE base_r (i int, v int);+CREATE TABLE base_s (i int, v int);+INSERT INTO base_r VALUES (1, 10), (2, 20), (3, 30);+INSERT INTO base_s VALUES (1, 100), (2, 200), (3, 300);+CREATE INCREMENTAL MATERIALIZED VIEW mv(v1, v2) AS+ SELECT r.v, s.v FROM base_r AS r JOIN base_s AS s USING(i);+SELECT * FROM mv ORDER BY v1;+WITH+ ins_r AS (INSERT INTO base_r VALUES (1,11) RETURNING 1),+ ins_r2 AS (INSERT INTO base_r VALUES (3,33) RETURNING 1),+ ins_s AS (INSERT INTO base_s VALUES (2,222) RETURNING 1),+ upd_r AS (UPDATE base_r SET v = v + 1000 WHERE i = 2 RETURNING 1),+ dlt_s AS (DELETE FROM base_s WHERE i = 3 RETURNING 1)+SELECT NULL;+SELECT * FROM mv ORDER BY v1;+ROLLBACK;++-- support foreign reference constraints+BEGIN;+CREATE TABLE ri1 (i int PRIMARY KEY);+CREATE TABLE ri2 (i int PRIMARY KEY REFERENCES ri1(i) ON UPDATE CASCADE ON DELETE CASCADE, v int);+INSERT INTO ri1 VALUES (1),(2),(3);+INSERT INTO ri2 VALUES (1),(2),(3);+CREATE INCREMENTAL MATERIALIZED VIEW mv_ri(i1, i2) AS+ SELECT ri1.i, ri2.i FROM ri1 JOIN ri2 USING(i);+SELECT * FROM mv_ri ORDER BY i1;+UPDATE ri1 SET i=10 where i=1;+DELETE FROM ri1 WHERE i=2;+SELECT * FROM mv_ri ORDER BY i2;+ROLLBACK;++-- views including NULL+BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (1,10),(2, NULL);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;+SELECT * FROM mv ORDER BY i;+UPDATE base_t SET v = 20 WHERE i = 2;+SELECT * FROM mv ORDER BY i;+ROLLBACK;++BEGIN;+CREATE TABLE base_t (i int);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base_t;+SELECT * FROM mv ORDER BY i;+INSERT INTO base_t VALUES (1),(NULL);+SELECT * FROM mv ORDER BY i;+ROLLBACK;++BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (1, 10), (1, 20);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, sum(v) FROM base_t GROUP BY i;+SELECT * FROM mv ORDER BY i;+UPDATE base_t SET v = v * 10;+SELECT * FROM mv ORDER BY i;+ROLLBACK;++BEGIN;+CREATE TABLE base_t (i int, v int);+INSERT INTO base_t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5);+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT i, min(v), max(v) FROM base_t GROUP BY i;+SELECT * FROM mv ORDER BY i;+DELETE FROM base_t WHERE v = 1;+SELECT * FROM mv ORDER BY i;+DELETE FROM base_t WHERE v = 3;+SELECT * FROM mv ORDER BY i;+DELETE FROM base_t WHERE v = 5;+SELECT * FROM mv ORDER BY i;+ROLLBACK;++-- IMMV containing user defined type+BEGIN;++CREATE TYPE mytype;+CREATE FUNCTION mytype_in(cstring)+ RETURNS mytype AS 'int4in'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE FUNCTION mytype_out(mytype)+ RETURNS cstring AS 'int4out'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE TYPE mytype (+ LIKE = int4,+ INPUT = mytype_in,+ OUTPUT = mytype_out+);++CREATE FUNCTION mytype_eq(mytype, mytype)+ RETURNS bool AS 'int4eq'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE FUNCTION mytype_lt(mytype, mytype)+ RETURNS bool AS 'int4lt'+ LANGUAGE INTERNAL STRICT IMMUTABLE;+CREATE FUNCTION mytype_cmp(mytype, mytype)+ RETURNS integer AS 'btint4cmp'+ LANGUAGE INTERNAL STRICT IMMUTABLE;++CREATE OPERATOR = (+ leftarg = mytype, rightarg = mytype,+ procedure = mytype_eq);+CREATE OPERATOR < (+ leftarg = mytype, rightarg = mytype,+ procedure = mytype_lt);++CREATE OPERATOR CLASS mytype_ops+ DEFAULT FOR TYPE mytype USING btree AS+ OPERATOR 1 <,+ OPERATOR 3 = ,+ FUNCTION 1 mytype_cmp(mytype,mytype);++CREATE TABLE t_mytype (x mytype);+CREATE INCREMENTAL MATERIALIZED VIEW mv_mytype AS+ SELECT * FROM t_mytype;+INSERT INTO t_mytype VALUES ('1'::mytype);+SELECT * FROM mv_mytype;++ROLLBACK;++-- outer join is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i;+-- CTE is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv AS+ WITH b AS ( SELECT * FROM mv_base_b) SELECT a.i,a.j FROM mv_base_a a, b WHERE a.i = b.i;+-- contain system column+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm01 AS SELECT i,j,xmin FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT i,j,xmin::text AS x_min FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm06 AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a;+-- contain subquery+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm04 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;+-- contain ORDER BY+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) ORDER BY i,j,k;+-- contain HAVING+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT i,j,k FROM mv_base_a a INNER JOIN mv_base_b b USING(i) GROUP BY i,j,k HAVING SUM(i) > 5;++-- contain view or materialized view+CREATE VIEW b_view AS SELECT i,k FROM mv_base_b;+CREATE MATERIALIZED VIEW b_mview AS SELECT i,k FROM mv_base_b;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm07 AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm08 AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR a.i > 5;++-- contain mutable functions+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;++-- LIMIT/OFFSET is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm13 AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5;++-- DISTINCT ON is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm14 AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;++-- TABLESAMPLE clause is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm15 AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50);++-- window functions are not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm16 AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a;++-- aggregate function with some options is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm17 AS SELECT COUNT(*) FILTER(WHERE i < 3) FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm18 AS SELECT COUNT(DISTINCT i) FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm19 AS SELECT array_agg(j ORDER BY i DESC) FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm20 AS SELECT i,SUM(j) FROM mv_base_a GROUP BY GROUPING SETS((i),());++-- inheritance parent is not supported+BEGIN;+CREATE TABLE parent (i int, v int);+CREATE TABLE child_a(options text) INHERITS(parent);+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm21 AS SELECT * FROM parent;+ROLLBACK;++-- UNION statement is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm22 AS SELECT i,j FROM mv_base_a UNION ALL SELECT i,k FROM mv_base_b;;++-- empty target list is not allowed with IVM+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm25 AS SELECT FROM mv_base_a;++-- FOR UPDATE/SHARE is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm26 AS SELECT i,j FROM mv_base_a FOR UPDATE;++-- tartget list cannot contain ivm column that start with '__ivm'+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm28 AS SELECT i AS "__ivm_count__" FROM mv_base_a;++-- expressions specified in GROUP BY must appear in the target list.+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm29 AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;++-- experssions containing an aggregate is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm30 AS SELECT sum(i)*0.5 FROM mv_base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm31 AS SELECT sum(i)/sum(j) FROM mv_base_a;++-- VALUES is not supported+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_only_values1 AS values(1);++-- views containing base tables with Row Level Security+DROP USER IF EXISTS regress_ivm_admin;+DROP USER IF EXISTS regress_ivm_user;+CREATE USER regress_ivm_admin;+CREATE USER regress_ivm_user;++--- create a table with RLS+SET SESSION AUTHORIZATION regress_ivm_admin;+CREATE TABLE rls_tbl(id int, data text, owner name);+INSERT INTO rls_tbl VALUES+ (1,'foo','regress_ivm_user'),+ (2,'bar','postgres');+CREATE TABLE num_tbl(id int, num text);+INSERT INTO num_tbl VALUES+ (1,'one'),+ (2,'two'),+ (3,'three'),+ (4,'four'),+ (5,'five'),+ (6,'six');++--- Users can access only their own rows+CREATE POLICY rls_tbl_policy ON rls_tbl FOR SELECT TO PUBLIC USING(owner = current_user);+ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;+GRANT ALL on rls_tbl TO PUBLIC;+GRANT ALL on num_tbl TO PUBLIC;++--- create a view owned by regress_ivm_user+SET SESSION AUTHORIZATION regress_ivm_user;++CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls AS SELECT * FROM rls_tbl;+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;+RESET SESSION AUTHORIZATION;++--- inserts rows owned by different users+INSERT INTO rls_tbl VALUES+ (3,'baz','regress_ivm_user'),+ (4,'qux','postgres');+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;++--- combination of diffent kinds of commands+WITH+ i AS (INSERT INTO rls_tbl VALUES(5,'quux','postgres'), (6,'corge','regress_ivm_user')),+ u AS (UPDATE rls_tbl SET owner = 'postgres' WHERE id = 1),+ u2 AS (UPDATE rls_tbl SET owner = 'regress_ivm_user' WHERE id = 2)+SELECT;+SELECT id, data, owner FROM ivm_rls ORDER BY 1,2,3;++---+SET SESSION AUTHORIZATION regress_ivm_user;+CREATE INCREMENTAL MATERIALIZED VIEW ivm_rls2 AS SELECT * FROM rls_tbl JOIN num_tbl USING(id);+RESET SESSION AUTHORIZATION;++WITH+ x AS (UPDATE rls_tbl SET data = data || '_2' where id in (3,4)),+ y AS (UPDATE num_tbl SET num = num || '_2' where id in (3,4))+SELECT;+SELECT * FROM ivm_rls2 ORDER BY 1,2,3;++-- trigger updating the same table+BEGIN;+CREATE TABLE tbl_update_same (i int);+CREATE FUNCTION func_update_same() RETURNS TRIGGER AS+ $$ BEGIN UPDATE tbl_update_same SET i = i + 1; RETURN NEW; END; $$+ LANGUAGE plpgsql;+CREATE TRIGGER trig_update_same AFTER INSERT ON tbl_update_same FOR EACH ROW+ EXECUTE FUNCTION func_update_same();+CREATE INCREMENTAL MATERIALIZED VIEW mv_update_same AS SELECT * FROM tbl_update_same;+INSERT INTO tbl_update_same VALUES (1);+SELECT * FROM mv_update_same;++-- self-referential FKs+CREATE TABLE tbl_self_ref (a int primary key,+ b int references tbl_self_ref(a) ON UPDATE CASCADE);+INSERT INTO tbl_self_ref VALUES (1, null), (2, 1), (3, 2);+CREATE INCREMENTAL MATERIALIZED VIEW mv_self_ref AS SELECT * FROM tbl_self_ref;+UPDATE tbl_self_ref set a = a + 10;+SELECT * FROM mv_self_ref ORDER BY a;+ROLLBACK;++-- automatic index creation+BEGIN;+CREATE TABLE base_a (i int primary key, j int);+CREATE TABLE base_b (i int primary key, j int);++--- group by: create an index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx1 AS SELECT i, sum(j) FROM base_a GROUP BY i;++--- distinct: create an index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx2 AS SELECT DISTINCT j FROM base_a;++--- with all pkey columns: create an index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx3(i_a, i_b) AS SELECT a.i, b.i FROM base_a a, base_b b;++--- missing some pkey columns: no index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx4 AS SELECT j FROM base_a;+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx5 AS SELECT a.i, b.j FROM base_a a, base_b b;++--- with set-returning function: no index+CREATE INCREMENTAL MATERIALIZED VIEW mv_idx6 AS SELECT i FROM base_a, generate_series(1,10);++ROLLBACK;++-- type that doesn't have default operator class for access method btree+BEGIN;+CREATE TABLE table_json (j json);+CREATE INCREMENTAL MATERIALIZED VIEW mv_json AS SELECT * from table_json;+ROLLBACK;++-- cleanup++DROP TABLE rls_tbl CASCADE;+DROP TABLE num_tbl CASCADE;+DROP USER regress_ivm_user;+DROP USER regress_ivm_admin;++DROP TABLE mv_base_b CASCADE;+DROP TABLE mv_base_a CASCADE;
--
2.43.0
--Multipart=_Fri__29_May_2026_23_14_17_+0900_Te0o73X2VqYK57Gd
Content-Type: text/x-diff;
name="v37-0009-Add-support-for-min-max-aggregates-for-IVM.patch"
Content-Disposition: attachment;
filename="v37-0009-Add-support-for-min-max-aggregates-for-IVM.patch"
Content-Transfer-Encoding: 7bit
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nagata@sraoss.co.jp
Subject: Re: [PATCH v37 10/11] Add regression tests for Incremental View Maintenance
In-Reply-To: <no-message-id-373946@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox