1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
-- create a table to use as a basis for views and materialized views in various combinations
CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
INSERT INTO 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 tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type;
SELECT * FROM tv ORDER BY type;
-- create a materialized view with no data, and confirm correct behavior
EXPLAIN (costs off)
CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
SELECT relispopulated FROM pg_class WHERE oid = 'tm'::regclass;
SELECT * FROM tm;
REFRESH MATERIALIZED VIEW tm;
SELECT relispopulated FROM pg_class WHERE oid = 'tm'::regclass;
CREATE UNIQUE INDEX tm_type ON tm (type);
SELECT * FROM tm;
-- create various views
EXPLAIN (costs off)
CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;
CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;
SELECT * FROM tvm;
CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm;
CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm;
CREATE UNIQUE INDEX tvmm_expr ON tvmm ((grandtot > 0));
CREATE UNIQUE INDEX tvmm_pred ON tvmm (grandtot) WHERE grandtot < 0;
CREATE VIEW tvv AS SELECT sum(totamt) AS grandtot FROM tv;
EXPLAIN (costs off)
CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;
CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;
CREATE VIEW tvvmv AS SELECT * FROM tvvm;
CREATE MATERIALIZED VIEW bb AS SELECT * FROM tvvmv;
CREATE INDEX aa ON bb (grandtot);
-- check that plans seem reasonable
\d+ tvm
\d+ tvm
\d+ tvvm
\d+ bb
-- test schema behavior
CREATE SCHEMA mvschema;
ALTER MATERIALIZED VIEW tvm SET SCHEMA mvschema;
\d+ tvm
\d+ tvmm
SET search_path = mvschema, public;
\d+ tvm
-- modify the underlying table data
INSERT INTO t VALUES (6, 'z', 13);
-- confirm pre- and post-refresh contents of fairly simple materialized views
SELECT * FROM tm ORDER BY type;
SELECT * FROM tvm ORDER BY type;
REFRESH MATERIALIZED VIEW CONCURRENTLY tm;
REFRESH MATERIALIZED VIEW tvm;
SELECT * FROM tm ORDER BY type;
SELECT * FROM tvm ORDER BY type;
RESET search_path;
-- confirm pre- and post-refresh contents of nested materialized views
EXPLAIN (costs off)
SELECT * FROM tmm;
EXPLAIN (costs off)
SELECT * FROM tvmm;
EXPLAIN (costs off)
SELECT * FROM tvvm;
SELECT * FROM tmm;
SELECT * FROM tvmm;
SELECT * FROM tvvm;
REFRESH MATERIALIZED VIEW tmm;
REFRESH MATERIALIZED VIEW CONCURRENTLY tvmm;
REFRESH MATERIALIZED VIEW tvmm;
REFRESH MATERIALIZED VIEW tvvm;
EXPLAIN (costs off)
SELECT * FROM tmm;
EXPLAIN (costs off)
SELECT * FROM tvmm;
EXPLAIN (costs off)
SELECT * FROM tvvm;
SELECT * FROM tmm;
SELECT * FROM tvmm;
SELECT * FROM tvvm;
-- test diemv when the mv does not exist
DROP MATERIALIZED VIEW IF EXISTS no_such_mv;
-- make sure invalid comination of options is prohibited
REFRESH MATERIALIZED VIEW CONCURRENTLY tvmm WITH NO DATA;
-- no tuple locks on materialized views
SELECT * FROM tvvm FOR SHARE;
-- test join of mv and view
SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM tm m LEFT JOIN tv v USING (type) ORDER BY type;
-- make sure that dependencies are reported properly when they block the drop
DROP TABLE t;
-- 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 t CASCADE;
ROLLBACK;
-- some additional tests not using base tables
CREATE VIEW v_test1 AS SELECT 1 moo;
CREATE VIEW v_test2 AS SELECT moo, 2*moo FROM v_test1 UNION ALL SELECT moo, 3*moo FROM v_test1;
\d+ v_test2
CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
\d+ mv_test2
CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
DROP VIEW v_test1 CASCADE;
-- test that vacuum does not make empty matview look unpopulated
CREATE TABLE hoge (i int);
INSERT INTO hoge VALUES (generate_series(1,100000));
CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge WHERE i % 2 = 0;
CREATE INDEX hogeviewidx ON hogeview (i);
DELETE FROM hoge;
REFRESH MATERIALIZED VIEW hogeview;
SELECT * FROM hogeview WHERE i < 10;
VACUUM ANALYZE;
SELECT * FROM hogeview WHERE i < 10;
DROP TABLE hoge CASCADE;
-- test that duplicate values on unique index prevent refresh
CREATE TABLE foo(a, b) AS VALUES(1, 10);
CREATE MATERIALIZED VIEW mv AS SELECT * FROM foo;
CREATE UNIQUE INDEX ON mv(a);
INSERT INTO foo SELECT * FROM foo;
REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv;
DROP TABLE foo CASCADE;
-- make sure that all indexes covered by unique indexes works
CREATE TABLE foo(a, b, c) AS VALUES(1, 2, 3);
CREATE MATERIALIZED VIEW mv AS SELECT * FROM foo;
CREATE UNIQUE INDEX ON mv (a);
CREATE UNIQUE INDEX ON mv (b);
CREATE UNIQUE INDEX on mv (c);
INSERT INTO foo VALUES(2, 3, 4);
INSERT INTO foo VALUES(3, 4, 5);
REFRESH MATERIALIZED VIEW mv;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv;
DROP TABLE foo CASCADE;
|
