From: Yugo Nagata <nagata@sraoss.co.jp>
Subject: [PATCH v40 4/5] Add regression tests for Incremental View Maintenance
Date: Fri, 29 May 2026 18:31:23 +0900
---
.../isolation/expected/ivm-create-insert.out | 285 ++++++++
.../isolation/expected/ivm-create-insert2.out | 305 ++++++++
.../isolation/expected/ivm-create-insert3.out | 305 ++++++++
.../isolation/expected/ivm-insert-insert.out | 497 +++++++++++++
.../isolation/expected/ivm-insert-insert2.out | 373 ++++++++++
.../isolation/expected/ivm-insert-insert3.out | 373 ++++++++++
.../isolation/expected/ivm-refresh-insert.out | 213 ++++++
.../expected/ivm-refresh-insert2.out | 191 +++++
.../expected/ivm-refresh-insert3.out | 191 +++++
src/test/isolation/isolation_schedule | 9 +
.../isolation/specs/ivm-create-insert.spec | 47 ++
.../isolation/specs/ivm-create-insert2.spec | 54 ++
.../isolation/specs/ivm-create-insert3.spec | 54 ++
.../isolation/specs/ivm-insert-insert.spec | 62 ++
.../isolation/specs/ivm-insert-insert2.spec | 62 ++
.../isolation/specs/ivm-insert-insert3.spec | 62 ++
.../isolation/specs/ivm-refresh-insert.spec | 46 ++
.../isolation/specs/ivm-refresh-insert2.spec | 46 ++
.../isolation/specs/ivm-refresh-insert3.spec | 46 ++
.../regress/expected/incremental_matview.out | 667 ++++++++++++++++++
src/test/regress/parallel_schedule | 1 +
src/test/regress/sql/incremental_matview.sql | 407 +++++++++++
22 files changed, 4296 insertions(+)
create mode 100644 src/test/isolation/expected/ivm-create-insert.out
create mode 100644 src/test/isolation/expected/ivm-create-insert2.out
create mode 100644 src/test/isolation/expected/ivm-create-insert3.out
create mode 100644 src/test/isolation/expected/ivm-insert-insert.out
create mode 100644 src/test/isolation/expected/ivm-insert-insert2.out
create mode 100644 src/test/isolation/expected/ivm-insert-insert3.out
create mode 100644 src/test/isolation/expected/ivm-refresh-insert.out
create mode 100644 src/test/isolation/expected/ivm-refresh-insert2.out
create mode 100644 src/test/isolation/expected/ivm-refresh-insert3.out
create mode 100644 src/test/isolation/specs/ivm-create-insert.spec
create mode 100644 src/test/isolation/specs/ivm-create-insert2.spec
create mode 100644 src/test/isolation/specs/ivm-create-insert3.spec
create mode 100644 src/test/isolation/specs/ivm-insert-insert.spec
create mode 100644 src/test/isolation/specs/ivm-insert-insert2.spec
create mode 100644 src/test/isolation/specs/ivm-insert-insert3.spec
create mode 100644 src/test/isolation/specs/ivm-refresh-insert.spec
create mode 100644 src/test/isolation/specs/ivm-refresh-insert2.spec
create mode 100644 src/test/isolation/specs/ivm-refresh-insert3.spec
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/expected/ivm-create-insert.out b/src/test/isolation/expected/ivm-create-insert.outnew file mode 100644index 00000000000..236a6e8121c--- /dev/null+++ b/src/test/isolation/expected/ivm-create-insert.out@@ -0,0 +1,285 @@+Parsed test spec with 2 sessions++starting permutation: s1 create s2 insert c1 check2 c2 mv+step s1: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step s2: SELECT;+step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 create s2 c1 insert check2 c2 mv+step s1: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step s2: SELECT;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 create insert c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert create c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+ <waiting ...>+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 create c1 insert check2 c2 mv+step s1: SELECT;+step s2: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 create c2 check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+ <waiting ...>+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 c2 create check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert c2 create check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert: INSERT INTO a VALUES (2);+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/expected/ivm-create-insert2.out b/src/test/isolation/expected/ivm-create-insert2.outnew file mode 100644index 00000000000..101e051152d--- /dev/null+++ b/src/test/isolation/expected/ivm-create-insert2.out@@ -0,0 +1,305 @@+Parsed test spec with 2 sessions++starting permutation: s1 create s2 insert c1 check2 c2 mv+step s1: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step s2: SELECT;+step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 create s2 c1 insert check2 c2 mv+step s1: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step s2: SELECT;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 create insert c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert create c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+ <waiting ...>+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+++starting permutation: s1 s2 create c1 insert check2 c2 mv+step s1: SELECT;+step s2: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 create c2 check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+ <waiting ...>+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+++starting permutation: s2 insert s1 c2 create check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+++starting permutation: s2 s1 insert c2 create check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert: INSERT INTO a VALUES (2);+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+diff --git a/src/test/isolation/expected/ivm-create-insert3.out b/src/test/isolation/expected/ivm-create-insert3.outnew file mode 100644index 00000000000..101e051152d--- /dev/null+++ b/src/test/isolation/expected/ivm-create-insert3.out@@ -0,0 +1,305 @@+Parsed test spec with 2 sessions++starting permutation: s1 create s2 insert c1 check2 c2 mv+step s1: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step s2: SELECT;+step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 create s2 c1 insert check2 c2 mv+step s1: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step s2: SELECT;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 create insert c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert create c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+ <waiting ...>+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+++starting permutation: s1 s2 create c1 insert check2 c2 mv+step s1: SELECT;+step s2: SELECT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 create c2 check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+ <waiting ...>+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+++starting permutation: s2 insert s1 c2 create check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+++starting permutation: s2 s1 insert c2 create check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert: INSERT INTO a VALUES (2);+step c2: COMMIT;+tx1: NOTICE: could not create an index on materialized view "mv" automatically+DETAIL: This target list does not have all the primary key columns. +HINT: Create an index on the materialized view for efficient incremental maintenance.+tx1: WARNING: inconsistent view can be created in isolation level SERIALIZABLE or REPEATABLE READ+DETAIL: The view may not include effects of a concurrent transaction.+HINT: The view with incremental maintenance should be created in isolation level READ COMMITTED, or refreshed manually to make sure the view is consistent.+step create: + CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;++step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ng +(1 row)+diff --git a/src/test/isolation/expected/ivm-insert-insert.out b/src/test/isolation/expected/ivm-insert-insert.outnew file mode 100644index 00000000000..5ca4cfea677--- /dev/null+++ b/src/test/isolation/expected/ivm-insert-insert.out@@ -0,0 +1,497 @@+Parsed test spec with 2 sessions++starting permutation: s1 update1 s2 update2 c1 check2 c2 mv+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1; <waiting ...>+step c1: COMMIT;+step update2: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 update1 s2 c1 update2 check2 c2 mv+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step s2: SELECT;+step c1: COMMIT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update1 update2 c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step update2: UPDATE b SET j = 111 WHERE i = 1; <waiting ...>+step c1: COMMIT;+step update2: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update2 update1 c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step update1: UPDATE a SET j = 11 WHERE i = 1; <waiting ...>+step c2: COMMIT;+step update1: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update1 c1 update2 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step c1: COMMIT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 update2 s1 update1 c2 check1 c1 mv+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1; <waiting ...>+step c2: COMMIT;+step update1: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 update2 s1 c2 update1 check1 c1 mv+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step s1: SELECT;+step c2: COMMIT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 update2 c2 update1 check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step c2: COMMIT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 insert1 s2 insert2 c1 check2 c2 mv+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201); <waiting ...>+step c1: COMMIT;+step insert2: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 insert1 s2 c1 insert2 check2 c2 mv+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step s2: SELECT;+step c1: COMMIT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert1 insert2 c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201); <waiting ...>+step c1: COMMIT;+step insert2: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert2 insert1 c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200); <waiting ...>+step c2: COMMIT;+step insert1: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert1 c1 insert2 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step c1: COMMIT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert2 s1 insert1 c2 check1 c1 mv+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200); <waiting ...>+step c2: COMMIT;+step insert1: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert2 s1 c2 insert1 check1 c1 mv+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step s1: SELECT;+step c2: COMMIT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert2 c2 insert1 check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step c2: COMMIT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+20|20|200+20|20|201+20|21|200+20|21|201+21|20|200+21|20|201+21|21|200+21|21|201+(12 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/expected/ivm-insert-insert2.out b/src/test/isolation/expected/ivm-insert-insert2.outnew file mode 100644index 00000000000..e1c66d4de5e--- /dev/null+++ b/src/test/isolation/expected/ivm-insert-insert2.out@@ -0,0 +1,373 @@+Parsed test spec with 2 sessions++starting permutation: s1 update1 s2 update2 c1 check2 c2 mv+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 update1 s2 c1 update2 check2 c2 mv+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step s2: SELECT;+step c1: COMMIT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update1 update2 c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update2 update1 c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update1 c1 update2 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step c1: COMMIT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 update2 s1 update1 c2 check1 c1 mv+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 update2 s1 c2 update1 check1 c1 mv+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step s1: SELECT;+step c2: COMMIT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 update2 c2 update1 check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step c2: COMMIT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 insert1 s2 insert2 c1 check2 c2 mv+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 insert1 s2 c1 insert2 check2 c2 mv+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step s2: SELECT;+step c1: COMMIT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert1 insert2 c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert2 insert1 c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert1 c1 insert2 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step c1: COMMIT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert2 s1 insert1 c2 check1 c1 mv+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert2 s1 c2 insert1 check1 c1 mv+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step s1: SELECT;+step c2: COMMIT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert2 c2 insert1 check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step c2: COMMIT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/expected/ivm-insert-insert3.out b/src/test/isolation/expected/ivm-insert-insert3.outnew file mode 100644index 00000000000..e1c66d4de5e--- /dev/null+++ b/src/test/isolation/expected/ivm-insert-insert3.out@@ -0,0 +1,373 @@+Parsed test spec with 2 sessions++starting permutation: s1 update1 s2 update2 c1 check2 c2 mv+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 update1 s2 c1 update2 check2 c2 mv+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step s2: SELECT;+step c1: COMMIT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update1 update2 c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update2 update1 c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 update1 c1 update2 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+step c1: COMMIT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+11|11|100+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 update2 s1 update1 c2 check1 c1 mv+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step s1: SELECT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 update2 s1 c2 update1 check1 c1 mv+step s2: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step s1: SELECT;+step c2: COMMIT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 update2 c2 update1 check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step update2: UPDATE b SET j = 111 WHERE i = 1;+step c2: COMMIT;+step update1: UPDATE a SET j = 11 WHERE i = 1;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|111+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 insert1 s2 insert2 c1 check2 c2 mv+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 insert1 s2 c1 insert2 check2 c2 mv+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step s2: SELECT;+step c1: COMMIT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert1 insert2 c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert2 insert1 c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert1 c1 insert2 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+step c1: COMMIT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+20|20|200+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert2 s1 insert1 c2 check1 c1 mv+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step s1: SELECT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c2: COMMIT;+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert2 s1 c2 insert1 check1 c1 mv+step s2: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step s1: SELECT;+step c2: COMMIT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert2 c2 insert1 check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert2: INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201);+step c2: COMMIT;+step insert1: INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200);+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv();+ x| y| z+--+--+---+10|10|100+10|11|100+11|10|100+11|11|100+21|21|201+(5 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/expected/ivm-refresh-insert.out b/src/test/isolation/expected/ivm-refresh-insert.outnew file mode 100644index 00000000000..a925938bf1c--- /dev/null+++ b/src/test/isolation/expected/ivm-refresh-insert.out@@ -0,0 +1,213 @@+Parsed test spec with 2 sessions++starting permutation: s1 refresh s2 insert c1 check2 c2 mv+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 refresh s2 c1 insert check2 c2 mv+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step s2: SELECT;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 refresh insert c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step insert: INSERT INTO a VALUES (2); <waiting ...>+step c1: COMMIT;+step insert: <... completed>+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert refresh c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step refresh: REFRESH MATERIALIZED VIEW mv; <waiting ...>+step c2: COMMIT;+step refresh: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 refresh c1 insert check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 refresh c2 check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv; <waiting ...>+step c2: COMMIT;+step refresh: <... completed>+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 c2 refresh check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step c2: COMMIT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert c2 refresh check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert: INSERT INTO a VALUES (2);+step c2: COMMIT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step check1: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/expected/ivm-refresh-insert2.out b/src/test/isolation/expected/ivm-refresh-insert2.outnew file mode 100644index 00000000000..dc1b16ae34e--- /dev/null+++ b/src/test/isolation/expected/ivm-refresh-insert2.out@@ -0,0 +1,191 @@+Parsed test spec with 2 sessions++starting permutation: s1 refresh s2 insert c1 check2 c2 mv+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 refresh s2 c1 insert check2 c2 mv+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step s2: SELECT;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 refresh insert c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step insert: INSERT INTO a VALUES (2);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert refresh c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step refresh: REFRESH MATERIALIZED VIEW mv; <waiting ...>+step c2: COMMIT;+step refresh: <... completed>+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 refresh c1 insert check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 refresh c2 check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv; <waiting ...>+step c2: COMMIT;+step refresh: <... completed>+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 c2 refresh check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step c2: COMMIT;+step refresh: REFRESH MATERIALIZED VIEW mv;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert c2 refresh check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert: INSERT INTO a VALUES (2);+step c2: COMMIT;+step refresh: REFRESH MATERIALIZED VIEW mv;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/expected/ivm-refresh-insert3.out b/src/test/isolation/expected/ivm-refresh-insert3.outnew file mode 100644index 00000000000..dc1b16ae34e--- /dev/null+++ b/src/test/isolation/expected/ivm-refresh-insert3.out@@ -0,0 +1,191 @@+Parsed test spec with 2 sessions++starting permutation: s1 refresh s2 insert c1 check2 c2 mv+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 refresh s2 c1 insert check2 c2 mv+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step s2: SELECT;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 refresh insert c1 check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step insert: INSERT INTO a VALUES (2);+ERROR: could not obtain lock on materialized view "mv" during incremental maintenance+step c1: COMMIT;+step check2: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+(1 row)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 insert refresh c2 check1 c1 mv+step s1: SELECT;+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step refresh: REFRESH MATERIALIZED VIEW mv; <waiting ...>+step c2: COMMIT;+step refresh: <... completed>+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s1 s2 refresh c1 insert check2 c2 mv+step s1: SELECT;+step s2: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv;+step c1: COMMIT;+step insert: INSERT INTO a VALUES (2);+step check2: SELECT check_mv();+check_mv+--------+ok +(1 row)++step c2: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 refresh c2 check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step refresh: REFRESH MATERIALIZED VIEW mv; <waiting ...>+step c2: COMMIT;+step refresh: <... completed>+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 insert s1 c2 refresh check1 c1 mv+step s2: SELECT;+step insert: INSERT INTO a VALUES (2);+step s1: SELECT;+step c2: COMMIT;+step refresh: REFRESH MATERIALIZED VIEW mv;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+++starting permutation: s2 s1 insert c2 refresh check1 c1 mv+step s2: SELECT;+step s1: SELECT;+step insert: INSERT INTO a VALUES (2);+step c2: COMMIT;+step refresh: REFRESH MATERIALIZED VIEW mv;+ERROR: the materialized view is incrementally updated in concurrent transaction+step check1: SELECT check_mv();+ERROR: current transaction is aborted, commands ignored until end of transaction block+step c1: COMMIT;+step mv: SELECT * FROM mv ORDER BY 1,2; SELECT check_mv();+x|y+-+-+1|1+2|2+(2 rows)++check_mv+--------+ok +(1 row)+diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_scheduleindex 26abed9f9f0..b475bd57cce 100644--- a/src/test/isolation/isolation_schedule+++ b/src/test/isolation/isolation_schedule@@ -129,3 +129,12 @@ test: lock-nowait
test: for-portion-of
test: ddl-dependency-locking
test: pub-concurrent-drop
+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/isolation/specs/ivm-create-insert.spec b/src/test/isolation/specs/ivm-create-insert.specnew file mode 100644index 00000000000..1a1f11483d0--- /dev/null+++ b/src/test/isolation/specs/ivm-create-insert.spec@@ -0,0 +1,47 @@+# Test interaction between concurrent transactions performing+# create and insert in READ COMMITTED isolation level++setup+{+ CREATE TABLE a (i int);+ INSERT INTO a VALUES (1);+ CREATE VIEW v(x,y) AS SELECT * FROM a AS a1, a AS a2 WHERE a1.i = a2.i;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; }+step s1 { SELECT; }+step create {+ CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}+step check1 {SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; }+step s2 { SELECT; }+step insert { INSERT INTO a VALUES (2); }+step check2 { SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 create s2 insert c1 check2 c2 mv+permutation s1 create s2 c1 insert check2 c2 mv+permutation s1 s2 create insert c1 check2 c2 mv+permutation s1 s2 insert create c2 check1 c1 mv+permutation s1 s2 create c1 insert check2 c2 mv+permutation s2 insert s1 create c2 check1 c1 mv+permutation s2 insert s1 c2 create check1 c1 mv+permutation s2 s1 insert c2 create check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-create-insert2.spec b/src/test/isolation/specs/ivm-create-insert2.specnew file mode 100644index 00000000000..c6bbacf0af0--- /dev/null+++ b/src/test/isolation/specs/ivm-create-insert2.spec@@ -0,0 +1,54 @@+# Test interaction between concurrent transactions performing+# create and insert in REPEATABLE READ isolation level+#+# Note:+# In this isolation level, it is possible that create_immv could+# create an inconsistent view not including effects of a concurrent+# transaction. So, an warning message is raised to suggest using it+# in READ COMMITTED or executing refresh_immv to make sure to+# make the view contents consistent.++setup+{+ CREATE TABLE a (i int);+ INSERT INTO a VALUES (1);+ CREATE VIEW v(x,y) AS SELECT * FROM a AS a1, a AS a2 WHERE a1.i = a2.i;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }+step s1 { SELECT; }+step create {+ CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}+step check1 {SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }+step s2 { SELECT; }+step insert { INSERT INTO a VALUES (2); }+step check2 {SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 create s2 insert c1 check2 c2 mv+permutation s1 create s2 c1 insert check2 c2 mv+permutation s1 s2 create insert c1 check2 c2 mv+permutation s1 s2 insert create c2 check1 c1 mv+permutation s1 s2 create c1 insert check2 c2 mv+permutation s2 insert s1 create c2 check1 c1 mv+permutation s2 insert s1 c2 create check1 c1 mv+permutation s2 s1 insert c2 create check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-create-insert3.spec b/src/test/isolation/specs/ivm-create-insert3.specnew file mode 100644index 00000000000..e3ba6a70493--- /dev/null+++ b/src/test/isolation/specs/ivm-create-insert3.spec@@ -0,0 +1,54 @@+# Test interaction between concurrent transactions performing+# create and insert in SERIALIZABLE isolation level+#+# Note:+# In this isolation level, it is possible that create_immv could+# create an inconsistent view not including effects of a concurrent+# transaction. So, an warning message is raised to suggest using it+# in READ COMMITTED or executing refresh_immv to make sure to+# make the view contents consistent.++setup+{+ CREATE TABLE a (i int);+ INSERT INTO a VALUES (1);+ CREATE VIEW v(x,y) AS SELECT * FROM a AS a1, a AS a2 WHERE a1.i = a2.i;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; }+step s1 { SELECT; }+step create {+ CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}+step check1 {SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; }+step s2 { SELECT; }+step insert { INSERT INTO a VALUES (2); }+step check2 {SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 create s2 insert c1 check2 c2 mv+permutation s1 create s2 c1 insert check2 c2 mv+permutation s1 s2 create insert c1 check2 c2 mv+permutation s1 s2 insert create c2 check1 c1 mv+permutation s1 s2 create c1 insert check2 c2 mv+permutation s2 insert s1 create c2 check1 c1 mv+permutation s2 insert s1 c2 create check1 c1 mv+permutation s2 s1 insert c2 create check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-insert-insert.spec b/src/test/isolation/specs/ivm-insert-insert.specnew file mode 100644index 00000000000..ee9e46a3379--- /dev/null+++ b/src/test/isolation/specs/ivm-insert-insert.spec@@ -0,0 +1,62 @@+# Test interaction between concurrent transactions performing+# table modifications in READ COMMITTED isolation level++setup+{+ CREATE TABLE a (i int, j int);+ CREATE TABLE b (i int, j int);+ INSERT INTO a VALUES (1,10);+ INSERT INTO b VALUES (1,100);+ CREATE MATERIALIZED VIEW mv(x,y,z) WITH (incremental_view_maintenance) AS+ SELECT a1.j, a2.j,b.j FROM a AS a1, a AS a2,b WHERE a1.i = a2.i AND a1.i = b.i;+ CREATE VIEW v(x,y,z) AS+ SELECT a1.j, a2.j,b.j FROM a AS a1, a AS a2,b WHERE a1.i = a2.i AND a1.i = b.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+ DROP TABLE b;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; }+step s1 { SELECT; }+step insert1 { INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200); }+step update1 { UPDATE a SET j = 11 WHERE i = 1; }+step check1 { SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; }+step s2 { SELECT; }+step insert2 { INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201); }+step update2 { UPDATE b SET j = 111 WHERE i = 1; }+step check2 { SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 update1 s2 update2 c1 check2 c2 mv+permutation s1 update1 s2 c1 update2 check2 c2 mv+permutation s1 s2 update1 update2 c1 check2 c2 mv+permutation s1 s2 update2 update1 c2 check1 c1 mv+permutation s1 s2 update1 c1 update2 check2 c2 mv+permutation s2 update2 s1 update1 c2 check1 c1 mv+permutation s2 update2 s1 c2 update1 check1 c1 mv+permutation s2 s1 update2 c2 update1 check1 c1 mv++permutation s1 insert1 s2 insert2 c1 check2 c2 mv+permutation s1 insert1 s2 c1 insert2 check2 c2 mv+permutation s1 s2 insert1 insert2 c1 check2 c2 mv+permutation s1 s2 insert2 insert1 c2 check1 c1 mv+permutation s1 s2 insert1 c1 insert2 check2 c2 mv+permutation s2 insert2 s1 insert1 c2 check1 c1 mv+permutation s2 insert2 s1 c2 insert1 check1 c1 mv+permutation s2 s1 insert2 c2 insert1 check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-insert-insert2.spec b/src/test/isolation/specs/ivm-insert-insert2.specnew file mode 100644index 00000000000..9c312d600e3--- /dev/null+++ b/src/test/isolation/specs/ivm-insert-insert2.spec@@ -0,0 +1,62 @@+# Test interaction between concurrent transactions performing+# table modifications in REPEATABLE READ isolation level++setup+{+ CREATE TABLE a (i int, j int);+ CREATE TABLE b (i int, j int);+ INSERT INTO a VALUES (1,10);+ INSERT INTO b VALUES (1,100);+ CREATE MATERIALIZED VIEW mv(x,y,z) WITH (incremental_view_maintenance) AS+ SELECT a1.j, a2.j,b.j FROM a AS a1, a AS a2,b WHERE a1.i = a2.i AND a1.i = b.i;+ CREATE VIEW v(x,y,z) AS+ SELECT a1.j, a2.j,b.j FROM a AS a1, a AS a2,b WHERE a1.i = a2.i AND a1.i = b.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+ DROP TABLE b;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }+step s1 { SELECT; }+step insert1 { INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200); }+step update1 { UPDATE a SET j = 11 WHERE i = 1; }+step check1 { SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }+step s2 { SELECT; }+step insert2 { INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201); }+step update2 { UPDATE b SET j = 111 WHERE i = 1; }+step check2 { SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 update1 s2 update2 c1 check2 c2 mv+permutation s1 update1 s2 c1 update2 check2 c2 mv+permutation s1 s2 update1 update2 c1 check2 c2 mv+permutation s1 s2 update2 update1 c2 check1 c1 mv+permutation s1 s2 update1 c1 update2 check2 c2 mv+permutation s2 update2 s1 update1 c2 check1 c1 mv+permutation s2 update2 s1 c2 update1 check1 c1 mv+permutation s2 s1 update2 c2 update1 check1 c1 mv++permutation s1 insert1 s2 insert2 c1 check2 c2 mv+permutation s1 insert1 s2 c1 insert2 check2 c2 mv+permutation s1 s2 insert1 insert2 c1 check2 c2 mv+permutation s1 s2 insert2 insert1 c2 check1 c1 mv+permutation s1 s2 insert1 c1 insert2 check2 c2 mv+permutation s2 insert2 s1 insert1 c2 check1 c1 mv+permutation s2 insert2 s1 c2 insert1 check1 c1 mv+permutation s2 s1 insert2 c2 insert1 check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-insert-insert3.spec b/src/test/isolation/specs/ivm-insert-insert3.specnew file mode 100644index 00000000000..69c73a3c6cf--- /dev/null+++ b/src/test/isolation/specs/ivm-insert-insert3.spec@@ -0,0 +1,62 @@+# Test interaction between concurrent transactions performing+# table modifications in SERIALIZABLE isolation level++setup+{+ CREATE TABLE a (i int, j int);+ CREATE TABLE b (i int, j int);+ INSERT INTO a VALUES (1,10);+ INSERT INTO b VALUES (1,100);+ CREATE MATERIALIZED VIEW mv(x,y,z) WITH (incremental_view_maintenance) AS+ SELECT a1.j, a2.j,b.j FROM a AS a1, a AS a2,b WHERE a1.i = a2.i AND a1.i = b.i;+ CREATE VIEW v(x,y,z) AS+ SELECT a1.j, a2.j,b.j FROM a AS a1, a AS a2,b WHERE a1.i = a2.i AND a1.i = b.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+ DROP TABLE b;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; }+step s1 { SELECT; }+step insert1 { INSERT INTO a VALUES (2,20); INSERT INTO b VALUES (2,200); }+step update1 { UPDATE a SET j = 11 WHERE i = 1; }+step check1 { SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2,3; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; }+step s2 { SELECT; }+step insert2 { INSERT INTO a VALUES (1,11), (2,21); INSERT INTO b VALUES (2,201); }+step update2 { UPDATE b SET j = 111 WHERE i = 1; }+step check2 { SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 update1 s2 update2 c1 check2 c2 mv+permutation s1 update1 s2 c1 update2 check2 c2 mv+permutation s1 s2 update1 update2 c1 check2 c2 mv+permutation s1 s2 update2 update1 c2 check1 c1 mv+permutation s1 s2 update1 c1 update2 check2 c2 mv+permutation s2 update2 s1 update1 c2 check1 c1 mv+permutation s2 update2 s1 c2 update1 check1 c1 mv+permutation s2 s1 update2 c2 update1 check1 c1 mv++permutation s1 insert1 s2 insert2 c1 check2 c2 mv+permutation s1 insert1 s2 c1 insert2 check2 c2 mv+permutation s1 s2 insert1 insert2 c1 check2 c2 mv+permutation s1 s2 insert2 insert1 c2 check1 c1 mv+permutation s1 s2 insert1 c1 insert2 check2 c2 mv+permutation s2 insert2 s1 insert1 c2 check1 c1 mv+permutation s2 insert2 s1 c2 insert1 check1 c1 mv+permutation s2 s1 insert2 c2 insert1 check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-refresh-insert.spec b/src/test/isolation/specs/ivm-refresh-insert.specnew file mode 100644index 00000000000..9fe6a79509a--- /dev/null+++ b/src/test/isolation/specs/ivm-refresh-insert.spec@@ -0,0 +1,46 @@+# Test interaction between concurrent transactions performing+# refresh and insert in READ COMMITTED isolation level++setup+{+ CREATE TABLE a (i int);+ INSERT INTO a VALUES (1);+ CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE VIEW v(x,y) AS SELECT * FROM a AS a1, a AS a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; }+step s1 { SELECT; }+step refresh { REFRESH MATERIALIZED VIEW mv; }+step check1 {SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; }+step s2 { SELECT; }+step insert { INSERT INTO a VALUES (2); }+step check2 {SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 refresh s2 insert c1 check2 c2 mv+permutation s1 refresh s2 c1 insert check2 c2 mv+permutation s1 s2 refresh insert c1 check2 c2 mv+permutation s1 s2 insert refresh c2 check1 c1 mv+permutation s1 s2 refresh c1 insert check2 c2 mv+permutation s2 insert s1 refresh c2 check1 c1 mv+permutation s2 insert s1 c2 refresh check1 c1 mv+permutation s2 s1 insert c2 refresh check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-refresh-insert2.spec b/src/test/isolation/specs/ivm-refresh-insert2.specnew file mode 100644index 00000000000..94875ef04e4--- /dev/null+++ b/src/test/isolation/specs/ivm-refresh-insert2.spec@@ -0,0 +1,46 @@+# Test interaction between concurrent transactions performing+# refresh and insert in REPEATABLE READ isolation level++setup+{+ CREATE TABLE a (i int);+ INSERT INTO a VALUES (1);+ CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE VIEW v(x,y) AS SELECT * FROM a AS a1, a AS a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }+step s1 { SELECT; }+step refresh { REFRESH MATERIALIZED VIEW mv; }+step check1 {SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; }+step s2 { SELECT; }+step insert { INSERT INTO a VALUES (2); }+step check2 {SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 refresh s2 insert c1 check2 c2 mv+permutation s1 refresh s2 c1 insert check2 c2 mv+permutation s1 s2 refresh insert c1 check2 c2 mv+permutation s1 s2 insert refresh c2 check1 c1 mv+permutation s1 s2 refresh c1 insert check2 c2 mv+permutation s2 insert s1 refresh c2 check1 c1 mv+permutation s2 insert s1 c2 refresh check1 c1 mv+permutation s2 s1 insert c2 refresh check1 c1 mvdiff --git a/src/test/isolation/specs/ivm-refresh-insert3.spec b/src/test/isolation/specs/ivm-refresh-insert3.specnew file mode 100644index 00000000000..ea645e55b98--- /dev/null+++ b/src/test/isolation/specs/ivm-refresh-insert3.spec@@ -0,0 +1,46 @@+# Test interaction between concurrent transactions performing+# refresh and insert in SERIALIZABLE isolation level++setup+{+ CREATE TABLE a (i int);+ INSERT INTO a VALUES (1);+ CREATE MATERIALIZED VIEW mv(x,y) WITH (incremental_view_maintenance) AS SELECT * FROM a a1, a a2 WHERE a1.i = a2.i;+ CREATE VIEW v(x,y) AS SELECT * FROM a AS a1, a AS a2 WHERE a1.i = a2.i;+ CREATE FUNCTION check_mv() RETURNS text AS+ $$ SELECT CASE WHEN count(*) = 0 THEN 'ok' ELSE 'ng' END+ FROM ((SELECT * FROM mv EXCEPT ALL SELECT * FROM v) UNION ALL+ (SELECT * FROM v EXCEPT ALL SELECT * FROM mv)) v $$ LANGUAGE sql;+}++teardown+{+ DROP FUNCTION check_mv();+ DROP MATERIALIZED VIEW mv;+ DROP VIEW v;+ DROP TABLE a;+}++session tx1+setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; }+step s1 { SELECT; }+step refresh { REFRESH MATERIALIZED VIEW mv; }+step check1 {SELECT check_mv();}+step c1 { COMMIT; }+step mv { SELECT * FROM mv ORDER BY 1,2; SELECT check_mv(); }++session tx2+setup { BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; }+step s2 { SELECT; }+step insert { INSERT INTO a VALUES (2); }+step check2 {SELECT check_mv(); }+step c2 { COMMIT; }++permutation s1 refresh s2 insert c1 check2 c2 mv+permutation s1 refresh s2 c1 insert check2 c2 mv+permutation s1 s2 refresh insert c1 check2 c2 mv+permutation s1 s2 insert refresh c2 check1 c1 mv+permutation s1 s2 refresh c1 insert check2 c2 mv+permutation s2 insert s1 refresh c2 check1 c1 mv+permutation s2 insert s1 c2 refresh check1 c1 mv+permutation s2 s1 insert c2 refresh check1 c1 mvdiff --git a/src/test/regress/expected/incremental_matview.out b/src/test/regress/expected/incremental_matview.outnew file mode 100644index 00000000000..8b60ed2295b--- /dev/null+++ b/src/test/regress/expected/incremental_matview.out@@ -0,0 +1,667 @@+-- 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 MATERIALIZED VIEW mv_ivm_1 WITH (incremental_view_maintenance) 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. +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)++-- DISTINCT not supported+CREATE MATERIALIZED VIEW mv_ivm_distinct WITH (incremental_view_maintenance) AS SELECT DISTINCT * FROM mv_base_a;+ERROR: DISTINCT is not supported on incrementally maintainable materialized view+-- 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 MATERIALIZED VIEW mv_ivm_func WITH (incremental_view_maintenance) 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. +HINT: Create an index on the materialized view for efficient incremental maintenance.+CREATE MATERIALIZED VIEW mv_ivm_no_tbl WITH (incremental_view_maintenance) 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. +HINT: Create an index on the materialized view for efficient incremental maintenance.+ROLLBACK;+-- result of materialized view have duplicate result.+BEGIN;+CREATE MATERIALIZED VIEW mv_ivm_duplicate WITH (incremental_view_maintenance) 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. +HINT: Create an index on the materialized view for efficient incremental maintenance.+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)++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)++ROLLBACK;+-- aggregate not supported+CREATE MATERIALIZED VIEW mv_ivm_agg WITH (incremental_view_maintenance) AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;+ERROR: aggregate function is not supported on incrementally maintainable materialized view+-- 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 MATERIALIZED VIEW mv_self(v1, v2) WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv(v1, v2) WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv_ri(i1, i2) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv WITH (incremental_view_maintenance) 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. +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;+-- 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 MATERIALIZED VIEW mv_mytype WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv(a,b) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm01 WITH (incremental_view_maintenance) AS SELECT i,j,xmin FROM mv_base_a;+ERROR: system column is not supported on incrementally maintainable materialized view+CREATE MATERIALIZED VIEW mv_ivm02 WITH (incremental_view_maintenance) AS SELECT i,j FROM mv_base_a WHERE xmin = '610';+ERROR: system column is not supported on incrementally maintainable materialized view+CREATE MATERIALIZED VIEW mv_ivm04 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm06 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm03 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm04 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm05 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm07 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm08 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm07 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm08 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm09 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm10 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm12 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm13 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm14 WITH (incremental_view_maintenance) AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;+ERROR: DISTINCT is not supported on incrementally maintainable materialized view+-- TABLESAMPLE clause is not supported+CREATE MATERIALIZED VIEW mv_ivm15 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm16 WITH (incremental_view_maintenance) 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+-- inheritance parent is not supported+BEGIN;+CREATE TABLE parent (i int, v int);+CREATE TABLE child_a(options text) INHERITS(parent);+CREATE MATERIALIZED VIEW mv_ivm21 WITH (incremental_view_maintenance) AS SELECT * FROM parent;+ERROR: inheritance parent is not supported on incrementally maintainable materialized view+ROLLBACK;+-- UNION statement is not supported+CREATE MATERIALIZED VIEW mv_ivm22 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm25 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm26 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm28 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm29 WITH (incremental_view_maintenance) AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;+ERROR: aggregate function is not supported on incrementally maintainable materialized view+-- VALUES is not supported+CREATE MATERIALIZED VIEW mv_ivm_only_values1 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW ivm_rls WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW ivm_rls2 WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv_update_same WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv_self_ref WITH (incremental_view_maintenance) 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);+--- with all pkey columns: create an index+CREATE MATERIALIZED VIEW mv_idx3(i_a, i_b) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_idx4 WITH (incremental_view_maintenance) 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. +HINT: Create an index on the materialized view for efficient incremental maintenance.+CREATE MATERIALIZED VIEW mv_idx5 WITH (incremental_view_maintenance) 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. +HINT: Create an index on the materialized view for efficient incremental maintenance.+--- with set-returning function: no index+CREATE MATERIALIZED VIEW mv_idx6 WITH (incremental_view_maintenance) 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. +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 MATERIALIZED VIEW mv_json WITH (incremental_view_maintenance) 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..bf527199f96--- /dev/null+++ b/src/test/regress/sql/incremental_matview.sql@@ -0,0 +1,407 @@+-- 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 MATERIALIZED VIEW mv_ivm_1 WITH (incremental_view_maintenance) 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;++-- DISTINCT not supported+CREATE MATERIALIZED VIEW mv_ivm_distinct WITH (incremental_view_maintenance) AS SELECT DISTINCT * FROM mv_base_a;++-- 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 MATERIALIZED VIEW mv_ivm_func WITH (incremental_view_maintenance) AS SELECT * FROM ivm_func();+CREATE MATERIALIZED VIEW mv_ivm_no_tbl WITH (incremental_view_maintenance) AS SELECT 1;+ROLLBACK;++-- result of materialized view have duplicate result.+BEGIN;+CREATE MATERIALIZED VIEW mv_ivm_duplicate WITH (incremental_view_maintenance) AS SELECT j FROM mv_base_a;+INSERT INTO mv_base_a VALUES(6,20);+SELECT * FROM mv_ivm_duplicate ORDER BY 1;+DELETE FROM mv_base_a WHERE (i,j) = (2,20);+SELECT * FROM mv_ivm_duplicate ORDER BY 1;+ROLLBACK;++-- aggregate not supported+CREATE MATERIALIZED VIEW mv_ivm_agg WITH (incremental_view_maintenance) AS SELECT i, SUM(j), COUNT(i), AVG(j) FROM mv_base_a GROUP BY i;++-- 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 MATERIALIZED VIEW mv_self(v1, v2) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv(v1, v2) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ri(i1, i2) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv WITH (incremental_view_maintenance) 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;++-- 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 MATERIALIZED VIEW mv_mytype WITH (incremental_view_maintenance) AS+ SELECT * FROM t_mytype;+INSERT INTO t_mytype VALUES ('1'::mytype);+SELECT * FROM mv_mytype;++ROLLBACK;++-- outer join is not supported+CREATE MATERIALIZED VIEW mv(a,b) WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm01 WITH (incremental_view_maintenance) AS SELECT i,j,xmin FROM mv_base_a;+CREATE MATERIALIZED VIEW mv_ivm02 WITH (incremental_view_maintenance) AS SELECT i,j FROM mv_base_a WHERE xmin = '610';+CREATE MATERIALIZED VIEW mv_ivm04 WITH (incremental_view_maintenance) AS SELECT i,j,xmin::text AS x_min FROM mv_base_a;+CREATE MATERIALIZED VIEW mv_ivm06 WITH (incremental_view_maintenance) AS SELECT i,j,xidsend(xmin) AS x_min FROM mv_base_a;+-- contain subquery+CREATE MATERIALIZED VIEW mv_ivm03 WITH (incremental_view_maintenance) AS SELECT i,j FROM mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );+CREATE MATERIALIZED VIEW mv_ivm04 WITH (incremental_view_maintenance) AS SELECT a.i,a.j FROM mv_base_a a, (SELECT * FROM mv_base_b) b WHERE a.i = b.i;+CREATE MATERIALIZED VIEW mv_ivm05 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm07 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm08 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm07 WITH (incremental_view_maintenance) AS SELECT a.i,a.j FROM mv_base_a a,b_view b WHERE a.i = b.i;+CREATE MATERIALIZED VIEW mv_ivm08 WITH (incremental_view_maintenance) AS SELECT a.i,a.j FROM mv_base_a a,b_mview b WHERE a.i = b.i;+CREATE MATERIALIZED VIEW mv_ivm09 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm10 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm12 WITH (incremental_view_maintenance) AS SELECT i,j FROM mv_base_a WHERE i = random()::int;++-- LIMIT/OFFSET is not supported+CREATE MATERIALIZED VIEW mv_ivm13 WITH (incremental_view_maintenance) AS SELECT i,j FROM mv_base_a LIMIT 10 OFFSET 5;++-- DISTINCT ON is not supported+CREATE MATERIALIZED VIEW mv_ivm14 WITH (incremental_view_maintenance) AS SELECT DISTINCT ON(i) i, j FROM mv_base_a;++-- TABLESAMPLE clause is not supported+CREATE MATERIALIZED VIEW mv_ivm15 WITH (incremental_view_maintenance) AS SELECT i, j FROM mv_base_a TABLESAMPLE SYSTEM(50);++-- window functions are not supported+CREATE MATERIALIZED VIEW mv_ivm16 WITH (incremental_view_maintenance) AS SELECT *, cume_dist() OVER (ORDER BY i) AS rank FROM mv_base_a;++-- inheritance parent is not supported+BEGIN;+CREATE TABLE parent (i int, v int);+CREATE TABLE child_a(options text) INHERITS(parent);+CREATE MATERIALIZED VIEW mv_ivm21 WITH (incremental_view_maintenance) AS SELECT * FROM parent;+ROLLBACK;++-- UNION statement is not supported+CREATE MATERIALIZED VIEW mv_ivm22 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_ivm25 WITH (incremental_view_maintenance) AS SELECT FROM mv_base_a;++-- FOR UPDATE/SHARE is not supported+CREATE MATERIALIZED VIEW mv_ivm26 WITH (incremental_view_maintenance) AS SELECT i,j FROM mv_base_a FOR UPDATE;++-- tartget list cannot contain ivm column that start with '__ivm'+CREATE MATERIALIZED VIEW mv_ivm28 WITH (incremental_view_maintenance) AS SELECT i AS "__ivm_count__" FROM mv_base_a;++-- expressions specified in GROUP BY must appear in the target list.+CREATE MATERIALIZED VIEW mv_ivm29 WITH (incremental_view_maintenance) AS SELECT COUNT(i) FROM mv_base_a GROUP BY i;++-- VALUES is not supported+CREATE MATERIALIZED VIEW mv_ivm_only_values1 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW ivm_rls WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW ivm_rls2 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_update_same WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_self_ref WITH (incremental_view_maintenance) 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);++--- with all pkey columns: create an index+CREATE MATERIALIZED VIEW mv_idx3(i_a, i_b) WITH (incremental_view_maintenance) AS SELECT a.i, b.i FROM base_a a, base_b b;++--- missing some pkey columns: no index+CREATE MATERIALIZED VIEW mv_idx4 WITH (incremental_view_maintenance) AS SELECT j FROM base_a;+CREATE MATERIALIZED VIEW mv_idx5 WITH (incremental_view_maintenance) AS SELECT a.i, b.j FROM base_a a, base_b b;++--- with set-returning function: no index+CREATE MATERIALIZED VIEW mv_idx6 WITH (incremental_view_maintenance) 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 MATERIALIZED VIEW mv_json WITH (incremental_view_maintenance) 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=_Sun__2_Aug_2026_13_39_23_+0900_ERMG4bYmcAMN_ECT
Content-Type: text/x-diff;
name="v40-0003-Add-Incremental-View-Maintenance-support.patch"
Content-Disposition: attachment;
filename="v40-0003-Add-Incremental-View-Maintenance-support.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 v40 4/5] Add regression tests for Incremental View Maintenance
In-Reply-To: <no-message-id-1149759@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