From ce6d3bbdac073b4750745120f9a754c30bd57b66 Mon Sep 17 00:00:00 2001 From: Erik Wienhold Date: Tue, 28 May 2024 02:19:53 +0200 Subject: [PATCH v4 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS --- src/backend/parser/gram.y | 14 ++++++ .../expected/test_extensions.out | 45 +++++++++++++++++++ src/test/regress/expected/matview.out | 20 +++++++++ 3 files changed, 79 insertions(+) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6b32233127..dadc6b630a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4808,6 +4808,20 @@ CreateMatViewStmt: $8->rel->relpersistence = $2; $8->skipData = !($11); $$ = (Node *) ctas; + + if (ctas->into->rel->schemaname) + ereport(WARNING, + errmsg("IF NOT EXISTS is deprecated in materialized view creation"), + errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.%s.", + ctas->into->rel->schemaname, + ctas->into->rel->relname), + parser_errposition(@1)); + else + ereport(WARNING, + errmsg("IF NOT EXISTS is deprecated in materialized view creation"), + errhint("Use CREATE OR REPLACE MATERIALIZED VIEW %s.", + ctas->into->rel->relname), + parser_errposition(@1)); } | CREATE OR REPLACE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data { diff --git a/src/test/modules/test_extensions/expected/test_extensions.out b/src/test/modules/test_extensions/expected/test_extensions.out index d5388a1fec..9a1e0f7658 100644 --- a/src/test/modules/test_extensions/expected/test_extensions.out +++ b/src/test/modules/test_extensions/expected/test_extensions.out @@ -404,6 +404,11 @@ Objects in extension "test_ext_cor" CREATE COLLATION ext_cine_coll ( LC_COLLATE = "C", LC_CTYPE = "C" ); CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: collation ext_cine_coll is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE COLLATION IF NOT EXISTS ext_cine_coll @@ -412,6 +417,11 @@ extension script file "test_ext_cine--1.0.sql", near line 10 DROP COLLATION ext_cine_coll; CREATE MATERIALIZED VIEW ext_cine_mv AS SELECT 11 AS f1; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: materialized view ext_cine_mv is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1" @@ -420,6 +430,11 @@ DROP MATERIALIZED VIEW ext_cine_mv; CREATE FOREIGN DATA WRAPPER dummy; CREATE SERVER ext_cine_srv FOREIGN DATA WRAPPER dummy; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: server ext_cine_srv is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE SERVER IF NOT EXISTS ext_cine_srv FOREIGN DATA WRAPPER ext_cine_fdw" @@ -427,6 +442,11 @@ extension script file "test_ext_cine--1.0.sql", near line 17 DROP SERVER ext_cine_srv; CREATE SCHEMA ext_cine_schema; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: schema ext_cine_schema is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE SCHEMA IF NOT EXISTS ext_cine_schema" @@ -434,6 +454,11 @@ extension script file "test_ext_cine--1.0.sql", near line 19 DROP SCHEMA ext_cine_schema; CREATE SEQUENCE ext_cine_seq; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: sequence ext_cine_seq is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE SEQUENCE IF NOT EXISTS ext_cine_seq" @@ -441,6 +466,11 @@ extension script file "test_ext_cine--1.0.sql", near line 21 DROP SEQUENCE ext_cine_seq; CREATE TABLE ext_cine_tab1 (x int); CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: table ext_cine_tab1 is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab1 (x int)" @@ -448,12 +478,22 @@ extension script file "test_ext_cine--1.0.sql", near line 23 DROP TABLE ext_cine_tab1; CREATE TABLE ext_cine_tab2 AS SELECT 42 AS y; CREATE EXTENSION test_ext_cine; -- fail +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; ERROR: table ext_cine_tab2 is not a member of extension "test_ext_cine" DETAIL: An extension may only use CREATE ... IF NOT EXISTS to skip object creation if the conflicting object is one that it already owns. CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS ext_cine_tab2 AS SELECT 42 AS y" extension script file "test_ext_cine--1.0.sql", near line 25 DROP TABLE ext_cine_tab2; CREATE EXTENSION test_ext_cine; +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; \dx+ test_ext_cine Objects in extension "test_ext_cine" Object description @@ -475,6 +515,11 @@ Objects in extension "test_ext_cine" (14 rows) ALTER EXTENSION test_ext_cine UPDATE TO '1.1'; +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW ext_cine_mv. +QUERY: CREATE MATERIALIZED VIEW IF NOT EXISTS ext_cine_mv AS SELECT 42 AS f1; \dx+ test_ext_cine Objects in extension "test_ext_cine" Object description diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index e2e2a13396..cefd0d442c 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -565,6 +565,10 @@ CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; CREATE MATERIALIZED VIEW mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; ERROR: relation "mvtest_mv_foo" already exists CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELECT * FROM mvtest_foo_data; +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS mvtest_mv_foo AS SELE... + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW mvtest_mv_foo. NOTICE: relation "mvtest_mv_foo" already exists, skipping CREATE UNIQUE INDEX ON mvtest_mv_foo (i); RESET ROLE; @@ -662,12 +666,20 @@ 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 +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. 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 +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 1: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) CREATE MATERIALIZED VIEW matview_ine_tab AS @@ -676,6 +688,10 @@ 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 +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping QUERY PLAN ------------ @@ -688,6 +704,10 @@ 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 +WARNING: IF NOT EXISTS is deprecated in materialized view creation +LINE 2: CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS + ^ +HINT: Use CREATE OR REPLACE MATERIALIZED VIEW matview_ine_tab. NOTICE: relation "matview_ine_tab" already exists, skipping QUERY PLAN ------------ -- 2.47.0