Skip to content

Commit 9ed439a

Browse files
tglsfdctglsfdc
committed
Fix unsupported options in CREATE TABLE ... AS EXECUTE.
The WITH [NO] DATA option was not supported, nor the ability to specify replacement column names; the former limitation wasn't even documented, as per recent complaint from Naoya Anzai. Fix by moving the responsibility for supporting these options into the executor. It actually takes less code this way ... catversion bump due to change in representation of IntoClause, which might affect stored rules.
1 parent e90710f commit 9ed439a

File tree

10 files changed

+62
-127
lines changed

10 files changed

+62
-127
lines changed

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -105,10 +105,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE <replaceable
105105
<listitem>
106106
<para>
107107
The name of a column in the new table. If column names are not
108-
provided, they are taken from the output column names of the
109-
query. If the table is created from an
110-
<command>EXECUTE</command> command, a column name list cannot be
111-
specified.
108+
provided, they are taken from the output column names of the query.
112109
</para>
113110
</listitem>
114111
</varlistentry>
@@ -252,7 +249,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE <replaceable
252249
identical to pre-8.0 releases. Applications that
253250
require OIDs in the table created by <command>CREATE TABLE
254251
AS</command> should explicitly specify <literal>WITH (OIDS)</literal>
255-
to ensure proper behavior.
252+
to ensure desired behavior.
256253
</para>
257254
</refsect1>
258255

‎src/backend/executor/execMain.c

Lines changed: 37 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,7 @@
5656
#include "storage/smgr.h"
5757
#include "tcop/utility.h"
5858
#include "utils/acl.h"
59+
#include "utils/builtins.h"
5960
#include "utils/lsyscache.h"
6061
#include "utils/memutils.h"
6162
#include "utils/snapmgr.h"
@@ -305,6 +306,13 @@ standard_ExecutorRun(QueryDesc *queryDesc,
305306
if (sendTuples)
306307
(*dest->rStartup) (dest, operation, queryDesc->tupDesc);
307308

309+
/*
310+
* if it's CREATE TABLE AS ... WITH NO DATA, skip plan execution
311+
*/
312+
if (estate->es_select_into &&
313+
queryDesc->plannedstmt->intoClause->skipData)
314+
direction = NoMovementScanDirection;
315+
308316
/*
309317
* run plan
310318
*/
@@ -2388,6 +2396,7 @@ OpenIntoRel(QueryDesc *queryDesc)
23882396
{
23892397
IntoClause *into = queryDesc->plannedstmt->intoClause;
23902398
EState *estate = queryDesc->estate;
2399+
TupleDesc intoTupDesc = queryDesc->tupDesc;
23912400
Relation intoRelationDesc;
23922401
char *intoName;
23932402
Oid namespaceId;
@@ -2415,6 +2424,31 @@ OpenIntoRel(QueryDesc *queryDesc)
24152424
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
24162425
errmsg("ON COMMIT can only be used on temporary tables")));
24172426

2427+
/*
2428+
* If a column name list was specified in CREATE TABLE AS, override the
2429+
* column names derived from the query. (Too few column names are OK, too
2430+
* many are not.) It would probably be all right to scribble directly on
2431+
* the query's result tupdesc, but let's be safe and make a copy.
2432+
*/
2433+
if (into->colNames)
2434+
{
2435+
ListCell *lc;
2436+
2437+
intoTupDesc = CreateTupleDescCopy(intoTupDesc);
2438+
attnum = 1;
2439+
foreach(lc, into->colNames)
2440+
{
2441+
char *colname = strVal(lfirst(lc));
2442+
2443+
if (attnum > intoTupDesc->natts)
2444+
ereport(ERROR,
2445+
(errcode(ERRCODE_SYNTAX_ERROR),
2446+
errmsg("CREATE TABLE AS specifies too many column names")));
2447+
namestrcpy(&(intoTupDesc->attrs[attnum - 1]->attname), colname);
2448+
attnum++;
2449+
}
2450+
}
2451+
24182452
/*
24192453
* Find namespace to create in, check its permissions
24202454
*/
@@ -2477,7 +2511,7 @@ OpenIntoRel(QueryDesc *queryDesc)
24772511
InvalidOid,
24782512
InvalidOid,
24792513
GetUserId(),
2480-
queryDesc->tupDesc,
2514+
intoTupDesc,
24812515
NIL,
24822516
RELKIND_RELATION,
24832517
into->rel->relpersistence,
@@ -2519,15 +2553,15 @@ OpenIntoRel(QueryDesc *queryDesc)
25192553
intoRelationDesc = heap_open(intoRelationId, AccessExclusiveLock);
25202554

