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