Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * createas.c
4 : * Execution of CREATE TABLE ... AS, a/k/a SELECT INTO.
5 : * Since CREATE MATERIALIZED VIEW shares syntax and most behaviors,
6 : * we implement that here, too.
7 : *
8 : * We implement this by diverting the query's normal output to a
9 : * specialized DestReceiver type.
10 : *
11 : * Formerly, CTAS was implemented as a variant of SELECT, which led
12 : * to assorted legacy behaviors that we still try to preserve, notably that
13 : * we must return a tuples-processed count in the QueryCompletion. (We no
14 : * longer do that for CTAS ... WITH NO DATA, however.)
15 : *
16 : * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
17 : * Portions Copyright (c) 1994, Regents of the University of California
18 : *
19 : *
20 : * IDENTIFICATION
21 : * src/backend/commands/createas.c
22 : *
23 : *-------------------------------------------------------------------------
24 : */
25 : #include "postgres.h"
26 :
27 : #include "access/heapam.h"
28 : #include "access/reloptions.h"
29 : #include "access/tableam.h"
30 : #include "access/xact.h"
31 : #include "catalog/namespace.h"
32 : #include "catalog/toasting.h"
33 : #include "commands/createas.h"
34 : #include "commands/matview.h"
35 : #include "commands/prepare.h"
36 : #include "commands/tablecmds.h"
37 : #include "commands/view.h"
38 : #include "nodes/makefuncs.h"
39 : #include "nodes/nodeFuncs.h"
40 : #include "nodes/queryjumble.h"
41 : #include "parser/analyze.h"
42 : #include "rewrite/rewriteHandler.h"
43 : #include "tcop/tcopprot.h"
44 : #include "utils/builtins.h"
45 : #include "utils/lsyscache.h"
46 : #include "utils/rls.h"
47 : #include "utils/snapmgr.h"
48 :
49 : typedef struct
50 : {
51 : DestReceiver pub; /* publicly-known function pointers */
52 : IntoClause *into; /* target relation specification */
53 : /* These fields are filled by intorel_startup: */
54 : Relation rel; /* relation to write to */
55 : ObjectAddress reladdr; /* address of rel, for ExecCreateTableAs */
56 : CommandId output_cid; /* cmin to insert in output tuples */
57 : int ti_options; /* table_tuple_insert performance options */
58 : BulkInsertState bistate; /* bulk insert state */
59 : } DR_intorel;
60 :
61 : /* utility functions for CTAS definition creation */
62 : static ObjectAddress create_ctas_internal(List *attrList, IntoClause *into);
63 : static ObjectAddress create_ctas_nodata(List *tlist, IntoClause *into);
64 :
65 : /* DestReceiver routines for collecting data */
66 : static void intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
67 : static bool intorel_receive(TupleTableSlot *slot, DestReceiver *self);
68 : static void intorel_shutdown(DestReceiver *self);
69 : static void intorel_destroy(DestReceiver *self);
70 :
71 :
72 : /*
73 : * create_ctas_internal
74 : *
75 : * Internal utility used for the creation of the definition of a relation
76 : * created via CREATE TABLE AS or a materialized view. Caller needs to
77 : * provide a list of attributes (ColumnDef nodes).
78 : */
79 : static ObjectAddress
80 1644 : create_ctas_internal(List *attrList, IntoClause *into)
81 : {
82 1644 : CreateStmt *create = makeNode(CreateStmt);
83 : bool is_matview;
84 : char relkind;
85 : Datum toast_options;
86 1644 : const char *const validnsps[] = HEAP_RELOPT_NAMESPACES;
87 : ObjectAddress intoRelationAddr;
88 :
89 : /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
90 1644 : is_matview = (into->viewQuery != NULL);
91 1644 : relkind = is_matview ? RELKIND_MATVIEW : RELKIND_RELATION;
92 :
93 : /*
94 : * Create the target relation by faking up a CREATE TABLE parsetree and
95 : * passing it to DefineRelation.
96 : */
97 1644 : create->relation = into->rel;
98 1644 : create->tableElts = attrList;
99 1644 : create->inhRelations = NIL;
100 1644 : create->ofTypename = NULL;
101 1644 : create->constraints = NIL;
102 1644 : create->options = into->options;
103 1644 : create->oncommit = into->onCommit;
104 1644 : create->tablespacename = into->tableSpaceName;
105 1644 : create->if_not_exists = false;
106 1644 : create->accessMethod = into->accessMethod;
107 :
108 : /*
109 : * Create the relation. (This will error out if there's an existing view,
110 : * so we don't need more code to complain if "replace" is false.)
111 : */
112 1644 : intoRelationAddr = DefineRelation(create, relkind, InvalidOid, NULL, NULL);
113 :
114 : /*
115 : * If necessary, create a TOAST table for the target table. Note that
116 : * NewRelationCreateToastTable ends with CommandCounterIncrement(), so
117 : * that the TOAST table will be visible for insertion.
118 : */
119 1626 : CommandCounterIncrement();
120 :
121 : /* parse and validate reloptions for the toast table */
122 1626 : toast_options = transformRelOptions((Datum) 0,
123 : create->options,
124 : "toast",
125 : validnsps,
126 : true, false);
127 :
128 1626 : (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
129 :
130 1626 : NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options);
131 :
132 : /* Create the "view" part of a materialized view. */
133 1626 : if (is_matview)
134 : {
135 : /* StoreViewQuery scribbles on tree, so make a copy */
136 456 : Query *query = copyObject(into->viewQuery);
137 :
138 456 : StoreViewQuery(intoRelationAddr.objectId, query, false);
139 450 : CommandCounterIncrement();
140 : }
141 :
142 1620 : return intoRelationAddr;
143 : }
144 :
145 :
146 : /*
147 : * create_ctas_nodata
148 : *
149 : * Create CTAS or materialized view when WITH NO DATA is used, starting from
150 : * the targetlist of the SELECT or view definition.
151 : */
152 : static ObjectAddress
153 484 : create_ctas_nodata(List *tlist, IntoClause *into)
154 : {
155 : List *attrList;
156 : ListCell *t,
157 : *lc;
158 :
159 : /*
160 : * Build list of ColumnDefs from non-junk elements of the tlist. If a
161 : * column name list was specified in CREATE TABLE AS, override the column
162 : * names in the query. (Too few column names are OK, too many are not.)
163 : */
164 484 : attrList = NIL;
165 484 : lc = list_head(into->colNames);
166 1538 : foreach(t, tlist)
167 : {
168 1054 : TargetEntry *tle = (TargetEntry *) lfirst(t);
169 :
170 1054 : if (!tle->resjunk)
171 : {
172 : ColumnDef *col;
173 : char *colname;
174 :
175 1054 : if (lc)
176 : {
177 124 : colname = strVal(lfirst(lc));
178 124 : lc = lnext(into->colNames, lc);
179 : }
180 : else
181 930 : colname = tle->resname;
182 :
183 1054 : col = makeColumnDef(colname,
184 1054 : exprType((Node *) tle->expr),
185 1054 : exprTypmod((Node *) tle->expr),
186 1054 : exprCollation((Node *) tle->expr));
187 :
188 : /*
189 : * It's possible that the column is of a collatable type but the
190 : * collation could not be resolved, so double-check. (We must
191 : * check this here because DefineRelation would adopt the type's
192 : * default collation rather than complaining.)
193 : */
194 1924 : if (!OidIsValid(col->collOid) &&
195 870 : type_is_collatable(col->typeName->typeOid))
196 0 : ereport(ERROR,
197 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
198 : errmsg("no collation was derived for column \"%s\" with collatable type %s",
199 : col->colname,
200 : format_type_be(col->typeName->typeOid)),
201 : errhint("Use the COLLATE clause to set the collation explicitly.")));
202 :
203 1054 : attrList = lappend(attrList, col);
204 : }
205 : }
206 :
207 484 : if (lc != NULL)
208 18 : ereport(ERROR,
209 : (errcode(ERRCODE_SYNTAX_ERROR),
210 : errmsg("too many column names were specified")));
211 :
212 : /* Create the relation definition using the ColumnDef list */
213 466 : return create_ctas_internal(attrList, into);
214 : }
215 :
216 :
217 : /*
218 : * ExecCreateTableAs -- execute a CREATE TABLE AS command
219 : */
220 : ObjectAddress
221 1718 : ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
222 : ParamListInfo params, QueryEnvironment *queryEnv,
223 : QueryCompletion *qc)
224 : {
225 1718 : Query *query = castNode(Query, stmt->query);
226 1718 : IntoClause *into = stmt->into;
227 1718 : JumbleState *jstate = NULL;
228 1718 : bool is_matview = (into->viewQuery != NULL);
229 1718 : bool do_refresh = false;
230 : DestReceiver *dest;
231 : ObjectAddress address;
232 :
233 : /* Check if the relation exists or not */
234 1718 : if (CreateTableAsRelExists(stmt))
235 46 : return InvalidObjectAddress;
236 :
237 : /*
238 : * Create the tuple receiver object and insert info it will need
239 : */
240 1626 : dest = CreateIntoRelDestReceiver(into);
241 :
242 : /* Query contained by CTAS needs to be jumbled if requested */
243 1626 : if (IsQueryIdEnabled())
244 432 : jstate = JumbleQuery(query);
245 :
246 1626 : if (post_parse_analyze_hook)
247 432 : (*post_parse_analyze_hook) (pstate, query, jstate);
248 :
249 : /*
250 : * The contained Query could be a SELECT, or an EXECUTE utility command.
251 : * If the latter, we just pass it off to ExecuteQuery.
252 : */
253 1626 : if (query->commandType == CMD_UTILITY &&
254 46 : IsA(query->utilityStmt, ExecuteStmt))
255 : {
256 46 : ExecuteStmt *estmt = castNode(ExecuteStmt, query->utilityStmt);
257 :
258 : Assert(!is_matview); /* excluded by syntax */
259 46 : ExecuteQuery(pstate, estmt, into, params, dest, qc);
260 :
261 : /* get object address that intorel_startup saved for us */
262 46 : address = ((DR_intorel *) dest)->reladdr;
263 :
264 46 : return address;
265 : }
266 : Assert(query->commandType == CMD_SELECT);
267 :
268 : /*
269 : * For materialized views, always skip data during table creation, and use
270 : * REFRESH instead (see below).
271 : */
272 1580 : if (is_matview)
273 : {
274 456 : do_refresh = !into->skipData;
275 456 : into->skipData = true;
276 : }
277 :
278 1580 : if (into->skipData)
279 : {
280 : /*
281 : * If WITH NO DATA was specified, do not go through the rewriter,
282 : * planner and executor. Just define the relation using a code path
283 : * similar to CREATE VIEW. This avoids dump/restore problems stemming
284 : * from running the planner before all dependencies are set up.
285 : */
286 484 : address = create_ctas_nodata(query->targetList, into);
287 :
288 : /*
289 : * For materialized views, reuse the REFRESH logic, which locks down
290 : * security-restricted operations and restricts the search_path. This
291 : * reduces the chance that a subsequent refresh will fail.
292 : */
293 460 : if (do_refresh)
294 362 : RefreshMatViewByOid(address.objectId, true, false, false,
295 : pstate->p_sourcetext, qc);
296 :
297 : }
298 : else
299 : {
300 : List *rewritten;
301 : PlannedStmt *plan;
302 : QueryDesc *queryDesc;
303 :
304 : Assert(!is_matview);
305 :
306 : /*
307 : * Parse analysis was done already, but we still have to run the rule
308 : * rewriter. We do not do AcquireRewriteLocks: we assume the query
309 : * either came straight from the parser, or suitable locks were
310 : * acquired by plancache.c.
311 : */
312 1096 : rewritten = QueryRewrite(query);
313 :
314 : /* SELECT should never rewrite to more or less than one SELECT query */
315 1096 : if (list_length(rewritten) != 1)
316 0 : elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT");
317 1096 : query = linitial_node(Query, rewritten);
318 : Assert(query->commandType == CMD_SELECT);
319 :
320 : /* plan the query */
321 1096 : plan = pg_plan_query(query, pstate->p_sourcetext,
322 : CURSOR_OPT_PARALLEL_OK, params);
323 :
324 : /*
325 : * Use a snapshot with an updated command ID to ensure this query sees
326 : * results of any previously executed queries. (This could only
327 : * matter if the planner executed an allegedly-stable function that
328 : * changed the database contents, but let's do it anyway to be
329 : * parallel to the EXPLAIN code path.)
330 : */
331 1096 : PushCopiedSnapshot(GetActiveSnapshot());
332 1096 : UpdateActiveSnapshotCommandId();
333 :
334 : /* Create a QueryDesc, redirecting output to our tuple receiver */
335 1096 : queryDesc = CreateQueryDesc(plan, NULL, pstate->p_sourcetext,
336 : GetActiveSnapshot(), InvalidSnapshot,
337 : dest, params, queryEnv, 0);
338 :
339 : /* call ExecutorStart to prepare the plan for execution */
340 1096 : if (!ExecutorStart(queryDesc, GetIntoRelEFlags(into)))
341 0 : elog(ERROR, "ExecutorStart() failed unexpectedly");
342 :
343 : /* run the plan to completion */
344 1096 : ExecutorRun(queryDesc, ForwardScanDirection, 0);
345 :
346 : /* save the rowcount if we're given a qc to fill */
347 1060 : if (qc)
348 1052 : SetQueryCompletion(qc, CMDTAG_SELECT, queryDesc->estate->es_processed);
349 :
350 : /* get object address that intorel_startup saved for us */
351 1060 : address = ((DR_intorel *) dest)->reladdr;
352 :
353 : /* and clean up */
354 1060 : ExecutorFinish(queryDesc);
355 1060 : ExecutorEnd(queryDesc);
356 :
357 1060 : FreeQueryDesc(queryDesc);
358 :
359 1060 : PopActiveSnapshot();
360 : }
361 :
362 1514 : return address;
363 : }
364 :
365 : /*
366 : * GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
367 : *
368 : * This is exported because EXPLAIN and PREPARE need it too. (Note: those
369 : * callers still need to deal explicitly with the skipData flag; since they
370 : * use different methods for suppressing execution, it doesn't seem worth
371 : * trying to encapsulate that part.)
372 : */
373 : int
374 1248 : GetIntoRelEFlags(IntoClause *intoClause)
375 : {
376 1248 : int flags = 0;
377 :
378 1248 : if (intoClause->skipData)
379 42 : flags |= EXEC_FLAG_WITH_NO_DATA;
380 :
381 1248 : return flags;
382 : }
383 :
384 : /*
385 : * CreateTableAsRelExists --- check existence of relation for CreateTableAsStmt
386 : *
387 : * Utility wrapper checking if the relation pending for creation in this
388 : * CreateTableAsStmt query already exists or not. Returns true if the
389 : * relation exists, otherwise false.
390 : */
391 : bool
392 1884 : CreateTableAsRelExists(CreateTableAsStmt *ctas)
393 : {
394 : Oid nspid;
395 : Oid oldrelid;
396 : ObjectAddress address;
397 1884 : IntoClause *into = ctas->into;
398 :
399 1884 : nspid = RangeVarGetCreationNamespace(into->rel);
400 :
401 1884 : oldrelid = get_relname_relid(into->rel->relname, nspid);
402 1884 : if (OidIsValid(oldrelid))
403 : {
404 152 : if (!ctas->if_not_exists)
405 72 : ereport(ERROR,
406 : (errcode(ERRCODE_DUPLICATE_TABLE),
407 : errmsg("relation \"%s\" already exists",
408 : into->rel->relname)));
409 :
410 : /*
411 : * The relation exists and IF NOT EXISTS has been specified.
412 : *
413 : * If we are in an extension script, insist that the pre-existing
414 : * object be a member of the extension, to avoid security risks.
415 : */
416 80 : ObjectAddressSet(address, RelationRelationId, oldrelid);
417 80 : checkMembershipInCurrentExtension(&address);
418 :
419 : /* OK to skip */
420 76 : ereport(NOTICE,
421 : (errcode(ERRCODE_DUPLICATE_TABLE),
422 : errmsg("relation \"%s\" already exists, skipping",
423 : into->rel->relname)));
424 76 : return true;
425 : }
426 :
427 : /* Relation does not exist, it can be created */
428 1732 : return false;
429 : }
430 :
431 : /*
432 : * CreateIntoRelDestReceiver -- create a suitable DestReceiver object
433 : *
434 : * intoClause will be NULL if called from CreateDestReceiver(), in which
435 : * case it has to be provided later. However, it is convenient to allow
436 : * self->into to be filled in immediately for other callers.
437 : */
438 : DestReceiver *
439 1732 : CreateIntoRelDestReceiver(IntoClause *intoClause)
440 : {
441 1732 : DR_intorel *self = (DR_intorel *) palloc0(sizeof(DR_intorel));
442 :
443 1732 : self->pub.receiveSlot = intorel_receive;
444 1732 : self->pub.rStartup = intorel_startup;
445 1732 : self->pub.rShutdown = intorel_shutdown;
446 1732 : self->pub.rDestroy = intorel_destroy;
447 1732 : self->pub.mydest = DestIntoRel;
448 1732 : self->into = intoClause;
449 : /* other private fields will be set during intorel_startup */
450 :
451 1732 : return (DestReceiver *) self;
452 : }
453 :
454 : /*
455 : * intorel_startup --- executor startup
456 : */
457 : static void
458 1196 : intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
459 : {
460 1196 : DR_intorel *myState = (DR_intorel *) self;
461 1196 : IntoClause *into = myState->into;
462 : bool is_matview;
463 : List *attrList;
464 : ObjectAddress intoRelationAddr;
465 : Relation intoRelationDesc;
466 : ListCell *lc;
467 : int attnum;
468 :
469 : Assert(into != NULL); /* else somebody forgot to set it */
470 :
471 : /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
472 1196 : is_matview = (into->viewQuery != NULL);
473 :
474 : /*
475 : * Build column definitions using "pre-cooked" type and collation info. If
476 : * a column name list was specified in CREATE TABLE AS, override the
477 : * column names derived from the query. (Too few column names are OK, too
478 : * many are not.)
479 : */
480 1196 : attrList = NIL;
481 1196 : lc = list_head(into->colNames);
482 4840 : for (attnum = 0; attnum < typeinfo->natts; attnum++)
483 : {
484 3656 : Form_pg_attribute attribute = TupleDescAttr(typeinfo, attnum);
485 : ColumnDef *col;
486 : char *colname;
487 :
488 3656 : if (lc)
489 : {
490 216 : colname = strVal(lfirst(lc));
491 216 : lc = lnext(into->colNames, lc);
492 : }
493 : else
494 3440 : colname = NameStr(attribute->attname);
495 :
496 3656 : col = makeColumnDef(colname,
497 : attribute->atttypid,
498 : attribute->atttypmod,
499 : attribute->attcollation);
500 :
501 : /*
502 : * It's possible that the column is of a collatable type but the
503 : * collation could not be resolved, so double-check. (We must check
504 : * this here because DefineRelation would adopt the type's default
505 : * collation rather than complaining.)
506 : */
507 6790 : if (!OidIsValid(col->collOid) &&
508 3134 : type_is_collatable(col->typeName->typeOid))
509 12 : ereport(ERROR,
510 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
511 : errmsg("no collation was derived for column \"%s\" with collatable type %s",
512 : col->colname,
513 : format_type_be(col->typeName->typeOid)),
514 : errhint("Use the COLLATE clause to set the collation explicitly.")));
515 :
516 3644 : attrList = lappend(attrList, col);
517 : }
518 :
519 1184 : if (lc != NULL)
520 6 : ereport(ERROR,
521 : (errcode(ERRCODE_SYNTAX_ERROR),
522 : errmsg("too many column names were specified")));
523 :
524 : /*
525 : * Actually create the target table
526 : */
527 1178 : intoRelationAddr = create_ctas_internal(attrList, into);
528 :
529 : /*
530 : * Finally we can open the target table
531 : */
532 1160 : intoRelationDesc = table_open(intoRelationAddr.objectId, AccessExclusiveLock);
533 :
534 : /*
535 : * Make sure the constructed table does not have RLS enabled.
536 : *
537 : * check_enable_rls() will ereport(ERROR) itself if the user has requested
538 : * something invalid, and otherwise will return RLS_ENABLED if RLS should
539 : * be enabled here. We don't actually support that currently, so throw
540 : * our own ereport(ERROR) if that happens.
541 : */
542 1160 : if (check_enable_rls(intoRelationAddr.objectId, InvalidOid, false) == RLS_ENABLED)
543 0 : ereport(ERROR,
544 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
545 : errmsg("policies not yet implemented for this command")));
546 :
547 : /*
548 : * Tentatively mark the target as populated, if it's a matview and we're
549 : * going to fill it; otherwise, no change needed.
550 : */
551 1160 : if (is_matview && !into->skipData)
552 6 : SetMatViewPopulatedState(intoRelationDesc, true);
553 :
554 : /*
555 : * Fill private fields of myState for use by later routines
556 : */
557 1160 : myState->rel = intoRelationDesc;
558 1160 : myState->reladdr = intoRelationAddr;
559 1160 : myState->output_cid = GetCurrentCommandId(true);
560 1160 : myState->ti_options = TABLE_INSERT_SKIP_FSM;
561 :
562 : /*
563 : * If WITH NO DATA is specified, there is no need to set up the state for
564 : * bulk inserts as there are no tuples to insert.
565 : */
566 1160 : if (!into->skipData)
567 1124 : myState->bistate = GetBulkInsertState();
568 : else
569 36 : myState->bistate = NULL;
570 :
571 : /*
572 : * Valid smgr_targblock implies something already wrote to the relation.
573 : * This may be harmless, but this function hasn't planned for it.
574 : */
575 : Assert(RelationGetTargetBlock(intoRelationDesc) == InvalidBlockNumber);
576 1160 : }
577 :
578 : /*
579 : * intorel_receive --- receive one tuple
580 : */
581 : static bool
582 2137282 : intorel_receive(TupleTableSlot *slot, DestReceiver *self)
583 : {
584 2137282 : DR_intorel *myState = (DR_intorel *) self;
585 :
586 : /* Nothing to insert if WITH NO DATA is specified. */
587 2137282 : if (!myState->into->skipData)
588 : {
589 : /*
590 : * Note that the input slot might not be of the type of the target
591 : * relation. That's supported by table_tuple_insert(), but slightly
592 : * less efficient than inserting with the right slot - but the
593 : * alternative would be to copy into a slot of the right type, which
594 : * would not be cheap either. This also doesn't allow accessing per-AM
595 : * data (say a tuple's xmin), but since we don't do that here...
596 : */
597 2137282 : table_tuple_insert(myState->rel,
598 : slot,
599 : myState->output_cid,
600 : myState->ti_options,
601 : myState->bistate);
602 : }
603 :
604 : /* We know this is a newly created relation, so there are no indexes */
605 :
606 2137282 : return true;
607 : }
608 :
609 : /*
610 : * intorel_shutdown --- executor end
611 : */
612 : static void
613 1160 : intorel_shutdown(DestReceiver *self)
614 : {
615 1160 : DR_intorel *myState = (DR_intorel *) self;
616 1160 : IntoClause *into = myState->into;
617 :
618 1160 : if (!into->skipData)
619 : {
620 1124 : FreeBulkInsertState(myState->bistate);
621 1124 : table_finish_bulk_insert(myState->rel, myState->ti_options);
622 : }
623 :
624 : /* close rel, but keep lock until commit */
625 1160 : table_close(myState->rel, NoLock);
626 1160 : myState->rel = NULL;
627 1160 : }
628 :
629 : /*
630 : * intorel_destroy --- release DestReceiver object
631 : */
632 : static void
633 106 : intorel_destroy(DestReceiver *self)
634 : {
635 106 : pfree(self);
636 106 : }
|
