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 "miscadmin.h"
39 : #include "nodes/makefuncs.h"
40 : #include "nodes/nodeFuncs.h"
41 : #include "rewrite/rewriteHandler.h"
42 : #include "tcop/tcopprot.h"
43 : #include "utils/builtins.h"
44 : #include "utils/lsyscache.h"
45 : #include "utils/rel.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 1614 : create_ctas_internal(List *attrList, IntoClause *into)
81 : {
82 1614 : CreateStmt *create = makeNode(CreateStmt);
83 : bool is_matview;
84 : char relkind;
85 : Datum toast_options;
86 : static char *validnsps[] = HEAP_RELOPT_NAMESPACES;
87 : ObjectAddress intoRelationAddr;
88 :
89 : /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
90 1614 : is_matview = (into->viewQuery != NULL);
91 1614 : 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 1614 : create->relation = into->rel;
98 1614 : create->tableElts = attrList;
99 1614 : create->inhRelations = NIL;
100 1614 : create->ofTypename = NULL;
101 1614 : create->constraints = NIL;
102 1614 : create->options = into->options;
103 1614 : create->oncommit = into->onCommit;
104 1614 : create->tablespacename = into->tableSpaceName;
105 1614 : create->if_not_exists = false;
106 1614 : 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 1614 : 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 1596 : CommandCounterIncrement();
120 :
121 : /* parse and validate reloptions for the toast table */
122 1596 : toast_options = transformRelOptions((Datum) 0,
123 : create->options,
124 : "toast",
125 : validnsps,
126 : true, false);
127 :
128 1596 : (void) heap_reloptions(RELKIND_TOASTVALUE, toast_options, true);
129 :
130 1596 : NewRelationCreateToastTable(intoRelationAddr.objectId, toast_options);
131 :
132 : /* Create the "view" part of a materialized view. */
133 1596 : if (is_matview)
134 : {
135 : /* StoreViewQuery scribbles on tree, so make a copy */
136 444 : Query *query = (Query *) copyObject(into->viewQuery);
137 :
138 444 : StoreViewQuery(intoRelationAddr.objectId, query, false);
139 444 : CommandCounterIncrement();
140 : }
141 :
142 1596 : 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 472 : 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 472 : attrList = NIL;
165 472 : lc = list_head(into->colNames);
166 1512 : foreach(t, tlist)
167 : {
168 1040 : TargetEntry *tle = (TargetEntry *) lfirst(t);
169 :
170 1040 : if (!tle->resjunk)
171 : {
172 : ColumnDef *col;
173 : char *colname;
174 :
175 1040 : if (lc)
176 : {
177 124 : colname = strVal(lfirst(lc));
178 124 : lc = lnext(into->colNames, lc);
179 : }
180 : else
181 916 : colname = tle->resname;
182 :
183 1040 : col = makeColumnDef(colname,
184 1040 : exprType((Node *) tle->expr),
185 1040 : exprTypmod((Node *) tle->expr),
186 1040 : 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 1896 : if (!OidIsValid(col->collOid) &&
195 856 : 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 1040 : attrList = lappend(attrList, col);
204 : }
205 : }
206 :
207 472 : 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 454 : return create_ctas_internal(attrList, into);
214 : }
215 :
216 :
217 : /*
218 : * ExecCreateTableAs -- execute a CREATE TABLE AS command
219 : */
220 : ObjectAddress
221 1688 : ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
222 : ParamListInfo params, QueryEnvironment *queryEnv,
223 : QueryCompletion *qc)
224 : {
225 1688 : Query *query = castNode(Query, stmt->query);
226 1688 : IntoClause *into = stmt->into;
227 1688 : bool is_matview = (into->viewQuery != NULL);
228 1688 : bool do_refresh = false;
229 : DestReceiver *dest;
230 : ObjectAddress address;
231 : List *rewritten;
232 : PlannedStmt *plan;
233 : QueryDesc *queryDesc;
234 :
235 : /* Check if the relation exists or not */
236 1688 : if (CreateTableAsRelExists(stmt))
237 46 : return InvalidObjectAddress;
238 :
239 : /*
240 : * Create the tuple receiver object and insert info it will need
241 : */
242 1596 : dest = CreateIntoRelDestReceiver(into);
243 :
244 : /*
245 : * The contained Query could be a SELECT, or an EXECUTE utility command.
246 : * If the latter, we just pass it off to ExecuteQuery.
247 : */
248 1596 : if (query->commandType == CMD_UTILITY &&
249 42 : IsA(query->utilityStmt, ExecuteStmt))
250 : {
251 42 : ExecuteStmt *estmt = castNode(ExecuteStmt, query->utilityStmt);
252 :
253 : Assert(!is_matview); /* excluded by syntax */
254 42 : ExecuteQuery(pstate, estmt, into, params, dest, qc);
255 :
256 : /* get object address that intorel_startup saved for us */
257 42 : address = ((DR_intorel *) dest)->reladdr;
258 :
259 42 : return address;
260 : }
261 : Assert(query->commandType == CMD_SELECT);
262 :
263 : /*
264 : * For materialized views, always skip data during table creation, and use
265 : * REFRESH instead (see below).
266 : */
267 1554 : if (is_matview)
268 : {
269 444 : do_refresh = !into->skipData;
270 444 : into->skipData = true;
271 : }
272 :
273 1554 : if (into->skipData)
274 : {
275 : /*
276 : * If WITH NO DATA was specified, do not go through the rewriter,
277 : * planner and executor. Just define the relation using a code path
278 : * similar to CREATE VIEW. This avoids dump/restore problems stemming
279 : * from running the planner before all dependencies are set up.
280 : */
281 472 : address = create_ctas_nodata(query->targetList, into);
282 : }
283 : else
284 : {
285 : /*
286 : * Parse analysis was done already, but we still have to run the rule
287 : * rewriter. We do not do AcquireRewriteLocks: we assume the query
288 : * either came straight from the parser, or suitable locks were
289 : * acquired by plancache.c.
290 : */
291 1082 : rewritten = QueryRewrite(query);
292 :
293 : /* SELECT should never rewrite to more or less than one SELECT query */
294 1082 : if (list_length(rewritten) != 1)
295 0 : elog(ERROR, "unexpected rewrite result for %s",
296 : is_matview ? "CREATE MATERIALIZED VIEW" :
297 : "CREATE TABLE AS SELECT");
298 1082 : query = linitial_node(Query, rewritten);
299 : Assert(query->commandType == CMD_SELECT);
300 :
301 : /* plan the query */
302 1082 : plan = pg_plan_query(query, pstate->p_sourcetext,
303 : CURSOR_OPT_PARALLEL_OK, params);
304 :
305 : /*
306 : * Use a snapshot with an updated command ID to ensure this query sees
307 : * results of any previously executed queries. (This could only
308 : * matter if the planner executed an allegedly-stable function that
309 : * changed the database contents, but let's do it anyway to be
310 : * parallel to the EXPLAIN code path.)
311 : */
312 1082 : PushCopiedSnapshot(GetActiveSnapshot());
313 1082 : UpdateActiveSnapshotCommandId();
314 :
315 : /* Create a QueryDesc, redirecting output to our tuple receiver */
316 1082 : queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
317 : GetActiveSnapshot(), InvalidSnapshot,
318 : dest, params, queryEnv, 0);
319 :
320 : /* call ExecutorStart to prepare the plan for execution */
321 1082 : ExecutorStart(queryDesc, GetIntoRelEFlags(into));
322 :
323 : /* run the plan to completion */
324 1082 : ExecutorRun(queryDesc, ForwardScanDirection, 0, true);
325 :
326 : /* save the rowcount if we're given a qc to fill */
327 1046 : if (qc)
328 1038 : SetQueryCompletion(qc, CMDTAG_SELECT, queryDesc->estate->es_processed);
329 :
330 : /* get object address that intorel_startup saved for us */
331 1046 : address = ((DR_intorel *) dest)->reladdr;
332 :
333 : /* and clean up */
334 1046 : ExecutorFinish(queryDesc);
335 1046 : ExecutorEnd(queryDesc);
336 :
337 1046 : FreeQueryDesc(queryDesc);
338 :
339 1046 : PopActiveSnapshot();
340 : }
341 :
342 : /*
343 : * For materialized views, reuse the REFRESH logic, which locks down
344 : * security-restricted operations and restricts the search_path. This
345 : * reduces the chance that a subsequent refresh will fail.
346 : */
347 1500 : if (do_refresh)
348 : {
349 356 : RefreshMatViewByOid(address.objectId, false, false,
350 : pstate->p_sourcetext, NULL, qc);
351 :
352 350 : if (qc)
353 338 : qc->commandTag = CMDTAG_SELECT;
354 : }
355 :
356 1494 : return address;
357 : }
358 :
359 : /*
360 : * GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
361 : *
362 : * This is exported because EXPLAIN and PREPARE need it too. (Note: those
363 : * callers still need to deal explicitly with the skipData flag; since they
364 : * use different methods for suppressing execution, it doesn't seem worth
365 : * trying to encapsulate that part.)
366 : */
367 : int
368 1220 : GetIntoRelEFlags(IntoClause *intoClause)
369 : {
370 1220 : int flags = 0;
371 :
372 1220 : if (intoClause->skipData)
373 42 : flags |= EXEC_FLAG_WITH_NO_DATA;
374 :
375 1220 : return flags;
376 : }
377 :
378 : /*
379 : * CreateTableAsRelExists --- check existence of relation for CreateTableAsStmt
380 : *
381 : * Utility wrapper checking if the relation pending for creation in this
382 : * CreateTableAsStmt query already exists or not. Returns true if the
383 : * relation exists, otherwise false.
384 : */
385 : bool
386 1844 : CreateTableAsRelExists(CreateTableAsStmt *ctas)
387 : {
388 : Oid nspid;
389 : Oid oldrelid;
390 : ObjectAddress address;
391 1844 : IntoClause *into = ctas->into;
392 :
393 1844 : nspid = RangeVarGetCreationNamespace(into->rel);
394 :
395 1844 : oldrelid = get_relname_relid(into->rel->relname, nspid);
396 1844 : if (OidIsValid(oldrelid))
397 : {
398 152 : if (!ctas->if_not_exists)
399 72 : ereport(ERROR,
400 : (errcode(ERRCODE_DUPLICATE_TABLE),
401 : errmsg("relation \"%s\" already exists",
402 : into->rel->relname)));
403 :
404 : /*
405 : * The relation exists and IF NOT EXISTS has been specified.
406 : *
407 : * If we are in an extension script, insist that the pre-existing
408 : * object be a member of the extension, to avoid security risks.
409 : */
410 80 : ObjectAddressSet(address, RelationRelationId, oldrelid);
411 80 : checkMembershipInCurrentExtension(&address);
412 :
413 : /* OK to skip */
414 76 : ereport(NOTICE,
415 : (errcode(ERRCODE_DUPLICATE_TABLE),
416 : errmsg("relation \"%s\" already exists, skipping",
417 : into->rel->relname)));
418 76 : return true;
419 : }
420 :
421 : /* Relation does not exist, it can be created */
422 1692 : return false;
423 : }
424 :
425 : /*
426 : * CreateIntoRelDestReceiver -- create a suitable DestReceiver object
427 : *
428 : * intoClause will be NULL if called from CreateDestReceiver(), in which
429 : * case it has to be provided later. However, it is convenient to allow
430 : * self->into to be filled in immediately for other callers.
431 : */
432 : DestReceiver *
433 1692 : CreateIntoRelDestReceiver(IntoClause *intoClause)
434 : {
435 1692 : DR_intorel *self = (DR_intorel *) palloc0(sizeof(DR_intorel));
436 :
437 1692 : self->pub.receiveSlot = intorel_receive;
438 1692 : self->pub.rStartup = intorel_startup;
439 1692 : self->pub.rShutdown = intorel_shutdown;
440 1692 : self->pub.rDestroy = intorel_destroy;
441 1692 : self->pub.mydest = DestIntoRel;
442 1692 : self->into = intoClause;
443 : /* other private fields will be set during intorel_startup */
444 :
445 1692 : return (DestReceiver *) self;
446 : }
447 :
448 : /*
449 : * intorel_startup --- executor startup
450 : */
451 : static void
452 1178 : intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
453 : {
454 1178 : DR_intorel *myState = (DR_intorel *) self;
455 1178 : IntoClause *into = myState->into;
456 : bool is_matview;
457 : List *attrList;
458 : ObjectAddress intoRelationAddr;
459 : Relation intoRelationDesc;
460 : ListCell *lc;
461 : int attnum;
462 :
463 : Assert(into != NULL); /* else somebody forgot to set it */
464 :
465 : /* This code supports both CREATE TABLE AS and CREATE MATERIALIZED VIEW */
466 1178 : is_matview = (into->viewQuery != NULL);
467 :
468 : /*
469 : * Build column definitions using "pre-cooked" type and collation info. If
470 : * a column name list was specified in CREATE TABLE AS, override the
471 : * column names derived from the query. (Too few column names are OK, too
472 : * many are not.)
473 : */
474 1178 : attrList = NIL;
475 1178 : lc = list_head(into->colNames);
476 4796 : for (attnum = 0; attnum < typeinfo->natts; attnum++)
477 : {
478 3630 : Form_pg_attribute attribute = TupleDescAttr(typeinfo, attnum);
479 : ColumnDef *col;
480 : char *colname;
481 :
482 3630 : if (lc)
483 : {
484 216 : colname = strVal(lfirst(lc));
485 216 : lc = lnext(into->colNames, lc);
486 : }
487 : else
488 3414 : colname = NameStr(attribute->attname);
489 :
490 3630 : col = makeColumnDef(colname,
491 : attribute->atttypid,
492 : attribute->atttypmod,
493 : attribute->attcollation);
494 :
495 : /*
496 : * It's possible that the column is of a collatable type but the
497 : * collation could not be resolved, so double-check. (We must check
498 : * this here because DefineRelation would adopt the type's default
499 : * collation rather than complaining.)
500 : */
501 6740 : if (!OidIsValid(col->collOid) &&
502 3110 : type_is_collatable(col->typeName->typeOid))
503 12 : ereport(ERROR,
504 : (errcode(ERRCODE_INDETERMINATE_COLLATION),
505 : errmsg("no collation was derived for column \"%s\" with collatable type %s",
506 : col->colname,
507 : format_type_be(col->typeName->typeOid)),
508 : errhint("Use the COLLATE clause to set the collation explicitly.")));
509 :
510 3618 : attrList = lappend(attrList, col);
511 : }
512 :
513 1166 : if (lc != NULL)
514 6 : ereport(ERROR,
515 : (errcode(ERRCODE_SYNTAX_ERROR),
516 : errmsg("too many column names were specified")));
517 :
518 : /*
519 : * Actually create the target table
520 : */
521 1160 : intoRelationAddr = create_ctas_internal(attrList, into);
522 :
523 : /*
524 : * Finally we can open the target table
525 : */
526 1142 : intoRelationDesc = table_open(intoRelationAddr.objectId, AccessExclusiveLock);
527 :
528 : /*
529 : * Make sure the constructed table does not have RLS enabled.
530 : *
531 : * check_enable_rls() will ereport(ERROR) itself if the user has requested
532 : * something invalid, and otherwise will return RLS_ENABLED if RLS should
533 : * be enabled here. We don't actually support that currently, so throw
534 : * our own ereport(ERROR) if that happens.
535 : */
536 1142 : if (check_enable_rls(intoRelationAddr.objectId, InvalidOid, false) == RLS_ENABLED)
537 0 : ereport(ERROR,
538 : (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
539 : errmsg("policies not yet implemented for this command")));
540 :
541 : /*
542 : * Tentatively mark the target as populated, if it's a matview and we're
543 : * going to fill it; otherwise, no change needed.
544 : */
545 1142 : if (is_matview && !into->skipData)
546 6 : SetMatViewPopulatedState(intoRelationDesc, true);
547 :
548 : /*
549 : * Fill private fields of myState for use by later routines
550 : */
551 1142 : myState->rel = intoRelationDesc;
552 1142 : myState->reladdr = intoRelationAddr;
553 1142 : myState->output_cid = GetCurrentCommandId(true);
554 1142 : myState->ti_options = TABLE_INSERT_SKIP_FSM;
555 :
556 : /*
557 : * If WITH NO DATA is specified, there is no need to set up the state for
558 : * bulk inserts as there are no tuples to insert.
559 : */
560 1142 : if (!into->skipData)
561 1106 : myState->bistate = GetBulkInsertState();
562 : else
563 36 : myState->bistate = NULL;
564 :
565 : /*
566 : * Valid smgr_targblock implies something already wrote to the relation.
567 : * This may be harmless, but this function hasn't planned for it.
568 : */
569 : Assert(RelationGetTargetBlock(intoRelationDesc) == InvalidBlockNumber);
570 1142 : }
571 :
572 : /*
573 : * intorel_receive --- receive one tuple
574 : */
575 : static bool
576 2120522 : intorel_receive(TupleTableSlot *slot, DestReceiver *self)
577 : {
578 2120522 : DR_intorel *myState = (DR_intorel *) self;
579 :
580 : /* Nothing to insert if WITH NO DATA is specified. */
581 2120522 : if (!myState->into->skipData)
582 : {
583 : /*
584 : * Note that the input slot might not be of the type of the target
585 : * relation. That's supported by table_tuple_insert(), but slightly
586 : * less efficient than inserting with the right slot - but the
587 : * alternative would be to copy into a slot of the right type, which
588 : * would not be cheap either. This also doesn't allow accessing per-AM
589 : * data (say a tuple's xmin), but since we don't do that here...
590 : */
591 2120522 : table_tuple_insert(myState->rel,
592 : slot,
593 : myState->output_cid,
594 : myState->ti_options,
595 : myState->bistate);
596 : }
597 :
598 : /* We know this is a newly created relation, so there are no indexes */
599 :
600 2120522 : return true;
601 : }
602 :
603 : /*
604 : * intorel_shutdown --- executor end
605 : */
606 : static void
607 1142 : intorel_shutdown(DestReceiver *self)
608 : {
609 1142 : DR_intorel *myState = (DR_intorel *) self;
610 1142 : IntoClause *into = myState->into;
611 :
612 1142 : if (!into->skipData)
613 : {
614 1106 : FreeBulkInsertState(myState->bistate);
615 1106 : table_finish_bulk_insert(myState->rel, myState->ti_options);
616 : }
617 :
618 : /* close rel, but keep lock until commit */
619 1142 : table_close(myState->rel, NoLock);
620 1142 : myState->rel = NULL;
621 1142 : }
622 :
623 : /*
624 : * intorel_destroy --- release DestReceiver object
625 : */
626 : static void
627 96 : intorel_destroy(DestReceiver *self)
628 : {
629 96 : pfree(self);
630 96 : }
|
