Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 599D11DCB2B2 for ; Mon, 27 Feb 2012 19:32:52 -0400 (AST) Received: from mail.anarazel.de ([217.115.131.40]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S2A3i-0003wW-2c for pgsql-hackers@postgresql.org; Mon, 27 Feb 2012 23:32:51 +0000 Received: by mail.anarazel.de (Postfix, from userid 108) id C48A4450005; Tue, 28 Feb 2012 00:32:33 +0100 (CET) X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on mail X-Spam-Level: X-Spam-Status: No, score=-4.2 required=5.0 tests=ALL_TRUSTED,AWL,BAYES_00 autolearn=ham version=3.2.5 Received: from anarazel.de (brln-4dbaa8c0.pool.mediaWays.net [77.186.168.192]) (Authenticated sender: andres@anarazel.de) by mail.anarazel.de (Postfix) with ESMTPSA id 31ED0448004; Tue, 28 Feb 2012 00:32:28 +0100 (CET) Received: by anarazel.de (Postfix, from userid 1000) id 8CB881C0092; Tue, 28 Feb 2012 00:32:27 +0100 (CET) From: Andres Freund To: pgsql-hackers@postgresql.org, Tom Lane Subject: Re: Command Triggers, patch v11 Date: Tue, 28 Feb 2012 00:32:26 +0100 User-Agent: KMail/1.13.7 (Linux/3.3.0-rc3-andres-00278-g4903062; KDE/4.6.5; x86_64; ; ) Cc: Thom Brown , Dimitri Fontaine References: <201202272037.25322.andres@anarazel.de> In-Reply-To: <201202272037.25322.andres@anarazel.de> MIME-Version: 1.0 Content-Type: Multipart/Mixed; boundary="Boundary-00=_LKBTPb2u7VrQ8wm" Message-Id: <201202280032.27171.andres@anarazel.de> X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201202/1355 X-Sequence-Number: 203719 --Boundary-00=_LKBTPb2u7VrQ8wm Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit On Monday, February 27, 2012 08:37:24 PM Andres Freund wrote: > On Monday, February 27, 2012 08:30:31 PM Thom Brown wrote: > > On 27 February 2012 19:19, Dimitri Fontaine wrote: > > > Thom Brown writes: > > >> test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b; > > >> SELECT 1 > > >> > > >> This doesn't even get picked up by ANY COMMAND. > > > > > > You won't believe it: CTAS is not implemented as a DDL. Andres did > > > some work about that and sent a patch that received positive reviews by > > > both Tom and Robert, once that's in I can easily add support for the > > > command. > > I actually don't think anybody actually reviewed the patch so far. Tom and > I discussed the implementation strategy beforehand a bit though. > > > > Thanks Andres :) > > Youre welcome. Thanks for your awesome work that actually made it necessary > ;) > > > I don't see it anywhere in the commitfest. Has it been properly > > submitted? > > I actually always viewed it as a part of the Dim's patch which is why I > didn't submit it as a separate patch. Maybe that was a mistake... > > http://archives.postgresql.org/message- > id/201112112346.07611.andres@anarazel.de contains the latest revision. > I refreshed the patch so it works again on current HEAD. Basically some trivial fixes and dfd26f9c5f371437f243249025863ea9911aacaa. The latter doesn't seem necessary to me after the changes, so I simply ditched it. Am I missing something? I noticed no new things I dislike about the patch besides what I voiced last time round: > I attached the - from my side - final version of the patch. I dislike two > things about it: > * code duplication due to error handling. Before making the error message > for various illegal SELECT INTOs the patch actually shrank the code size... > If anybody has a good idea to avoid duplicating that loop around SelectStmt- ops I would be happy. > * new executor flags to define whether oids should be returned It would be great if somebody could take a look. Andres --Boundary-00=_LKBTPb2u7VrQ8wm Content-Type: text/x-patch; charset="UTF-8"; name="0001-Transform-CREATE-TABLE-AS-SELECT-INTO-into-a-utility.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="0001-Transform-CREATE-TABLE-AS-SELECT-INTO-into-a-utility.patch" =46rom 10a3e5c26c4196cdb8ce4ba588c19872d2f1b246 Mon Sep 17 00:00:00 2001 =46rom: Andres Freund Date: Tue, 28 Feb 2012 00:19:50 +0100 Subject: [PATCH] Transform CREATE TABLE AS/SELECT INTO into a utility statement This basically includes a revert of d8fb1f9adbddd1eef123d66a89a9fc0ecd96f60b which doesn't seem to be necessary anymore now CTAS is a proper utility statement in any incarnation. =2D-- src/backend/commands/copy.c | 20 +- src/backend/commands/prepare.c | 69 +---- src/backend/commands/tablecmds.c | 335 +++++++++++++++++++++ src/backend/commands/view.c | 20 +- src/backend/executor/execMain.c | 441 ++----------------------= =2D--- src/backend/executor/execUtils.c | 2 - src/backend/executor/functions.c | 4 +- src/backend/executor/spi.c | 10 +- src/backend/nodes/copyfuncs.c | 18 +- src/backend/nodes/equalfuncs.c | 16 +- src/backend/nodes/outfuncs.c | 4 +- src/backend/nodes/readfuncs.c | 1 - src/backend/optimizer/plan/planner.c | 1 - src/backend/optimizer/plan/subselect.c | 1 - src/backend/optimizer/prep/prepjointree.c | 6 +- src/backend/optimizer/util/clauses.c | 4 +- src/backend/parser/analyze.c | 113 +++++--- src/backend/parser/gram.y | 28 ++- src/backend/parser/parse_clause.c | 21 +- src/backend/parser/parse_cte.c | 21 +- src/backend/parser/parse_expr.c | 21 +- src/backend/parser/parser.c | 38 +++ src/backend/rewrite/rewriteDefine.c | 3 +- src/backend/tcop/pquery.c | 13 +- src/backend/tcop/utility.c | 46 ++-- src/include/access/htup.h | 2 +- src/include/commands/prepare.h | 3 + src/include/commands/tablecmds.h | 4 + src/include/executor/executor.h | 5 + src/include/nodes/execnodes.h | 9 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 20 +- src/include/nodes/plannodes.h | 2 - src/pl/plpgsql/src/pl_exec.c | 27 +-- src/test/regress/expected/select_into.out | 19 ++ src/test/regress/expected/transactions.out | 2 +- src/test/regress/sql/select_into.sql | 15 + 37 files changed, 720 insertions(+), 645 deletions(-) diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 110480f..a3c5b4b 100644 =2D-- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -1190,6 +1190,20 @@ BeginCopy(bool is_from, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("COPY (SELECT) WITH OIDS is not supported"))); =20 + + /* Query mustn't use INTO, either */ + if(IsA(raw_query, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)raw_query; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("COPY (SELECT INTO) is not supported"))); + } + /* * Run parse analysis and rewrite. Note this also acquires sufficient * locks on the source table(s). @@ -1211,12 +1225,6 @@ BeginCopy(bool is_from, Assert(query->commandType =3D=3D CMD_SELECT); Assert(query->utilityStmt =3D=3D NULL); =20 =2D /* Query mustn't use INTO, either */ =2D if (query->intoClause) =2D ereport(ERROR, =2D (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), =2D errmsg("COPY (SELECT INTO) is not supported"))); =2D /* plan the query */ plan =3D planner(query, 0, NULL); =20 diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index 4883abe..86e8cf9 100644 =2D-- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -43,8 +43,6 @@ static HTAB *prepared_queries =3D NULL; =20 static void InitQueryHashTable(void); =2Dstatic ParamListInfo EvaluateParams(PreparedStatement *pstmt, List *para= ms, =2D const char *queryString, EState *estate); static Datum build_regtype_array(Oid *param_types, int num_params); =20 /* @@ -177,6 +175,9 @@ PrepareQuery(PrepareStmt *stmt, const char *queryString) * EXECUTE, which we might need for error reporting while processing the * parameter expressions. The query_string that we copy from the plan * source is that of the original PREPARE. + * + * If you add additional things here you should check whether + * CreateTableAs also needs them. */ void ExecuteQuery(ExecuteStmt *stmt, const char *queryString, @@ -222,51 +223,9 @@ ExecuteQuery(ExecuteStmt *stmt, const char *queryStrin= g, query_string =3D MemoryContextStrdup(PortalGetHeapMemory(portal), entry->plansource->query_string); =20 =2D /* =2D * For CREATE TABLE / AS EXECUTE, we must make a copy of the stored que= ry =2D * so that we can modify its destination (yech, but this has always been =2D * ugly). For regular EXECUTE we can just use the cached query, since = the =2D * executor is read-only. =2D */ =2D if (stmt->into) =2D { =2D MemoryContext oldContext; =2D PlannedStmt *pstmt; =2D =2D /* Replan if needed, and increment plan refcount transiently */ =2D cplan =3D GetCachedPlan(entry->plansource, paramLI, true); =2D =2D /* Copy plan into portal's context, and modify */ =2D oldContext =3D MemoryContextSwitchTo(PortalGetHeapMemory(portal)); =2D =2D plan_list =3D copyObject(cplan->stmt_list); =2D =2D if (list_length(plan_list) !=3D 1) =2D ereport(ERROR, =2D (errcode(ERRCODE_WRONG_OBJECT_TYPE), =2D errmsg("prepared statement is not a SELECT"))); =2D pstmt =3D (PlannedStmt *) linitial(plan_list); =2D if (!IsA(pstmt, PlannedStmt) || =2D pstmt->commandType !=3D CMD_SELECT || =2D pstmt->utilityStmt !=3D NULL) =2D ereport(ERROR, =2D (errcode(ERRCODE_WRONG_OBJECT_TYPE), =2D errmsg("prepared statement is not a SELECT"))); =2D pstmt->intoClause =3D copyObject(stmt->into); =2D =2D MemoryContextSwitchTo(oldContext); =2D =2D /* We no longer need the cached plan refcount ... */ =2D ReleaseCachedPlan(cplan, true); =2D /* ... and we don't want the portal to depend on it, either */ =2D cplan =3D NULL; =2D } =2D else =2D { =2D /* Replan if needed, and increment plan refcount for portal */ =2D cplan =3D GetCachedPlan(entry->plansource, paramLI, false); =2D plan_list =3D cplan->stmt_list; =2D } + /* Replan if needed, and increment plan refcount for portal */ + cplan =3D GetCachedPlan(entry->plansource, paramLI, false); + plan_list =3D cplan->stmt_list; =20 PortalDefineQuery(portal, NULL, @@ -302,7 +261,7 @@ ExecuteQuery(ExecuteStmt *stmt, const char *queryString, * CreateQueryDesc(), which allows the executor to make use of the paramet= ers * during query execution. */ =2Dstatic ParamListInfo +ParamListInfo EvaluateParams(PreparedStatement *pstmt, List *params, const char *queryString, EState *estate) { @@ -666,20 +625,6 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, ExplainStat= e *es, =20 if (IsA(pstmt, PlannedStmt)) { =2D if (execstmt->into) =2D { =2D if (pstmt->commandType !=3D CMD_SELECT || =2D pstmt->utilityStmt !=3D NULL) =2D ereport(ERROR, =2D (errcode(ERRCODE_WRONG_OBJECT_TYPE), =2D errmsg("prepared statement is not a SELECT"))); =2D =2D /* Copy the stmt so we can modify it */ =2D pstmt =3D copyObject(pstmt); =2D =2D pstmt->intoClause =3D execstmt->into; =2D } =2D ExplainOnePlan(pstmt, es, query_string, paramLI); } else diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec= mds.c index cd4490a..9a67cf5 100644 =2D-- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -44,6 +44,7 @@ #include "commands/cluster.h" #include "commands/comment.h" #include "commands/defrem.h" +#include "commands/prepare.h" #include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" @@ -68,6 +69,7 @@ #include "parser/parser.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteHandler.h" +#include "tcop/tcopprot.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/lock.h" @@ -195,6 +197,18 @@ struct dropmsgstrings const char *drophint_msg; }; =20 +/* + * Support structure for CTAS + */ +typedef struct +{ + DestReceiver pub; /* publicly-known function pointers */ + EState *estate; /* EState we are working with */ + Relation rel; /* Relation to write to */ + int hi_options; /* heap_insert performance options */ + BulkInsertState bistate; /* bulk insert state */ +} DR_intorel; + static const struct dropmsgstrings dropmsgstringarray[] =3D { {RELKIND_RELATION, ERRCODE_UNDEFINED_TABLE, @@ -671,6 +685,235 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ow= nerId) return relationId; } =20 +void +CreateTableAs(CreateTableAsStmt *stmt, const char *queryString, + ParamListInfo params, DestReceiver *dest){ + IntoClause *into =3D stmt->into; + Query *query; + List *rewritten; + PlannedStmt *plan; + QueryDesc *queryDesc; + Relation intoRelationDesc; + CreateStmt *create; + int natt; + Oid intoRelationId; + ListCell *lc; + int eflags =3D 0; + EState *param_estate =3D NULL; + CachedPlan *cplan =3D NULL; + + Assert(IsA(stmt->query, Query)); + query =3D (Query*)stmt->query; + + /* + * Construct plans for the supported + */ + if(query->commandType =3D=3D CMD_SELECT){ + rewritten =3D QueryRewrite((Query *) copyObject(stmt->query)); + + if(!rewritten) + elog(ERROR, "CREATE TABLE AS/SELECT INTO query rewrote to nothing"); + + if(list_length(rewritten) !=3D 1) + elog(ERROR, "CREATE TABLE AS/SELECT INTO query rewrote to more than one= query"); + + plan =3D pg_plan_query(lfirst(list_head(rewritten)), 0, params); + } + else if(query->commandType =3D=3D CMD_UTILITY && + IsA(query->utilityStmt, ExecuteStmt)){ + /* + * We cannot easily share more infrastructure with prepare.c's + * ExecuteQuery because teaching it to return a started + * executor would amount to about the same amount of + * duplication as doing it here. + * + */ + ExecuteStmt *stmt; + PreparedStatement *entry; + List *plan_list; + ParamListInfo paramLI =3D NULL; + + Assert(IsA(query->utilityStmt, ExecuteStmt)); + stmt =3D (ExecuteStmt*)(query->utilityStmt); + + entry =3D FetchPreparedStatement(stmt->name, true); + + /* Shouldn't find a non-fixed-result cached plan */ + if (!entry->plansource->fixed_result) + elog(ERROR, "EXECUTE does not support variable-result cached plans"); + + /* Evaluate parameters, if any */ + if (entry->plansource->num_params > 0) + { + param_estate =3D CreateExecutorState(); + param_estate->es_param_list_info =3D params; + paramLI =3D EvaluateParams(entry, stmt->params, + queryString, param_estate); + + } + + cplan =3D GetCachedPlan(entry->plansource, paramLI, true); + plan_list =3D cplan->stmt_list; + + if (list_length(plan_list) !=3D 1) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("prepared statement is not a SELECT"))); + + plan =3D (PlannedStmt *) linitial(plan_list); + if (!IsA(plan, PlannedStmt) || + plan->commandType !=3D CMD_SELECT || + plan->utilityStmt !=3D NULL) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("prepared statement is not a SELECT"))); + + queryString =3D pstrdup(entry->plansource->query_string); + } + else{ + elog(ERROR, "unsupported command for CREATE TABLE AS"); + abort();//silence compiler + } + + + /* + * Use a snapshot with an updated command ID to ensure this query sees + * results of any previously executed queries. + */ + PushCopiedSnapshot(GetActiveSnapshot()); + UpdateActiveSnapshotCommandId(); + + queryDesc =3D CreateQueryDesc(plan, queryString, + GetActiveSnapshot(), InvalidSnapshot, + CreateDestReceiver(DestIntoRel), params, + false); + + /* + * We need to tell the executor whether it has to produce oids or + * not because it doesn't have enough information to do so itself + * as were not telling it that its inserting into a table (because + * that would be too complicated for now) + */ + if(interpretOidsOption(into->options)) + eflags |=3D EXEC_FLAG_FORCE_OIDS_ON; + else + eflags |=3D EXEC_FLAG_FORCE_OIDS_OFF; + + /* + * call ExecutorStart to prepare the plan for execution. Only + * after this we have enough information to actually create a + * target relation + */ + ExecutorStart(queryDesc, eflags); + + /* + * create the target relation + */ + create =3D makeNode(CreateStmt); + create->relation =3D into->rel; + create->options =3D into->options; + create->oncommit =3D into->onCommit; + create->tablespacename =3D into->tableSpaceName; + + /* + * If a column name list was specified in CREATE TABLE AS, + * override the column names derived from the query. (Too few + * column names are OK, too many are not.) + */ + if (list_length(into->colNames) > queryDesc->tupDesc->natts) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("CREATE TABLE AS specifies too many column names"))); + lc =3D list_head(into->colNames); + for(natt =3D 0; natt < queryDesc->tupDesc->natts; natt++){ + ColumnDef *col =3D makeNode(ColumnDef); + TypeName *type =3D makeNode(TypeName); + + Form_pg_attribute attribute =3D queryDesc->tupDesc->attrs[natt]; + + if(!lc) + col->colname =3D NameStr(attribute->attname); + else{ + col->colname =3D strVal(lfirst(lc)); + lc =3D lnext(lc); + } + + col->collOid =3D attribute->attcollation; + + type->typeOid =3D attribute->atttypid; + type->typemod =3D attribute->atttypmod; + col->typeName=3D type; + + /* + * We check the column type here because collOid=3DInvalidOid + * denotes an invalid oid for a collatable type in + * queryDesc->tupDesc but GetColumnDefCollation - which will + * be called by DefineRelation - defers the default collation + * out of that... + * XXX: A better solution would be welcome. + */ + CheckAttributeType(col->colname, col->typeName->typeOid, col->collOid, + NULL, false); + create->tableElts =3D lappend(create->tableElts, col); + } + + /* + * Actually create the target table + */ + intoRelationId =3D DefineRelation(create, RELKIND_RELATION, InvalidOid); + intoRelationDesc =3D heap_open(intoRelationId, AccessExclusiveLock); + + + if(!into->skipData){ + DR_intorel *myState =3D (DR_intorel *) queryDesc->dest; + /* setup the target of the DestIntoRel receiver */ + myState->estate =3D queryDesc->estate; + myState->rel =3D intoRelationDesc; + + /* run the plan */ + ExecutorRun(queryDesc, ForwardScanDirection, 0L); + + } + + /* + * Check INSERT permission on the constructed table. + * + * XXX: It would arguable make sense to do this check only if + * into->skipData is true. + */ + { + RangeTblEntry *rte =3D makeNode(RangeTblEntry); + rte->rtekind =3D RTE_RELATION; + rte->relid =3D intoRelationId; + rte->relkind =3D RELKIND_RELATION; + rte->requiredPerms =3D ACL_INSERT; + + for (natt =3D 1; natt <=3D intoRelationDesc->rd_att->natts; natt++) + rte->modifiedCols =3D bms_add_member(rte->modifiedCols, + natt - FirstLowInvalidHeapAttributeN= umber); + + ExecCheckRTPerms(list_make1(rte), true); + } + + /* run cleanup too */ + ExecutorFinish(queryDesc); + ExecutorEnd(queryDesc); + + /* close rel, but keep lock until commit */ + heap_close(intoRelationDesc, NoLock); + + FreeQueryDesc(queryDesc); + + if(cplan){ + ReleaseCachedPlan(cplan, true); + + if (param_estate) + FreeExecutorState(param_estate); + } + + PopActiveSnapshot(); +} + /* * Emit the right error or warning message for a "DROP" command issued on a * non-existent relation @@ -10238,3 +10481,95 @@ RangeVarCallbackForAlterRelation(const RangeVar *r= v, Oid relid, Oid oldrelid, =20 ReleaseSysCache(tuple); } + +/* + * Support for SELECT INTO (aka CREATE TABLE AS) + * + * We implement SELECT INTO by diverting SELECT's normal output with + * a specialized DestReceiver type. + */ + +/* + * intorel_startup --- executor startup + */ +static void +intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) +{ + DR_intorel *myState =3D (DR_intorel *) self; + myState->bistate =3D GetBulkInsertState(); + myState->hi_options =3D HEAP_INSERT_SKIP_FSM; + if (!XLogIsNeeded()) + myState->hi_options |=3D HEAP_INSERT_SKIP_WAL; + +} + +/* + * intorel_receive --- receive one tuple + */ +static void +intorel_receive(TupleTableSlot *slot, DestReceiver *self) +{ + DR_intorel *myState =3D (DR_intorel *) self; + HeapTuple tuple; + + /* + * get the heap tuple out of the tuple table slot, making sure we have a + * writable copy + */ + tuple =3D ExecMaterializeSlot(slot); + + /* + * force assignment of new OID (see comments in ExecInsert) + */ + if (myState->rel->rd_rel->relhasoids) + HeapTupleSetOid(tuple, InvalidOid); + + heap_insert(myState->rel, + tuple, + myState->estate->es_output_cid, + myState->hi_options, + myState->bistate); + + /* We know this is a newly created relation, so there are no indexes */ +} + +/* + * intorel_shutdown --- executor end + */ +static void +intorel_shutdown(DestReceiver *self) +{ + DR_intorel *myState =3D (DR_intorel *) self; + + FreeBulkInsertState(myState->bistate); + if (myState->hi_options & HEAP_INSERT_SKIP_WAL) + heap_sync(myState->rel); +} + +/* + * intorel_destroy --- release DestReceiver object + */ +static void +intorel_destroy(DestReceiver *self) +{ + pfree(self); +} + +/* + * CreateIntoRelDestReceiver -- create a suitable DestReceiver object + * + */ +DestReceiver * +CreateIntoRelDestReceiver(void) +{ + DR_intorel *self =3D (DR_intorel *) palloc0(sizeof(DR_intorel)); + + self->pub.receiveSlot =3D intorel_receive; + self->pub.rStartup =3D intorel_startup; + self->pub.rShutdown =3D intorel_shutdown; + self->pub.rDestroy =3D intorel_destroy; + self->pub.mydest =3D DestIntoRel; + /* private fields will be set on startup */ + + return (DestReceiver *) self; +} diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 99fb7db..93e523a 100644 =2D-- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -428,6 +428,22 @@ DefineView(ViewStmt *stmt, const char *queryString) RangeVar *view; =20 /* + * We check for SELECT INTO before parse analysis because that + * would complain about the SELECT INTO without specific enough detail. + */ + if(IsA(stmt->query, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)stmt->query; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("views must not contain SELECT INTO"))); + } + + /* * Run parse analysis to convert the raw parse tree to a Query. Note this * also acquires sufficient locks on the source table(s). * @@ -449,10 +465,6 @@ DefineView(ViewStmt *stmt, const char *queryString) * DefineQueryRewrite(), but that function will complain about a bogus ON * SELECT rule, and we'd rather the message complain about a view. */ =2D if (viewParse->intoClause !=3D NULL) =2D ereport(ERROR, =2D (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), =2D errmsg("views must not contain SELECT INTO"))); if (viewParse->hasModifyingCTE) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMai= n.c index 36dcc8e..1979325 100644 =2D-- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -90,12 +90,6 @@ static char *ExecBuildSlotValueDescription(TupleTableSlo= t *slot, int maxfieldlen); static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate, Plan *planTree); =2Dstatic void OpenIntoRel(QueryDesc *queryDesc); =2Dstatic void CloseIntoRel(QueryDesc *queryDesc); =2Dstatic void intorel_startup(DestReceiver *self, int operation, TupleDesc= typeinfo); =2Dstatic void intorel_receive(TupleTableSlot *slot, DestReceiver *self); =2Dstatic void intorel_shutdown(DestReceiver *self); =2Dstatic void intorel_destroy(DestReceiver *self); =20 /* end of local decls */ =20 @@ -174,11 +168,10 @@ standard_ExecutorStart(QueryDesc *queryDesc, int efla= gs) case CMD_SELECT: =20 /* =2D * SELECT INTO, SELECT FOR UPDATE/SHARE and modifying CTEs need to + * SELECT FOR UPDATE/SHARE and modifying CTEs need to * mark tuples */ =2D if (queryDesc->plannedstmt->intoClause !=3D NULL || =2D queryDesc->plannedstmt->rowMarks !=3D NIL || + if (queryDesc->plannedstmt->rowMarks !=3D NIL || queryDesc->plannedstmt->hasModifyingCTE) estate->es_output_cid =3D GetCurrentCommandId(true); =20 @@ -212,6 +205,13 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflag= s) estate->es_top_eflags =3D eflags; estate->es_instrument =3D queryDesc->instrument_options; =20 + if(eflags & EXEC_FLAG_FORCE_OIDS_ON) + estate->es_force_oids =3D ESTATE_FORCE_OIDS_ON; + else if(eflags & EXEC_FLAG_FORCE_OIDS_OFF) + estate->es_force_oids =3D ESTATE_FORCE_OIDS_OFF; + else + estate->es_force_oids =3D ESTATE_FORCE_OIDS_DEFAULT; + /* * Initialize the plan state tree */ @@ -310,13 +310,6 @@ standard_ExecutorRun(QueryDesc *queryDesc, (*dest->rStartup) (dest, operation, queryDesc->tupDesc); =20 /* =2D * if it's CREATE TABLE AS ... WITH NO DATA, skip plan execution =2D */ =2D if (estate->es_select_into && =2D queryDesc->plannedstmt->intoClause->skipData) =2D direction =3D NoMovementScanDirection; =2D =2D /* * run plan */ if (!ScanDirectionIsNoMovement(direction)) @@ -451,12 +444,6 @@ standard_ExecutorEnd(QueryDesc *queryDesc) =20 ExecEndPlan(queryDesc->planstate, estate); =20 =2D /* =2D * Close the SELECT INTO relation if any =2D */ =2D if (estate->es_select_into) =2D CloseIntoRel(queryDesc); =2D /* do away with our snapshots */ UnregisterSnapshot(estate->es_snapshot); UnregisterSnapshot(estate->es_crosscheck_snapshot); @@ -706,15 +693,6 @@ ExecCheckXactReadOnly(PlannedStmt *plannedstmt) { ListCell *l; =20 =2D /* =2D * CREATE TABLE AS or SELECT INTO? =2D * =2D * XXX should we allow this if the destination is temp? Considering th= at =2D * it would still require catalog changes, probably not. =2D */ =2D if (plannedstmt->intoClause !=3D NULL) =2D PreventCommandIfReadOnly(CreateCommandTag((Node *) plannedstmt)); =2D /* Fail if write permissions are requested on any non-temp table */ foreach(l, plannedstmt->rtable) { @@ -864,18 +842,6 @@ InitPlan(QueryDesc *queryDesc, int eflags) } =20 /* =2D * Detect whether we're doing SELECT INTO. If so, set the es_into_oids =2D * flag appropriately so that the plan tree will be initialized with the =2D * correct tuple descriptors. (Other SELECT INTO stuff comes later.) =2D */ =2D estate->es_select_into =3D false; =2D if (operation =3D=3D CMD_SELECT && plannedstmt->intoClause !=3D NULL) =2D { =2D estate->es_select_into =3D true; =2D estate->es_into_oids =3D interpretOidsOption(plannedstmt->intoClause->= options); =2D } =2D =2D /* * Initialize the executor's tuple table to empty. */ estate->es_tupleTable =3D NIL; @@ -926,9 +892,7 @@ InitPlan(QueryDesc *queryDesc, int eflags) planstate =3D ExecInitNode(plan, estate, eflags); =20 /* =2D * Get the tuple descriptor describing the type of tuples to return. (t= his =2D * is especially important if we are creating a relation with "SELECT =2D * INTO") + * Get the tuple descriptor describing the type of tuples to return. */ tupType =3D ExecGetResultType(planstate); =20 @@ -968,16 +932,6 @@ InitPlan(QueryDesc *queryDesc, int eflags) =20 queryDesc->tupDesc =3D tupType; queryDesc->planstate =3D planstate; =2D =2D /* =2D * If doing SELECT INTO, initialize the "into" relation. We must wait =2D * till now so we have the "clean" result tuple type to create the new =2D * table from. =2D * =2D * If EXPLAIN, skip creating the "into" relation. =2D */ =2D if (estate->es_select_into && !(eflags & EXEC_FLAG_EXPLAIN_ONLY)) =2D OpenIntoRel(queryDesc); } =20 /* @@ -1230,7 +1184,7 @@ ExecGetTriggerResultRel(EState *estate, Oid relid) /* * ExecContextForcesOids * =2D * This is pretty grotty: when doing INSERT, UPDATE, or SELECT INTO, + * This is pretty grotty: when doing INSERT or UPDATE * we need to ensure that result tuples have space for an OID iff they are * going to be stored into a relation that has OIDs. In other contexts * we are free to choose whether to leave space for OIDs in result tuples @@ -1255,15 +1209,25 @@ ExecGetTriggerResultRel(EState *estate, Oid relid) * the ModifyTable node, so ModifyTable has to set es_result_relation_info * while initializing each subplan. * =2D * SELECT INTO is even uglier, because we don't have the INTO relation's =2D * descriptor available when this code runs; we have to look aside at a =2D * flag set by InitPlan(). + * SELECT INTO is even uglier, because we don't have the INTO + * relation's descriptor available - because its only defined after + * the query started - when this code runs; we have to look aside at + * flags which is passed to ExecutorStart via eflags. */ bool ExecContextForcesOids(PlanState *planstate, bool *hasoids) { ResultRelInfo *ri =3D planstate->state->es_result_relation_info; =20 + if(planstate->state->es_force_oids =3D=3D ESTATE_FORCE_OIDS_ON){ + *hasoids =3D true; + return true; + } + else if(planstate->state->es_force_oids =3D=3D ESTATE_FORCE_OIDS_OFF){ + *hasoids =3D false; + return true; + } + if (ri !=3D NULL) { Relation rel =3D ri->ri_RelationDesc; @@ -1275,12 +1239,6 @@ ExecContextForcesOids(PlanState *planstate, bool *ha= soids) } } =20 =2D if (planstate->state->es_select_into) =2D { =2D *hasoids =3D planstate->state->es_into_oids; =2D return true; =2D } =2D return false; } =20 @@ -2290,8 +2248,7 @@ EvalPlanQualStart(EPQState *epqstate, EState *parente= state, Plan *planTree) estate->es_rowMarks =3D parentestate->es_rowMarks; estate->es_top_eflags =3D parentestate->es_top_eflags; estate->es_instrument =3D parentestate->es_instrument; =2D estate->es_select_into =3D parentestate->es_select_into; =2D estate->es_into_oids =3D parentestate->es_into_oids; + /* es_auxmodifytables must NOT be copied */ =20 /* @@ -2423,351 +2380,3 @@ EvalPlanQualEnd(EPQState *epqstate) epqstate->planstate =3D NULL; epqstate->origslot =3D NULL; } =2D =2D =2D/* =2D * Support for SELECT INTO (a/k/a CREATE TABLE AS) =2D * =2D * We implement SELECT INTO by diverting SELECT's normal output with =2D * a specialized DestReceiver type. =2D */ =2D =2Dtypedef struct =2D{ =2D DestReceiver pub; /* publicly-known function pointers */ =2D EState *estate; /* EState we are working with */ =2D DestReceiver *origdest; /* QueryDesc's original receiver */ =2D Relation rel; /* Relation to write to */ =2D int hi_options; /* heap_insert performance options */ =2D BulkInsertState bistate; /* bulk insert state */ =2D} DR_intorel; =2D =2D/* =2D * OpenIntoRel --- actually create the SELECT INTO target relation =2D * =2D * This also replaces QueryDesc->dest with the special DestReceiver for =2D * SELECT INTO. We assume that the correct result tuple type has already =2D * been placed in queryDesc->tupDesc. =2D */ =2Dstatic void =2DOpenIntoRel(QueryDesc *queryDesc) =2D{ =2D IntoClause *into =3D queryDesc->plannedstmt->intoClause; =2D EState *estate =3D queryDesc->estate; =2D TupleDesc intoTupDesc =3D queryDesc->tupDesc; =2D Relation intoRelationDesc; =2D char *intoName; =2D Oid namespaceId; =2D Oid tablespaceId; =2D Datum reloptions; =2D Oid intoRelationId; =2D DR_intorel *myState; =2D RangeTblEntry *rte; =2D AttrNumber attnum; =2D static char *validnsps[] =3D HEAP_RELOPT_NAMESPACES; =2D =2D Assert(into); =2D =2D /* =2D * XXX This code needs to be kept in sync with DefineRelation(). Maybe = we =2D * should try to use that function instead. =2D */ =2D =2D /* =2D * Check consistency of arguments =2D */ =2D if (into->onCommit !=3D ONCOMMIT_NOOP =2D && into->rel->relpersistence !=3D RELPERSISTENCE_TEMP) =2D ereport(ERROR, =2D (errcode(ERRCODE_INVALID_TABLE_DEFINITION), =2D errmsg("ON COMMIT can only be used on temporary tables"))); =2D =2D { =2D AclResult aclresult; =2D int i; =2D =2D for (i =3D 0; i < intoTupDesc->natts; i++) =2D { =2D Oid atttypid =3D intoTupDesc->attrs[i]->atttypid; =2D =2D aclresult =3D pg_type_aclcheck(atttypid, GetUserId(), ACL_USAGE); =2D if (aclresult !=3D ACLCHECK_OK) =2D aclcheck_error(aclresult, ACL_KIND_TYPE, =2D format_type_be(atttypid)); =2D } =2D } =2D =2D /* =2D * If a column name list was specified in CREATE TABLE AS, override the =2D * column names derived from the query. (Too few column names are OK, = too =2D * many are not.) It would probably be all right to scribble directly = on =2D * the query's result tupdesc, but let's be safe and make a copy. =2D */ =2D if (into->colNames) =2D { =2D ListCell *lc; =2D =2D intoTupDesc =3D CreateTupleDescCopy(intoTupDesc); =2D attnum =3D 1; =2D foreach(lc, into->colNames) =2D { =2D char *colname =3D strVal(lfirst(lc)); =2D =2D if (attnum > intoTupDesc->natts) =2D ereport(ERROR, =2D (errcode(ERRCODE_SYNTAX_ERROR), =2D errmsg("CREATE TABLE AS specifies too many column names"))); =2D namestrcpy(&(intoTupDesc->attrs[attnum - 1]->attname), colname); =2D attnum++; =2D } =2D } =2D =2D /* =2D * Find namespace to create in, check its permissions, lock it against =2D * concurrent drop, and mark into->rel as RELPERSISTENCE_TEMP if the =2D * selected namespace is temporary. =2D */ =2D intoName =3D into->rel->relname; =2D namespaceId =3D RangeVarGetAndCheckCreationNamespace(into->rel, NoLock, =2D NULL); =2D =2D /* =2D * Security check: disallow creating temp tables from security-restrict= ed =2D * code. This is needed because calling code might not expect untrusted =2D * tables to appear in pg_temp at the front of its search path. =2D */ =2D if (into->rel->relpersistence =3D=3D RELPERSISTENCE_TEMP =2D && InSecurityRestrictedOperation()) =2D ereport(ERROR, =2D (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), =2D errmsg("cannot create temporary table within security-restricted op= eration"))); =2D =2D /* =2D * Select tablespace to use. If not specified, use default tablespace =2D * (which may in turn default to database's default). =2D */ =2D if (into->tableSpaceName) =2D { =2D tablespaceId =3D get_tablespace_oid(into->tableSpaceName, false); =2D } =2D else =2D { =2D tablespaceId =3D GetDefaultTablespace(into->rel->relpersistence); =2D /* note InvalidOid is OK in this case */ =2D } =2D =2D /* Check permissions except when using the database's default space */ =2D if (OidIsValid(tablespaceId) && tablespaceId !=3D MyDatabaseTableSpace) =2D { =2D AclResult aclresult; =2D =2D aclresult =3D pg_tablespace_aclcheck(tablespaceId, GetUserId(), =2D ACL_CREATE); =2D =2D if (aclresult !=3D ACLCHECK_OK) =2D aclcheck_error(aclresult, ACL_KIND_TABLESPACE, =2D get_tablespace_name(tablespaceId)); =2D } =2D =2D /* Parse and validate any reloptions */ =2D reloptions =3D transformRelOptions((Datum) 0, =2D into->options, =2D NULL, =2D validnsps, =2D true, =2D false); =2D (void) heap_reloptions(RELKIND_RELATION, reloptions, true); =2D =2D /* Now we can actually create the new relation */ =2D intoRelationId =3D heap_create_with_catalog(intoName, =2D namespaceId, =2D tablespaceId, =2D InvalidOid, =2D InvalidOid, =2D InvalidOid, =2D GetUserId(), =2D intoTupDesc, =2D NIL, =2D RELKIND_RELATION, =2D into->rel->relpersistence, =2D false, =2D false, =2D true, =2D 0, =2D into->onCommit, =2D reloptions, =2D true, =2D allowSystemTableMods); =2D Assert(intoRelationId !=3D InvalidOid); =2D =2D /* =2D * Advance command counter so that the newly-created relation's catalog =2D * tuples will be visible to heap_open. =2D */ =2D CommandCounterIncrement(); =2D =2D /* =2D * If necessary, create a TOAST table for the INTO relation. Note that =2D * AlterTableCreateToastTable ends with CommandCounterIncrement(), so t= hat =2D * the TOAST table will be visible for insertion. =2D */ =2D reloptions =3D transformRelOptions((Datum) 0, =2D into->options, =2D "toast", =2D validnsps, =2D true, =2D false); =2D =2D (void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true); =2D =2D AlterTableCreateToastTable(intoRelationId, reloptions); =2D =2D /* =2D * And open the constructed table for writing. =2D */ =2D intoRelationDesc =3D heap_open(intoRelationId, AccessExclusiveLock); =2D =2D /* =2D * Check INSERT permission on the constructed table. =2D */ =2D rte =3D makeNode(RangeTblEntry); =2D rte->rtekind =3D RTE_RELATION; =2D rte->relid =3D intoRelationId; =2D rte->relkind =3D RELKIND_RELATION; =2D rte->requiredPerms =3D ACL_INSERT; =2D =2D for (attnum =3D 1; attnum <=3D intoTupDesc->natts; attnum++) =2D rte->modifiedCols =3D bms_add_member(rte->modifiedCols, =2D attnum - FirstLowInvalidHeapAttributeNumber); =2D =2D ExecCheckRTPerms(list_make1(rte), true); =2D =2D /* =2D * Now replace the query's DestReceiver with one for SELECT INTO =2D */ =2D myState =3D (DR_intorel *) CreateDestReceiver(DestIntoRel); =2D Assert(myState->pub.mydest =3D=3D DestIntoRel); =2D myState->estate =3D estate; =2D myState->origdest =3D queryDesc->dest; =2D myState->rel =3D intoRelationDesc; =2D =2D queryDesc->dest =3D (DestReceiver *) myState; =2D =2D /* =2D * We can skip WAL-logging the insertions, unless PITR or streaming =2D * replication is in use. We can skip the FSM in any case. =2D */ =2D myState->hi_options =3D HEAP_INSERT_SKIP_FSM | =2D (XLogIsNeeded() ? 0 : HEAP_INSERT_SKIP_WAL); =2D myState->bistate =3D GetBulkInsertState(); =2D =2D /* Not using WAL requires smgr_targblock be initially invalid */ =2D Assert(RelationGetTargetBlock(intoRelationDesc) =3D=3D InvalidBlockNumb= er); =2D} =2D =2D/* =2D * CloseIntoRel --- clean up SELECT INTO at ExecutorEnd time =2D */ =2Dstatic void =2DCloseIntoRel(QueryDesc *queryDesc) =2D{ =2D DR_intorel *myState =3D (DR_intorel *) queryDesc->dest; =2D =2D /* =2D * OpenIntoRel might never have gotten called, and we also want to guard =2D * against double destruction. =2D */ =2D if (myState && myState->pub.mydest =3D=3D DestIntoRel) =2D { =2D FreeBulkInsertState(myState->bistate); =2D =2D /* If we skipped using WAL, must heap_sync before commit */ =2D if (myState->hi_options & HEAP_INSERT_SKIP_WAL) =2D heap_sync(myState->rel); =2D =2D /* close rel, but keep lock until commit */ =2D heap_close(myState->rel, NoLock); =2D =2D /* restore the receiver belonging to executor's caller */ =2D queryDesc->dest =3D myState->origdest; =2D =2D /* might as well invoke my destructor */ =2D intorel_destroy((DestReceiver *) myState); =2D } =2D} =2D =2D/* =2D * CreateIntoRelDestReceiver -- create a suitable DestReceiver object =2D */ =2DDestReceiver * =2DCreateIntoRelDestReceiver(void) =2D{ =2D DR_intorel *self =3D (DR_intorel *) palloc0(sizeof(DR_intorel)); =2D =2D self->pub.receiveSlot =3D intorel_receive; =2D self->pub.rStartup =3D intorel_startup; =2D self->pub.rShutdown =3D intorel_shutdown; =2D self->pub.rDestroy =3D intorel_destroy; =2D self->pub.mydest =3D DestIntoRel; =2D =2D /* private fields will be set by OpenIntoRel */ =2D =2D return (DestReceiver *) self; =2D} =2D =2D/* =2D * intorel_startup --- executor startup =2D */ =2Dstatic void =2Dintorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo) =2D{ =2D /* no-op */ =2D} =2D =2D/* =2D * intorel_receive --- receive one tuple =2D */ =2Dstatic void =2Dintorel_receive(TupleTableSlot *slot, DestReceiver *self) =2D{ =2D DR_intorel *myState =3D (DR_intorel *) self; =2D HeapTuple tuple; =2D =2D /* =2D * get the heap tuple out of the tuple table slot, making sure we have a =2D * writable copy =2D */ =2D tuple =3D ExecMaterializeSlot(slot); =2D =2D /* =2D * force assignment of new OID (see comments in ExecInsert) =2D */ =2D if (myState->rel->rd_rel->relhasoids) =2D HeapTupleSetOid(tuple, InvalidOid); =2D =2D heap_insert(myState->rel, =2D tuple, =2D myState->estate->es_output_cid, =2D myState->hi_options, =2D myState->bistate); =2D =2D /* We know this is a newly created relation, so there are no indexes */ =2D} =2D =2D/* =2D * intorel_shutdown --- executor end =2D */ =2Dstatic void =2Dintorel_shutdown(DestReceiver *self) =2D{ =2D /* no-op */ =2D} =2D =2D/* =2D * intorel_destroy --- release DestReceiver object =2D */ =2Dstatic void =2Dintorel_destroy(DestReceiver *self) =2D{ =2D pfree(self); =2D} diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUt= ils.c index 6db42e7..40cd5ce 100644 =2D-- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -137,8 +137,6 @@ CreateExecutorState(void) =20 estate->es_top_eflags =3D 0; estate->es_instrument =3D 0; =2D estate->es_select_into =3D false; =2D estate->es_into_oids =3D false; estate->es_finished =3D false; =20 estate->es_exprcontexts =3D NIL; diff --git a/src/backend/executor/functions.c b/src/backend/executor/functi= ons.c index 61f4622..ae8d374 100644 =2D-- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -535,7 +535,6 @@ init_execution_state(List *queryTree_list, =20 if (ps->commandType =3D=3D CMD_SELECT && ps->utilityStmt =3D=3D NULL && =2D ps->intoClause =3D=3D NULL && !ps->hasModifyingCTE) fcache->lazyEval =3D lasttages->lazyEval =3D true; } @@ -1493,8 +1492,7 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *q= ueryTreeList, */ if (parse && parse->commandType =3D=3D CMD_SELECT && =2D parse->utilityStmt =3D=3D NULL && =2D parse->intoClause =3D=3D NULL) + parse->utilityStmt =3D=3D NULL) { tlist_ptr =3D &parse->targetList; tlist =3D parse->targetList; diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index 81f284c..ce6abcf 100644 =2D-- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -1917,7 +1917,11 @@ _SPI_execute_plan(SPIPlanPtr plan, ParamListInfo par= amLI, if (_SPI_current->tuptable) _SPI_current->processed =3D _SPI_current->tuptable->alloced - _SPI_current->tuptable->free; =2D res =3D SPI_OK_UTILITY; + if(nodeTag(stmt) =3D=3D T_CreateTableAsStmt && + ((CreateTableAsStmt*)stmt)->is_select_into) + res =3D SPI_OK_SELINTO; + else + res =3D SPI_OK_UTILITY; } =20 /* @@ -2042,9 +2046,7 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers,= long tcount) { case CMD_SELECT: Assert(queryDesc->plannedstmt->utilityStmt =3D=3D NULL); =2D if (queryDesc->plannedstmt->intoClause) /* select into table? */ =2D res =3D SPI_OK_SELINTO; =2D else if (queryDesc->dest->mydest !=3D DestSPI) + if (queryDesc->dest->mydest !=3D DestSPI) { /* Don't return SPI_OK_SELECT if we're discarding result */ res =3D SPI_OK_UTILITY; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 7fec4db..e4f1815 100644 =2D-- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -85,7 +85,6 @@ _copyPlannedStmt(const PlannedStmt *from) COPY_NODE_FIELD(rtable); COPY_NODE_FIELD(resultRelations); COPY_NODE_FIELD(utilityStmt); =2D COPY_NODE_FIELD(intoClause); COPY_NODE_FIELD(subplans); COPY_BITMAPSET_FIELD(rewindPlanIDs); COPY_NODE_FIELD(rowMarks); @@ -2419,7 +2418,6 @@ _copyQuery(const Query *from) COPY_SCALAR_FIELD(canSetTag); COPY_NODE_FIELD(utilityStmt); COPY_SCALAR_FIELD(resultRelation); =2D COPY_NODE_FIELD(intoClause); COPY_SCALAR_FIELD(hasAggs); COPY_SCALAR_FIELD(hasWindowFuncs); COPY_SCALAR_FIELD(hasSubLinks); @@ -3207,6 +3205,18 @@ _copyExplainStmt(const ExplainStmt *from) return newnode; } =20 +static CreateTableAsStmt * +_copyCreateTableAsStmt(CreateTableAsStmt *from) +{ + CreateTableAsStmt *newnode =3D makeNode(CreateTableAsStmt); + + COPY_NODE_FIELD(query); + COPY_NODE_FIELD(into); + COPY_SCALAR_FIELD(is_select_into); + + return newnode; +} + static CreateSeqStmt * _copyCreateSeqStmt(const CreateSeqStmt *from) { @@ -3615,7 +3625,6 @@ _copyExecuteStmt(const ExecuteStmt *from) ExecuteStmt *newnode =3D makeNode(ExecuteStmt); =20 COPY_STRING_FIELD(name); =2D COPY_NODE_FIELD(into); COPY_NODE_FIELD(params); =20 return newnode; @@ -4250,6 +4259,9 @@ copyObject(const void *from) case T_ExplainStmt: retval =3D _copyExplainStmt(from); break; + case T_CreateTableAsStmt: + retval =3D _copyCreateTableAsStmt(from); + break; case T_CreateSeqStmt: retval =3D _copyCreateSeqStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index d2a79eb..4ac895e 100644 =2D-- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -900,7 +900,6 @@ _equalQuery(const Query *a, const Query *b) COMPARE_SCALAR_FIELD(canSetTag); COMPARE_NODE_FIELD(utilityStmt); COMPARE_SCALAR_FIELD(resultRelation); =2D COMPARE_NODE_FIELD(intoClause); COMPARE_SCALAR_FIELD(hasAggs); COMPARE_SCALAR_FIELD(hasWindowFuncs); COMPARE_SCALAR_FIELD(hasSubLinks); @@ -1564,6 +1563,17 @@ _equalExplainStmt(const ExplainStmt *a, const Explai= nStmt *b) return true; } =20 + +static bool +_equalCreateTableAsStmt(CreateTableAsStmt *a, CreateTableAsStmt *b) +{ + COMPARE_NODE_FIELD(query); + COMPARE_NODE_FIELD(into); + COMPARE_SCALAR_FIELD(is_select_into); + + return true; +} + static bool _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b) { @@ -1908,7 +1918,6 @@ static bool _equalExecuteStmt(const ExecuteStmt *a, const ExecuteStmt *b) { COMPARE_STRING_FIELD(name); =2D COMPARE_NODE_FIELD(into); COMPARE_NODE_FIELD(params); =20 return true; @@ -2793,6 +2802,9 @@ equal(const void *a, const void *b) case T_ExplainStmt: retval =3D _equalExplainStmt(a, b); break; + case T_CreateTableAsStmt: + retval =3D _equalCreateTableAsStmt(a, b); + break; case T_CreateSeqStmt: retval =3D _equalCreateSeqStmt(a, b); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 25a215e..9cb8592 100644 =2D-- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -248,7 +248,6 @@ _outPlannedStmt(StringInfo str, const PlannedStmt *node) WRITE_NODE_FIELD(rtable); WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(utilityStmt); =2D WRITE_NODE_FIELD(intoClause); WRITE_NODE_FIELD(subplans); WRITE_BITMAPSET_FIELD(rewindPlanIDs); WRITE_NODE_FIELD(rowMarks); @@ -2187,7 +2186,6 @@ _outQuery(StringInfo str, const Query *node) appendStringInfo(str, " :utilityStmt <>"); =20 WRITE_INT_FIELD(resultRelation); =2D WRITE_NODE_FIELD(intoClause); WRITE_BOOL_FIELD(hasAggs); WRITE_BOOL_FIELD(hasWindowFuncs); WRITE_BOOL_FIELD(hasSubLinks); @@ -2805,7 +2803,7 @@ _outNode(StringInfo str, const void *obj) case T_RangeVar: _outRangeVar(str, obj); break; =2D case T_IntoClause: + case T_IntoClause:/*XXX: This could be removed but dim would need to ad= d it right back*/ _outIntoClause(str, obj); break; case T_Var: diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index b9258ad..9b57956 100644 =2D-- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -198,7 +198,6 @@ _readQuery(void) READ_BOOL_FIELD(canSetTag); READ_NODE_FIELD(utilityStmt); READ_INT_FIELD(resultRelation); =2D READ_NODE_FIELD(intoClause); READ_BOOL_FIELD(hasAggs); READ_BOOL_FIELD(hasWindowFuncs); READ_BOOL_FIELD(hasSubLinks); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/p= lan/planner.c index 8bbe977..6b0541b 100644 =2D-- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -233,7 +233,6 @@ standard_planner(Query *parse, int cursorOptions, Param= ListInfo boundParams) result->rtable =3D glob->finalrtable; result->resultRelations =3D glob->resultRelations; result->utilityStmt =3D parse->utilityStmt; =2D result->intoClause =3D parse->intoClause; result->subplans =3D glob->subplans; result->rewindPlanIDs =3D glob->rewindPlanIDs; result->rowMarks =3D glob->finalrowmarks; diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer= /plan/subselect.c index 40a420a..8006787 100644 =2D-- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -1399,7 +1399,6 @@ simplify_EXISTS_query(Query *query) * are complex. */ if (query->commandType !=3D CMD_SELECT || =2D query->intoClause || query->setOperations || query->hasAggs || query->hasWindowFuncs || diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimi= zer/prep/prepjointree.c index 59a5268..0304c9d 100644 =2D-- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1129,8 +1129,7 @@ is_simple_subquery(Query *subquery) */ if (!IsA(subquery, Query) || subquery->commandType !=3D CMD_SELECT || =2D subquery->utilityStmt !=3D NULL || =2D subquery->intoClause !=3D NULL) + subquery->utilityStmt !=3D NULL) elog(ERROR, "subquery is bogus"); =20 /* @@ -1216,8 +1215,7 @@ is_simple_union_all(Query *subquery) /* Let's just make sure it's a valid subselect ... */ if (!IsA(subquery, Query) || subquery->commandType !=3D CMD_SELECT || =2D subquery->utilityStmt !=3D NULL || =2D subquery->intoClause !=3D NULL) + subquery->utilityStmt !=3D NULL) elog(ERROR, "subquery is bogus"); =20 /* Is it a set-operation query at all? */ diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/u= til/clauses.c index cd3da46..7d40e3e 100644 =2D-- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -4168,7 +4168,6 @@ inline_function(Oid funcid, Oid result_type, Oid resu= lt_collid, if (!IsA(querytree, Query) || querytree->commandType !=3D CMD_SELECT || querytree->utilityStmt || =2D querytree->intoClause || querytree->hasAggs || querytree->hasWindowFuncs || querytree->hasSubLinks || @@ -4682,8 +4681,7 @@ inline_set_returning_function(PlannerInfo *root, Rang= eTblEntry *rte) */ if (!IsA(querytree, Query) || querytree->commandType !=3D CMD_SELECT || =2D querytree->utilityStmt || =2D querytree->intoClause) + querytree->utilityStmt) goto fail; =20 /* diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index b187b03..182d6f7 100644 =2D-- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -62,6 +62,8 @@ static Query *transformDeclareCursorStmt(ParseState *psta= te, DeclareCursorStmt *stmt); static Query *transformExplainStmt(ParseState *pstate, ExplainStmt *stmt); +static Query *transformCreateTableAsStmt(ParseState *pstate, + CreateTableAsStmt *stmt); static void transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, bool pushedDown); =20 @@ -202,6 +204,11 @@ transformStmt(ParseState *pstate, Node *parseTree) (ExplainStmt *) parseTree); break; =20 + case T_CreateTableAsStmt: + result =3D transformCreateTableAsStmt(pstate, + (CreateTableAsStmt *) parseTree); + break; + default: =20 /* @@ -257,6 +264,7 @@ analyze_requires_snapshot(Node *parseTree) result =3D true; break; =20 + case T_CreateTableAsStmt: case T_ExplainStmt: /* yes, because we must analyze the contained statement */ result =3D true; @@ -455,21 +463,29 @@ transformInsertStmt(ParseState *pstate, InsertStmt *s= tmt) sub_pstate->p_relnamespace =3D sub_relnamespace; sub_pstate->p_varnamespace =3D sub_varnamespace; =20 + /* The grammar should have produced a SELECT, but it might have INTO */ + if(IsA(stmt->selectStmt, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)stmt->selectStmt; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("INSERT ... SELECT cannot specify INTO"), + parser_errposition(pstate, + exprLocation((Node *)first->intoClause)))); + } + selectQuery =3D transformStmt(sub_pstate, stmt->selectStmt); =20 free_parsestate(sub_pstate); =20 =2D /* The grammar should have produced a SELECT, but it might have INTO */ if (!IsA(selectQuery, Query) || selectQuery->commandType !=3D CMD_SELECT || selectQuery->utilityStmt !=3D NULL) elog(ERROR, "unexpected non-SELECT command in INSERT ... SELECT"); =2D if (selectQuery->intoClause) =2D ereport(ERROR, =2D (errcode(ERRCODE_SYNTAX_ERROR), =2D errmsg("INSERT ... SELECT cannot specify INTO"), =2D parser_errposition(pstate, =2D exprLocation((Node *) selectQuery->intoClause)))); =20 /* * Make the source be a subquery in the INSERT's rangetable, and add @@ -876,6 +892,18 @@ transformSelectStmt(ParseState *pstate, SelectStmt *st= mt) Node *qual; ListCell *l; =20 + /* + * Validity-check whether we got called from somewhere where + * ... INTO was not allowed + */ + if (stmt->intoClause) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("INTO is only allowed on first SELECT of UNION/INTERSECT/EXCEP= T"), + parser_errposition(pstate, + exprLocation((Node *) stmt->intoClause)))); + + qry->commandType =3D CMD_SELECT; =20 /* process the WITH clause independently of all else */ @@ -963,8 +991,6 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stm= t) pstate->p_windowdefs, &qry->targetList); =20 =2D /* SELECT INTO/CREATE TABLE AS spec is just passed through */ =2D qry->intoClause =3D stmt->intoClause; =20 qry->rtable =3D pstate->p_rtable; qry->jointree =3D makeFromExpr(pstate->p_joinlist, qual); @@ -1185,9 +1211,6 @@ transformValuesClause(ParseState *pstate, SelectStmt = *stmt) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("SELECT FOR UPDATE/SHARE cannot be applied to VALUES"))); =20 =2D /* CREATE TABLE AS spec is just passed through */ =2D qry->intoClause =3D stmt->intoClause; =2D /* * There mustn't have been any table references in the expressions, else * strange things would happen, like Cartesian products of those tables @@ -1253,7 +1276,6 @@ static Query * transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) { Query *qry =3D makeNode(Query); =2D SelectStmt *leftmostSelect; int leftmostRTI; Query *leftmostQuery; SetOperationStmt *sostmt; @@ -1286,20 +1308,6 @@ transformSetOperationStmt(ParseState *pstate, Select= Stmt *stmt) } =20 /* =2D * Find leftmost leaf SelectStmt; extract the one-time-only items from = it =2D * and from the top-level node. =2D */ =2D leftmostSelect =3D stmt->larg; =2D while (leftmostSelect && leftmostSelect->op !=3D SETOP_NONE) =2D leftmostSelect =3D leftmostSelect->larg; =2D Assert(leftmostSelect && IsA(leftmostSelect, SelectStmt) && =2D leftmostSelect->larg =3D=3D NULL); =2D qry->intoClause =3D leftmostSelect->intoClause; =2D =2D /* clear this to prevent complaints in transformSetOperationTree() */ =2D leftmostSelect->intoClause =3D NULL; =2D =2D /* * These are not one-time, exactly, but we want to process them here and * not let transformSetOperationTree() see them --- else it'll just * recurse right back here! @@ -1330,7 +1338,7 @@ transformSetOperationStmt(ParseState *pstate, SelectS= tmt *stmt) qry->setOperations =3D (Node *) sostmt; =20 /* =2D * Re-find leftmost SELECT (now it's a sub-query in rangetable) + * Find leftmost SELECT (it's a sub-query in rangetable) */ node =3D sostmt->larg; while (node && IsA(node, SetOperationStmt)) @@ -2099,6 +2107,25 @@ transformDeclareCursorStmt(ParseState *pstate, Decla= reCursorStmt *stmt) (errcode(ERRCODE_INVALID_CURSOR_DEFINITION), errmsg("cannot specify both SCROLL and NO SCROLL"))); =20 + /* + * We must explicitly disallow DECLARE CURSOR ... SELECT INTO We + * have to do this before transformStmt() as that will holler if + * it ever finds a intoClause + */ + if(IsA(stmt->query, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)stmt->query; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_INVALID_CURSOR_DEFINITION), + errmsg("DECLARE CURSOR cannot specify INTO"), + parser_errposition(pstate, + exprLocation((Node *) first->intoClause)))); + } + result =3D transformStmt(pstate, stmt->query); =20 /* Grammar should not have allowed anything but SELECT */ @@ -2107,14 +2134,6 @@ transformDeclareCursorStmt(ParseState *pstate, Decla= reCursorStmt *stmt) result->utilityStmt !=3D NULL) elog(ERROR, "unexpected non-SELECT command in DECLARE CURSOR"); =20 =2D /* But we must explicitly disallow DECLARE CURSOR ... SELECT INTO */ =2D if (result->intoClause) =2D ereport(ERROR, =2D (errcode(ERRCODE_INVALID_CURSOR_DEFINITION), =2D errmsg("DECLARE CURSOR cannot specify INTO"), =2D parser_errposition(pstate, =2D exprLocation((Node *) result->intoClause)))); =2D /* * We also disallow data-modifying WITH in a cursor. (This could be * allowed, but the semantics of when the updates occur might be @@ -2183,6 +2202,28 @@ transformExplainStmt(ParseState *pstate, ExplainStmt= *stmt) =20 =20 /* + * transformCreateTableAsStmt - + * transform an CREATE TABLE AS/SELECT ... INTO Statement + * + */ +static Query * +transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt) +{ + Query *result; + + /* transform contained query */ + stmt->query =3D (Node *) transformStmt(pstate, stmt->query); + + /* represent the command as a utility Query */ + result =3D makeNode(Query); + result->commandType =3D CMD_UTILITY; + result->utilityStmt =3D (Node *) stmt; + + return result; +} + + +/* * Check for features that are not supported together with FOR UPDATE/SHAR= E. * * exported so planner can check again after rewriting, query pullup, etc diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index d1ce2ab..2208594 100644 =2D-- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -3052,23 +3052,28 @@ ExistingIndex: USING INDEX index_name { $$ =3D= $3; } CreateAsStmt: CREATE OptTemp TABLE create_as_target AS SelectStmt opt_with_data { + SelectStmt *n; + CreateTableAsStmt* ctas =3D makeNode(CreateTableAsStmt); + ctas->into =3D $4; + ctas->query =3D $6; + ctas->is_select_into =3D false; + $4->skipData =3D !($7); + $4->rel->relpersistence =3D $2; + $4->skipData =3D !($7); + $$ =3D (Node*)ctas; + /* * When the SelectStmt is a set-operation tree, we must * stuff the INTO information into the leftmost component * Select, because that's where analyze.c will expect * to find it. */ =2D SelectStmt *n =3D findLeftmostSelect((SelectStmt *) $6); + n =3D findLeftmostSelect((SelectStmt *) $6); if (n->intoClause !=3D NULL) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("CREATE TABLE AS cannot specify INTO"), parser_errposition(exprLocation((Node *) n->intoClause)))); =2D n->intoClause =3D $4; =2D /* cram additional flags into the IntoClause */ =2D $4->rel->relpersistence =3D $2; =2D $4->skipData =3D !($7); =2D $$ =3D $6; } ; =20 @@ -8275,20 +8280,25 @@ ExecuteStmt: EXECUTE name execute_param_clause ExecuteStmt *n =3D makeNode(ExecuteStmt); n->name =3D $2; n->params =3D $3; =2D n->into =3D NULL; $$ =3D (Node *) n; } | CREATE OptTemp TABLE create_as_target AS EXECUTE name execute_param_clause opt_with_data { ExecuteStmt *n =3D makeNode(ExecuteStmt); + CreateTableAsStmt* ctas =3D makeNode(CreateTableAsStmt); + n->name =3D $7; n->params =3D $8; =2D n->into =3D $4; + /* cram additional flags into the IntoClause */ $4->rel->relpersistence =3D $2; $4->skipData =3D !($9); =2D $$ =3D (Node *) n; + + ctas->into =3D $4; + ctas->query =3D (Node*)n; + ctas->is_select_into =3D false; + $$ =3D (Node *) ctas; } ; =20 diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_c= lause.c index 7f4da92..52f449f 100644 =2D-- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -481,6 +481,21 @@ transformRangeSubselect(ParseState *pstate, RangeSubse= lect *r) if (r->alias =3D=3D NULL) elog(ERROR, "subquery in FROM must have an alias"); =20 + + if(IsA(r->subquery, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)r->subquery; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("subquery in FROM cannot have SELECT INTO"), + parser_errposition(pstate, + exprLocation((Node *)first->intoClause)))); + } + /* * Analyze and transform the subquery. */ @@ -495,12 +510,6 @@ transformRangeSubselect(ParseState *pstate, RangeSubse= lect *r) query->commandType !=3D CMD_SELECT || query->utilityStmt !=3D NULL) elog(ERROR, "unexpected non-SELECT command in subquery in FROM"); =2D if (query->intoClause) =2D ereport(ERROR, =2D (errcode(ERRCODE_SYNTAX_ERROR), =2D errmsg("subquery in FROM cannot have SELECT INTO"), =2D parser_errposition(pstate, =2D exprLocation((Node *) query->intoClause)))); =20 /* * The subquery cannot make use of any variables from FROM items created diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index c0c6240..4ea8ee6 100644 =2D-- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -241,6 +241,20 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) /* Analysis not done already */ Assert(!IsA(cte->ctequery, Query)); =20 + if(IsA(cte->ctequery, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)cte->ctequery; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("subquery in WITH cannot have SELECT INTO"), + parser_errposition(pstate, + exprLocation((Node *) first->intoClause)))); + } + query =3D parse_sub_analyze(cte->ctequery, pstate, cte, false); cte->ctequery =3D (Node *) query; =20 @@ -253,13 +267,6 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) if (query->utilityStmt !=3D NULL) elog(ERROR, "unexpected utility statement in WITH"); =20 =2D if (query->intoClause) =2D ereport(ERROR, =2D (errcode(ERRCODE_SYNTAX_ERROR), =2D errmsg("subquery in WITH cannot have SELECT INTO"), =2D parser_errposition(pstate, =2D exprLocation((Node *) query->intoClause)))); =2D /* * We disallow data-modifying WITH except at the top level of a query, * because it's not clear when such a modification should be executed. diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_exp= r.c index d22d8a1..d4d9121 100644 =2D-- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1398,6 +1398,21 @@ transformSubLink(ParseState *pstate, SubLink *sublin= k) return result; =20 pstate->p_hasSubLinks =3D true; + + if(IsA(sublink->subselect, SelectStmt)) + { + SelectStmt* first =3D (SelectStmt*)sublink->subselect; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + + if (first->intoClause) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("subquery cannot have SELECT INTO"), + parser_errposition(pstate, + exprLocation((Node *) first->intoClause)))); + } + qtree =3D parse_sub_analyze(sublink->subselect, pstate, NULL, false); =20 /* @@ -1408,12 +1423,6 @@ transformSubLink(ParseState *pstate, SubLink *sublin= k) qtree->commandType !=3D CMD_SELECT || qtree->utilityStmt !=3D NULL) elog(ERROR, "unexpected non-SELECT command in SubLink"); =2D if (qtree->intoClause) =2D ereport(ERROR, =2D (errcode(ERRCODE_SYNTAX_ERROR), =2D errmsg("subquery cannot have SELECT INTO"), =2D parser_errposition(pstate, =2D exprLocation((Node *) qtree->intoClause)))); =20 sublink->subselect =3D (Node *) qtree; =20 diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index f9ec2b2..854925c 100644 =2D-- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -37,6 +37,7 @@ raw_parser(const char *str) core_yyscan_t yyscanner; base_yy_extra_type yyextra; int yyresult; + ListCell *c; =20 /* initialize the flex scanner */ yyscanner =3D scanner_init(str, &yyextra.core_yy_extra, @@ -57,6 +58,43 @@ raw_parser(const char *str) if (yyresult) /* error */ return NIL; =20 + /* + * Some things are rather hard to properly diagnose in grammar + * without complicating/duplicating it too much. So we do some + * postprocessing here. + */ + foreach(c, yyextra.parsetree){ + switch(nodeTag(lfirst(c))){ + /* + * The grammar currently doesn't disambiguate between + * SELECT and SELECT ... INTO. Do that now. + */ + case T_SelectStmt: + { + SelectStmt* s =3D (SelectStmt*)lfirst(c); + SelectStmt* first =3D s; + while (first && first->op !=3D SETOP_NONE) + first =3D first->larg; + Assert(first && IsA(first, SelectStmt) && first->larg =3D=3D NULL); + if(first->intoClause){ + CreateTableAsStmt* ctas =3D makeNode(CreateTableAsStmt); + ctas->into =3D first->intoClause; + ctas->query =3D (Node*)s; + ctas->is_select_into =3D true; + /* + * this way everyone can complain if this is set + * without further checks because it shall never + * be set but here. + */ + first->intoClause =3D NULL; + lfirst(c) =3D ctas; + break; + } + } + default: + break; + } + } return yyextra.parsetree; } =20 diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewr= iteDefine.c index 8c87ac5..24199e8 100644 =2D-- a/src/backend/rewrite/rewriteDefine.c +++ b/src/backend/rewrite/rewriteDefine.c @@ -323,8 +323,7 @@ DefineQueryRewrite(char *rulename, query =3D (Query *) linitial(action); if (!is_instead || query->commandType !=3D CMD_SELECT || =2D query->utilityStmt !=3D NULL || =2D query->intoClause !=3D NULL) + query->utilityStmt !=3D NULL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("rules on SELECT must have action INSTEAD SELECT"))); diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 42a0fb0..7c50284 100644 =2D-- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -260,8 +260,7 @@ ChoosePortalStrategy(List *stmts) if (query->canSetTag) { if (query->commandType =3D=3D CMD_SELECT && =2D query->utilityStmt =3D=3D NULL && =2D query->intoClause =3D=3D NULL) + query->utilityStmt =3D=3D NULL) { if (query->hasModifyingCTE) return PORTAL_ONE_MOD_WITH; @@ -285,8 +284,7 @@ ChoosePortalStrategy(List *stmts) if (pstmt->canSetTag) { if (pstmt->commandType =3D=3D CMD_SELECT && =2D pstmt->utilityStmt =3D=3D NULL && =2D pstmt->intoClause =3D=3D NULL) + pstmt->utilityStmt =3D=3D NULL) { if (pstmt->hasModifyingCTE) return PORTAL_ONE_MOD_WITH; @@ -395,8 +393,7 @@ FetchStatementTargetList(Node *stmt) else { if (query->commandType =3D=3D CMD_SELECT && =2D query->utilityStmt =3D=3D NULL && =2D query->intoClause =3D=3D NULL) + query->utilityStmt =3D=3D NULL) return query->targetList; if (query->returningList) return query->returningList; @@ -408,8 +405,7 @@ FetchStatementTargetList(Node *stmt) PlannedStmt *pstmt =3D (PlannedStmt *) stmt; =20 if (pstmt->commandType =3D=3D CMD_SELECT && =2D pstmt->utilityStmt =3D=3D NULL && =2D pstmt->intoClause =3D=3D NULL) + pstmt->utilityStmt =3D=3D NULL) return pstmt->planTree->targetlist; if (pstmt->hasReturning) return pstmt->planTree->targetlist; @@ -430,7 +426,6 @@ FetchStatementTargetList(Node *stmt) ExecuteStmt *estmt =3D (ExecuteStmt *) stmt; PreparedStatement *entry; =20 =2D Assert(!estmt->into); entry =3D FetchPreparedStatement(estmt->name, true); return FetchPreparedStatementTargetList(entry); } diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 5b81c0b..35ccdb3 100644 =2D-- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -127,9 +127,7 @@ CommandIsReadOnly(Node *parsetree) switch (stmt->commandType) { case CMD_SELECT: =2D if (stmt->intoClause !=3D NULL) =2D return false; /* SELECT INTO */ =2D else if (stmt->rowMarks !=3D NIL) + if (stmt->rowMarks !=3D NIL) return false; /* SELECT FOR UPDATE/SHARE */ else if (stmt->hasModifyingCTE) return false; /* data-modifying CTE */ @@ -198,6 +196,7 @@ check_xact_readonly(Node *parsetree) case T_CreateSchemaStmt: case T_CreateSeqStmt: case T_CreateStmt: + case T_CreateTableAsStmt: case T_CreateTableSpaceStmt: case T_CreateTrigStmt: case T_CompositeTypeStmt: @@ -1036,6 +1035,10 @@ standard_ProcessUtility(Node *parsetree, ExplainQuery((ExplainStmt *) parsetree, queryString, params, dest); break; =20 + case T_CreateTableAsStmt: + CreateTableAs((CreateTableAsStmt *) parsetree, queryString, params, des= t); + break; + case T_VariableSetStmt: ExecSetVariableStmt((VariableSetStmt *) parsetree); break; @@ -1230,8 +1233,6 @@ UtilityReturnsTuples(Node *parsetree) ExecuteStmt *stmt =3D (ExecuteStmt *) parsetree; PreparedStatement *entry; =20 =2D if (stmt->into) =2D return false; entry =3D FetchPreparedStatement(stmt->name, false); if (!entry) return false; /* not our business to raise error */ @@ -1282,8 +1283,6 @@ UtilityTupleDescriptor(Node *parsetree) ExecuteStmt *stmt =3D (ExecuteStmt *) parsetree; PreparedStatement *entry; =20 =2D if (stmt->into) =2D return NULL; entry =3D FetchPreparedStatement(stmt->name, false); if (!entry) return NULL; /* not our business to raise error */ @@ -1318,8 +1317,7 @@ QueryReturnsTuples(Query *parsetree) { case CMD_SELECT: /* returns tuples ... unless it's DECLARE CURSOR or SELECT INTO */ =2D if (parsetree->utilityStmt =3D=3D NULL && =2D parsetree->intoClause =3D=3D NULL) + if (parsetree->utilityStmt =3D=3D NULL) return true; break; case CMD_INSERT: @@ -1907,6 +1905,13 @@ CreateCommandTag(Node *parsetree) tag =3D "EXPLAIN"; break; =20 + case T_CreateTableAsStmt: + if (((CreateTableAsStmt*)parsetree)->is_select_into) + tag =3D "SELECT INTO"; + else + tag =3D "CREATE TABLE AS"; + break; + case T_VariableSetStmt: switch (((VariableSetStmt *) parsetree)->kind) { @@ -2060,8 +2065,6 @@ CreateCommandTag(Node *parsetree) Assert(IsA(stmt->utilityStmt, DeclareCursorStmt)); tag =3D "DECLARE CURSOR"; } =2D else if (stmt->intoClause !=3D NULL) =2D tag =3D "SELECT INTO"; else if (stmt->rowMarks !=3D NIL) { /* not 100% but probably close enough */ @@ -2110,8 +2113,6 @@ CreateCommandTag(Node *parsetree) Assert(IsA(stmt->utilityStmt, DeclareCursorStmt)); tag =3D "DECLARE CURSOR"; } =2D else if (stmt->intoClause !=3D NULL) =2D tag =3D "SELECT INTO"; else if (stmt->rowMarks !=3D NIL) { /* not 100% but probably close enough */ @@ -2178,10 +2179,7 @@ GetCommandLogLevel(Node *parsetree) break; =20 case T_SelectStmt: =2D if (((SelectStmt *) parsetree)->intoClause) =2D lev =3D LOGSTMT_DDL; /* CREATE AS, SELECT INTO */ =2D else =2D lev =3D LOGSTMT_ALL; + lev =3D LOGSTMT_ALL; break; =20 /* utility statements --- same whether raw or cooked */ @@ -2429,6 +2427,10 @@ GetCommandLogLevel(Node *parsetree) } break; =20 + case T_CreateTableAsStmt: + lev =3D LOGSTMT_DDL; + break; + case T_VariableSetStmt: lev =3D LOGSTMT_ALL; break; @@ -2529,10 +2531,7 @@ GetCommandLogLevel(Node *parsetree) switch (stmt->commandType) { case CMD_SELECT: =2D if (stmt->intoClause !=3D NULL) =2D lev =3D LOGSTMT_DDL; /* CREATE AS, SELECT INTO */ =2D else =2D lev =3D LOGSTMT_ALL; /* SELECT or DECLARE CURSOR */ + lev =3D LOGSTMT_ALL; break; =20 case CMD_UPDATE: @@ -2558,10 +2557,7 @@ GetCommandLogLevel(Node *parsetree) switch (stmt->commandType) { case CMD_SELECT: =2D if (stmt->intoClause !=3D NULL) =2D lev =3D LOGSTMT_DDL; /* CREATE AS, SELECT INTO */ =2D else =2D lev =3D LOGSTMT_ALL; /* SELECT or DECLARE CURSOR */ + lev =3D LOGSTMT_ALL; break; =20 case CMD_UPDATE: diff --git a/src/include/access/htup.h b/src/include/access/htup.h index 6a3778d..abbc152 100644 =2D-- a/src/include/access/htup.h +++ b/src/include/access/htup.h @@ -311,7 +311,7 @@ do { \ =20 #define HeapTupleHeaderSetOid(tup, oid) \ do { \ =2D Assert((tup)->t_infomask & HEAP_HASOID); \ + if(!((tup)->t_infomask & HEAP_HASOID)) elog(ERROR, "HEAP_HASOID"); \ *((Oid *) ((char *)(tup) + (tup)->t_hoff - sizeof(Oid))) =3D (oid); \ } while (0) =20 diff --git a/src/include/commands/prepare.h b/src/include/commands/prepare.h index 472a357..6b545dc 100644 =2D-- a/src/include/commands/prepare.h +++ b/src/include/commands/prepare.h @@ -54,4 +54,7 @@ extern List *FetchPreparedStatementTargetList(PreparedSta= tement *stmt); =20 void DropAllPreparedStatements(void); =20 +extern ParamListInfo EvaluateParams(PreparedStatement *pstmt, List *params, + const char *queryString, EState *estat= e); + #endif /* PREPARE_H */ diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablec= mds.h index 03f397d..212a51d 100644 =2D-- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -15,6 +15,7 @@ #define TABLECMDS_H =20 #include "access/htup.h" +#include "executor/executor.h" #include "nodes/parsenodes.h" #include "storage/lock.h" #include "utils/relcache.h" @@ -22,6 +23,9 @@ =20 extern Oid DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId); =20 +extern void CreateTableAs(CreateTableAsStmt *stmt, const char *queryString, + ParamListInfo params, DestReceiver *dest); + extern void RemoveRelations(DropStmt *drop); =20 extern Oid AlterTableLookupRelation(AlterTableStmt *stmt, LOCKMODE lockmod= e); diff --git a/src/include/executor/executor.h b/src/include/executor/executo= r.h index 7f27669..47bd1ee 100644 =2D-- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -49,12 +49,17 @@ * AfterTriggerBeginQuery/AfterTriggerEndQuery. This does not necessarily * mean that the plan can't queue any AFTER triggers; just that the caller * is responsible for there being a trigger context for them to be queued = in. + * + * FORCE_OIDS tells the executor to build a tupleDesc that includes + * the oid column. This is used from tablecmd.c's CreateTableAs. */ #define EXEC_FLAG_EXPLAIN_ONLY 0x0001 /* EXPLAIN, no ANALYZE */ #define EXEC_FLAG_REWIND 0x0002 /* need efficient rescan */ #define EXEC_FLAG_BACKWARD 0x0004 /* need backward scan */ #define EXEC_FLAG_MARK 0x0008 /* need mark/restore */ #define EXEC_FLAG_SKIP_TRIGGERS 0x0010 /* skip AfterTrigger calls */ +#define EXEC_FLAG_FORCE_OIDS_ON 0x0020 /* force oids in returned tuples */ +#define EXEC_FLAG_FORCE_OIDS_OFF 0x0040 /* force oids in returned tuples */ =20 =20 /* diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 5207102..d268fb9 100644 =2D-- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -322,6 +322,12 @@ typedef struct ResultRelInfo ProjectionInfo *ri_projectReturning; } ResultRelInfo; =20 +typedef enum { + ESTATE_FORCE_OIDS_DEFAULT, + ESTATE_FORCE_OIDS_ON, + ESTATE_FORCE_OIDS_OFF +} EStateForceOids; + /* ---------------- * EState information * @@ -371,8 +377,7 @@ typedef struct EState =20 int es_top_eflags; /* eflags passed to ExecutorStart */ int es_instrument; /* OR of InstrumentOption flags */ =2D bool es_select_into; /* true if doing SELECT INTO */ =2D bool es_into_oids; /* true to generate OIDs in SELECT INTO */ + EStateForceOids es_force_oids; /* used by CreateTableAs */ bool es_finished; /* true when ExecutorFinish is done */ =20 List *es_exprcontexts; /* List of ExprContexts within EState */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 0e7d184..f4d9ce2 100644 =2D-- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -304,6 +304,7 @@ typedef enum NodeTag T_DropdbStmt, T_VacuumStmt, T_ExplainStmt, + T_CreateTableAsStmt, T_CreateSeqStmt, T_AlterSeqStmt, T_VariableSetStmt, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ab55639..5c2797c 100644 =2D-- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -111,8 +111,6 @@ typedef struct Query int resultRelation; /* rtable index of target relation for * INSERT/UPDATE/DELETE; 0 for SELECT */ =20 =2D IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */ =2D bool hasAggs; /* has aggregates in tlist or havingQual */ bool hasWindowFuncs; /* has window functions in tlist */ bool hasSubLinks; /* has subquery SubLink */ @@ -1009,7 +1007,7 @@ typedef struct SelectStmt */ List *distinctClause; /* NULL, list of DISTINCT ON exprs, or * lcons(NIL,NIL) for all (SELECT DISTINCT) */ =2D IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */ + IntoClause *intoClause; /* target for SELECT INTO */ List *targetList; /* the target list (of ResTarget) */ List *fromClause; /* the FROM clause */ Node *whereClause; /* WHERE qualification */ @@ -2384,7 +2382,7 @@ typedef struct VacuumStmt * Explain Statement * * The "query" field is either a raw parse tree (SelectStmt, InsertStmt, e= tc) =2D * or a Query node if parse analysis has been done. Note that rewriting= and + * or a Query node if parse analysis has been done. Note that rewriting and * planning of the query are always postponed until execution of EXPLAIN. * ---------------------- */ @@ -2396,6 +2394,19 @@ typedef struct ExplainStmt } ExplainStmt; =20 /* ---------------------- + * analyzing, rewriting and planning are handled as in explain + * statements (see comment above) only that query can only be a + * SelectStmt and not some other type. + */ +typedef struct CreateTableAsStmt +{ + NodeTag type; + IntoClause *into; + Node *query; /* the query (see comments above) */ + bool is_select_into; /* plpgsql wants to disambiguate */ +} CreateTableAsStmt; + +/* ---------------------- * Checkpoint Statement * ---------------------- */ @@ -2509,7 +2520,6 @@ typedef struct ExecuteStmt { NodeTag type; char *name; /* The name of the plan to execute */ =2D IntoClause *into; /* Optional table to store results in */ List *params; /* Values to assign to parameters */ } ExecuteStmt; =20 diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 7d90b91..b67d988 100644 =2D-- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -54,8 +54,6 @@ typedef struct PlannedStmt =20 Node *utilityStmt; /* non-null if this is DECLARE CURSOR */ =20 =2D IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */ =2D List *subplans; /* Plan trees for SubPlan expressions */ =20 Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */ diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 0b126a3..231210a 100644 =2D-- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -3286,29 +3286,18 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, break; =20 case SPI_OK_SELINTO: =2D /* * We want to disallow SELECT INTO for now, because its behavior * is not consistent with SELECT INTO in a normal plpgsql context. * (We need to reimplement EXECUTE to parse the string as a * plpgsql command, not just feed it to SPI_execute.) However, =2D * CREATE AS should be allowed ... and since it produces the same =2D * parsetree as SELECT INTO, there's no way to tell the difference =2D * except to look at the source text. Wotta kluge! + * CREATE AS should be allowed ... */ =2D { =2D char *ptr; =2D =2D for (ptr =3D querystr; *ptr; ptr++) =2D if (!scanner_isspace(*ptr)) =2D break; =2D if (*ptr =3D=3D 'S' || *ptr =3D=3D 's') =2D ereport(ERROR, =2D (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), =2D errmsg("EXECUTE of SELECT ... INTO is not implemented"), =2D errhint("You might want to use EXECUTE ... INTO or EXECUTE CREAT= E TABLE ... AS instead."))); =2D break; =2D } + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("EXECUTE of SELECT ... INTO is not implemented"), + errhint("You might want to use EXECUTE ... INTO or EXECUTE CRE= ATE TABLE ... AS instead."))); + break; =20 /* Some SPI errors deserve specific error messages */ case SPI_ERROR_COPY: @@ -5759,7 +5748,7 @@ exec_simple_check_plan(PLpgSQL_expr *expr) */ if (!IsA(query, Query)) return; =2D if (query->commandType !=3D CMD_SELECT || query->intoClause) + if (query->commandType !=3D CMD_SELECT) return; if (query->rtable !=3D NIL) return; @@ -5833,7 +5822,7 @@ exec_simple_recheck_plan(PLpgSQL_expr *expr, CachedPl= an *cplan) */ if (!IsA(stmt, PlannedStmt)) return; =2D if (stmt->commandType !=3D CMD_SELECT || stmt->intoClause) + if (stmt->commandType !=3D CMD_SELECT) return; plan =3D stmt->planTree; if (!IsA(plan, Result)) diff --git a/src/test/regress/expected/select_into.out b/src/test/regress/e= xpected/select_into.out index c8327f6..d124382 100644 =2D-- a/src/test/regress/expected/select_into.out +++ b/src/test/regress/expected/select_into.out @@ -44,6 +44,25 @@ CREATE TABLE selinto_schema.tmp3 (a,b,c) AS SELECT oid,relname,relacl FROM pg_class WHERE relname like '%c%'; -- OK RESET SESSION AUTHORIZATION; +-- +-- disallowed uses of SELECT ... INTO. All should fail +-- +DECLARE foo CURSOR FOR SELECT 1 INTO b; +ERROR: DECLARE CURSOR cannot specify INTO +LINE 1: DECLARE foo CURSOR FOR SELECT 1 INTO b; + ^ +COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blub'; +ERROR: COPY (SELECT INTO) is not supported +SELECT * FROM (SELECT 1 INTO f) bar; +ERROR: subquery in FROM cannot have SELECT INTO +LINE 1: SELECT * FROM (SELECT 1 INTO f) bar; + ^ +CREATE VIEW foo AS SELECT 1 INTO b; +ERROR: views must not contain SELECT INTO +INSERT INTO b SELECT 1 INTO f; +ERROR: INSERT ... SELECT cannot specify INTO +LINE 1: INSERT INTO b SELECT 1 INTO f; + ^ DROP SCHEMA selinto_schema CASCADE; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table selinto_schema.tmp1 diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/= expected/transactions.out index e9d3908..6981692 100644 =2D-- a/src/test/regress/expected/transactions.out +++ b/src/test/regress/expected/transactions.out @@ -139,7 +139,7 @@ SELECT * FROM writetest, temptest; -- ok (0 rows) =20 CREATE TABLE test AS SELECT * FROM writetest; -- fail =2DERROR: cannot execute SELECT INTO in a read-only transaction +ERROR: cannot execute CREATE TABLE AS in a read-only transaction START TRANSACTION READ WRITE; DROP TABLE writetest; -- ok COMMIT; diff --git a/src/test/regress/sql/select_into.sql b/src/test/regress/sql/se= lect_into.sql index 09d210b..0c07f76 100644 =2D-- a/src/test/regress/sql/select_into.sql +++ b/src/test/regress/sql/select_into.sql @@ -50,6 +50,21 @@ CREATE TABLE selinto_schema.tmp3 (a,b,c) WHERE relname like '%c%'; -- OK RESET SESSION AUTHORIZATION; =20 + +-- +-- disallowed uses of SELECT ... INTO. All should fail +-- +DECLARE foo CURSOR FOR SELECT 1 INTO b; + +COPY (SELECT 1 INTO frak UNION SELECT 2) TO 'blub'; + +SELECT * FROM (SELECT 1 INTO f) bar; + +CREATE VIEW foo AS SELECT 1 INTO b; + +INSERT INTO b SELECT 1 INTO f; + + DROP SCHEMA selinto_schema CASCADE; DROP USER selinto_user; =20 =2D-=20 1.7.6.409.ge7a85.dirty --Boundary-00=_LKBTPb2u7VrQ8wm--