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