| -- 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); |
| ANALYZE mvtest_t; |
| -- 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 IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type); |
| QUERY PLAN |
| ------------------------------------------------------------ |
| GroupAggregate |
| Group Key: type |
| -> Sort |
| Sort Key: type |
| -> Redistribute Motion 3:3 (slice1; segments: 3) |
| Hash Key: type |
| -> Seq Scan on mvtest_t |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (8 rows) |
| |
| CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_tm AS SELECT type, sum(amt) AS totamt FROM mvtest_t GROUP BY type WITH NO DATA distributed by(type); |
| 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; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| QUERY PLAN |
| ------------------------------------------------------------------------------ |
| Result |
| -> Sort |
| Sort Key: type |
| -> Redistribute Motion 3:3 (slice1; segments: 3) |
| -> GroupAggregate |
| Group Key: type |
| -> Sort |
| Sort Key: type |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: type |
| -> Seq Scan on mvtest_t |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (12 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 distributed by(grandtot); |
| CREATE UNIQUE INDEX mvtest_tvmm_expr ON mvtest_tvmm ((grandtot > 0)); |
| ERROR: UNIQUE index must contain all columns in the table's distribution key |
| DETAIL: Distribution key column "grandtot" is not included in the constraint. |
| 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 |
| ------------------------------------------------------------------------------------------ |
| Result |
| -> Redistribute Motion 1:3 (slice1; segments: 1) |
| -> Finalize Aggregate |
| -> Gather Motion 3:1 (slice2; segments: 3) |
| -> Partial Aggregate |
| -> GroupAggregate |
| Group Key: type |
| -> Sort |
| Sort Key: type |
| -> Redistribute Motion 3:3 (slice3; segments: 3) |
| Hash Key: type |
| -> Seq Scan on mvtest_t |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (13 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 mvtest_tv.type, |
| mvtest_tv.totamt |
| FROM mvtest_tv |
| ORDER BY mvtest_tv.type; |
| Distributed randomly |
| |
| \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 mvtest_tv.type, |
| mvtest_tv.totamt |
| FROM mvtest_tv |
| ORDER BY mvtest_tv.type; |
| Distributed randomly |
| |
| \d+ mvtest_tvvm |
| Materialized view "public.mvtest_tvvm" |
| Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| ----------+---------+-----------+----------+---------+---------+--------------+------------- |
| grandtot | numeric | | | | main | | |
| View definition: |
| SELECT mvtest_tvv.grandtot |
| FROM mvtest_tvv; |
| Distributed randomly |
| |
| \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 mvtest_tvvmv.grandtot |
| FROM mvtest_tvvmv; |
| Distributed randomly |
| |
| -- 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_pred" UNIQUE, btree (grandtot) WHERE grandtot < 0::numeric |
| View definition: |
| SELECT sum(mvtest_tvm.totamt) AS grandtot |
| FROM mvtest_mvschema.mvtest_tvm; |
| Distributed by: (grandtot) |
| |
| 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 mvtest_tv.type, |
| mvtest_tv.totamt |
| FROM mvtest_tv |
| ORDER BY mvtest_tv.type; |
| Distributed randomly |
| |
| -- 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 |
| ------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on mvtest_tmm |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.77.0 |
| (3 rows) |
| |
| EXPLAIN (costs off) |
| SELECT * FROM mvtest_tvmm; |
| QUERY PLAN |
| ------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on mvtest_tvmm |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.77.0 |
| (3 rows) |
| |
| EXPLAIN (costs off) |
| SELECT * FROM mvtest_tvvm; |
| QUERY PLAN |
| ------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on mvtest_tvvm |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.77.0 |
| (3 rows) |
| |
| 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 |
| ------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on mvtest_tmm |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.77.0 |
| (3 rows) |
| |
| EXPLAIN (costs off) |
| SELECT * FROM mvtest_tvmm; |
| QUERY PLAN |
| ------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on mvtest_tvmm |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.77.0 |
| (3 rows) |
| |
| EXPLAIN (costs off) |
| SELECT * FROM mvtest_tvvm; |
| QUERY PLAN |
| ------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on mvtest_tvvm |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.77.0 |
| (3 rows) |
| |
| 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 |
| -- start_ignore |
| SELECT * FROM mvtest_tvvm FOR SHARE; |
| ERROR: cannot lock rows in materialized view "mvtest_tvvm" |
| -- end_ignore |
| -- 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; |
| Distributed randomly |
| |
| 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 distributed by(a); |
| 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 distributed by(a); |
| CREATE UNIQUE INDEX ON mvtest_mv (a); |
| 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 distributed by(id); |
| 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 distributed by(ii); -- 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 CREATE IF NOT EXISTS accept DISTRIBUTED BY |
| CREATE MATERIALIZED VIEW IF NOT EXISTS mv_ine_distr (a, b) AS |
| SELECT generate_series(1, 10) a, generate_series(1, 10) b DISTRIBUTED BY (b); |
| \d+ mv_ine_distr |
| Materialized view "public.mv_ine_distr" |
| Column | Type | Collation | Nullable | Default | Storage | Stats target | Description |
| --------+---------+-----------+----------+---------+---------+--------------+------------- |
| a | integer | | | | plain | | |
| b | integer | | | | plain | | |
| View definition: |
| SELECT generate_series(1, 10) AS a, |
| generate_series(1, 10) AS b; |
| Distributed by: (b) |
| |
| DROP MATERIALIZED VIEW mv_ine_distr; |
| -- 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; |
| Distributed randomly |
| |
| 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; |
| ERROR: division by zero |
| -- 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, |
| md5(random()::text) AS mv, |
| md5(random()::text) AS newdata, |
| md5(random()::text) AS newdata2, |
| md5(random()::text) AS diff |
| FROM generate_series(1, 10) i; |
| CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data distributed by(i); |
| CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data distributed by(i); |
| 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; |
| -- 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; |
| -- make sure refresh mat view will dispatch oid at the final |
| -- execution of the mat view's body query. See Github Issue |
| -- https://github.com/greenplum-db/gpdb/issues/11956 for details. |
| create table t_github_issue_11956(a int, b int) distributed randomly; |
| insert into t_github_issue_11956 values (1, 1); |
| create function f_github_issue_11956() returns int as |
| $$ |
| select sum(a+b)::int from t_github_issue_11956 |
| $$ |
| language sql stable; |
| create materialized view mat_view_github_issue_11956 |
| as |
| select * from t_github_issue_11956 where a > f_github_issue_11956() |
| distributed randomly; |
| refresh materialized view mat_view_github_issue_11956; |
| drop materialized view mat_view_github_issue_11956; |
| drop table t_github_issue_11956; |
| -- INSERT privileges if relation owner is not allowed to insert. |
| CREATE SCHEMA matview_schema; |
| CREATE USER regress_matview_user; |
| NOTICE: resource queue required -- using default resource queue "pg_default" |
| 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 DISTRIBUTED BY (a); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) |
| CREATE MATERIALIZED VIEW matview_schema.mv_withdata2 (a) AS |
| SELECT generate_series(1, 10) WITH DATA DISTRIBUTED BY (a); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| QUERY PLAN |
| -------------------------------------------------- |
| Result (actual rows=5 loops=1) |
| -> Result (actual rows=5 loops=1) |
| -> ProjectSet (actual rows=10 loops=1) |
| -> Result (actual rows=1 loops=1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| 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; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Result (never executed) |
| -> Redistribute Motion 3:3 (slice1; segments: 3) (never executed) |
| -> Result (never executed) |
| One-Time Filter: (gp_execution_segment() = 0) |
| -> ProjectSet (never executed) |
| -> Result (never executed) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (7 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; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| 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; |
| -- test REFRESH fast path |
| create materialized view mv_fast as select * from mvtest_t; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| set gp_enable_refresh_fast_path = off; |
| select relfilenode into temp mv_fast_relfilenode_0 from pg_class where oid = 'mv_fast'::regclass::oid; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relfilenode' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| refresh materialized view mv_fast; |
| select relfilenode into temp mv_fast_relfilenode_1 from pg_class where oid = 'mv_fast'::regclass::oid; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relfilenode' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- shoule be 0 |
| select count(*) from mv_fast_relfilenode_0 natural join mv_fast_relfilenode_1; |
| count |
| ------- |
| 0 |
| (1 row) |
| |
| -- relfilenode should not be changed then. |
| set gp_enable_refresh_fast_path = on; |
| refresh materialized view mv_fast; |
| select relfilenode into temp mv_fast_relfilenode_2 from pg_class where oid = 'mv_fast'::regclass::oid; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relfilenode' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- shoule be 1 |
| select count(*) from mv_fast_relfilenode_1 natural join mv_fast_relfilenode_2; |
| count |
| ------- |
| 1 |
| (1 row) |
| |
| reset gp_enable_refresh_fast_path; |
| drop materialized view mv_fast; |
| -- test REFRESH MATERIALIZED VIEW with 'WITH NO DATA' option can be executed immediately. |
| DROP TABLE IF EXISTS mvtest_twn; |
| CREATE TABLE mvtest_twn(a int); |
| CREATE MATERIALIZED VIEW mat_view_twn as SELECT a.a as p, b.a as q, c.a as x, d.a as y FROM mvtest_twn a, mvtest_twn b, mvtest_twn c, mvtest_twn d; |
| INSERT INTO mvtest_twn SELECT i FROM generate_series(1,10000)i; |
| -- t1 contains 10000 tuples, after cross join it four times, the output is much too huge |
| -- refresh with 'no data' should not actually execute the sql |
| set statement_timeout = 5000; |
| REFRESH MATERIALIZED VIEW mat_view_twn WITH NO DATA; |
| reset statement_timeout; |
| SELECT relispopulated FROM pg_class WHERE oid = 'mat_view_twn'::regclass; |
| relispopulated |
| ---------------- |
| f |
| (1 row) |
| |
| SELECT relispopulated FROM gp_dist_random('pg_class') WHERE oid = 'mat_view_twn'::regclass; |
| relispopulated |
| ---------------- |
| f |
| f |
| f |
| (3 rows) |
| |
| SELECT * FROM mat_view_twn; |
| ERROR: materialized view "mat_view_twn" has not been populated |
| HINT: Use the REFRESH MATERIALIZED VIEW command. |
| DROP MATERIALIZED VIEW mat_view_twn; |
| DROP TABLE mvtest_twn; |
| -- |
| -- https://github.com/apache/cloudberry/issues/865 |
| -- |
| set default_table_access_method TO AO_ROW; |
| CREATE TABLE t_issue_865_ao |
| ( |
| id bigint NOT NULL, |
| user_id bigint |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t_issue_865_ao values (1, 1), (2, 1), (3, 2), (4, 2), (5, 3), (6, 3), (7, 4), (8, 4), (9, 5), (10, 5); |
| CREATE MATERIALIZED VIEW matview_issue_865_ao AS SELECT * FROM t_issue_865_ao WHERE id < 6; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| CREATE INDEX idx_matview_issue_865_ao ON matview_issue_865_ao USING btree (user_id); |
| BEGIN; |
| UPDATE t_issue_865_ao SET id = id WHERE id = 1; |
| UPDATE t_issue_865_ao SET id = id WHERE id = 2; |
| UPDATE t_issue_865_ao SET id = id WHERE id = 3; |
| COMMIT; |
| VACUUM t_issue_865_ao; |
| REFRESH MATERIALIZED VIEW matview_issue_865_ao; |
| -- AOCS |
| set default_table_access_method TO AO_COLUMN; |
| CREATE TABLE t_issue_865_aocs |
| ( |
| id bigint NOT NULL, |
| user_id bigint |
| ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t_issue_865_aocs values (1, 1), (2, 1), (3, 2), (4, 2), (5, 3), (6, 3), (7, 4), (8, 4), (9, 5), (10, 5); |
| CREATE MATERIALIZED VIEW matview_issue_865_aocs AS SELECT * FROM t_issue_865_aocs WHERE id < 6; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry. |
| CREATE INDEX idx_matview_issue_865_aocs ON matview_issue_865_aocs USING btree (user_id); |
| BEGIN; |
| UPDATE t_issue_865_aocs SET id = id WHERE id = 1; |
| UPDATE t_issue_865_aocs SET id = id WHERE id = 2; |
| UPDATE t_issue_865_aocs SET id = id WHERE id = 3; |
| COMMIT; |
| VACUUM t_issue_865_aocs; |
| REFRESH MATERIALIZED VIEW matview_issue_865_aocs; |
| RESET default_table_access_method; |
| DROP TABLE t_issue_865_ao CASCADE; |
| NOTICE: drop cascades to materialized view matview_issue_865_ao |
| DROP TABLE t_issue_865_aocs CASCADE; |
| NOTICE: drop cascades to materialized view matview_issue_865_aocs |