25212555
/*
2522-
* check INSERT permission on the constructed table.
2556+
* Check INSERT permission on the constructed table.
25232557
*/
25242558
rte = makeNode(RangeTblEntry);
25252559
rte->rtekind = RTE_RELATION;
25262560
rte->relid = intoRelationId;
25272561
rte->relkind = RELKIND_RELATION;
25282562
rte->requiredPerms = ACL_INSERT;
25292563

2530-
for (attnum = 1; attnum <= queryDesc->tupDesc->natts; attnum++)
2564+
for (attnum = 1; attnum <= intoTupDesc->natts; attnum++)
25312565
rte->modifiedCols = bms_add_member(rte->modifiedCols,
25322566
attnum - FirstLowInvalidHeapAttributeNumber);
25332567

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1041,6 +1041,7 @@ _copyIntoClause(IntoClause *from)
10411041
COPY_NODE_FIELD(options);
10421042
COPY_SCALAR_FIELD(onCommit);
10431043
COPY_STRING_FIELD(tableSpaceName);
1044+
COPY_SCALAR_FIELD(skipData);
10441045

10451046
return newnode;
10461047
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -119,6 +119,7 @@ _equalIntoClause(IntoClause *a, IntoClause *b)
119119
COMPARE_NODE_FIELD(options);
120120
COMPARE_SCALAR_FIELD(onCommit);
121121
COMPARE_STRING_FIELD(tableSpaceName);
122+
COMPARE_SCALAR_FIELD(skipData);
122123

123124
return true;
124125
}

‎src/backend/nodes/outfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -899,6 +899,7 @@ _outIntoClause(StringInfo str, IntoClause *node)
899899
WRITE_NODE_FIELD(options);
900900
WRITE_ENUM_FIELD(onCommit, OnCommitAction);
901901
WRITE_STRING_FIELD(tableSpaceName);
902+
WRITE_BOOL_FIELD(skipData);
902903
}
903904

904905
static void

‎src/backend/nodes/readfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -394,6 +394,7 @@ _readIntoClause(void)
394394
READ_NODE_FIELD(options);
395395
READ_ENUM_FIELD(onCommit, OnCommitAction);
396396
READ_STRING_FIELD(tableSpaceName);
397+
READ_BOOL_FIELD(skipData);
397398

398399
READ_DONE();
399400
}

‎src/backend/parser/analyze.c

Lines changed: 5 additions & 72 deletions
Original file line numberDiff line numberDiff line change
@@ -56,7 +56,6 @@ static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
5656
bool isTopLevel, List **targetlist);
5757
static void determineRecursiveColTypes(ParseState *pstate,
5858
Node *larg, List *nrtargetlist);
59-
static void applyColumnNames(List *dst, List *src);
6059
static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
6160
static List *transformReturningList(ParseState *pstate, List *returningList);
6261
static Query *transformDeclareCursorStmt(ParseState *pstate,
@@ -964,13 +963,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
964963
pstate->p_windowdefs,
965964
&qry->targetList);
966965

967-
/* handle any SELECT INTO/CREATE TABLE AS spec */
968-
if (stmt->intoClause)
969-
{
970-
qry->intoClause = stmt->intoClause;
971-
if (stmt->intoClause->colNames)
972-
applyColumnNames(qry->targetList, stmt->intoClause->colNames);
973-
}
966+
/* SELECT INTO/CREATE TABLE AS spec is just passed through */
967+
qry->intoClause = stmt->intoClause;
974968

975969
qry->rtable = pstate->p_rtable;
976970
qry->jointree = makeFromExpr(pstate->p_joinlist, qual);
@@ -1191,13 +1185,8 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
11911185
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
11921186
errmsg("SELECT FOR UPDATE/SHARE cannot be applied to VALUES")));
11931187

1194-
/* handle any CREATE TABLE AS spec */
1195-
if (stmt->intoClause)
1196-
{
1197-
qry->intoClause = stmt->intoClause;
1198-
if (stmt->intoClause->colNames)
1199-
applyColumnNames(qry->targetList, stmt->intoClause->colNames);
1200-
}
1188+
/* CREATE TABLE AS spec is just passed through */
1189+
qry->intoClause = stmt->intoClause;
12011190

