diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/matview.out | 694 |
1 files changed, 694 insertions, 0 deletions
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out new file mode 100644 index 0000000..67a50bd --- /dev/null +++ b/src/test/regress/expected/matview.out @@ -0,0 +1,694 @@ +-- create a table to use as a basis for views and materialized views in various combinations +CREATE TABLE mvtest_t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL); +INSERT INTO mvtest_t VALUES + (1, 'x', 2), + (2, 'x', 3), + (3, 'y', 5), + (4, 'y', 7), + (5, 'z', 11); +-- we want a view based on the table, too, since views present additional challenges +CREATE VIEW mvtest_tv AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type; +SELECT * FROM mvtest_tv ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +-- create a materialized view with no data, and confirm correct behavior +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA; + QUERY PLAN +---------------------------- + HashAggregate + Group Key: type + -> Seq Scan on mvtest_t +(3 rows) + +CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA; +SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass; + relispopulated +---------------- + f +(1 row) + +SELECT * FROM mvtest_tm ORDER BY type; +ERROR: materialized view "mvtest_tm" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +REFRESH MATERIALIZED VIEW mvtest_tm; +SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass; + relispopulated +---------------- + t +(1 row) + +CREATE UNIQUE INDEX mvtest_tm_type ON mvtest_tm (type); +SELECT * FROM mvtest_tm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +-- create various views +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type; + QUERY PLAN +---------------------------------- + Sort + Sort Key: mvtest_t.type + -> HashAggregate + Group Key: mvtest_t.type + -> Seq Scan on mvtest_t +(5 rows) + +CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type; +SELECT * FROM mvtest_tvm; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +CREATE MATERIALIZED VIEW mvtest_tmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tm; +CREATE MATERIALIZED VIEW mvtest_tvmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tvm; +CREATE UNIQUE INDEX mvtest_tvmm_expr ON mvtest_tvmm ((grandtot > 0)); +CREATE UNIQUE INDEX mvtest_tvmm_pred ON mvtest_tvmm (grandtot) WHERE grandtot < 0; +CREATE VIEW mvtest_tvv AS SELECT sum(totamt) AS grandtot FROM mvtest_tv; +EXPLAIN (costs off) + CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv; + QUERY PLAN +---------------------------------- + Aggregate + -> HashAggregate + Group Key: mvtest_t.type + -> Seq Scan on mvtest_t +(4 rows) + +CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv; +CREATE VIEW mvtest_tvvmv AS SELECT * FROM mvtest_tvvm; +CREATE MATERIALIZED VIEW mvtest_bb AS SELECT * FROM mvtest_tvvmv; +CREATE INDEX mvtest_aa ON mvtest_bb (grandtot); +-- check that plans seem reasonable +\d+ mvtest_tvm + Materialized view "public.mvtest_tvm" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+----------+--------------+------------- + type | text | | | | extended | | + totamt | numeric | | | | main | | +View definition: + SELECT type, + totamt + FROM mvtest_tv + ORDER BY type; + +\d+ mvtest_tvm + Materialized view "public.mvtest_tvm" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+----------+--------------+------------- + type | text | | | | extended | | + totamt | numeric | | | | main | | +View definition: + SELECT type, + totamt + FROM mvtest_tv + ORDER BY type; + +\d+ mvtest_tvvm + Materialized view "public.mvtest_tvvm" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------+---------+-----------+----------+---------+---------+--------------+------------- + grandtot | numeric | | | | main | | +View definition: + SELECT grandtot + FROM mvtest_tvv; + +\d+ mvtest_bb + Materialized view "public.mvtest_bb" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------+---------+-----------+----------+---------+---------+--------------+------------- + grandtot | numeric | | | | main | | +Indexes: + "mvtest_aa" btree (grandtot) +View definition: + SELECT grandtot + FROM mvtest_tvvmv; + +-- test schema behavior +CREATE SCHEMA mvtest_mvschema; +ALTER MATERIALIZED VIEW mvtest_tvm SET SCHEMA mvtest_mvschema; +\d+ mvtest_tvm +\d+ mvtest_tvmm + Materialized view "public.mvtest_tvmm" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------+---------+-----------+----------+---------+---------+--------------+------------- + grandtot | numeric | | | | main | | +Indexes: + "mvtest_tvmm_expr" UNIQUE, btree ((grandtot > 0::numeric)) + "mvtest_tvmm_pred" UNIQUE, btree (grandtot) WHERE grandtot < 0::numeric +View definition: + SELECT sum(totamt) AS grandtot + FROM mvtest_mvschema.mvtest_tvm; + +SET search_path = mvtest_mvschema, public; +\d+ mvtest_tvm + Materialized view "mvtest_mvschema.mvtest_tvm" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+----------+--------------+------------- + type | text | | | | extended | | + totamt | numeric | | | | main | | +View definition: + SELECT type, + totamt + FROM mvtest_tv + ORDER BY type; + +-- modify the underlying table data +INSERT INTO mvtest_t VALUES (6, 'z', 13); +-- confirm pre- and post-refresh contents of fairly simple materialized views +SELECT * FROM mvtest_tm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +SELECT * FROM mvtest_tvm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 11 +(3 rows) + +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tm; +REFRESH MATERIALIZED VIEW mvtest_tvm; +SELECT * FROM mvtest_tm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 24 +(3 rows) + +SELECT * FROM mvtest_tvm ORDER BY type; + type | totamt +------+-------- + x | 5 + y | 12 + z | 24 +(3 rows) + +RESET search_path; +-- confirm pre- and post-refresh contents of nested materialized views +EXPLAIN (costs off) + SELECT * FROM mvtest_tmm; + QUERY PLAN +------------------------ + Seq Scan on mvtest_tmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM mvtest_tvmm; + QUERY PLAN +------------------------- + Seq Scan on mvtest_tvmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM mvtest_tvvm; + QUERY PLAN +------------------------- + Seq Scan on mvtest_tvvm +(1 row) + +SELECT * FROM mvtest_tmm; + grandtot +---------- + 28 +(1 row) + +SELECT * FROM mvtest_tvmm; + grandtot +---------- + 28 +(1 row) + +SELECT * FROM mvtest_tvvm; + grandtot +---------- + 28 +(1 row) + +REFRESH MATERIALIZED VIEW mvtest_tmm; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm; +ERROR: cannot refresh materialized view "public.mvtest_tvmm" concurrently +HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view. +REFRESH MATERIALIZED VIEW mvtest_tvmm; +REFRESH MATERIALIZED VIEW mvtest_tvvm; +EXPLAIN (costs off) + SELECT * FROM mvtest_tmm; + QUERY PLAN +------------------------ + Seq Scan on mvtest_tmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM mvtest_tvmm; + QUERY PLAN +------------------------- + Seq Scan on mvtest_tvmm +(1 row) + +EXPLAIN (costs off) + SELECT * FROM mvtest_tvvm; + QUERY PLAN +------------------------- + Seq Scan on mvtest_tvvm +(1 row) + +SELECT * FROM mvtest_tmm; + grandtot +---------- + 41 +(1 row) + +SELECT * FROM mvtest_tvmm; + grandtot +---------- + 41 +(1 row) + +SELECT * FROM mvtest_tvvm; + grandtot +---------- + 41 +(1 row) + +-- test diemv when the mv does not exist +DROP MATERIALIZED VIEW IF EXISTS no_such_mv; +NOTICE: materialized view "no_such_mv" does not exist, skipping +-- make sure invalid combination of options is prohibited +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm WITH NO DATA; +ERROR: CONCURRENTLY and WITH NO DATA options cannot be used together +-- no tuple locks on materialized views +SELECT * FROM mvtest_tvvm FOR SHARE; +ERROR: cannot lock rows in materialized view "mvtest_tvvm" +-- test join of mv and view +SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM mvtest_tm m LEFT JOIN mvtest_tv v USING (type) ORDER BY type; + type | mtot | vtot +------+------+------ + x | 5 | 5 + y | 12 | 12 + z | 24 | 24 +(3 rows) + +-- make sure that dependencies are reported properly when they block the drop +DROP TABLE mvtest_t; +ERROR: cannot drop table mvtest_t because other objects depend on it +DETAIL: view mvtest_tv depends on table mvtest_t +materialized view mvtest_mvschema.mvtest_tvm depends on view mvtest_tv +materialized view mvtest_tvmm depends on materialized view mvtest_mvschema.mvtest_tvm +view mvtest_tvv depends on view mvtest_tv +materialized view mvtest_tvvm depends on view mvtest_tvv +view mvtest_tvvmv depends on materialized view mvtest_tvvm +materialized view mvtest_bb depends on view mvtest_tvvmv +materialized view mvtest_tm depends on table mvtest_t +materialized view mvtest_tmm depends on materialized view mvtest_tm +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- make sure dependencies are dropped and reported +-- and make sure that transactional behavior is correct on rollback +-- incidentally leaving some interesting materialized views for pg_dump testing +BEGIN; +DROP TABLE mvtest_t CASCADE; +NOTICE: drop cascades to 9 other objects +DETAIL: drop cascades to view mvtest_tv +drop cascades to materialized view mvtest_mvschema.mvtest_tvm +drop cascades to materialized view mvtest_tvmm +drop cascades to view mvtest_tvv +drop cascades to materialized view mvtest_tvvm +drop cascades to view mvtest_tvvmv +drop cascades to materialized view mvtest_bb +drop cascades to materialized view mvtest_tm +drop cascades to materialized view mvtest_tmm +ROLLBACK; +-- some additional tests not using base tables +CREATE VIEW mvtest_vt1 AS SELECT 1 moo; +CREATE VIEW mvtest_vt2 AS SELECT moo, 2*moo FROM mvtest_vt1 UNION ALL SELECT moo, 3*moo FROM mvtest_vt1; +\d+ mvtest_vt2 + View "public.mvtest_vt2" + Column | Type | Collation | Nullable | Default | Storage | Description +----------+---------+-----------+----------+---------+---------+------------- + moo | integer | | | | plain | + ?column? | integer | | | | plain | +View definition: + SELECT mvtest_vt1.moo, + 2 * mvtest_vt1.moo AS "?column?" + FROM mvtest_vt1 +UNION ALL + SELECT mvtest_vt1.moo, + 3 * mvtest_vt1.moo + FROM mvtest_vt1; + +CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM mvtest_vt2 UNION ALL SELECT moo, 3*moo FROM mvtest_vt2; +\d+ mv_test2 + Materialized view "public.mv_test2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +----------+---------+-----------+----------+---------+---------+--------------+------------- + moo | integer | | | | plain | | + ?column? | integer | | | | plain | | +View definition: + SELECT mvtest_vt2.moo, + 2 * mvtest_vt2.moo AS "?column?" + FROM mvtest_vt2 +UNION ALL + SELECT mvtest_vt2.moo, + 3 * mvtest_vt2.moo + FROM mvtest_vt2; + +CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345; +SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass; + relispopulated +---------------- + t +(1 row) + +DROP VIEW mvtest_vt1 CASCADE; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to view mvtest_vt2 +drop cascades to materialized view mv_test2 +drop cascades to materialized view mv_test3 +-- test that duplicate values on unique index prevent refresh +CREATE TABLE mvtest_foo(a, b) AS VALUES(1, 10); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv(a); +INSERT INTO mvtest_foo SELECT * FROM mvtest_foo; +REFRESH MATERIALIZED VIEW mvtest_mv; +ERROR: could not create unique index "mvtest_mv_a_idx" +DETAIL: Key (a)=(1) is duplicated. +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +ERROR: new data for materialized view "mvtest_mv" contains duplicate rows without any null columns +DETAIL: Row: (1,10) +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- make sure that all columns covered by unique indexes works +CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); +CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; +CREATE UNIQUE INDEX ON mvtest_mv (a); +CREATE UNIQUE INDEX ON mvtest_mv (b); +CREATE UNIQUE INDEX on mvtest_mv (c); +INSERT INTO mvtest_foo VALUES(2, 3, 4); +INSERT INTO mvtest_foo VALUES(3, 4, 5); +REFRESH MATERIALIZED VIEW mvtest_mv; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; +DROP TABLE mvtest_foo CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv +-- allow subquery to reference unpopulated matview if WITH NO DATA is specified +CREATE MATERIALIZED VIEW mvtest_mv1 AS SELECT 1 AS col1 WITH NO DATA; +CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 + WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; +DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv2 +-- make sure that types with unusual equality tests work +CREATE TABLE mvtest_boxes (id serial primary key, b box); +INSERT INTO mvtest_boxes (b) VALUES + ('(32,32),(31,31)'), + ('(2.0000004,2.0000004),(1,1)'), + ('(1.9999996,1.9999996),(1,1)'); +CREATE MATERIALIZED VIEW mvtest_boxmv AS SELECT * FROM mvtest_boxes; +CREATE UNIQUE INDEX mvtest_boxmv_id ON mvtest_boxmv (id); +UPDATE mvtest_boxes SET b = '(2,2),(1,1)' WHERE id = 2; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_boxmv; +SELECT * FROM mvtest_boxmv ORDER BY id; + id | b +----+----------------------------- + 1 | (32,32),(31,31) + 2 | (2,2),(1,1) + 3 | (1.9999996,1.9999996),(1,1) +(3 rows) + +DROP TABLE mvtest_boxes CASCADE; +NOTICE: drop cascades to materialized view mvtest_boxmv +-- make sure that column names are handled correctly +CREATE TABLE mvtest_v (i int, j int); +CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error +ERROR: too many column names were specified +CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok +CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok +CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error +ERROR: too many column names were specified +CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok +CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok +ALTER TABLE mvtest_v RENAME COLUMN i TO x; +INSERT INTO mvtest_v values (1, 2); +CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii); +REFRESH MATERIALIZED VIEW mvtest_mv_v; +UPDATE mvtest_v SET j = 3 WHERE x = 1; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_v; +REFRESH MATERIALIZED VIEW mvtest_mv_v_2; +REFRESH MATERIALIZED VIEW mvtest_mv_v_3; +REFRESH MATERIALIZED VIEW mvtest_mv_v_4; +SELECT * FROM mvtest_v; + x | j +---+--- + 1 | 3 +(1 row) + +SELECT * FROM mvtest_mv_v; + ii | jj +----+---- + 1 | 3 +(1 row) + +SELECT * FROM mvtest_mv_v_2; + ii | j +----+--- + 1 | 3 +(1 row) + +SELECT * FROM mvtest_mv_v_3; + ii | jj +----+---- + 1 | 3 +(1 row) + +SELECT * FROM mvtest_mv_v_4; + ii | j +----+--- + 1 | 3 +(1 row) + +DROP TABLE mvtest_v CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to materialized view mvtest_mv_v +drop cascades to materialized view mvtest_mv_v_2 +drop cascades to materialized view mvtest_mv_v_3 +drop cascades to materialized view mvtest_mv_v_4 +-- Check that unknown literals are converted to "text" in CREATE MATVIEW, +-- so that we don't end up with unknown-type columns. +CREATE MATERIALIZED VIEW mv_unspecified_types AS + SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +\d+ mv_unspecified_types + Materialized view "public.mv_unspecified_types" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+----------+--------------+------------- + i | integer | | | | plain | | + num | numeric | | | | main | | + u | text | | | | extended | | + u2 | text | | | | extended | | + n | text | | | | extended | | +View definition: + SELECT 42 AS i, + 42.5 AS num, + 'foo'::text AS u, + 'foo'::text AS u2, + NULL::text AS n; + +SELECT * FROM mv_unspecified_types; + i | num | u | u2 | n +----+------+-----+-----+--- + 42 | 42.5 | foo | foo | +(1 row) + +DROP MATERIALIZED VIEW mv_unspecified_types; +-- make sure that create WITH NO DATA does not plan the query (bug #13907) +create materialized view mvtest_error as select 1/0 as x; -- fail +ERROR: division by zero +create materialized view mvtest_error as select 1/0 as x with no data; +refresh materialized view mvtest_error; -- fail here +ERROR: division by zero +drop materialized view mvtest_error; +-- make sure that matview rows can be referenced as source rows (bug #9398) +CREATE TABLE mvtest_v AS SELECT generate_series(1,10) AS a; +CREATE MATERIALIZED VIEW mvtest_mv_v AS SELECT a FROM mvtest_v WHERE a <= 5; +DELETE FROM mvtest_v WHERE EXISTS ( SELECT * FROM mvtest_mv_v WHERE mvtest_mv_v.a = mvtest_v.a ); +SELECT * FROM mvtest_v; + a +---- + 6 + 7 + 8 + 9 + 10 +(5 rows) + +SELECT * FROM mvtest_mv_v; + a +--- + 1 + 2 + 3 + 4 + 5 +(5 rows) + +DROP TABLE mvtest_v CASCADE; +NOTICE: drop cascades to materialized view mvtest_mv_v +-- make sure running as superuser works when MV owned by another role (bug #11208) +CREATE ROLE regress_user_mvtest; +SET ROLE regress_user_mvtest; +-- this test case also checks for ambiguity in the queries issued by +-- refresh_by_match_merge(), by choosing column names that intentionally +-- duplicate all the aliases used in those queries +CREATE TABLE mvtest_foo_data AS SELECT i, + i+1 AS tid, + fipshash(random()::text) AS mv, + fipshash(random()::text) AS newdata, + fipshash(random()::text) AS newdata2, + fipshash(random()::text) AS diff + FROM generate_series(1, 10) i; +CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; +CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; +ERROR: relation "mvtest_mv_foo" already exists +CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; +NOTICE: relation "mvtest_mv_foo" already exists, skipping +CREATE UNIQUE INDEX ON mvtest_mv_foo (i); +RESET ROLE; +REFRESH MATERIALIZED VIEW mvtest_mv_foo; +REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo; +DROP OWNED BY regress_user_mvtest CASCADE; +DROP ROLE regress_user_mvtest; +-- Concurrent refresh requires a unique index on the materialized +-- view. Test what happens if it's dropped during the refresh. +CREATE OR REPLACE FUNCTION mvtest_drop_the_index() + RETURNS bool AS $$ +BEGIN + EXECUTE 'DROP INDEX IF EXISTS mvtest_drop_idx'; + RETURN true; +END; +$$ LANGUAGE plpgsql; +CREATE MATERIALIZED VIEW drop_idx_matview AS + SELECT 1 as i WHERE mvtest_drop_the_index(); +NOTICE: index "mvtest_drop_idx" does not exist, skipping +CREATE UNIQUE INDEX mvtest_drop_idx ON drop_idx_matview (i); +REFRESH MATERIALIZED VIEW CONCURRENTLY drop_idx_matview; +ERROR: could not find suitable unique index on materialized view +DROP MATERIALIZED VIEW drop_idx_matview; -- clean up +-- make sure that create WITH NO DATA works via SPI +BEGIN; +CREATE FUNCTION mvtest_func() + RETURNS void AS $$ +BEGIN + CREATE MATERIALIZED VIEW mvtest1 AS SELECT 1 AS x; + CREATE MATERIALIZED VIEW mvtest2 AS SELECT 1 AS x WITH NO DATA; +END; +$$ LANGUAGE plpgsql; +SELECT mvtest_func(); + mvtest_func +------------- + +(1 row) + +SELECT * FROM mvtest1; + x +--- + 1 +(1 row) + +SELECT * FROM mvtest2; +ERROR: materialized view "mvtest2" has not been populated +HINT: Use the REFRESH MATERIALIZED VIEW command. +ROLLBACK; +-- INSERT privileges if relation owner is not allowed to insert. +CREATE SCHEMA matview_schema; +CREATE USER regress_matview_user; +ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user + REVOKE INSERT ON TABLES FROM regress_matview_user; +GRANT ALL ON SCHEMA matview_schema TO public; +SET SESSION AUTHORIZATION regress_matview_user; +CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS + SELECT generate_series(1, 10) WITH DATA; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS + SELECT generate_series(1, 10) WITH DATA; + QUERY PLAN +-------------------------------------- + ProjectSet (actual rows=10 loops=1) + -> Result (actual rows=1 loops=1) +(2 rows) + +REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2; +CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS + SELECT generate_series(1, 10) WITH NO DATA; +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS + SELECT generate_series(1, 10) WITH NO DATA; + QUERY PLAN +------------------------------- + ProjectSet (never executed) + -> Result (never executed) +(2 rows) + +REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2; +RESET SESSION AUTHORIZATION; +ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user + GRANT INSERT ON TABLES TO regress_matview_user; +DROP SCHEMA matview_schema CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to materialized view matview_schema.mv_withdata1 +drop cascades to materialized view matview_schema.mv_withdata2 +drop cascades to materialized view matview_schema.mv_nodata1 +drop cascades to materialized view matview_schema.mv_nodata2 +DROP USER regress_matview_user; +-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS +CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1; +CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error +ERROR: relation "matview_ine_tab" already exists +CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + SELECT 1 / 0; -- ok +NOTICE: relation "matview_ine_tab" already exists, skipping +CREATE MATERIALIZED VIEW matview_ine_tab AS + SELECT 1 / 0 WITH NO DATA; -- error +ERROR: relation "matview_ine_tab" already exists +CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + SELECT 1 / 0 WITH NO DATA; -- ok +NOTICE: relation "matview_ine_tab" already exists, skipping +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + CREATE MATERIALIZED VIEW matview_ine_tab AS + SELECT 1 / 0; -- error +ERROR: relation "matview_ine_tab" already exists +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + SELECT 1 / 0; -- ok +NOTICE: relation "matview_ine_tab" already exists, skipping + QUERY PLAN +------------ +(0 rows) + +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + CREATE MATERIALIZED VIEW matview_ine_tab AS + SELECT 1 / 0 WITH NO DATA; -- error +ERROR: relation "matview_ine_tab" already exists +EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) + CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + SELECT 1 / 0 WITH NO DATA; -- ok +NOTICE: relation "matview_ine_tab" already exists, skipping + QUERY PLAN +------------ +(0 rows) + +DROP MATERIALIZED VIEW matview_ine_tab; |