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-2024, 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 1628 : create_ctas_internal(List *attrList, IntoClause *into)
81 : {
82 1628 : CreateStmt *create = makeNode(CreateStmt);
83 : bool is_matview;
84 : char relkind;
85 : Datum toast_options;
86 1628 : const char *const validnsps[] = HEAP_RELOPT_NAMESPACES;
87 : ObjectAddress intoRelationAddr;
88 :
89 : /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
90 1628 : is_matview = (into->viewQuery != NULL);
91 1628 : 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 1628 : create->relation = into->rel;
98 1628 : create->tableElts = attrList;
99 1628 : create->inhRelations = NIL;
100 1628 : create->ofTypename = NULL;
101 1628 : create->constraints = NIL;
102 1628 : create->options = into->options;
103 1628 : create->oncommit = into->onCommit;
104 1628 : create->tablespacename = into->tableSpaceName;
105 1628 : create->if_not_exists = false;
106 1628 : 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 1628 : 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 1610 : CommandCounterIncrement();
120 :
121 : /* parse and validate reloptions for the toast table */
122 1610 : toast_options = transformRelOptions((Datum) 0,
123 : create->options,
124 : "toast",
125 : validnsps,
126 : true, false);
127 :
128 1610 : (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
129 :
130 1610 : NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options);
131 :
132 : /* Create the "view" part of a materialized view. */
133 1610 : if (is_matview)
134 : {
135 : /* StoreViewQuery scribbles on tree, so make a copy */
136 448 : Query *query = copyObject(into->viewQuery);
137 :
138 448 : StoreViewQuery(intoRelationAddr.objectId, query, false);
139 448 : CommandCounterIncrement();
140 : }
141 :
142 1610 : 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 476 : 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 476 : attrList = NIL;
165 476 : lc = list_head(into->colNames);
166 1520 : foreach(t, tlist)
167 : {
168 1044 : TargetEntry *tle = (TargetEntry *) lfirst(t);
169 :
170 1044 : if (!tle->resjunk)
171 : {
172 : ColumnDef *col;
173 : char *colname;
174 :
175 1044 : if (lc)
176 : {
177 124 : colname = strVal(lfirst(lc));
178 124 : lc = lnext(into->colNames, lc);
179 : }
180 : else
181 920 : colname = tle->resname;
182 :
183 1044 : col = makeColumnDef(colname,
184 1044 : exprType((Node *) tle->expr),
185 1044 : exprTypmod((Node *) tle->expr),
186 1044 : 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 1904 : if (!OidIsValid(col->collOid) &&
195 860 : 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 1044 : attrList = lappend(attrList, col);
204 : }
205 : }
206 :
207 476 : 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 458 : return create_ctas_internal(attrList, into);
214 : }
215 :
216 :
217 : /*
218 : * ExecCreateTableAs -- execute a CREATE TABLE AS command
219 : */
220 : ObjectAddress
221 1702 : ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
222 : ParamListInfo params, QueryEnvironment *queryEnv,
223 : QueryCompletion *qc)
224 : {
225 1702 : Query *query = castNode(Query, stmt->query);
226 1702 : IntoClause *into = stmt->into;
227 1702 : JumbleState *jstate = NULL;
228 1702 : bool is_matview = (into->viewQuery != NULL);
229 1702 : bool do_refresh = false;
230 : DestReceiver *dest;
231 : ObjectAddress address;
232 :
233 : /* Check if the relation exists or not */
234 1702 : if (CreateTableAsRelExists(stmt))
235 46 : return InvalidObjectAddress;
236 :
237 : /*
238 : * Create the tuple receiver object and insert info it will need
239 : */
240 1610 : dest = CreateIntoRelDestReceiver(into);
241 :
242 : /* Query contained by CTAS needs to be jumbled if requested */
243 1610 : if (IsQueryIdEnabled())
244 430 : jstate = JumbleQuery(query);
245 :
246 1610 : if (post_parse_analyze_hook)
247 430 : (*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 1610 : 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 1564 : if (is_matview)
273 : {
274 448 : do_refresh = !into->skipData;
275 448 : into->skipData = true;
276 : }
277 :
278 1564 : 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 476 : 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 458 : if (do_refresh)
294 360 : 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 1088 : rewritten = QueryRewrite(query);
313 :
314 : /* SELECT should never rewrite to more or less than one SELECT query */
315 1088 : if (list_length(rewritten) != 1)
316 0 : elog(ERROR, "unexpected rewrite result for CREATE TABLE AS SELECT");
317 1088 : query = linitial_node(Query, rewritten);
318 : Assert(query->commandType == CMD_SELECT);
319 :
320 : /* plan the query */
321 1088 : 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 1088 : PushCopiedSnapshot(GetActiveSnapshot());
332 1088 : UpdateActiveSnapshotCommandId();
333 :
334 : /* Create a QueryDesc, redirecting output to our tuple receiver */
335 1088 : queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
336 : GetActiveSnapshot(), InvalidSnapshot,
337 : dest, params, queryEnv, 0);
338 :
339 : /* call ExecutorStart to prepare the plan for execution */
340 1088 : ExecutorStart(queryDesc, GetIntoRelEFlags(into));
341 :
342 : /* run the plan to completion */
343 1088 : ExecutorRun(queryDesc, ForwardScanDirection, 0, true);
344 :
345 : /* save the rowcount if we're given a qc to fill */
346 1052 : if (qc)
347 1044 : SetQueryCompletion(qc, CMDTAG_SELECT, queryDesc->estate->es_processed);
348 :
349 : /* get object address that intorel_startup saved for us */
350 1052 : address = ((DR_intorel *) dest)->reladdr;
351 :
352 : /* and clean up */
353 1052 : ExecutorFinish(queryDesc);
354 1052 : ExecutorEnd(queryDesc);
355 :
356 1052 : FreeQueryDesc(queryDesc);
357 :
358 1052 : PopActiveSnapshot();
359 : }
360 :
361 1504 : return address;
362 : }
363 :
364 : /*
365 : * GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
366 : *
367 : * This is exported because EXPLAIN and PREPARE need it too. (Note: those
368 : * callers still need to deal explicitly with the skipData flag; since they
369 : * use different methods for suppressing execution, it doesn't seem worth
370 : * trying to encapsulate that part.)
371 : */
372 : int
373 1240 : GetIntoRelEFlags(IntoClause *intoClause)
374 : {
375 1240 : int flags = 0;
376 :
377 1240 : if (intoClause->skipData)
378 42 : flags |= EXEC_FLAG_WITH_NO_DATA;
379 :
380 1240 : return flags;
381 : }
382 :
383 : /*
384 : * CreateTableAsRelExists --- check existence of relation for CreateTableAsStmt
385 : *
386 : * Utility wrapper checking if the relation pending for creation in this
387 : * CreateTableAsStmt query already exists or not. Returns true if the
388 : * relation exists, otherwise false.
389 : */
390 : bool
391 1868 : CreateTableAsRelExists(CreateTableAsStmt *ctas)
392 : {
393 : Oid nspid;
394 : Oid oldrelid;
395 : ObjectAddress address;
396 1868 : IntoClause *into = ctas->into;
397 :
398 1868 : nspid = RangeVarGetCreationNamespace(into->rel);
399 :
400 1868 : oldrelid = get_relname_relid(into->rel->relname, nspid);
401 1868 : if (OidIsValid(oldrelid))
402 : {
403 152 : if (!ctas->if_not_exists)
404 72 : ereport(ERROR,
405 : (errcode(ERRCODE_DUPLICATE_TABLE),
406 : errmsg("relation \"%s\" already exists",
407 : into->rel->relname)));
408 :
409 : /*
410 : * The relation exists and IF NOT EXISTS has been specified.
411 : *
412 : * If we are in an extension script, insist that the pre-existing
413 : * object be a member of the extension, to avoid security risks.
414 : */
415 80 : ObjectAddressSet(address, RelationRelationId, oldrelid);
416 80 : checkMembershipInCurrentExtension(&address);
417 :
418 : /* OK to skip */
419 76 : ereport(NOTICE,
420 : (errcode(ERRCODE_DUPLICATE_TABLE),
421 : errmsg("relation \"%s\" already exists, skipping",
422 : into->rel->relname)));
423 76 : return true;
424 : }
425 :
426 : /* Relation does not exist, it can be created */
427 1716 : return false;
428 : }
429 :
430 : /*
431 : * CreateIntoRelDestReceiver -- create a suitable DestReceiver object
432 : *
433 : * intoClause will be NULL if called from CreateDestReceiver(), in which
434 : * case it has to be provided later. However, it is convenient to allow
435 : * self->into to be filled in immediately for other callers.
436 : */
437 : DestReceiver *
438 1716 : CreateIntoRelDestReceiver(IntoClause *intoClause)
439 : {
440 1716 : DR_intorel *self = (DR_intorel *) palloc0(sizeof(DR_intorel));
441 :
442 1716 : self->pub.receiveSlot = intorel_receive;
443 1716 : self->pub.rStartup = intorel_startup;
444 1716 : self->pub.rShutdown = intorel_shutdown;
445 1716 : self->pub.rDestroy = intorel_destroy;
446 1716 : self->pub.mydest = DestIntoRel;
447 1716 : self->into = intoClause;
448 : /* other private fields will be set during intorel_startup */
449 :
450 1716 : return (DestReceiver *) self;
451 : }
452 :
453 : /*
454 : * intorel_startup --- executor startup
455 : */
456 : static void
457 1188 : intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
458 : {
459 1188 : DR_intorel *myState = (DR_intorel *) self;
460 1188 : IntoClause *into = myState->into;
461 : bool is_matview;
462 : List *attrList;
463 : ObjectAddress intoRelationAddr;
464 : Relation intoRelationDesc;
465 : ListCell *lc;
466 : int attnum;
467 :
468 : Assert(into != NULL); /* else somebody forgot to set it */
469 :
470 : /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
471 1188 : is_matview = (into->viewQuery != NULL);
472 :
473 : /*
474 : * Build column definitions using "pre-cooked" type and collation info. If
475 : * a column name list was specified in CREATE TABLE AS, override the
476 : * column names derived from the query. (Too few column names are OK, too
477 : * many are not.)
478 : */
479 1188 : attrList = NIL;
480 1188 : lc = list_head(into->colNames);
481 4818 : for (attnum = 0; attnum < typeinfo->natts; attnum++)
482 : {
483 3642 : Form_pg_attribute attribute = TupleDescAttr(typeinfo, attnum);
484 : ColumnDef *col;
485 : char *colname;
486 :
487 3642 : if (lc)
488 : {
489 216 : colname = strVal(lfirst(lc));
490 216 : lc = lnext(into->colNames, lc);
491 : }
492 : else
493 3426 : colname = NameStr(attribute->attname);
494 :
495 3642 : col = makeColumnDef(colname,
496 : attribute->atttypid,
497 : attribute->atttypmod,
498 : attribute->attcollation);
499 :
500 : /*
501 : * It's possible that the column is of a collatable type but the
502 : * collation could not be resolved, so double-check. (We must check
503 : * this here because DefineRelation would adopt the type's default
504 : * collation rather than complaining.)
505 : */
506 6762 : if (!OidIsValid(col->collOid) &&
507 3120 : type_is_collatable(col->typeName->typeOid))
508 12 : ereport(ERROR,
509 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
510 : errmsg("no collation was derived for column \"%s\" with collatable type %s",
511 : col->colname,
512 : format_type_be(col->typeName->typeOid)),
513 : errhint("Use the COLLATE clause to set the collation explicitly.")));
514 :
515 3630 : attrList = lappend(attrList, col);
516 : }
517 :
518 1176 : if (lc != NULL)
519 6 : ereport(ERROR,
520 : (errcode(ERRCODE_SYNTAX_ERROR),
521 : errmsg("too many column names were specified")));
522 :
523 : /*
524 : * Actually create the target table
525 : */
526 1170 : intoRelationAddr = create_ctas_internal(attrList, into);
527 :
528 : /*
529 : * Finally we can open the target table
530 : */
531 1152 : intoRelationDesc = table_open(intoRelationAddr.objectId, AccessExclusiveLock);
532 :
533 : /*
534 : * Make sure the constructed table does not have RLS enabled.
535 : *
536 : * check_enable_rls() will ereport(ERROR) itself if the user has requested
537 : * something invalid, and otherwise will return RLS_ENABLED if RLS should
538 : * be enabled here. We don't actually support that currently, so throw
539 : * our own ereport(ERROR) if that happens.
540 : */
541 1152 : if (check_enable_rls(intoRelationAddr.objectId, InvalidOid, false) == RLS_ENABLED)
542 0 : ereport(ERROR,
543 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
544 : errmsg("policies not yet implemented for this command")));
545 :
546 : /*
547 : * Tentatively mark the target as populated, if it's a matview and we're
548 : * going to fill it; otherwise, no change needed.
549 : */
550 1152 : if (is_matview && !into->skipData)
551 6 : SetMatViewPopulatedState(intoRelationDesc, true);
552 :
553 : /*
554 : * Fill private fields of myState for use by later routines
555 : */
556 1152 : myState->rel = intoRelationDesc;
557 1152 : myState->reladdr = intoRelationAddr;
558 1152 : myState->output_cid = GetCurrentCommandId(true);
559 1152 : myState->ti_options = TABLE_INSERT_SKIP_FSM;
560 :
561 : /*
562 : * If WITH NO DATA is specified, there is no need to set up the state for
563 : * bulk inserts as there are no tuples to insert.
564 : */
565 1152 : if (!into->skipData)
566 1116 : myState->bistate = GetBulkInsertState();
567 : else
568 36 : myState->bistate = NULL;
569 :
570 : /*
571 : * Valid smgr_targblock implies something already wrote to the relation.
572 : * This may be harmless, but this function hasn't planned for it.
573 : */
574 : Assert(RelationGetTargetBlock(intoRelationDesc) == InvalidBlockNumber);
575 1152 : }
576 :
577 : /*
578 : * intorel_receive --- receive one tuple
579 : */
580 : static bool
581 2136980 : intorel_receive(TupleTableSlot *slot, DestReceiver *self)
582 : {
583 2136980 : DR_intorel *myState = (DR_intorel *) self;
584 :
585 : /* Nothing to insert if WITH NO DATA is specified. */
586 2136980 : if (!myState->into->skipData)
587 : {
588 : /*
589 : * Note that the input slot might not be of the type of the target
590 : * relation. That's supported by table_tuple_insert(), but slightly
591 : * less efficient than inserting with the right slot - but the
592 : * alternative would be to copy into a slot of the right type, which
593 : * would not be cheap either. This also doesn't allow accessing per-AM
594 : * data (say a tuple's xmin), but since we don't do that here...
595 : */
596 2136980 : table_tuple_insert(myState->rel,
597 : slot,
598 : myState->output_cid,
599 : myState->ti_options,
600 : myState->bistate);
601 : }
602 :
603 : /* We know this is a newly created relation, so there are no indexes */
604 :
605 2136980 : return true;
606 : }
607 :
608 : /*
609 : * intorel_shutdown --- executor end
610 : */
611 : static void
612 1152 : intorel_shutdown(DestReceiver *self)
613 : {
614 1152 : DR_intorel *myState = (DR_intorel *) self;
615 1152 : IntoClause *into = myState->into;
616 :
617 1152 : if (!into->skipData)
618 : {
619 1116 : FreeBulkInsertState(myState->bistate);
620 1116 : table_finish_bulk_insert(myState->rel, myState->ti_options);
621 : }
622 :
623 : /* close rel, but keep lock until commit */
624 1152 : table_close(myState->rel, NoLock);
625 1152 : myState->rel = NULL;
626 1152 : }
627 :
628 : /*
629 : * intorel_destroy --- release DestReceiver object
630 : */
631 : static void
632 106 : intorel_destroy(DestReceiver *self)
633 : {
634 106 : pfree(self);
635 106 : }
|