12021191
/*
12031192
* There mustn't have been any table references in the expressions, else
@@ -1268,7 +1257,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
12681257
int leftmostRTI;
12691258
Query *leftmostQuery;
12701259
SetOperationStmt *sostmt;
1271-
List *intoColNames = NIL;
12721260
List *sortClause;
12731261
Node *limitOffset;
12741262
Node *limitCount;
@@ -1306,11 +1294,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
13061294
leftmostSelect = leftmostSelect->larg;
13071295
Assert(leftmostSelect && IsA(leftmostSelect, SelectStmt) &&
13081296
leftmostSelect->larg == NULL);
1309-
if (leftmostSelect->intoClause)
1310-
{
1311-
qry->intoClause = leftmostSelect->intoClause;
1312-
intoColNames = leftmostSelect->intoClause->colNames;
1313-
}
1297+
qry->intoClause = leftmostSelect->intoClause;
13141298

13151299
/* clear this to prevent complaints in transformSetOperationTree() */
13161300
leftmostSelect->intoClause = NULL;
@@ -1460,19 +1444,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
14601444
qry->limitCount = transformLimitClause(pstate, limitCount,
14611445
"LIMIT");
14621446

1463-
/*
1464-
* Handle SELECT INTO/CREATE TABLE AS.
1465-
*
1466-
* Any column names from CREATE TABLE AS need to be attached to both the
1467-
* top level and the leftmost subquery. We do not do this earlier because
1468-
* we do *not* want sortClause processing to be affected.
1469-
*/
1470-
if (intoColNames)
1471-
{
1472-
applyColumnNames(qry->targetList, intoColNames);
1473-
applyColumnNames(leftmostQuery->targetList, intoColNames);
1474-
}
1475-
14761447
qry->rtable = pstate->p_rtable;
14771448
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
14781449

@@ -1892,44 +1863,6 @@ determineRecursiveColTypes(ParseState *pstate, Node *larg, List *nrtargetlist)
18921863
analyzeCTETargetList(pstate, pstate->p_parent_cte, targetList);
18931864
}
18941865

1895-
/*
1896-
* Attach column names from a ColumnDef list to a TargetEntry list
1897-
* (for CREATE TABLE AS)
1898-
*/
1899-
static void
1900-
applyColumnNames(List *dst, List *src)
1901-
{
1902-
ListCell *dst_item;
1903-
ListCell *src_item;
1904-
1905-
src_item = list_head(src);
1906-
1907-
foreach(dst_item, dst)
1908-
{
1909-
TargetEntry *d = (TargetEntry *) lfirst(dst_item);
1910-
ColumnDef *s;
1911-
1912-
/* junk targets don't count */
1913-
if (d->resjunk)
1914-
continue;
1915-
1916-
/* fewer ColumnDefs than target entries is OK */
1917-
if (src_item == NULL)
1918-
break;
1919-
1920-
s = (ColumnDef *) lfirst(src_item);
1921-
src_item = lnext(src_item);
1922-
1923-
d->resname = pstrdup(s->colname);
1924-
}
1925-
1926-
/* more ColumnDefs than target entries is not OK */
1927-
if (src_item != NULL)
1928-
ereport(ERROR,
1929-
(errcode(ERRCODE_SYNTAX_ERROR),
1930-
errmsg("CREATE TABLE AS specifies too many column names")));
1931-
}
1932-
19331866

19341867
/*
19351868
* transformUpdateStmt -

‎src/backend/parser/gram.y

Lines changed: 12 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -387,8 +387,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
387387
%type <node> case_expr case_arg when_clause case_default
388388
%type <list> when_clause_list
389389
%type <ival> sub_type
390-
%type <list> OptCreateAs CreateAsList
391-
%type <node> CreateAsElement ctext_expr
390+
%type <node> ctext_expr
392391
%type <value> NumericOnly
393392
%type <list> NumericOnly_list
394393
%type <alias> alias_clause
@@ -3015,63 +3014,32 @@ CreateAsStmt:
30153014
* When the SelectStmt is a set-operation tree, we must
30163015
* stuff the INTO information into the leftmost component
30173016
* Select, because that's where analyze.c will expect
3018-
* to find it. Similarly, the output column names must
3019-
* be attached to that Select's target list.
3017+
* to find it.
30203018
*/
30213019
SelectStmt *n = findLeftmostSelect((SelectStmt *) $6);
30223020
if (n->intoClause != NULL)
30233021
ereport(ERROR,
30243022
(errcode(ERRCODE_SYNTAX_ERROR),
30253023
errmsg("CREATE TABLE AS cannot specify INTO"),
30263024
parser_errposition(exprLocation((Node *) n->intoClause))));
3027-
$4->rel->relpersistence = $2;
30283025
n->intoClause = $4;
3029-
/* Implement WITH NO DATA by forcing top-level LIMIT 0 */
3030-
if (!$7)
3031-
((SelectStmt *) $6)->limitCount = makeIntConst(0, -1);
3026+
/* cram additional flags into the IntoClause */
3027+
$4->rel->relpersistence = $2;
3028+
$4->skipData = !($7);
30323029
$$ = $6;
30333030
}
30343031
;
30353032

