Writeable CTEs, again
| От | Marko Tiikkaja |
|---|---|
| Тема | Writeable CTEs, again |
| Дата | |
| Msg-id | 4AE0D7D5.6090804@cs.helsinki.fi обсуждение исходный текст |
| Список | pgsql-hackers |
Hi,
Attached is a WIP patch which implements writeable CTEs. This patch has
some defects I'll be discussing below. Also, I haven't implemented the
grammar changes for using WITH ( .. RETURNING ) in non-SELECT queries
yet.
What's not obvious from the patch:
- estate->es_result_relation_info is currently only set during
EvalPlanQual(). ModifyTable nodes have an array of
ResultRelInfos they will be operating on. That array is part of
estate->es_result_relations.
- I removed resultRelations from PlannerInfo completely because I
didn't find use for it any more. That list is now stored first
in ModifyTable nodes, and then added to PlannerGlobal's
new resultRelations list during set_plan_refs().
Currently, we don't allow DO ALSO SELECT .. rules for SELECT queries.
But with this patch you could have a top-level SELECT which results in
multiple SELECTs when the DML operations inside CTEs are rewritten.
Consider this example:
=> CREATE RULE additional_select AS ON INSERT TO foo DO ALSO SELECT *
FROM bar;
=> WITH t AS (INSERT INTO foo VALUES(0) RETURNING *) SELECT * FROM t;
INSERT INTO foo VALUES(0) is ran first, but the results of that are
ignored. What you actually see is the output of SELECT * FROM bar which
is certainly surprising. What do you think should happen here?
INSERT/UPDATE/DELETE works as expected; both queries are ran but you get
the output of SELECT * FROM t;
Currently we also only allow cursors for simple SELECT queries. IMHO we
should also allow cursor for SELECT queries like the one above; the
INSERT is run to completion first, but then the user could use a cursor
to scan through the RETURNING tuples. I haven't looked into this very
thoroughly yet, but I don't see any obvious problems.
I'd appreciate any input.
Regards,
Marko Tiikkaja
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index b2741bc..111ed6a 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -1499,7 +1499,7 @@ SELECT 3, 'three';
<synopsis>
SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable>
</synopsis>
- and can appear anywhere a <literal>SELECT</> can. For example, you can
+ and can appear anywhere a <literal>SELECT</literal> can. For example, you can
use it as part of a <literal>UNION</>, or attach a
<replaceable>sort_specification</replaceable> (<literal>ORDER BY</>,
<literal>LIMIT</>, and/or <literal>OFFSET</>) to it. <literal>VALUES</>
@@ -1529,10 +1529,11 @@ SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression
</indexterm>
<para>
- <literal>WITH</> provides a way to write subqueries for use in a larger
- <literal>SELECT</> query. The subqueries can be thought of as defining
- temporary tables that exist just for this query. One use of this feature
- is to break down complicated queries into simpler parts. An example is:
+ <literal>WITH</> provides a way to write subqueries for use in a
+ larger query. The subqueries can be thought of as defining
+ temporary tables that exist just for this query. One use of this
+ feature is to break down complicated queries into simpler parts.
+ An example is:
<programlisting>
WITH regional_sales AS (
@@ -1560,6 +1561,28 @@ GROUP BY region, product;
</para>
<para>
+ <literal>WITH</literal> clauses can also have a
+ <literal>INSERT</literal>, <literal>UPDATE</literal>,
+ <literal>DELETE</literal>(each optionally with a
+ <literal>RETURNING</literal> clause) in them. The example below
+ moves rows from the main table, foo_log into a partition,
+ foo_log_200910.
+
+<programlisting>
+WITH t AS (
+ DELETE FROM foo_log
+ WHERE
+ foo_date >= '2009-10-01' AND
+ foo_date < '2009-11-01'
+ RETURNING *
+)
+INSERT INTO foo_log_200910
+SELECT * FROM t;
+</programlisting>
+
+ </para>
+
+ <para>
The optional <literal>RECURSIVE</> modifier changes <literal>WITH</>
from a mere syntactic convenience into a feature that accomplishes
things not otherwise possible in standard SQL. Using
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 05d90dc..29cc9db 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -58,7 +58,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
- <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable
class="parameter">column_name</replaceable>[, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> )
+ <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable
class="parameter">column_name</replaceable>[, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> |
(<replaceableclass="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> |
<replaceableclass="parameter">delete</replaceable> [ RETURNING...]))
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable
class="parameter">with_query_name</replaceable>}
</synopsis>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 9100dd9..78d2344 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -2160,7 +2160,8 @@ CopyFrom(CopyState cstate)
heap_insert(cstate->rel, tuple, mycid, hi_options, bistate);
if (resultRelInfo->ri_NumIndices > 0)
- recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
+ recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
+ slot, &(tuple->t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 756c65c..2446c6f 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2159,7 +2159,7 @@ ltrmark:;
TupleTableSlot *epqslot;
epqslot = EvalPlanQual(estate,
- relinfo->ri_RangeTableIndex,
+ relinfo,
subplanstate,
&update_ctid,
update_xmax);
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index c9c9baa..cd63630 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -3067,7 +3067,7 @@ move_chain_tuple(Relation rel,
if (ec->resultRelInfo->ri_NumIndices > 0)
{
ExecStoreTuple(&newtup, ec->slot, InvalidBuffer, false);
- ExecInsertIndexTuples(ec->slot, &(newtup.t_self), ec->estate, true);
+ ExecInsertIndexTuples(ec->resultRelInfo, ec->slot, &(newtup.t_self), ec->estate, true);
ResetPerTupleExprContext(ec->estate);
}
}
@@ -3193,7 +3193,7 @@ move_plain_tuple(Relation rel,
if (ec->resultRelInfo->ri_NumIndices > 0)
{
ExecStoreTuple(&newtup, ec->slot, InvalidBuffer, false);
- ExecInsertIndexTuples(ec->slot, &(newtup.t_self), ec->estate, true);
+ ExecInsertIndexTuples(ec->resultRelInfo, ec->slot, &(newtup.t_self), ec->estate, true);
ResetPerTupleExprContext(ec->estate);
}
}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 6e79405..7858f97 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -171,7 +171,8 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
case CMD_SELECT:
/* SELECT INTO and SELECT FOR UPDATE/SHARE need to mark tuples */
if (queryDesc->plannedstmt->intoClause != NULL ||
- queryDesc->plannedstmt->rowMarks != NIL)
+ queryDesc->plannedstmt->rowMarks != NIL ||
+ queryDesc->plannedstmt->hasWritableCtes)
estate->es_output_cid = GetCurrentCommandId(true);
break;
@@ -1173,6 +1174,92 @@ ExecutePlan(EState *estate,
*/
estate->es_direction = direction;
+ /* Process top-level CTEs in case they have writes inside */
+ {
+ ListCell *lc;
+
+ foreach(lc, estate->es_plannedstmt->planTree->initPlan)
+ {
+ SubPlan *sp;
+ int cte_param_id;
+ ParamExecData* prmdata;
+ CteScanState *leader;
+
+ sp = (SubPlan *) lfirst(lc);
+
+ if (sp->subLinkType != CTE_SUBLINK)
+ continue;
+
+ cte_param_id = linitial_int(sp->setParam);
+ prmdata = &(estate->es_param_exec_vals[cte_param_id]);
+ leader = (CteScanState *) DatumGetPointer(prmdata->value);
+
+ /*
+ * If there's no leader, the CTE isn't referenced anywhere
+ * so we can just go ahead and scan the plan
+ */
+ if (!leader)
+ {
+ TupleTableSlot *slot;
+ PlanState *ps = (PlanState *) list_nth(estate->es_subplanstates,
+ sp->plan_id - 1);
+
+ Assert(IsA(ps, ModifyTableState));
+
+ /*
+ * We might have a RETURNING here, which means that
+ * we might have to loop until the plan returns NULL
+ */
+ for (;;)
+ {
+ slot = ExecProcNode(ps);
+ if (TupIsNull(slot))
+ break;
+ }
+ }
+ else
+ {
+ TupleTableSlot* slot;
+ PlanState *ps = (PlanState *) list_nth(estate->es_subplanstates,
+ sp->plan_id - 1);
+
+ /*
+ * Regular CTE, ignore
+ *
+ * XXX this isn't probably the best way to determine whether there's
+ * an INSERT/UPDATE/DELETE inside the CTE
+ */
+ if (!IsA(ps, ModifyTableState))
+ continue;
+
+ /*
+ * Tell the CTE leader to scan itself and then return
+ *
+ * XXX would there be something to gain in using a custom function
+ * in nodeCtescan.c for this? */
+ for (;;)
+ {
+ slot = ExecProcNode((PlanState *) leader);
+ if (TupIsNull(slot))
+ break;
+ }
+
+ ExecReScan((PlanState *) leader, NULL);
+ }
+
+ /*
+ * bump CID.
+ *
+ * XXX we should probably update the snapshot a bit differently
+ */
+ CommandCounterIncrement();
+ estate->es_output_cid = GetCurrentCommandId(true);
+ estate->es_snapshot->curcid = estate->es_output_cid;
+
+ /* Should we do something for crosscheck snapshot here? */
+ }
+ }
+
/*
* Loop until we've processed the proper number of tuples from the plan.
*/
@@ -1230,7 +1317,6 @@ ExecutePlan(EState *estate,
}
}
-
/*
* ExecRelCheck --- check that tuple meets constraints for result relation
*/
@@ -1337,7 +1423,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
* See backend/executor/README for some info about how this works.
*
* estate - executor state data
- * rti - rangetable index of table containing tuple
+ * resultRelInfo - ResultRelInfo of table containing tuple
* subplanstate - portion of plan tree that needs to be re-evaluated
* *tid - t_ctid from the outdated tuple (ie, next updated version)
* priorXmax - t_xmax from the outdated tuple
@@ -1349,15 +1435,23 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
* NULL if we determine we shouldn't process the row.
*/
TupleTableSlot *
-EvalPlanQual(EState *estate, Index rti,
+EvalPlanQual(EState *estate, ResultRelInfo *resultRelInfo,
PlanState *subplanstate,
ItemPointer tid, TransactionId priorXmax)
{
TupleTableSlot *slot;
HeapTuple copyTuple;
+ Index rti;
+
+ Assert(resultRelInfo != NULL);
+
+ rti = resultRelInfo->ri_RangeTableIndex;
Assert(rti != 0);
+ /* Set es_result_relation_info correctly for EvalPlanQualFetch() */
+ estate->es_result_relation_info = resultRelInfo;
+
/*
* Get the updated version of the row; if fail, return NULL.
*/
@@ -1420,6 +1514,8 @@ EvalPlanQual(EState *estate, Index rti,
*/
EvalPlanQualPop(estate, subplanstate);
+ estate->es_result_relation_info = NULL;
+
return slot;
}
@@ -1892,7 +1988,8 @@ EvalPlanQualStart(evalPlanQual *epq, EState *estate, Plan *planTree,
* ExecInitSubPlan expects to be able to find these entries.
* Some of the SubPlans might not be used in the part of the plan tree
* we intend to run, but since it's not easy to tell which, we just
- * initialize them all.
+ * initialize them all. However, we will never run ModifyTable nodes in
+ * EvalPlanQual() so don't initialize them.
*/
Assert(epqstate->es_subplanstates == NIL);
foreach(l, estate->es_plannedstmt->subplans)
@@ -1900,7 +1997,11 @@ EvalPlanQualStart(evalPlanQual *epq, EState *estate, Plan *planTree,
Plan *subplan = (Plan *) lfirst(l);
PlanState *subplanstate;
- subplanstate = ExecInitNode(subplan, epqstate, 0);
+ /* Don't initialize ModifyTable subplans. */
+ if (IsA(subplan, ModifyTable))
+ subplanstate = NULL;
+ else
+ subplanstate = ExecInitNode(subplan, epqstate, 0);
epqstate->es_subplanstates = lappend(epqstate->es_subplanstates,
subplanstate);
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 23d987e..dbaa131 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -968,13 +968,13 @@ ExecCloseIndices(ResultRelInfo *resultRelInfo)
* ----------------------------------------------------------------
*/
List *
-ExecInsertIndexTuples(TupleTableSlot *slot,
+ExecInsertIndexTuples(ResultRelInfo* resultRelInfo,
+ TupleTableSlot *slot,
ItemPointer tupleid,
EState *estate,
bool is_vacuum_full)
{
List *result = NIL;
- ResultRelInfo *resultRelInfo;
int i;
int numIndices;
RelationPtr relationDescs;
@@ -987,7 +987,6 @@ ExecInsertIndexTuples(TupleTableSlot *slot,
/*
* Get information from the result relation info structure.
*/
- resultRelInfo = estate->es_result_relation_info;
numIndices = resultRelInfo->ri_NumIndices;
relationDescs = resultRelInfo->ri_IndexRelationDescs;
indexInfoArray = resultRelInfo->ri_IndexRelationInfo;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index d623e0b..5f22416 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -158,12 +158,12 @@ ExecProcessReturning(ProjectionInfo *projectReturning,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
-ExecInsert(TupleTableSlot *slot,
+ExecInsert(ResultRelInfo *resultRelInfo,
+ TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
HeapTuple tuple;
- ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
Oid newId;
List *recheckIndexes = NIL;
@@ -177,7 +177,6 @@ ExecInsert(TupleTableSlot *slot,
/*
* get information on the (current) result relation
*/
- resultRelInfo = estate->es_result_relation_info;
resultRelationDesc = resultRelInfo->ri_RelationDesc;
/*
@@ -247,7 +246,8 @@ ExecInsert(TupleTableSlot *slot,
* insert index entries for tuple
*/
if (resultRelInfo->ri_NumIndices > 0)
- recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
+ recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
+ slot, &(tuple->t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
@@ -271,12 +271,12 @@ ExecInsert(TupleTableSlot *slot,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
-ExecDelete(ItemPointer tupleid,
+ExecDelete(ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid,
TupleTableSlot *planSlot,
PlanState *subplanstate,
EState *estate)
{
- ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
HTSU_Result result;
ItemPointerData update_ctid;
@@ -285,7 +285,6 @@ ExecDelete(ItemPointer tupleid,
/*
* get information on the (current) result relation
*/
- resultRelInfo = estate->es_result_relation_info;
resultRelationDesc = resultRelInfo->ri_RelationDesc;
/* BEFORE ROW DELETE Triggers */
@@ -334,7 +333,7 @@ ldelete:;
TupleTableSlot *epqslot;
epqslot = EvalPlanQual(estate,
- resultRelInfo->ri_RangeTableIndex,
+ resultRelInfo,
subplanstate,
&update_ctid,
update_xmax);
@@ -413,14 +412,14 @@ ldelete:;
* ----------------------------------------------------------------
*/
static TupleTableSlot *
-ExecUpdate(ItemPointer tupleid,
+ExecUpdate(ResultRelInfo *resultRelInfo,
+ ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
PlanState *subplanstate,
EState *estate)
{
HeapTuple tuple;
- ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
HTSU_Result result;
ItemPointerData update_ctid;
@@ -442,7 +441,6 @@ ExecUpdate(ItemPointer tupleid,
/*
* get information on the (current) result relation
*/
- resultRelInfo = estate->es_result_relation_info;
resultRelationDesc = resultRelInfo->ri_RelationDesc;
/* BEFORE ROW UPDATE Triggers */
@@ -520,7 +518,7 @@ lreplace:;
TupleTableSlot *epqslot;
epqslot = EvalPlanQual(estate,
- resultRelInfo->ri_RangeTableIndex,
+ resultRelInfo,
subplanstate,
&update_ctid,
update_xmax);
@@ -559,7 +557,8 @@ lreplace:;
* If it's a HOT update, we mustn't insert new index entries.
*/
if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple))
- recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
+ recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
+ slot, &(tuple->t_self),
estate, false);
/* AFTER ROW UPDATE Triggers */
@@ -585,15 +584,15 @@ fireBSTriggers(ModifyTableState *node)
{
case CMD_INSERT:
ExecBSInsertTriggers(node->ps.state,
- node->ps.state->es_result_relations);
+ node->resultRelInfo);
break;
case CMD_UPDATE:
ExecBSUpdateTriggers(node->ps.state,
- node->ps.state->es_result_relations);
+ node->resultRelInfo);
break;
case CMD_DELETE:
ExecBSDeleteTriggers(node->ps.state,
- node->ps.state->es_result_relations);
+ node->resultRelInfo);
break;
default:
elog(ERROR, "unknown operation");
@@ -611,15 +610,15 @@ fireASTriggers(ModifyTableState *node)
{
case CMD_INSERT:
ExecASInsertTriggers(node->ps.state,
- node->ps.state->es_result_relations);
+ node->resultRelInfo);
break;
case CMD_UPDATE:
ExecASUpdateTriggers(node->ps.state,
- node->ps.state->es_result_relations);
+ node->resultRelInfo);
break;
case CMD_DELETE:
ExecASDeleteTriggers(node->ps.state,
- node->ps.state->es_result_relations);
+ node->resultRelInfo);
break;
default:
elog(ERROR, "unknown operation");
@@ -641,6 +640,7 @@ ExecModifyTable(ModifyTableState *node)
EState *estate = node->ps.state;
CmdType operation = node->operation;
PlanState *subplanstate;
+ ResultRelInfo *resultRelInfo;
JunkFilter *junkfilter;
TupleTableSlot *slot;
TupleTableSlot *planSlot;
@@ -656,17 +656,10 @@ ExecModifyTable(ModifyTableState *node)
node->fireBSTriggers = false;
}
- /*
- * es_result_relation_info must point to the currently active result
- * relation. (Note we assume that ModifyTable nodes can't be nested.)
- * We want it to be NULL whenever we're not within ModifyTable, though.
- */
- estate->es_result_relation_info =
- estate->es_result_relations + node->mt_whichplan;
-
/* Preload local variables */
subplanstate = node->mt_plans[node->mt_whichplan];
- junkfilter = estate->es_result_relation_info->ri_junkFilter;
+ resultRelInfo = node->resultRelInfo + node->mt_whichplan;
+ junkfilter = resultRelInfo->ri_junkFilter;
/*
* Fetch rows from subplan(s), and execute the required table modification
@@ -682,9 +675,9 @@ ExecModifyTable(ModifyTableState *node)
node->mt_whichplan++;
if (node->mt_whichplan < node->mt_nplans)
{
- estate->es_result_relation_info++;
subplanstate = node->mt_plans[node->mt_whichplan];
- junkfilter = estate->es_result_relation_info->ri_junkFilter;
+ resultRelInfo = node->resultRelInfo + node->mt_whichplan;
+ junkfilter = resultRelInfo->ri_junkFilter;
continue;
}
else
@@ -724,14 +717,17 @@ ExecModifyTable(ModifyTableState *node)
switch (operation)
{
case CMD_INSERT:
- slot = ExecInsert(slot, planSlot, estate);
+ slot = ExecInsert(resultRelInfo,
+ slot, planSlot, estate);
break;
case CMD_UPDATE:
- slot = ExecUpdate(tupleid, slot, planSlot,
+ slot = ExecUpdate(resultRelInfo,
+ tupleid, slot, planSlot,
subplanstate, estate);
break;
case CMD_DELETE:
- slot = ExecDelete(tupleid, planSlot,
+ slot = ExecDelete(resultRelInfo,
+ tupleid, planSlot,
subplanstate, estate);
break;
default:
@@ -744,15 +740,9 @@ ExecModifyTable(ModifyTableState *node)
* the work on next call.
*/
if (slot)
- {
- estate->es_result_relation_info = NULL;
return slot;
- }
}
- /* Reset es_result_relation_info before exiting */
- estate->es_result_relation_info = NULL;
-
/*
* We're done, but fire AFTER STATEMENT triggers before exiting.
*/
@@ -799,23 +789,22 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans);
mtstate->mt_nplans = nplans;
mtstate->operation = operation;
+ mtstate->resultRelIndex = node->resultRelIndex;
+ mtstate->resultRelInfo = estate->es_result_relations + node->resultRelIndex;
mtstate->fireBSTriggers = true;
- /* For the moment, assume our targets are exactly the global result rels */
-
/*
* call ExecInitNode on each of the plans to be executed and save the
* results into the array "mt_plans". Note we *must* set
* estate->es_result_relation_info correctly while we initialize each
* sub-plan; ExecContextForcesOids depends on that!
*/
- estate->es_result_relation_info = estate->es_result_relations;
i = 0;
foreach(l, node->plans)
{
subplan = (Plan *) lfirst(l);
+ estate->es_result_relation_info = mtstate->resultRelInfo + i;
mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags);
- estate->es_result_relation_info++;
i++;
}
estate->es_result_relation_info = NULL;
@@ -851,8 +840,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
/*
* Build a projection for each result rel.
*/
- Assert(list_length(node->returningLists) == estate->es_num_result_relations);
- resultRelInfo = estate->es_result_relations;
+ resultRelInfo = mtstate->resultRelInfo;
foreach(l, node->returningLists)
{
List *rlist = (List *) lfirst(l);
@@ -919,7 +907,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
if (junk_filter_needed)
{
- resultRelInfo = estate->es_result_relations;
+ resultRelInfo = mtstate->resultRelInfo;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
@@ -948,7 +936,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
else
{
if (operation == CMD_INSERT)
- ExecCheckPlanOutput(estate->es_result_relations->ri_RelationDesc,
+ ExecCheckPlanOutput(mtstate->resultRelInfo->ri_RelationDesc,
subplan->targetlist);
}
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 5b9591e..104b341 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -84,6 +84,7 @@ _copyPlannedStmt(PlannedStmt *from)
COPY_NODE_FIELD(resultRelations);
COPY_NODE_FIELD(utilityStmt);
COPY_NODE_FIELD(intoClause);
+ COPY_SCALAR_FIELD(hasWritableCtes);
COPY_NODE_FIELD(subplans);
COPY_BITMAPSET_FIELD(rewindPlanIDs);
COPY_NODE_FIELD(rowMarks);
@@ -172,6 +173,7 @@ _copyModifyTable(ModifyTable *from)
*/
COPY_SCALAR_FIELD(operation);
COPY_NODE_FIELD(resultRelations);
+ COPY_SCALAR_FIELD(resultRelIndex);
COPY_NODE_FIELD(plans);
COPY_NODE_FIELD(returningLists);
@@ -1452,7 +1454,7 @@ _copyXmlExpr(XmlExpr *from)
return newnode;
}
-
+
/*
* _copyNullIfExpr (same as OpExpr)
*/
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index b40db0b..5412e01 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2383,6 +2383,50 @@ bool
return true;
}
break;
+ case T_InsertStmt:
+ {
+ InsertStmt *stmt = (InsertStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->cols, context))
+ return true;
+ if (walker(stmt->selectStmt, context))
+ return true;
+ if (walker(stmt->returningList, context))
+ return true;
+ }
+ break;
+ case T_UpdateStmt:
+ {
+ UpdateStmt *stmt = (UpdateStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->targetList, context))
+ return true;
+ if (walker(stmt->whereClause, context))
+ return true;
+ if (walker(stmt->fromClause, context))
+ return true;
+ if (walker(stmt->returningList, context))
+ return true;
+ }
+ break;
+ case T_DeleteStmt:
+ {
+ DeleteStmt *stmt = (DeleteStmt *) node;
+
+ if (walker(stmt->relation, context))
+ return true;
+ if (walker(stmt->usingClause, context))
+ return true;
+ if (walker(stmt->whereClause, context))
+ return true;
+ if (walker(stmt->returningList, context))
+ return true;
+ }
+ break;
case T_A_Expr:
{
A_Expr *expr = (A_Expr *) node;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 75d5be5..79eef3b 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -327,6 +327,7 @@ _outModifyTable(StringInfo str, ModifyTable *node)
WRITE_ENUM_FIELD(operation, CmdType);
WRITE_NODE_FIELD(resultRelations);
+ WRITE_INT_FIELD(resultRelIndex);
WRITE_NODE_FIELD(plans);
WRITE_NODE_FIELD(returningLists);
}
@@ -1511,6 +1512,7 @@ _outPlannerGlobal(StringInfo str, PlannerGlobal *node)
WRITE_NODE_FIELD(finalrowmarks);
WRITE_NODE_FIELD(relationOids);
WRITE_NODE_FIELD(invalItems);
+ WRITE_NODE_FIELD(resultRelations);
WRITE_UINT_FIELD(lastPHId);
WRITE_UINT_FIELD(lastRowmarkId);
WRITE_BOOL_FIELD(transientPlan);
@@ -1526,7 +1528,6 @@ _outPlannerInfo(StringInfo str, PlannerInfo *node)
WRITE_NODE_FIELD(glob);
WRITE_UINT_FIELD(query_level);
WRITE_NODE_FIELD(join_rel_list);
- WRITE_NODE_FIELD(resultRelations);
WRITE_NODE_FIELD(init_plans);
WRITE_NODE_FIELD(cte_plan_ids);
WRITE_NODE_FIELD(eq_classes);
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 2167eba..4c9a7f5 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -3757,10 +3757,6 @@ make_modifytable(CmdType operation, List *resultRelations,
double total_size;
ListCell *subnode;
- Assert(list_length(resultRelations) == list_length(subplans));
- Assert(returningLists == NIL ||
- list_length(resultRelations) == list_length(returningLists));
-
/*
* Compute cost as sum of subplan costs.
*/
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d3d355..328d52c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -158,6 +158,8 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
glob->finalrowmarks = NIL;
glob->relationOids = NIL;
glob->invalItems = NIL;
+ glob->hasWritableCtes = false;
+ glob->resultRelations = NIL;
glob->lastPHId = 0;
glob->lastRowmarkId = 0;
glob->transientPlan = false;
@@ -236,9 +238,10 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
result->transientPlan = glob->transientPlan;
result->planTree = top_plan;
result->rtable = glob->finalrtable;
- result->resultRelations = root->resultRelations;
+ result->resultRelations = glob->resultRelations;
result->utilityStmt = parse->utilityStmt;
result->intoClause = parse->intoClause;
+ result->hasWritableCtes = glob->hasWritableCtes;
result->subplans = glob->subplans;
result->rewindPlanIDs = glob->rewindPlanIDs;
result->rowMarks = glob->finalrowmarks;
@@ -535,7 +538,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
returningLists = NIL;
plan = (Plan *) make_modifytable(parse->commandType,
- copyObject(root->resultRelations),
+ list_make1_int(parse->resultRelation),
list_make1(plan),
returningLists);
}
@@ -698,12 +701,12 @@ inheritance_planner(PlannerInfo *root)
Query *parse = root->parse;
int parentRTindex = parse->resultRelation;
List *subplans = NIL;
- List *resultRelations = NIL;
List *returningLists = NIL;
List *rtable = NIL;
List *tlist;
PlannerInfo subroot;
ListCell *l;
+ List *resultRelations = NIL;
foreach(l, root->append_rel_list)
{
@@ -763,8 +766,6 @@ inheritance_planner(PlannerInfo *root)
}
}
- root->resultRelations = resultRelations;
-
/* Mark result as unordered (probably unnecessary) */
root->query_pathkeys = NIL;
@@ -774,7 +775,9 @@ inheritance_planner(PlannerInfo *root)
*/
if (subplans == NIL)
{
- root->resultRelations = list_make1_int(parentRTindex);
+ /* XXX what should we do here? */
+ //resultRelations = list_make1_int(parentRTindex);
+
/* although dummy, it must have a valid tlist for executor */
tlist = preprocess_targetlist(root, parse->targetList);
return (Plan *) make_result(root,
@@ -799,7 +802,7 @@ inheritance_planner(PlannerInfo *root)
/* And last, tack on a ModifyTable node to do the UPDATE/DELETE work */
return (Plan *) make_modifytable(parse->commandType,
- copyObject(root->resultRelations),
+ resultRelations,
subplans,
returningLists);
}
@@ -1637,12 +1640,6 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
parse->rowMarks);
}
- /* Compute result-relations list if needed */
- if (parse->resultRelation)
- root->resultRelations = list_make1_int(parse->resultRelation);
- else
- root->resultRelations = NIL;
-
/*
* Return the actual output ordering in query_pathkeys for possible use by
* an outer query level.
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 55922f3..f9bb52f 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -498,16 +498,23 @@ set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset)
*/
Assert(splan->plan.qual == NIL);
- foreach(l, splan->resultRelations)
- {
- lfirst_int(l) += rtoffset;
- }
+ Assert(plan->lefttree == NULL && plan->righttree == NULL);
+
foreach(l, splan->plans)
{
lfirst(l) = set_plan_refs(glob,
(Plan *) lfirst(l),
rtoffset);
}
+
+ foreach(l, splan->resultRelations)
+ {
+ lfirst_int(l) += rtoffset;
+ }
+
+ splan->resultRelIndex = list_length(glob->resultRelations);
+ glob->resultRelations = list_concat(glob->resultRelations,
+ splan->resultRelations);
}
break;
case T_Append:
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 8e077f5..cf013b1 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -868,16 +868,26 @@ SS_process_ctes(PlannerInfo *root)
Bitmapset *tmpset;
int paramid;
Param *prm;
+ CmdType cmdType = ((Query *) cte->ctequery)->commandType;
/*
- * Ignore CTEs that are not actually referenced anywhere.
+ * Ignore SELECT CTEs that are not actually referenced anywhere.
*/
- if (cte->cterefcount == 0)
+ if (cte->cterefcount == 0 && cmdType == CMD_SELECT)
{
/* Make a dummy entry in cte_plan_ids */
root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
continue;
}
+ else if (cte->cterefcount > 0 &&
+ cmdType != CMD_SELECT &&
+ ((Query *) cte->ctequery)->returningList == NIL)
+ {
+ /* XXX Should this be in parse_relation.c? */
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("INSERT/UPDATE/DELETE without RETURNING is only allowed inside a non-referenced CTE")));
+ }
/*
* Copy the source Query node. Probably not necessary, but let's keep
@@ -894,6 +904,11 @@ SS_process_ctes(PlannerInfo *root)
cte->cterecursive, 0.0,
&subroot);
+ /* XXX Do we really need to know this? */
+ if (subroot->parse->commandType != CMD_SELECT)
+ root->glob->hasWritableCtes = true;
+
+
/*
* Make a SubPlan node for it. This is just enough unlike
* build_subplan that we can't share code.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b6b32c5..07c65d3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -7371,6 +7371,33 @@ common_table_expr: name opt_name_list AS select_with_parens
n->location = @1;
$$ = (Node *) n;
}
+ | name opt_name_list AS '(' InsertStmt ')'
+ {
+ CommonTableExpr *n = makeNode(CommonTableExpr);
+ n->ctename = $1;
+ n->aliascolnames = $2;
+ n->ctequery = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | name opt_name_list AS '(' UpdateStmt ')'
+ {
+ CommonTableExpr *n = makeNode(CommonTableExpr);
+ n->ctename = $1;
+ n->aliascolnames = $2;
+ n->ctequery = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
+ | name opt_name_list AS '(' DeleteStmt ')'
+ {
+ CommonTableExpr *n = makeNode(CommonTableExpr);
+ n->ctename = $1;
+ n->aliascolnames = $2;
+ n->ctequery = $5;
+ n->location = @1;
+ $$ = (Node *) n;
+ }
;
into_clause:
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index 19bfb8e..7ce5dad 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -18,6 +18,7 @@
#include "nodes/nodeFuncs.h"
#include "parser/analyze.h"
#include "parser/parse_cte.h"
+#include "nodes/plannodes.h"
#include "utils/builtins.h"
@@ -225,22 +226,30 @@ static void
analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
{
Query *query;
+ List *cteList;
/* Analysis not done already */
+ /*
Assert(IsA(cte->ctequery, SelectStmt));
+ */
query = parse_sub_analyze(cte->ctequery, pstate, cte);
cte->ctequery = (Node *) query;
+ if (query->commandType == CMD_SELECT)
+ cteList = query->targetList;
+ else
+ cteList = query->returningList;
+
/*
* Check that we got something reasonable. Many of these conditions are
* impossible given restrictions of the grammar, but check 'em anyway.
- * (These are the same checks as in transformRangeSubselect.)
+ * Note, however, that we can't yet decice whether to allow
+ * INSERT/UPDATE/DELETE without a RETURNING clause or not because we don't
+ * know the refcount.
*/
- if (!IsA(query, Query) ||
- query->commandType != CMD_SELECT ||
- query->utilityStmt != NULL)
- elog(ERROR, "unexpected non-SELECT command in subquery in WITH");
+ Assert(IsA(query, Query) && query->utilityStmt == NULL);
+
if (query->intoClause)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -251,7 +260,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
if (!cte->cterecursive)
{
/* Compute the output column names/types if not done yet */
- analyzeCTETargetList(pstate, cte, query->targetList);
+ analyzeCTETargetList(pstate, cte, cteList);
}
else
{
@@ -269,7 +278,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
lctyp = list_head(cte->ctecoltypes);
lctypmod = list_head(cte->ctecoltypmods);
varattno = 0;
- foreach(lctlist, query->targetList)
+ foreach(lctlist, cteList)
{
TargetEntry *te = (TargetEntry *) lfirst(lctlist);
Node *texpr;
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index df546aa..ac9f137 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -24,6 +24,7 @@
#include "funcapi.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/plannodes.h"
#include "parser/parsetree.h"
#include "parser/parse_relation.h"
#include "parser/parse_type.h"
@@ -1391,8 +1392,8 @@ addRangeTableEntryForCTE(ParseState *pstate,
rte->ctelevelsup = levelsup;
/* Self-reference if and only if CTE's parse analysis isn't completed */
- rte->self_reference = !IsA(cte->ctequery, Query);
- Assert(cte->cterecursive || !rte->self_reference);
+ rte->self_reference = !IsA(cte->ctequery, Query) && !IsA(cte->ctequery, ModifyTable);
+ Assert(cte->cterecursive || !rte->self_reference || IsA(cte->ctequery, ModifyTable));
/* Bump the CTE's refcount if this isn't a self-reference */
if (!rte->self_reference)
cte->cterefcount++;
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index f6e2bbe..e7de319 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -310,10 +310,20 @@ markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
+ List *cteList;
+ Query *query;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
- ste = get_tle_by_resno(((Query *) cte->ctequery)->targetList,
+
+ query = (Query *) cte->ctequery;
+
+ if (query->commandType == CMD_SELECT)
+ cteList = query->targetList;
+ else
+ cteList = query->returningList;
+
+ ste = get_tle_by_resno(cteList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
@@ -1234,8 +1244,8 @@ expandRecordVariable(ParseState *pstate, Var *var, int levelsup)
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
- ste = get_tle_by_resno(((Query *) cte->ctequery)->targetList,
- attnum);
+
+ ste = get_tle_by_resno(((Query* ) cte->ctequery)->targetList, attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
rte->eref->aliasname, attnum);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 38930e1..50d21f3 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -1929,6 +1929,51 @@ QueryRewrite(Query *parsetree)
results = lappend(results, query);
}
+ {
+ ListCell *lc;
+ CommonTableExpr *cte;
+ Query *ctequery;
+ List *rewritten;
+
+ foreach(lc, parsetree->cteList)
+ {
+ cte = lfirst(lc);
+
+ ctequery = (Query *) cte->ctequery;
+
+ if (ctequery->commandType == CMD_SELECT)
+ continue;
+
+ rewritten = QueryRewrite(ctequery);
+
+ /*
+ * For UPDATE and DELETE, the actual query is
+ * added to the end of the list.
+ */
+ if (list_length(rewritten) > 1 && ctequery->commandType != CMD_INSERT)
+ {
+ ListCell *lc;
+ int n = 1;
+
+ foreach(lc, rewritten)
+ {
+ if (n == list_length(rewritten))
+ cte->ctequery = (Node *) lfirst(lc);
+ else
+ results = lcons((void *) lfirst(lc), results);
+
+ n++;
+ }
+ }
+ else
+ {
+ cte->ctequery = (Node *) linitial(rewritten);
+ results = list_concat(results,
+ list_delete_first(rewritten));
+ }
+ }
+ }
+
/*
* Step 3
*
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 6f46a29..1c6c56e 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -293,6 +293,7 @@ ChoosePortalStrategy(List *stmts)
if (pstmt->canSetTag)
{
if (pstmt->commandType == CMD_SELECT &&
+ pstmt->hasWritableCtes == false &&
pstmt->utilityStmt == NULL &&
pstmt->intoClause == NULL)
return PORTAL_ONE_SELECT;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea88c3b..7a7997f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3853,9 +3853,16 @@ get_name_for_var_field(Var *var, int fieldno,
}
if (lc != NULL)
{
- Query *ctequery = (Query *) cte->ctequery;
- TargetEntry *ste = get_tle_by_resno(ctequery->targetList,
- attnum);
+ Query *ctequery = (Query *) cte->ctequery;
+ List *ctelist;
+ TargetEntry *ste;
+
+ if (ctequery->commandType != CMD_SELECT)
+ ctelist = ctequery->returningList;
+ else
+ ctelist = ctequery->targetList;
+
+ ste = get_tle_by_resno(ctelist, attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 7dfaff0..5666137 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -166,7 +166,7 @@ extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids);
extern void ExecConstraints(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate);
-extern TupleTableSlot *EvalPlanQual(EState *estate, Index rti,
+extern TupleTableSlot *EvalPlanQual(EState *estate, ResultRelInfo *resultRelInfo,
PlanState *subplanstate,
ItemPointer tid, TransactionId priorXmax);
extern HeapTuple EvalPlanQualFetch(EState *estate, Index rti,
@@ -309,7 +309,8 @@ extern void ExecCloseScanRelation(Relation scanrel);
extern void ExecOpenIndices(ResultRelInfo *resultRelInfo);
extern void ExecCloseIndices(ResultRelInfo *resultRelInfo);
-extern List *ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid,
+extern List *ExecInsertIndexTuples(ResultRelInfo *resultRelInfo,
+ TupleTableSlot *slot, ItemPointer tupleid,
EState *estate, bool is_vacuum_full);
extern void RegisterExprContextCallback(ExprContext *econtext,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index bd48c32..cc9bfd8 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -991,6 +991,8 @@ typedef struct ModifyTableState
PlanState **mt_plans; /* subplans (one per target rel) */
int mt_nplans; /* number of plans in the array */
int mt_whichplan; /* which one is being executed (0..n-1) */
+ int resultRelIndex;
+ ResultRelInfo *resultRelInfo;
bool fireBSTriggers; /* do we need to fire stmt triggers? */
} ModifyTableState;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 9538b8f..7feb72e 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -55,6 +55,8 @@ typedef struct PlannedStmt
IntoClause *intoClause; /* target for SELECT INTO / CREATE TABLE AS */
+ bool hasWritableCtes;
+
List *subplans; /* Plan trees for SubPlan expressions */
Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */
@@ -165,6 +167,7 @@ typedef struct ModifyTable
Plan plan;
CmdType operation; /* INSERT, UPDATE, or DELETE */
List *resultRelations; /* integer list of RT indexes */
+ int resultRelIndex;
List *plans; /* plan(s) producing source data */
List *returningLists; /* per-target-table RETURNING tlists */
} ModifyTable;
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index f7eb915..d995d19 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -80,6 +80,10 @@ typedef struct PlannerGlobal
List *invalItems; /* other dependencies, as PlanInvalItems */
+ bool hasWritableCtes;/* is there an (INSERT|UPDATE|DELETE) .. RETURNING inside a CTE? */
+
+ List *resultRelations;/* list of result relations */
+
Index lastPHId; /* highest PlaceHolderVar ID assigned */
Index lastRowmarkId; /* highest RowMarkClause ID assigned */
@@ -144,8 +148,6 @@ typedef struct PlannerInfo
List *join_rel_list; /* list of join-relation RelOptInfos */
struct HTAB *join_rel_hash; /* optional hashtable for join relations */
- List *resultRelations; /* integer list of RT indexes, or NIL */
-
List *init_plans; /* init SubPlans for query */
List *cte_plan_ids; /* per-CTE-item list of subplan IDs */
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index a3e94e9..4cfb569 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -1026,3 +1026,85 @@ SELECT * FROM t;
10
(55 rows)
+--
+-- Writeable CTEs with RETURNING
+--
+WITH t AS (
+ INSERT INTO y
+ VALUES
+ (11),
+ (12),
+ (13),
+ (14),
+ (15),
+ (16),
+ (17),
+ (18),
+ (19),
+ (20)
+ RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+(10 rows)
+
+WITH t AS (
+ UPDATE y
+ SET a=a+1
+ RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+ 11
+ 12
+ 13
+ 14
+ 15
+ 16
+ 17
+ 18
+ 19
+ 20
+ 21
+(20 rows)
+
+WITH t AS (
+ DELETE FROM y
+ WHERE a <= 10
+ RETURNING *
+)
+SELECT * FROM t;
+ a
+----
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(9 rows)
+
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 2cbaa42..5b35af3 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -500,3 +500,38 @@ WITH RECURSIVE t(j) AS (
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
+
+--
+-- Writeable CTEs with RETURNING
+--
+
+WITH t AS (
+ INSERT INTO y
+ VALUES
+ (11),
+ (12),
+ (13),
+ (14),
+ (15),
+ (16),
+ (17),
+ (18),
+ (19),
+ (20)
+ RETURNING *
+)
+SELECT * FROM t;
+
+WITH t AS (
+ UPDATE y
+ SET a=a+1
+ RETURNING *
+)
+SELECT * FROM t;
+
+WITH t AS (
+ DELETE FROM y
+ WHERE a <= 10
+ RETURNING *
+)
+SELECT * FROM t;
В списке pgsql-hackers по дате отправления: