Skip to content

Commit e665769

Browse files
michaelpqmichaelpq
committed
Sanitize IF NOT EXISTS in EXPLAIN for CTAS and matviews
IF NOT EXISTS was ignored when specified in an EXPLAIN query for CREATE MATERIALIZED VIEW or CREATE TABLE AS. Hence, if this clause was specified, the caller would get a failure if the relation already exists instead of a success with a NOTICE message. This commit makes the behavior of IF NOT EXISTS in EXPLAIN consistent with the non-EXPLAIN'd DDL queries, preventing a failure with IF NOT EXISTS if the relation to-be-created already exists. The skip is done before the SELECT query used for the relation is planned or executed, and a "dummy" plan is generated instead depending on the format used by EXPLAIN. Author: Bharath Rupireddy Reviewed-by: Zhijie Hou, Michael Paquier Discussion: https://postgr.es/m/CALj2ACVa3oJ9O_wcGd+FtHWZds04dEKcakxphGz5POVgD4wC7Q@mail.gmail.com
1 parent 0aa8a01 commit e665769

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-
Oid nspid;
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-
return InvalidObjectAddress;
255-
}
256-
}
242+
/* Check if the relation exists or not */
243+
if (CreateTableAsRelExists(stmt))
244+
return InvalidObjectAddress;
257245

258246
/*
259247
* Create the tuple receiver object and insert info it will need
@@ -400,6 +388,41 @@ GetIntoRelEFlags(IntoClause *intoClause)
400388
return flags;
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+
Oid nspid;
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+
else if (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 int GetIntoRelEFlags(IntoClause *intoClause);
2929

3030
extern DestReceiver *CreateIntoRelDestReceiver(IntoClause *intoClause);
3131

32+
extern bool CreateTableAsRelExists(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
DROP SCHEMA matview_schema CASCADE;
266266
DROP USER regress_matview_user;
267+
268+
-- CREATE MATERIALIZED VIEW ... IF NOT EXISTS
269+
CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1;
270+
CREATE MATERIALIZED VIEW matview_ine_tab AS SELECT 1 / 0; -- error
271+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
272+
SELECT 1 / 0; -- ok
273+
CREATE MATERIALIZED VIEW matview_ine_tab AS
274+
SELECT 1 / 0 WITH NO DATA; -- error
275+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
276+
SELECT 1 / 0 WITH NO DATA; -- ok
277+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
278+
CREATE MATERIALIZED VIEW matview_ine_tab AS
279+
SELECT 1 / 0; -- error
280+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
281+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
282+
SELECT 1 / 0; -- ok
283+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
284+
CREATE MATERIALIZED VIEW matview_ine_tab AS
285+
SELECT 1 / 0 WITH NO DATA; -- error
286+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
287+
CREATE MATERIALIZED VIEW IF NOT EXISTS matview_ine_tab AS
288+
SELECT 1 / 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 (SELECT 1 INTO f) bar;
116116
CREATE VIEW foo AS SELECT 1 INTO b;
117117
INSERT INTO b SELECT 1 INTO f;
118+
119+
-- Test CREATE TABLE AS ... IF NOT EXISTS
120+
CREATE TABLE ctas_ine_tbl AS SELECT 1;
121+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
122+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
123+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
124+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
125+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
126+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
127+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
128+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0; -- ok
129+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
130+
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- error
131+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
132+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS SELECT 1 / 0 WITH NO DATA; -- ok
133+
PREPARE ctas_ine_query AS SELECT 1 / 0;
134+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
135+
CREATE TABLE ctas_ine_tbl AS EXECUTE ctas_ine_query; -- error
136+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
137+
CREATE TABLE IF NOT EXISTS ctas_ine_tbl AS EXECUTE ctas_ine_query; -- ok
138+
DROP TABLE ctas_ine_tbl;

0 commit comments

Comments
 (0)