Thread

[PATCH v37 10/11] Add regression tests for Incremental View Maintenance

Yugo Nagata <nagata@sraoss.co.jp> — 2026-05-29T09:31:23Z

---
 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_schedule
index 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-insert3
diff --git a/src/test/regress/expected/incremental_matview.out b/src/test/regress/expected/incremental_matview.out
new file mode 100644
index 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_schedule
index 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.sql
new file mode 100644
index 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