1-- create a table to use as a basis for views and materialized views in various combinations 2CREATE TABLE mvtest_t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL); 3INSERT INTO mvtest_t VALUES 4 (1, 'x', 2), 5 (2, 'x', 3), 6 (3, 'y', 5), 7 (4, 'y', 7), 8 (5, 'z', 11); 9 10-- we want a view based on the table, too, since views present additional challenges 11CREATE VIEW mvtest_tv AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type; 12SELECT * FROM mvtest_tv ORDER BY type; 13 14-- create a materialized view with no data, and confirm correct behavior 15EXPLAIN (costs off) 16 CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA; 17CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA; 18SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass; 19SELECT * FROM mvtest_tm; 20REFRESH MATERIALIZED VIEW mvtest_tm; 21SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass; 22CREATE UNIQUE INDEX mvtest_tm_type ON mvtest_tm (type); 23SELECT * FROM mvtest_tm; 24 25-- create various views 26EXPLAIN (costs off) 27 CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type; 28CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type; 29SELECT * FROM mvtest_tvm; 30CREATE MATERIALIZED VIEW mvtest_tmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tm; 31CREATE MATERIALIZED VIEW mvtest_tvmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tvm; 32CREATE UNIQUE INDEX mvtest_tvmm_expr ON mvtest_tvmm ((grandtot > 0)); 33CREATE UNIQUE INDEX mvtest_tvmm_pred ON mvtest_tvmm (grandtot) WHERE grandtot < 0; 34CREATE VIEW mvtest_tvv AS SELECT sum(totamt) AS grandtot FROM mvtest_tv; 35EXPLAIN (costs off) 36 CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv; 37CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv; 38CREATE VIEW mvtest_tvvmv AS SELECT * FROM mvtest_tvvm; 39CREATE MATERIALIZED VIEW mvtest_bb AS SELECT * FROM mvtest_tvvmv; 40CREATE INDEX mvtest_aa ON mvtest_bb (grandtot); 41 42-- check that plans seem reasonable 43\d+ mvtest_tvm 44\d+ mvtest_tvm 45\d+ mvtest_tvvm 46\d+ mvtest_bb 47 48-- test schema behavior 49CREATE SCHEMA mvtest_mvschema; 50ALTER MATERIALIZED VIEW mvtest_tvm SET SCHEMA mvtest_mvschema; 51\d+ mvtest_tvm 52\d+ mvtest_tvmm 53SET search_path = mvtest_mvschema, public; 54\d+ mvtest_tvm 55 56-- modify the underlying table data 57INSERT INTO mvtest_t VALUES (6, 'z', 13); 58 59-- confirm pre- and post-refresh contents of fairly simple materialized views 60SELECT * FROM mvtest_tm ORDER BY type; 61SELECT * FROM mvtest_tvm ORDER BY type; 62REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tm; 63REFRESH MATERIALIZED VIEW mvtest_tvm; 64SELECT * FROM mvtest_tm ORDER BY type; 65SELECT * FROM mvtest_tvm ORDER BY type; 66RESET search_path; 67 68-- confirm pre- and post-refresh contents of nested materialized views 69EXPLAIN (costs off) 70 SELECT * FROM mvtest_tmm; 71EXPLAIN (costs off) 72 SELECT * FROM mvtest_tvmm; 73EXPLAIN (costs off) 74 SELECT * FROM mvtest_tvvm; 75SELECT * FROM mvtest_tmm; 76SELECT * FROM mvtest_tvmm; 77SELECT * FROM mvtest_tvvm; 78REFRESH MATERIALIZED VIEW mvtest_tmm; 79REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm; 80REFRESH MATERIALIZED VIEW mvtest_tvmm; 81REFRESH MATERIALIZED VIEW mvtest_tvvm; 82EXPLAIN (costs off) 83 SELECT * FROM mvtest_tmm; 84EXPLAIN (costs off) 85 SELECT * FROM mvtest_tvmm; 86EXPLAIN (costs off) 87 SELECT * FROM mvtest_tvvm; 88SELECT * FROM mvtest_tmm; 89SELECT * FROM mvtest_tvmm; 90SELECT * FROM mvtest_tvvm; 91 92-- test diemv when the mv does not exist 93DROP MATERIALIZED VIEW IF EXISTS no_such_mv; 94 95-- make sure invalid combination of options is prohibited 96REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm WITH NO DATA; 97 98-- no tuple locks on materialized views 99SELECT * FROM mvtest_tvvm FOR SHARE; 100 101-- test join of mv and view 102SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM mvtest_tm m LEFT JOIN mvtest_tv v USING (type) ORDER BY type; 103 104-- make sure that dependencies are reported properly when they block the drop 105DROP TABLE mvtest_t; 106 107-- make sure dependencies are dropped and reported 108-- and make sure that transactional behavior is correct on rollback 109-- incidentally leaving some interesting materialized views for pg_dump testing 110BEGIN; 111DROP TABLE mvtest_t CASCADE; 112ROLLBACK; 113 114-- some additional tests not using base tables 115CREATE VIEW mvtest_vt1 AS SELECT 1 moo; 116CREATE VIEW mvtest_vt2 AS SELECT moo, 2*moo FROM mvtest_vt1 UNION ALL SELECT moo, 3*moo FROM mvtest_vt1; 117\d+ mvtest_vt2 118CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM mvtest_vt2 UNION ALL SELECT moo, 3*moo FROM mvtest_vt2; 119\d+ mv_test2 120CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345; 121SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass; 122 123DROP VIEW mvtest_vt1 CASCADE; 124 125-- test that duplicate values on unique index prevent refresh 126CREATE TABLE mvtest_foo(a, b) AS VALUES(1, 10); 127CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; 128CREATE UNIQUE INDEX ON mvtest_mv(a); 129INSERT INTO mvtest_foo SELECT * FROM mvtest_foo; 130REFRESH MATERIALIZED VIEW mvtest_mv; 131REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; 132DROP TABLE mvtest_foo CASCADE; 133 134-- make sure that all columns covered by unique indexes works 135CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3); 136CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo; 137CREATE UNIQUE INDEX ON mvtest_mv (a); 138CREATE UNIQUE INDEX ON mvtest_mv (b); 139CREATE UNIQUE INDEX on mvtest_mv (c); 140INSERT INTO mvtest_foo VALUES(2, 3, 4); 141INSERT INTO mvtest_foo VALUES(3, 4, 5); 142REFRESH MATERIALIZED VIEW mvtest_mv; 143REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv; 144DROP TABLE mvtest_foo CASCADE; 145 146-- allow subquery to reference unpopulated matview if WITH NO DATA is specified 147CREATE MATERIALIZED VIEW mvtest_mv1 AS SELECT 1 AS col1 WITH NO DATA; 148CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1 149 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA; 150DROP MATERIALIZED VIEW mvtest_mv1 CASCADE; 151 152-- make sure that types with unusual equality tests work 153CREATE TABLE mvtest_boxes (id serial primary key, b box); 154INSERT INTO mvtest_boxes (b) VALUES 155 ('(32,32),(31,31)'), 156 ('(2.0000004,2.0000004),(1,1)'), 157 ('(1.9999996,1.9999996),(1,1)'); 158CREATE MATERIALIZED VIEW mvtest_boxmv AS SELECT * FROM mvtest_boxes; 159CREATE UNIQUE INDEX mvtest_boxmv_id ON mvtest_boxmv (id); 160UPDATE mvtest_boxes SET b = '(2,2),(1,1)' WHERE id = 2; 161REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_boxmv; 162SELECT * FROM mvtest_boxmv ORDER BY id; 163DROP TABLE mvtest_boxes CASCADE; 164 165-- make sure that column names are handled correctly 166CREATE TABLE mvtest_v (i int, j int); 167CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error 168CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok 169CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok 170CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error 171CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok 172CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok 173ALTER TABLE mvtest_v RENAME COLUMN i TO x; 174INSERT INTO mvtest_v values (1, 2); 175CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii); 176REFRESH MATERIALIZED VIEW mvtest_mv_v; 177UPDATE mvtest_v SET j = 3 WHERE x = 1; 178REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_v; 179REFRESH MATERIALIZED VIEW mvtest_mv_v_2; 180REFRESH MATERIALIZED VIEW mvtest_mv_v_3; 181REFRESH MATERIALIZED VIEW mvtest_mv_v_4; 182SELECT * FROM mvtest_v; 183SELECT * FROM mvtest_mv_v; 184SELECT * FROM mvtest_mv_v_2; 185SELECT * FROM mvtest_mv_v_3; 186SELECT * FROM mvtest_mv_v_4; 187DROP TABLE mvtest_v CASCADE; 188 189-- make sure that create WITH NO DATA does not plan the query (bug #13907) 190create materialized view mvtest_error as select 1/0 as x; -- fail 191create materialized view mvtest_error as select 1/0 as x with no data; 192refresh materialized view mvtest_error; -- fail here 193drop materialized view mvtest_error; 194 195-- make sure that matview rows can be referenced as source rows (bug #9398) 196CREATE TABLE mvtest_v AS SELECT generate_series(1,10) AS a; 197CREATE MATERIALIZED VIEW mvtest_mv_v AS SELECT a FROM mvtest_v WHERE a <= 5; 198DELETE FROM mvtest_v WHERE EXISTS ( SELECT * FROM mvtest_mv_v WHERE mvtest_mv_v.a = mvtest_v.a ); 199SELECT * FROM mvtest_v; 200SELECT * FROM mvtest_mv_v; 201DROP TABLE mvtest_v CASCADE; 202 203-- make sure running as superuser works when MV owned by another role (bug #11208) 204CREATE ROLE regress_user_mvtest; 205SET ROLE regress_user_mvtest; 206-- this test case also checks for ambiguity in the queries issued by 207-- refresh_by_match_merge(), by choosing column names that intentionally 208-- duplicate all the aliases used in those queries 209CREATE TABLE mvtest_foo_data AS SELECT i, 210 i+1 AS tid, 211 md5(random()::text) AS mv, 212 md5(random()::text) AS newdata, 213 md5(random()::text) AS newdata2, 214 md5(random()::text) AS diff 215 FROM generate_series(1, 10) i; 216CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; 217CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; 218CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; 219CREATE UNIQUE INDEX ON mvtest_mv_foo (i); 220RESET ROLE; 221REFRESH MATERIALIZED VIEW mvtest_mv_foo; 222REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo; 223DROP OWNED BY regress_user_mvtest CASCADE; 224DROP ROLE regress_user_mvtest; 225 226-- make sure that create WITH NO DATA works via SPI 227BEGIN; 228CREATE FUNCTION mvtest_func() 229 RETURNS void AS $$ 230BEGIN 231 CREATE MATERIALIZED VIEW mvtest1 AS SELECT 1 AS x; 232 CREATE MATERIALIZED VIEW mvtest2 AS SELECT 1 AS x WITH NO DATA; 233END; 234$$ LANGUAGE plpgsql; 235SELECT mvtest_func(); 236SELECT * FROM mvtest1; 237SELECT * FROM mvtest2; 238ROLLBACK; 239