30363033
create_as_target:
3037-
qualified_name OptCreateAs OptWith OnCommitOption OptTableSpace
3034+
qualified_name opt_column_list OptWith OnCommitOption OptTableSpace
30383035
{
30393036
$$ = makeNode(IntoClause);
30403037
$$->rel = $1;
30413038
$$->colNames = $2;
30423039
$$->options = $3;
30433040
$$->onCommit = $4;
30443041
$$->tableSpaceName = $5;
3045-
}
3046-
;
3047-
3048-
OptCreateAs:
3049-
'(' CreateAsList ')' { $$ = $2; }
3050-
| /*EMPTY*/ { $$ = NIL; }
3051-
;
3052-
3053-
CreateAsList:
3054-
CreateAsElement { $$ = list_make1($1); }
3055-
| CreateAsList ',' CreateAsElement { $$ = lappend($1, $3); }
3056-
;
3057-
3058-
CreateAsElement:
3059-
ColId
3060-
{
3061-
ColumnDef *n = makeNode(ColumnDef);
3062-
n->colname = $1;
3063-
n->typeName = NULL;
3064-
n->inhcount = 0;
3065-
n->is_local = true;
3066-
n->is_not_null = false;
3067-
n->is_from_type = false;
3068-
n->storage = 0;
3069-
n->raw_default = NULL;
3070-
n->cooked_default = NULL;
3071-
n->collClause = NULL;
3072-
n->collOid = InvalidOid;
3073-
n->constraints = NIL;
3074-
$$ = (Node *)n;
3042+
$$->skipData = false; /* might get changed later */
30753043
}
30763044
;
30773045

@@ -8030,18 +7998,15 @@ ExecuteStmt: EXECUTE name execute_param_clause
80307998
$$ = (Node *) n;
80317999
}
80328000
| CREATE OptTemp TABLE create_as_target AS
8033-
EXECUTE name execute_param_clause
8001+
EXECUTE name execute_param_clause opt_with_data
80348002
{
80358003
ExecuteStmt *n = makeNode(ExecuteStmt);
80368004
n->name = $7;
80378005
n->params = $8;
8038-
$4->rel->relpersistence = $2;
80398006
n->into = $4;
8040-
if ($4->colNames)
8041-
ereport(ERROR,
8042-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
8043-
errmsg("column name list not allowed in CREATE TABLE / AS EXECUTE")));
8044-
/* ... because it's not implemented, but it could be */
8007+
/* cram additional flags into the IntoClause */
8008+
$4->rel->relpersistence = $2;
8009+
$4->skipData = !($9);
80458010
$$ = (Node *) n;
80468011
}
80478012
;
@@ -8583,6 +8548,7 @@ into_clause:
85838548
$$->options = NIL;
85848549
$$->onCommit = ONCOMMIT_NOOP;
85858550
$$->tableSpaceName = NULL;
8551+
$$->skipData = false;
85868552
}
85878553
| /*EMPTY*/
85888554
{ $$ = NULL; }

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201111231
56+
#define CATALOG_VERSION_NO 201111241
5757

5858
#endif

‎src/include/nodes/primnodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,7 @@ typedef struct IntoClause
9191
List *options; /* options from WITH clause */
9292
OnCommitAction onCommit; /* what do we do at COMMIT? */
9393
char *tableSpaceName; /* table space to use, or NULL */
94+
bool skipData; /* true for WITH NO DATA */
9495
} IntoClause;
9596

9697

0 commit comments

Comments
 (0)