agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Wienhold <[email protected]>
Subject: [PATCH v4 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS
Date: Tue, 28 May 2024 02:19:53 +0200
---
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
--hht4bymbvbva5al5
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0003-Replace-matview-WITH-OLD-DATA.patch"
view thread (1575+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH v4 2/3] Deprecate CREATE MATERIALIZED VIEW IF NOT EXISTS
In-Reply-To: <no-message-id-220139@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox