v5-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch
text/plain
Filename: v5-0002-Deprecate-CREATE-MATERIALIZED-VIEW-IF-NOT-EXISTS.patch
Type: text/plain
Part: 1
From 2478cd438360a2fd5d74082c96b8feabd49ac593 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Tue, 28 May 2024 02:19:53 +0200
Subject: [PATCH v5 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 c917c11f9e..cb5647dcb6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4853,6 +4853,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 71f6a0681d..d506c615da 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, BUFFERS 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, BUFFERS 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, BUFFERS 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.48.0