1 -- create a table to use as a basis for views and materialized views in various combinations
2 CREATE TABLE mvtest_t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
3 INSERT INTO mvtest_t VALUES
10 -- we want a view based on the table, too, since views present additional challenges
11 CREATE VIEW mvtest_tv AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type;
12 SELECT * FROM mvtest_tv ORDER BY type;
14 -- create a materialized view with no data, and confirm correct behavior
16 CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA;
17 CREATE MATERIALIZED VIEW mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA;
18 SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
19 SELECT * FROM mvtest_tm ORDER BY type;
20 REFRESH MATERIALIZED VIEW mvtest_tm;
21 SELECT relispopulated FROM pg_class WHERE oid = 'mvtest_tm'::regclass;
22 CREATE UNIQUE INDEX mvtest_tm_type ON mvtest_tm (type);
23 SELECT * FROM mvtest_tm ORDER BY type;
25 -- create various views
27 CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type;
28 CREATE MATERIALIZED VIEW mvtest_tvm AS SELECT * FROM mvtest_tv ORDER BY type;
29 SELECT * FROM mvtest_tvm;
30 CREATE MATERIALIZED VIEW mvtest_tmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tm;
31 CREATE MATERIALIZED VIEW mvtest_tvmm AS SELECT sum(totamt) AS grandtot FROM mvtest_tvm;
32 CREATE UNIQUE INDEX mvtest_tvmm_expr ON mvtest_tvmm ((grandtot > 0));
33 CREATE UNIQUE INDEX mvtest_tvmm_pred ON mvtest_tvmm (grandtot) WHERE grandtot < 0;
34 CREATE VIEW mvtest_tvv AS SELECT sum(totamt) AS grandtot FROM mvtest_tv;
36 CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv;
37 CREATE MATERIALIZED VIEW mvtest_tvvm AS SELECT * FROM mvtest_tvv;
38 CREATE VIEW mvtest_tvvmv AS SELECT * FROM mvtest_tvvm;
39 CREATE MATERIALIZED VIEW mvtest_bb AS SELECT * FROM mvtest_tvvmv;
40 CREATE INDEX mvtest_aa ON mvtest_bb (grandtot);
42 -- check that plans seem reasonable
48 -- test schema behavior
49 CREATE SCHEMA mvtest_mvschema;
50 ALTER MATERIALIZED VIEW mvtest_tvm SET SCHEMA mvtest_mvschema;
53 SET search_path = mvtest_mvschema, public;
56 -- modify the underlying table data
57 INSERT INTO mvtest_t VALUES (6, 'z', 13);
59 -- confirm pre- and post-refresh contents of fairly simple materialized views
60 SELECT * FROM mvtest_tm ORDER BY type;
61 SELECT * FROM mvtest_tvm ORDER BY type;
62 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tm;
63 REFRESH MATERIALIZED VIEW mvtest_tvm;
64 SELECT * FROM mvtest_tm ORDER BY type;
65 SELECT * FROM mvtest_tvm ORDER BY type;
68 -- confirm pre- and post-refresh contents of nested materialized views
70 SELECT * FROM mvtest_tmm;
72 SELECT * FROM mvtest_tvmm;
74 SELECT * FROM mvtest_tvvm;
75 SELECT * FROM mvtest_tmm;
76 SELECT * FROM mvtest_tvmm;
77 SELECT * FROM mvtest_tvvm;
78 REFRESH MATERIALIZED VIEW mvtest_tmm;
79 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm;
80 REFRESH MATERIALIZED VIEW mvtest_tvmm;
81 REFRESH MATERIALIZED VIEW mvtest_tvvm;
83 SELECT * FROM mvtest_tmm;
85 SELECT * FROM mvtest_tvmm;
87 SELECT * FROM mvtest_tvvm;
88 SELECT * FROM mvtest_tmm;
89 SELECT * FROM mvtest_tvmm;
90 SELECT * FROM mvtest_tvvm;
92 -- test diemv when the mv does not exist
93 DROP MATERIALIZED VIEW IF EXISTS no_such_mv;
95 -- make sure invalid combination of options is prohibited
96 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_tvmm WITH NO DATA;
98 -- no tuple locks on materialized views
99 SELECT * FROM mvtest_tvvm FOR SHARE;
101 -- test join of mv and view
102 SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM mvtest_tm m LEFT JOIN mvtest_tv v USING (type) ORDER BY type;
104 -- make sure that dependencies are reported properly when they block the drop
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
111 DROP TABLE mvtest_t CASCADE;
114 -- some additional tests not using base tables
115 CREATE VIEW mvtest_vt1 AS SELECT 1 moo;
116 CREATE VIEW mvtest_vt2 AS SELECT moo, 2*moo FROM mvtest_vt1 UNION ALL SELECT moo, 3*moo FROM mvtest_vt1;
118 CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM mvtest_vt2 UNION ALL SELECT moo, 3*moo FROM mvtest_vt2;
120 CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
121 SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
123 DROP VIEW mvtest_vt1 CASCADE;
125 -- test that duplicate values on unique index prevent refresh
126 CREATE TABLE mvtest_foo(a, b) AS VALUES(1, 10);
127 CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo;
128 CREATE UNIQUE INDEX ON mvtest_mv(a);
129 INSERT INTO mvtest_foo SELECT * FROM mvtest_foo;
130 REFRESH MATERIALIZED VIEW mvtest_mv;
131 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv;
132 DROP TABLE mvtest_foo CASCADE;
134 -- make sure that all columns covered by unique indexes works
135 CREATE TABLE mvtest_foo(a, b, c) AS VALUES(1, 2, 3);
136 CREATE MATERIALIZED VIEW mvtest_mv AS SELECT * FROM mvtest_foo;
137 CREATE UNIQUE INDEX ON mvtest_mv (a);
138 CREATE UNIQUE INDEX ON mvtest_mv (b);
139 CREATE UNIQUE INDEX on mvtest_mv (c);
140 INSERT INTO mvtest_foo VALUES(2, 3, 4);
141 INSERT INTO mvtest_foo VALUES(3, 4, 5);
142 REFRESH MATERIALIZED VIEW mvtest_mv;
143 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv;
144 DROP TABLE mvtest_foo CASCADE;
146 -- allow subquery to reference unpopulated matview if WITH NO DATA is specified
147 CREATE MATERIALIZED VIEW mvtest_mv1 AS SELECT 1 AS col1 WITH NO DATA;
148 CREATE MATERIALIZED VIEW mvtest_mv2 AS SELECT * FROM mvtest_mv1
149 WHERE col1 = (SELECT LEAST(col1) FROM mvtest_mv1) WITH NO DATA;
150 DROP MATERIALIZED VIEW mvtest_mv1 CASCADE;
152 -- make sure that types with unusual equality tests work
153 CREATE TABLE mvtest_boxes (id serial primary key, b box);
154 INSERT INTO mvtest_boxes (b) VALUES
156 ('(2.0000004,2.0000004),(1,1)'),
157 ('(1.9999996,1.9999996),(1,1)');
158 CREATE MATERIALIZED VIEW mvtest_boxmv AS SELECT * FROM mvtest_boxes;
159 CREATE UNIQUE INDEX mvtest_boxmv_id ON mvtest_boxmv (id);
160 UPDATE mvtest_boxes SET b = '(2,2),(1,1)' WHERE id = 2;
161 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_boxmv;
162 SELECT * FROM mvtest_boxmv ORDER BY id;
163 DROP TABLE mvtest_boxes CASCADE;
165 -- make sure that column names are handled correctly
166 CREATE TABLE mvtest_v (i int, j int);
167 CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj, kk) AS SELECT i, j FROM mvtest_v; -- error
168 CREATE MATERIALIZED VIEW mvtest_mv_v (ii, jj) AS SELECT i, j FROM mvtest_v; -- ok
169 CREATE MATERIALIZED VIEW mvtest_mv_v_2 (ii) AS SELECT i, j FROM mvtest_v; -- ok
170 CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj, kk) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- error
171 CREATE MATERIALIZED VIEW mvtest_mv_v_3 (ii, jj) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
172 CREATE MATERIALIZED VIEW mvtest_mv_v_4 (ii) AS SELECT i, j FROM mvtest_v WITH NO DATA; -- ok
173 ALTER TABLE mvtest_v RENAME COLUMN i TO x;
174 INSERT INTO mvtest_v values (1, 2);
175 CREATE UNIQUE INDEX mvtest_mv_v_ii ON mvtest_mv_v (ii);
176 REFRESH MATERIALIZED VIEW mvtest_mv_v;
177 UPDATE mvtest_v SET j = 3 WHERE x = 1;
178 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_v;
179 REFRESH MATERIALIZED VIEW mvtest_mv_v_2;
180 REFRESH MATERIALIZED VIEW mvtest_mv_v_3;
181 REFRESH MATERIALIZED VIEW mvtest_mv_v_4;
182 SELECT * FROM mvtest_v;
183 SELECT * FROM mvtest_mv_v;
184 SELECT * FROM mvtest_mv_v_2;
185 SELECT * FROM mvtest_mv_v_3;
186 SELECT * FROM mvtest_mv_v_4;
187 DROP TABLE mvtest_v CASCADE;
189 -- Check that unknown literals are converted to "text" in CREATE MATVIEW,
190 -- so that we don't end up with unknown-type columns.
191 CREATE MATERIALIZED VIEW mv_unspecified_types AS
192 SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n;
193 \d+ mv_unspecified_types
194 SELECT * FROM mv_unspecified_types;
195 DROP MATERIALIZED VIEW mv_unspecified_types;
197 -- make sure that create WITH NO DATA does not plan the query (bug #13907)
198 create materialized view mvtest_error as select 1/0 as x; -- fail
199 create materialized view mvtest_error as select 1/0 as x with no data;
200 refresh materialized view mvtest_error; -- fail here
201 drop materialized view mvtest_error;
203 -- make sure that matview rows can be referenced as source rows (bug #9398)
204 CREATE TABLE mvtest_v AS SELECT generate_series(1,10) AS a;
205 CREATE MATERIALIZED VIEW mvtest_mv_v AS SELECT a FROM mvtest_v WHERE a <= 5;
206 DELETE FROM mvtest_v WHERE EXISTS ( SELECT * FROM mvtest_mv_v WHERE mvtest_mv_v.a = mvtest_v.a );
207 SELECT * FROM mvtest_v;
208 SELECT * FROM mvtest_mv_v;
209 DROP TABLE mvtest_v CASCADE;
211 -- make sure running as superuser works when MV owned by another role (bug #11208)
212 CREATE ROLE regress_user_mvtest;
213 SET ROLE regress_user_mvtest;
214 -- this test case also checks for ambiguity in the queries issued by
215 -- refresh_by_match_merge(), by choosing column names that intentionally
216 -- duplicate all the aliases used in those queries
217 CREATE TABLE mvtest_foo_data AS SELECT i,
219 fipshash(random()::text) AS mv,
220 fipshash(random()::text) AS newdata,
221 fipshash(random()::text) AS newdata2,
222 fipshash(random()::text) AS diff
223 FROM generate_series(1, 10) i;
224 CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
225 CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
226 CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data;
227 CREATE UNIQUE INDEX ON mvtest_mv_foo (i);
229 REFRESH MATERIALIZED VIEW mvtest_mv_foo;
230 REFRESH MATERIALIZED VIEW CONCURRENTLY mvtest_mv_foo;
231 DROP OWNED BY regress_user_mvtest CASCADE;
232 DROP ROLE regress_user_mvtest;
234 -- Concurrent refresh requires a unique index on the materialized
235 -- view. Test what happens if it's dropped during the refresh.
236 SET search_path = mvtest_mvschema, public;
237 CREATE OR REPLACE FUNCTION mvtest_drop_the_index()
240 EXECUTE 'DROP INDEX IF EXISTS mvtest_mvschema.mvtest_drop_idx';
245 CREATE MATERIALIZED VIEW drop_idx_matview AS
246 SELECT 1 as i WHERE mvtest_drop_the_index();
248 CREATE UNIQUE INDEX mvtest_drop_idx ON drop_idx_matview (i);
249 REFRESH MATERIALIZED VIEW CONCURRENTLY drop_idx_matview;
250 DROP MATERIALIZED VIEW drop_idx_matview; -- clean up
253 -- make sure that create WITH NO DATA works via SPI
255 CREATE FUNCTION mvtest_func()
258 CREATE MATERIALIZED VIEW mvtest1 AS SELECT 1 AS x;
259 CREATE MATERIALIZED VIEW mvtest2 AS SELECT 1 AS x WITH NO DATA;
262 SELECT mvtest_func();
263 SELECT * FROM mvtest1;
264 SELECT * FROM mvtest2;
267 -- INSERT privileges if relation owner is not allowed to insert.
268 CREATE SCHEMA matview_schema;
269 CREATE USER regress_matview_user;
270 ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
271 REVOKE INSERT ON TABLES FROM regress_matview_user;
272 GRANT ALL ON SCHEMA matview_schema TO public;
274 SET SESSION AUTHORIZATION regress_matview_user;
275 CREATE MATERIALIZED VIEW matview_schema.mv_withdata1 (a) AS
276 SELECT generate_series(1, 10) WITH DATA;
277 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
278 CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS
279 SELECT generate_series(1, 10) WITH DATA;
280 REFRESH MATERIALIZED VIEW matview_schema.mv_withdata2;
281 CREATE MATERIALIZED VIEW matview_schema.mv_nodata1 (a) AS
282 SELECT generate_series(1, 10) WITH NO DATA;
283 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
284 CREATE MATERIALIZED VIEW matview_schema.mv_nodata2 (a) AS
285 SELECT generate_series(1, 10) WITH NO DATA;
286 REFRESH MATERIALIZED VIEW matview_schema.mv_nodata2;
287 RESET SESSION AUTHORIZATION;
289 ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
290 GRANT INSERT ON TABLES TO regress_matview_user;
292 DROP SCHEMA matview_schema CASCADE;
293 DROP USER regress_matview_user;
295 -- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
296 CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
297 CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
298 CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
300 CREATE MATERIALIZED VIEW matview_ine_tab AS
301 SELECT 1 / 0 WITH NO DATA; -- error
302 CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
303 SELECT 1 / 0 WITH NO DATA; -- ok
304 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
305 CREATE MATERIALIZED VIEW matview_ine_tab AS
306 SELECT 1 / 0; -- error
307 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
308 CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
310 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
311 CREATE MATERIALIZED VIEW matview_ine_tab AS
312 SELECT 1 / 0 WITH NO DATA; -- error
313 EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, BUFFERS OFF)
314 CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
315 SELECT 1 / 0 WITH NO DATA; -- ok
316 DROP MATERIALIZED VIEW matview_ine_tab;