Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite665769

Browse files
committed
Sanitize IF NOT EXISTS in EXPLAIN for CTAS and matviews
IF NOT EXISTS was ignored when specified in an EXPLAIN query for CREATEMATERIALIZED VIEW or CREATE TABLE AS. Hence, if this clause wasspecified, the caller would get a failure if the relation alreadyexists instead of a success with a NOTICE message.This commit makes the behavior of IF NOT EXISTS in EXPLAIN consistentwith the non-EXPLAIN'd DDL queries, preventing a failure with IF NOTEXISTS if the relation to-be-created already exists. The skip is donebefore the SELECT query used for the relation is planned or executed,and a "dummy" plan is generated instead depending on the format used byEXPLAIN.Author: Bharath RupireddyReviewed-by: Zhijie Hou, Michael PaquierDiscussion:https://postgr.es/m/CALj2ACVa3oJ9O_wcGd+FtHWZds04dEKcakxphGz5POVgD4wC7Q@mail.gmail.com
1 parent0aa8a01 commite665769

File tree

7 files changed

+180
-15
lines changed

7 files changed

+180
-15
lines changed

‎src/backend/commands/createas.c

Lines changed: 38 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -239,21 +239,9 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
239239
PlannedStmt*plan;
240240
QueryDesc*queryDesc;
241241

242-
if (stmt->if_not_exists)
243-
{
244-
Oidnspid;
245-
246-
nspid=RangeVarGetCreationNamespace(stmt->into->rel);
247-
248-
if (get_relname_relid(stmt->into->rel->relname,nspid))
249-
{
250-
ereport(NOTICE,
251-
(errcode(ERRCODE_DUPLICATE_TABLE),
252-
errmsg("relation \"%s\" already exists, skipping",
253-
stmt->into->rel->relname)));
254-
returnInvalidObjectAddress;
255-
}
256-
}
242+
/* Check if the relation exists or not */
243+
if (CreateTableAsRelExists(stmt))
244+
returnInvalidObjectAddress;
257245

