src/test/regress/expected/matview.out

   1 -- create a table to use as a basis for views and materialized views in various combinations
   2 CREATE TABLE t (id int NOT NULL PRIMARY KEY, type text NOT NULL, amt numeric NOT NULL);
   3 INSERT INTO t VALUES
   4   (1, 'x', 2),
   5   (2, 'x', 3),
   6   (3, 'y', 5),
   7   (4, 'y', 7),
   8   (5, 'z', 11);
   9 -- we want a view based on the table, too, since views present additional challenges
  10 CREATE VIEW tv AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type;
  11 SELECT * FROM tv ORDER BY type;
  12  type | totamt
  13 ------+--------
  14  x    |      5
  15  y    |     12
  16  z    |     11
  17 (3 rows)
  18
  19 -- create a materialized view with no data, and confirm correct behavior
  20 EXPLAIN (costs off)
  21   CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
  22      QUERY PLAN
  23 ---------------------
  24  HashAggregate
  25    ->  Seq Scan on t
  26 (2 rows)
  27
  28 CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
  29 SELECT pg_relation_is_scannable('tm'::regclass);
  30  pg_relation_is_scannable
  31 --------------------------
  32  f
  33 (1 row)
  34
  35 SELECT * FROM tm;
  36 ERROR:  materialized view "tm" has not been populated
  37 HINT:  Use the REFRESH MATERIALIZED VIEW command.
  38 REFRESH MATERIALIZED VIEW tm;
  39 SELECT pg_relation_is_scannable('tm'::regclass);
  40  pg_relation_is_scannable
  41 --------------------------
  42  t
  43 (1 row)
  44
  45 CREATE UNIQUE INDEX tm_type ON tm (type);
  46 SELECT * FROM tm;
  47  type | totamt
  48 ------+--------
  49  y    |     12
  50  z    |     11
  51  x    |      5
  52 (3 rows)
  53
  54 -- create various views
  55 EXPLAIN (costs off)
  56   CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;
  57         QUERY PLAN
  58 ---------------------------
  59  Sort
  60    Sort Key: t.type
  61    ->  HashAggregate
  62          ->  Seq Scan on t
  63 (4 rows)
  64
  65 CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;
  66 SELECT * FROM tvm;
  67  type | totamt
  68 ------+--------
  69  x    |      5
  70  y    |     12
  71  z    |     11
  72 (3 rows)
  73
  74 CREATE MATERIALIZED VIEW tmm AS SELECT sum(totamt) AS grandtot FROM tm;
  75 CREATE MATERIALIZED VIEW tvmm AS SELECT sum(totamt) AS grandtot FROM tvm;
  76 CREATE VIEW tvv AS SELECT sum(totamt) AS grandtot FROM tv;
  77 EXPLAIN (costs off)
  78   CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;
  79         QUERY PLAN
  80 ---------------------------
  81  Aggregate
  82    ->  HashAggregate
  83          ->  Seq Scan on t
  84 (3 rows)
  85
  86 CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;
  87 CREATE VIEW tvvmv AS SELECT * FROM tvvm;
  88 CREATE MATERIALIZED VIEW bb AS SELECT * FROM tvvmv;
  89 CREATE INDEX aa ON bb (grandtot);
  90 -- check that plans seem reasonable
  91 \d+ tvm
  92                     Materialized view "public.tvm"
  93  Column |  Type   | Modifiers | Storage  | Stats target | Description
  94 --------+---------+-----------+----------+--------------+-------------
  95  type   | text    |           | extended |              |
  96  totamt | numeric |           | main     |              |
  97 View definition:
  98  SELECT tv.type,
  99     tv.totamt
 100    FROM tv
 101   ORDER BY tv.type;
 102
 103 \d+ tvm
 104                     Materialized view "public.tvm"
 105  Column |  Type   | Modifiers | Storage  | Stats target | Description
 106 --------+---------+-----------+----------+--------------+-------------
 107  type   | text    |           | extended |              |
 108  totamt | numeric |           | main     |              |
 109 View definition:
 110  SELECT tv.type,
 111     tv.totamt
 112    FROM tv
 113   ORDER BY tv.type;
 114
 115 \d+ tvvm
 116                     Materialized view "public.tvvm"
 117   Column  |  Type   | Modifiers | Storage | Stats target | Description
 118 ----------+---------+-----------+---------+--------------+-------------
 119  grandtot | numeric |           | main    |              |
 120 View definition:
 121  SELECT tvv.grandtot
 122    FROM tvv;
 123
 124 \d+ bb
 125                      Materialized view "public.bb"
 126   Column  |  Type   | Modifiers | Storage | Stats target | Description
 127 ----------+---------+-----------+---------+--------------+-------------
 128  grandtot | numeric |           | main    |              |
 129 Indexes:
 130     "aa" btree (grandtot)
 131 View definition:
 132  SELECT tvvmv.grandtot
 133    FROM tvvmv;
 134
 135 -- test schema behavior
 136 CREATE SCHEMA mvschema;
 137 ALTER MATERIALIZED VIEW tvm SET SCHEMA mvschema;
 138 \d+ tvm
 139 \d+ tvmm
 140                     Materialized view "public.tvmm"
 141   Column  |  Type   | Modifiers | Storage | Stats target | Description
 142 ----------+---------+-----------+---------+--------------+-------------
 143  grandtot | numeric |           | main    |              |
 144 View definition:
 145  SELECT sum(tvm.totamt) AS grandtot
 146    FROM mvschema.tvm;
 147
 148 SET search_path = mvschema, public;
 149 \d+ tvm
 150                    Materialized view "mvschema.tvm"
 151  Column |  Type   | Modifiers | Storage  | Stats target | Description
 152 --------+---------+-----------+----------+--------------+-------------
 153  type   | text    |           | extended |              |
 154  totamt | numeric |           | main     |              |
 155 View definition:
 156  SELECT tv.type,
 157     tv.totamt
 158    FROM tv
 159   ORDER BY tv.type;
 160
 161 -- modify the underlying table data
 162 INSERT INTO t VALUES (6, 'z', 13);
 163 -- confirm pre- and post-refresh contents of fairly simple materialized views
 164 SELECT * FROM tm ORDER BY type;
 165  type | totamt
 166 ------+--------
 167  x    |      5
 168  y    |     12
 169  z    |     11
 170 (3 rows)
 171
 172 SELECT * FROM tvm ORDER BY type;
 173  type | totamt
 174 ------+--------
 175  x    |      5
 176  y    |     12
 177  z    |     11
 178 (3 rows)
 179
 180 REFRESH MATERIALIZED VIEW tm;
 181 REFRESH MATERIALIZED VIEW tvm;
 182 SELECT * FROM tm ORDER BY type;
 183  type | totamt
 184 ------+--------
 185  x    |      5
 186  y    |     12
 187  z    |     24
 188 (3 rows)
 189
 190 SELECT * FROM tvm ORDER BY type;
 191  type | totamt
 192 ------+--------
 193  x    |      5
 194  y    |     12
 195  z    |     24
 196 (3 rows)
 197
 198 RESET search_path;
 199 -- confirm pre- and post-refresh contents of nested materialized views
 200 EXPLAIN (costs off)
 201   SELECT * FROM tmm;
 202    QUERY PLAN
 203 -----------------
 204  Seq Scan on tmm
 205 (1 row)
 206
 207 EXPLAIN (costs off)
 208   SELECT * FROM tvmm;
 209     QUERY PLAN
 210 ------------------
 211  Seq Scan on tvmm
 212 (1 row)
 213
 214 EXPLAIN (costs off)
 215   SELECT * FROM tvvm;
 216     QUERY PLAN
 217 ------------------
 218  Seq Scan on tvvm
 219 (1 row)
 220
 221 SELECT * FROM tmm;
 222  grandtot
 223 ----------
 224        28
 225 (1 row)
 226
 227 SELECT * FROM tvmm;
 228  grandtot
 229 ----------
 230        28
 231 (1 row)
 232
 233 SELECT * FROM tvvm;
 234  grandtot
 235 ----------
 236        28
 237 (1 row)
 238
 239 REFRESH MATERIALIZED VIEW tmm;
 240 REFRESH MATERIALIZED VIEW tvmm;
 241 REFRESH MATERIALIZED VIEW tvvm;
 242 EXPLAIN (costs off)
 243   SELECT * FROM tmm;
 244    QUERY PLAN
 245 -----------------
 246  Seq Scan on tmm
 247 (1 row)
 248
 249 EXPLAIN (costs off)
 250   SELECT * FROM tvmm;
 251     QUERY PLAN
 252 ------------------
 253  Seq Scan on tvmm
 254 (1 row)
 255
 256 EXPLAIN (costs off)
 257   SELECT * FROM tvvm;
 258     QUERY PLAN
 259 ------------------
 260  Seq Scan on tvvm
 261 (1 row)
 262
 263 SELECT * FROM tmm;
 264  grandtot
 265 ----------
 266        41
 267 (1 row)
 268
 269 SELECT * FROM tvmm;
 270  grandtot
 271 ----------
 272        41
 273 (1 row)
 274
 275 SELECT * FROM tvvm;
 276  grandtot
 277 ----------
 278        41
 279 (1 row)
 280
 281 -- test diemv when the mv does not exist
 282 DROP MATERIALIZED VIEW IF EXISTS tum;
 283 NOTICE:  materialized view "tum" does not exist, skipping
 284 -- make sure that an unlogged materialized view works (in the absence of a crash)
 285 CREATE UNLOGGED MATERIALIZED VIEW tum AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;
 286 SELECT pg_relation_is_scannable('tum'::regclass);
 287  pg_relation_is_scannable
 288 --------------------------
 289  f
 290 (1 row)
 291
 292 SELECT * FROM tum;
 293 ERROR:  materialized view "tum" has not been populated
 294 HINT:  Use the REFRESH MATERIALIZED VIEW command.
 295 REFRESH MATERIALIZED VIEW tum;
 296 SELECT pg_relation_is_scannable('tum'::regclass);
 297  pg_relation_is_scannable
 298 --------------------------
 299  t
 300 (1 row)
 301
 302 SELECT * FROM tum;
 303  type | totamt
 304 ------+--------
 305  y    |     12
 306  z    |     24
 307  x    |      5
 308 (3 rows)
 309
 310 REFRESH MATERIALIZED VIEW tum WITH NO DATA;
 311 SELECT pg_relation_is_scannable('tum'::regclass);
 312  pg_relation_is_scannable
 313 --------------------------
 314  f
 315 (1 row)
 316
 317 SELECT * FROM tum;
 318 ERROR:  materialized view "tum" has not been populated
 319 HINT:  Use the REFRESH MATERIALIZED VIEW command.
 320 REFRESH MATERIALIZED VIEW tum WITH DATA;
 321 SELECT pg_relation_is_scannable('tum'::regclass);
 322  pg_relation_is_scannable
 323 --------------------------
 324  t
 325 (1 row)
 326
 327 SELECT * FROM tum;
 328  type | totamt
 329 ------+--------
 330  y    |     12
 331  z    |     24
 332  x    |      5
 333 (3 rows)
 334
 335 -- test join of mv and view
 336 SELECT type, m.totamt AS mtot, v.totamt AS vtot FROM tm m LEFT JOIN tv v USING (type) ORDER BY type;
 337  type | mtot | vtot
 338 ------+------+------
 339  x    |    5 |    5
 340  y    |   12 |   12
 341  z    |   24 |   24
 342 (3 rows)
 343
 344 -- test diemv when the mv does exist
 345 DROP MATERIALIZED VIEW IF EXISTS tum;
 346 -- make sure that dependencies are reported properly when they block the drop
 347 DROP TABLE t;
 348 ERROR:  cannot drop table t because other objects depend on it
 349 DETAIL:  view tv depends on table t
 350 view tvv depends on view tv
 351 materialized view tvvm depends on view tvv
 352 view tvvmv depends on materialized view tvvm
 353 materialized view bb depends on view tvvmv
 354 materialized view mvschema.tvm depends on view tv
 355 materialized view tvmm depends on materialized view mvschema.tvm
 356 materialized view tm depends on table t
 357 materialized view tmm depends on materialized view tm
 358 HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 359 -- make sure dependencies are dropped and reported
 360 -- and make sure that transactional behavior is correct on rollback
 361 -- incidentally leaving some interesting materialized views for pg_dump testing
 362 BEGIN;
 363 DROP TABLE t CASCADE;
 364 NOTICE:  drop cascades to 9 other objects
 365 DETAIL:  drop cascades to view tv
 366 drop cascades to view tvv
 367 drop cascades to materialized view tvvm
 368 drop cascades to view tvvmv
 369 drop cascades to materialized view bb
 370 drop cascades to materialized view mvschema.tvm
 371 drop cascades to materialized view tvmm
 372 drop cascades to materialized view tm
 373 drop cascades to materialized view tmm
 374 ROLLBACK;
 375 -- some additional tests not using base tables
 376 CREATE VIEW v_test1 AS SELECT 1 moo;
 377 CREATE VIEW v_test2 AS SELECT moo, 2*moo FROM v_test1 UNION ALL SELECT moo, 3*moo FROM v_test1;
 378 \d+ v_test2
 379                  View "public.v_test2"
 380   Column  |  Type   | Modifiers | Storage | Description
 381 ----------+---------+-----------+---------+-------------
 382  moo      | integer |           | plain   |
 383  ?column? | integer |           | plain   |
 384 View definition:
 385          SELECT v_test1.moo,
 386             2 * v_test1.moo
 387            FROM v_test1
 388 UNION ALL
 389          SELECT v_test1.moo,
 390             3 * v_test1.moo
 391            FROM v_test1;
 392
 393 CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
 394 \d+ mv_test2
 395                   Materialized view "public.mv_test2"
 396   Column  |  Type   | Modifiers | Storage | Stats target | Description
 397 ----------+---------+-----------+---------+--------------+-------------
 398  moo      | integer |           | plain   |              |
 399  ?column? | integer |           | plain   |              |
 400 View definition:
 401          SELECT v_test2.moo,
 402             2 * v_test2.moo
 403            FROM v_test2
 404 UNION ALL
 405          SELECT v_test2.moo,
 406             3 * v_test2.moo
 407            FROM v_test2;
 408
 409 CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
 410 SELECT pg_relation_is_scannable('mv_test3'::regclass);
 411  pg_relation_is_scannable
 412 --------------------------
 413  t
 414 (1 row)
 415
 416 DROP VIEW v_test1 CASCADE;
 417 NOTICE:  drop cascades to 3 other objects
 418 DETAIL:  drop cascades to view v_test2
 419 drop cascades to materialized view mv_test2
 420 drop cascades to materialized view mv_test3
 421 -- test that vacuum does not make empty matview look unpopulated
 422 CREATE TABLE hoge (i int);
 423 INSERT INTO hoge VALUES (generate_series(1,100000));
 424 CREATE MATERIALIZED VIEW hogeview AS SELECT * FROM hoge WHERE i % 2 = 0;
 425 CREATE INDEX hogeviewidx ON hogeview (i);
 426 DELETE FROM hoge;
 427 REFRESH MATERIALIZED VIEW hogeview;
 428 SELECT * FROM hogeview WHERE i < 10;
 429  i
 430 ---
 431 (0 rows)
 432
 433 VACUUM ANALYZE;
 434 SELECT * FROM hogeview WHERE i < 10;
 435  i
 436 ---
 437 (0 rows)
 438
 439 DROP TABLE hoge CASCADE;
 440 NOTICE:  drop cascades to materialized view hogeview