258246
/*
259247
* Create the tuple receiver object and insert info it will need
@@ -400,6 +388,41 @@ GetIntoRelEFlags(IntoClause *intoClause)
400388
returnflags;
401389
}
402390

391+
/*
392+
* CreateTableAsRelExists --- check existence of relation for CreateTableAsStmt
393+
*
394+
* Utility wrapper checking if the relation pending for creation in this
395+
* CreateTableAsStmt query already exists or not. Returns true if the
396+
* relation exists, otherwise false.
397+
*/
398+
bool
399+
CreateTableAsRelExists(CreateTableAsStmt*ctas)
400+
{
401+
Oidnspid;
402+
IntoClause*into=ctas->into;
403+
404+
nspid=RangeVarGetCreationNamespace(into->rel);
405+
406+
if (get_relname_relid(into->rel->relname,nspid))
407+
{
408+
if (!ctas->if_not_exists)
409+
ereport(ERROR,
410+
(errcode(ERRCODE_DUPLICATE_TABLE),
411+
errmsg("relation \"%s\" already exists",
412+
into->rel->relname)));
413+
414+
/* The relation exists and IF NOT EXISTS has been specified */
415+
ereport(NOTICE,
416+
(errcode(ERRCODE_DUPLICATE_TABLE),
417+
errmsg("relation \"%s\" already exists, skipping",
418+
into->rel->relname)));
419+
return true;
420+
}
421+
422+
/* Relation does not exist, it can be created */
423+
return false;
424+
}
425+
403426
/*
404427
* CreateIntoRelDestReceiver -- create a suitable DestReceiver object
405428
*

‎src/backend/commands/explain.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -435,6 +435,22 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es,
435435
CreateTableAsStmt*ctas= (CreateTableAsStmt*)utilityStmt;
436436
List*rewritten;
437437

438+
/*
439+
* Check if the relation exists or not. This is done at this stage to
440+
* avoid query planning or execution.
441+
*/
442+
if (CreateTableAsRelExists(ctas))
443+
{
444+
if (ctas->objtype==OBJECT_TABLE)
445+
ExplainDummyGroup("CREATE TABLE AS",NULL,es);
446+
elseif (ctas->objtype==OBJECT_MATVIEW)
447+
ExplainDummyGroup("CREATE MATERIALIZED VIEW",NULL,es);
448+
else
449+
elog(ERROR,"unexpected object type: %d",
450+
(int)ctas->objtype);
451+
return;
452+
}
453+
438454
rewritten=QueryRewrite(castNode(Query,copyObject(ctas->query)));
439455
Assert(list_length(rewritten)==1);
440456
ExplainOneQuery(linitial_node(Query,rewritten),

‎src/include/commands/createas.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,4 +29,6 @@ extern intGetIntoRelEFlags(IntoClause *intoClause);
2929

3030
externDestReceiver*CreateIntoRelDestReceiver(IntoClause*intoClause);
3131

32+
externboolCreateTableAsRelExists(CreateTableAsStmt*ctas);
33+
3234
#endif/* CREATEAS_H */

‎src/test/regress/expected/matview.out

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -630,3 +630,41 @@ drop cascades to materialized view matview_schema.mv_withdata2
630630
drop cascades to materialized view matview_schema.mv_nodata1
631631
drop cascades to materialized view matview_schema.mv_nodata2
632632
DROP USER regress_matview_user;
633+
-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
634+
CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
635+
CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
636+
ERROR: relation "matview_ine_tab" already exists
637+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
638+
SELECT 1 / 0; -- ok
639+
NOTICE: relation "matview_ine_tab" already exists, skipping
640+
CREATE MATERIALIZED VIEW matview_ine_tab AS
641+
SELECT 1 / 0 WITH NO DATA; -- error
642+
ERROR: relation "matview_ine_tab" already exists
643+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
644+
SELECT 1 / 0 WITH NO DATA; -- ok
645+
NOTICE: relation "matview_ine_tab" already exists, skipping
646+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
647+
CREATE MATERIALIZED VIEW matview_ine_tab AS
648+
SELECT 1 / 0; -- error
649+
ERROR: relation "matview_ine_tab" already exists
650+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
651+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
652+
SELECT 1 / 0; -- ok
653+
NOTICE: relation "matview_ine_tab" already exists, skipping
654+
QUERY PLAN
655+
------------
656+
(0 rows)
657+
658+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
659+
CREATE MATERIALIZED VIEW matview_ine_tab AS
660+
SELECT 1 / 0 WITH NO DATA; -- error
661+
ERROR: relation "matview_ine_tab" already exists
662+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
663+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
664+
SELECT 1 / 0 WITH NO DATA; -- ok
665+
NOTICE: relation "matview_ine_tab" already exists, skipping
666+
QUERY PLAN
667+
------------
668+
(0 rows)
669+
670+
DROP MATERIALIZED VIEW matview_ine_tab;

‎src/test/regress/expected/select_into.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -178,3 +178,45 @@ INSERT INTO b SELECT 1 INTO f;
178178
ERROR: SELECT ... INTO is not allowed here
179179
LINE 1: INSERT INTO b SELECT 1 INTO f;
180180
^
181+
-- Test CREATE TABLE AS ... IF NOT EXISTS
182+
CREATE TABLE ctas_ine_tbl AS SELECT 1;
183+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
184+
ERROR: relation "ctas_ine_tbl" already exists
185+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
186+
NOTICE: relation "ctas_ine_tbl" already exists, skipping
187+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
188+
ERROR: relation "ctas_ine_tbl" already exists
189+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
190+
NOTICE: relation "ctas_ine_tbl" already exists, skipping
191+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
192+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
193+
ERROR: relation "ctas_ine_tbl" already exists
194+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
195+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
196+
NOTICE: relation "ctas_ine_tbl" already exists, skipping
197+
QUERY PLAN
198+
------------
199+
(0 rows)
200+
201+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
202+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
203+
ERROR: relation "ctas_ine_tbl" already exists
204+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
205+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
206+
NOTICE: relation "ctas_ine_tbl" already exists, skipping
207+
QUERY PLAN
208+
------------
209+
(0 rows)
210+
211+
PREPARE ctas_ine_query AS SELECT 1 / 0;
212+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
213+
CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
214+
ERROR: relation "ctas_ine_tbl" already exists
215+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
216+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
217+
NOTICE: relation "ctas_ine_tbl" already exists, skipping
218+
QUERY PLAN
219+
------------
220+
(0 rows)
221+
222+
DROP TABLE ctas_ine_tbl;

‎src/test/regress/sql/matview.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -264,3 +264,26 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_matview_user
264264

265265
DROPSCHEMA matview_schema CASCADE;
266266
DROPUSER regress_matview_user;
267+
268+
-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
269+
CREATE MATERIALIZED VIEW matview_ine_tabASSELECT1;
270+
CREATE MATERIALIZED VIEW matview_ine_tabASSELECT1/0;-- error
271+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tabAS
272+
SELECT1/0;-- ok
273+
CREATE MATERIALIZED VIEW matview_ine_tabAS
274+
SELECT1/0 WITH NO DATA;-- error
275+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tabAS
276+
SELECT1/0 WITH NO DATA;-- ok
277+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
278+
CREATE MATERIALIZED VIEW matview_ine_tabAS
279+
SELECT1/0;-- error
280+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
281+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tabAS
282+
SELECT1/0;-- ok
283+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
284+
CREATE MATERIALIZED VIEW matview_ine_tabAS
285+
SELECT1/0 WITH NO DATA;-- error
286+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
287+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tabAS
288+
SELECT1/0 WITH NO DATA;-- ok
289+
DROP MATERIALIZED VIEW matview_ine_tab;

‎src/test/regress/sql/select_into.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -115,3 +115,24 @@ COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blob';
115115
SELECT*FROM (SELECT1 INTO f) bar;
116116
CREATEVIEWfooASSELECT1 INTO b;
117117
INSERT INTO bSELECT1 INTO f;
118+
119+
-- Test CREATE TABLE AS ... IF NOT EXISTS
120+
CREATETABLEctas_ine_tblASSELECT1;
121+
CREATETABLEctas_ine_tblASSELECT1/0;-- error
122+
CREATETABLEIF NOT EXISTS ctas_ine_tblASSELECT1/0;-- ok
123+
CREATETABLEctas_ine_tblASSELECT1/0 WITH NO DATA;-- error
124+
CREATETABLEIF NOT EXISTS ctas_ine_tblASSELECT1/0 WITH NO DATA;-- ok
125+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
126+
CREATETABLEctas_ine_tblASSELECT1/0;-- error
127+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
128+
CREATETABLEIF NOT EXISTS ctas_ine_tblASSELECT1/0;-- ok
129+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
130+
CREATETABLEctas_ine_tblASSELECT1/0 WITH NO DATA;-- error
131+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
132+
CREATETABLEIF NOT EXISTS ctas_ine_tblASSELECT1/0 WITH NO DATA;-- ok
133+
PREPARE ctas_ine_queryASSELECT1/0;
134+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
135+
CREATETABLEctas_ine_tblAS EXECUTE ctas_ine_query;-- error
136+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
137+
CREATETABLEIF NOT EXISTS ctas_ine_tblAS EXECUTE ctas_ine_query;-- ok
138+
DROPTABLE ctas_ine_tbl;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp