Re: Writeable CTE patch - Mailing list pgsql-hackers
| From | Marko Tiikkaja |
|---|---|
| Subject | Re: Writeable CTE patch |
| Date | |
| Msg-id | 4B0AFFA9.30108@cs.helsinki.fi Whole thread Raw |
| In response to | Re: Writeable CTE patch (Alex Hunsaker <badalex@gmail.com>) |
| Responses |
Re: Writeable CTE patch
Re: Writeable CTE patch |
| List | pgsql-hackers |
Hi,
Sorry for the delay, I've been very busy for the last two weeks.
Attached is the latest version of the patch.
The snapshot update code is still the same, I have no good idea what, if
anything, should be done to it. In addition to that, I decided to keep
the code in ExecutePlan() as it was in the last patch. I tried to make
the comments a bit more clear to avoid confusion.
Regards,
Marko Tiikkaja
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 1499,1505 **** 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
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</>
--- 1499,1505 ----
<synopsis>
SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable>
</synopsis>
! 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,1538 **** 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:
<programlisting>
WITH regional_sales AS (
--- 1529,1539 ----
</indexterm>
<para>
! <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,1565 **** GROUP BY region, product;
--- 1561,1590 ----
</para>
<para>
+ A <literal>WITH</literal> clause can also have an
+ <literal>INSERT</literal>, <literal>UPDATE</literal> or
+ <literal>DELETE</literal> (each optionally with a
+ <literal>RETURNING</literal> clause) statement in it. The example below
+ moves rows from the main table, foo_log into a partition,
+ foo_log_200910.
+
+ <programlisting>
+ WITH rows AS (
+ DELETE FROM ONLY foo_log
+ WHERE
+ foo_date >= '2009-10-01' AND
+ foo_date < '2009-11-01'
+ RETURNING *
+ ), t AS (
+ INSERT INTO foo_log_200910
+ SELECT * FROM rows
+ )
+ VALUES(true);
+ </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
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
***************
*** 58,64 **** 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> )
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable
class="parameter">with_query_name</replaceable>}
</synopsis>
--- 58,64 ----
<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> |
(<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>
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***************
*** 2165,2171 **** CopyFrom(CopyState cstate)
heap_insert(cstate->rel, tuple, mycid, hi_options, bistate);
if (resultRelInfo->ri_NumIndices > 0)
! recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
--- 2165,2172 ----
heap_insert(cstate->rel, tuple, mycid, hi_options, bistate);
if (resultRelInfo->ri_NumIndices > 0)
! recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
! slot, &(tuple->t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
*** a/src/backend/commands/portalcmds.c
--- b/src/backend/commands/portalcmds.c
***************
*** 48,53 **** PerformCursorOpen(PlannedStmt *stmt, ParamListInfo params,
--- 48,58 ----
Portal portal;
MemoryContext oldContext;
+ if (stmt->hasWritableCtes)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Non-SELECT cursors are not implemented")));
+
if (cstmt == NULL || !IsA(cstmt, DeclareCursorStmt))
elog(ERROR, "PerformCursorOpen called for non-cursor query");
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
***************
*** 925,931 **** ExecuteTruncate(TruncateStmt *stmt)
InitResultRelInfo(resultRelInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE, /* don't need any index info */
false);
resultRelInfo++;
}
--- 925,930 ----
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
***************
*** 3099,3105 **** 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);
ResetPerTupleExprContext(ec->estate);
}
}
--- 3099,3105 ----
if (ec->resultRelInfo->ri_NumIndices > 0)
{
ExecStoreTuple(&newtup, ec->slot, InvalidBuffer, false);
! ExecInsertIndexTuples(ec->resultRelInfo, ec->slot, &(newtup.t_self), ec->estate, true);
ResetPerTupleExprContext(ec->estate);
}
}
***************
*** 3225,3231 **** 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);
ResetPerTupleExprContext(ec->estate);
}
}
--- 3225,3231 ----
if (ec->resultRelInfo->ri_NumIndices > 0)
{
ExecStoreTuple(&newtup, ec->slot, InvalidBuffer, false);
! ExecInsertIndexTuples(ec->resultRelInfo, ec->slot, &(newtup.t_self), ec->estate, true);
ResetPerTupleExprContext(ec->estate);
}
}
*** a/src/backend/commands/view.c
--- b/src/backend/commands/view.c
***************
*** 394,399 **** DefineView(ViewStmt *stmt, const char *queryString)
--- 394,400 ----
Query *viewParse;
Oid viewOid;
RangeVar *view;
+ ListCell *lc;
/*
* Run parse analysis to convert the raw parse tree to a Query. Note this
***************
*** 412,417 **** DefineView(ViewStmt *stmt, const char *queryString)
--- 413,430 ----
viewParse->commandType != CMD_SELECT)
elog(ERROR, "unexpected parse analysis result");
+ /* .. but it doesn't check for DML inside CTEs */
+ foreach(lc, viewParse->cteList)
+ {
+ CommonTableExpr *cte;
+
+ cte = (CommonTableExpr *) lfirst(lc);
+ if (((Query *) cte->ctequery)->commandType != CMD_SELECT)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("INSERT/UPDATE/DELETE inside a CTE not allowed in a view definition")));
+ }
+
/*
* If a list of column names was given, run through and insert these into
* the actual query tree. - thomas 2000-03-08
*** a/src/backend/executor/execMain.c
--- b/src/backend/executor/execMain.c
***************
*** 665,671 **** InitPlan(QueryDesc *queryDesc, int eflags)
InitResultRelInfo(resultRelInfo,
resultRelation,
resultRelationIndex,
- operation,
estate->es_instrument);
resultRelInfo++;
}
--- 665,670 ----
***************
*** 858,864 **** void
InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- CmdType operation,
bool doInstrument)
{
/*
--- 857,862 ----
***************
*** 928,943 **** InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_ConstraintExprs = NULL;
resultRelInfo->ri_junkFilter = NULL;
resultRelInfo->ri_projectReturning = NULL;
-
- /*
- * If there are indices on the result relation, open them and save
- * descriptors in the result relation info, so that we can add new index
- * entries for the tuples we add/update. We need not do this for a
- * DELETE, however, since deletion doesn't affect indexes.
- */
- if (resultRelationDesc->rd_rel->relhasindex &&
- operation != CMD_DELETE)
- ExecOpenIndices(resultRelInfo);
}
/*
--- 926,931 ----
***************
*** 993,1007 **** ExecGetTriggerResultRel(EState *estate, Oid relid)
/*
* Make the new entry in the right context. Currently, we don't need any
! * index information in ResultRelInfos used only for triggers, so tell
! * InitResultRelInfo it's a DELETE.
*/
oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
rInfo = makeNode(ResultRelInfo);
InitResultRelInfo(rInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE,
estate->es_instrument);
estate->es_trig_target_relations =
lappend(estate->es_trig_target_relations, rInfo);
--- 981,993 ----
/*
* Make the new entry in the right context. Currently, we don't need any
! * index information in ResultRelInfos used only for triggers.
*/
oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
rInfo = makeNode(ResultRelInfo);
InitResultRelInfo(rInfo,
rel,
0, /* dummy rangetable index */
estate->es_instrument);
estate->es_trig_target_relations =
lappend(estate->es_trig_target_relations, rInfo);
***************
*** 1178,1183 **** ExecutePlan(EState *estate,
--- 1164,1250 ----
*/
estate->es_direction = direction;
+ /* Process top-level CTEs in case they have writes inside */
+ if (estate->es_plannedstmt->hasWritableCtes)
+ {
+ 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);
+
+
+ /*
+ * bump CID.
+ *
+ * This currently relies on the fact that the top-level statement
+ * can only be SELECT or VALUES. Otherwise we'd possibly have to
+ * increment the CID after the last CTE.
+ */
+ CommandCounterIncrement();
+ estate->es_output_cid = GetCurrentCommandId(true);
+ estate->es_snapshot->curcid = estate->es_output_cid;
+
+ /*
+ * 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 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 */
+ if (!IsA(ps, ModifyTableState))
+ continue;
+
+ /*
+ * Scan through the leader CTE so the RETURNING tuples are
+ * stored into the tuple store.
+ */
+ for (;;)
+ {
+ slot = ExecProcNode((PlanState *) leader);
+ if (TupIsNull(slot))
+ break;
+ }
+
+ ExecReScan((PlanState *) leader, NULL);
+ }
+ }
+ }
+
/*
* Loop until we've processed the proper number of tuples from the plan.
*/
***************
*** 1947,1953 **** EvalPlanQualStart(EPQState *epqstate, EState *parentestate, 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.
*/
Assert(estate->es_subplanstates == NIL);
foreach(l, parentestate->es_plannedstmt->subplans)
--- 2014,2021 ----
* 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. However, we will never run ModifyTable nodes in
! * EvalPlanQual() so don't initialize them.
*/
Assert(estate->es_subplanstates == NIL);
foreach(l, parentestate->es_plannedstmt->subplans)
***************
*** 1955,1961 **** EvalPlanQualStart(EPQState *epqstate, EState *parentestate, Plan *planTree)
Plan *subplan = (Plan *) lfirst(l);
PlanState *subplanstate;
! subplanstate = ExecInitNode(subplan, estate, 0);
estate->es_subplanstates = lappend(estate->es_subplanstates,
subplanstate);
--- 2023,2033 ----
Plan *subplan = (Plan *) lfirst(l);
PlanState *subplanstate;
! /* Don't initialize ModifyTable subplans. */
! if (IsA(subplan, ModifyTable))
! subplanstate = NULL;
! else
! subplanstate = ExecInitNode(subplan, estate, 0);
estate->es_subplanstates = lappend(estate->es_subplanstates,
subplanstate);
*** a/src/backend/executor/execUtils.c
--- b/src/backend/executor/execUtils.c
***************
*** 969,981 **** ExecCloseIndices(ResultRelInfo *resultRelInfo)
* ----------------------------------------------------------------
*/
List *
! ExecInsertIndexTuples(TupleTableSlot *slot,
ItemPointer tupleid,
EState *estate,
bool is_vacuum_full)
{
List *result = NIL;
- ResultRelInfo *resultRelInfo;
int i;
int numIndices;
RelationPtr relationDescs;
--- 969,981 ----
* ----------------------------------------------------------------
*/
List *
! ExecInsertIndexTuples(ResultRelInfo* resultRelInfo,
! TupleTableSlot *slot,
ItemPointer tupleid,
EState *estate,
bool is_vacuum_full)
{
List *result = NIL;
int i;
int numIndices;
RelationPtr relationDescs;
***************
*** 988,994 **** 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;
--- 988,993 ----
*** a/src/backend/executor/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 158,169 **** ExecProcessReturning(ProjectionInfo *projectReturning,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
HeapTuple tuple;
- ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
Oid newId;
List *recheckIndexes = NIL;
--- 158,169 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(ResultRelInfo *resultRelInfo,
! TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
HeapTuple tuple;
Relation resultRelationDesc;
Oid newId;
List *recheckIndexes = NIL;
***************
*** 177,183 **** ExecInsert(TupleTableSlot *slot,
/*
* get information on the (current) result relation
*/
- resultRelInfo = estate->es_result_relation_info;
resultRelationDesc = resultRelInfo->ri_RelationDesc;
/*
--- 177,182 ----
***************
*** 248,254 **** ExecInsert(TupleTableSlot *slot,
* insert index entries for tuple
*/
if (resultRelInfo->ri_NumIndices > 0)
! recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
--- 247,254 ----
* insert index entries for tuple
*/
if (resultRelInfo->ri_NumIndices > 0)
! recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
! slot, &(tuple->t_self),
estate, false);
/* AFTER ROW INSERT Triggers */
***************
*** 272,283 **** ExecInsert(TupleTableSlot *slot,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
{
- ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
HTSU_Result result;
ItemPointerData update_ctid;
--- 272,283 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(ResultRelInfo *resultRelInfo,
! ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
{
Relation resultRelationDesc;
HTSU_Result result;
ItemPointerData update_ctid;
***************
*** 286,292 **** ExecDelete(ItemPointer tupleid,
/*
* get information on the (current) result relation
*/
- resultRelInfo = estate->es_result_relation_info;
resultRelationDesc = resultRelInfo->ri_RelationDesc;
/* BEFORE ROW DELETE Triggers */
--- 286,291 ----
***************
*** 415,428 **** ldelete:;
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
{
HeapTuple tuple;
- ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
HTSU_Result result;
ItemPointerData update_ctid;
--- 414,427 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(ResultRelInfo *resultRelInfo,
! ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
{
HeapTuple tuple;
Relation resultRelationDesc;
HTSU_Result result;
ItemPointerData update_ctid;
***************
*** 444,450 **** ExecUpdate(ItemPointer tupleid,
/*
* get information on the (current) result relation
*/
- resultRelInfo = estate->es_result_relation_info;
resultRelationDesc = resultRelInfo->ri_RelationDesc;
/* BEFORE ROW UPDATE Triggers */
--- 443,448 ----
***************
*** 563,569 **** 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),
estate, false);
/* AFTER ROW UPDATE Triggers */
--- 561,568 ----
* If it's a HOT update, we mustn't insert new index entries.
*/
if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple))
! recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
! slot, &(tuple->t_self),
estate, false);
/* AFTER ROW UPDATE Triggers */
***************
*** 589,603 **** fireBSTriggers(ModifyTableState *node)
{
case CMD_INSERT:
ExecBSInsertTriggers(node->ps.state,
! node->ps.state->es_result_relations);
break;
case CMD_UPDATE:
ExecBSUpdateTriggers(node->ps.state,
! node->ps.state->es_result_relations);
break;
case CMD_DELETE:
ExecBSDeleteTriggers(node->ps.state,
! node->ps.state->es_result_relations);
break;
default:
elog(ERROR, "unknown operation");
--- 588,602 ----
{
case CMD_INSERT:
ExecBSInsertTriggers(node->ps.state,
! node->resultRelInfo);
break;
case CMD_UPDATE:
ExecBSUpdateTriggers(node->ps.state,
! node->resultRelInfo);
break;
case CMD_DELETE:
ExecBSDeleteTriggers(node->ps.state,
! node->resultRelInfo);
break;
default:
elog(ERROR, "unknown operation");
***************
*** 615,629 **** fireASTriggers(ModifyTableState *node)
{
case CMD_INSERT:
ExecASInsertTriggers(node->ps.state,
! node->ps.state->es_result_relations);
break;
case CMD_UPDATE:
ExecASUpdateTriggers(node->ps.state,
! node->ps.state->es_result_relations);
break;
case CMD_DELETE:
ExecASDeleteTriggers(node->ps.state,
! node->ps.state->es_result_relations);
break;
default:
elog(ERROR, "unknown operation");
--- 614,628 ----
{
case CMD_INSERT:
ExecASInsertTriggers(node->ps.state,
! node->resultRelInfo);
break;
case CMD_UPDATE:
ExecASUpdateTriggers(node->ps.state,
! node->resultRelInfo);
break;
case CMD_DELETE:
ExecASDeleteTriggers(node->ps.state,
! node->resultRelInfo);
break;
default:
elog(ERROR, "unknown operation");
***************
*** 645,650 **** ExecModifyTable(ModifyTableState *node)
--- 644,650 ----
EState *estate = node->ps.state;
CmdType operation = node->operation;
PlanState *subplanstate;
+ ResultRelInfo *resultRelInfo;
JunkFilter *junkfilter;
TupleTableSlot *slot;
TupleTableSlot *planSlot;
***************
*** 660,676 **** 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;
/*
* Fetch rows from subplan(s), and execute the required table modification
--- 660,669 ----
node->fireBSTriggers = false;
}
/* Preload local variables */
subplanstate = node->mt_plans[node->mt_whichplan];
! resultRelInfo = node->resultRelInfo + node->mt_whichplan;
! junkfilter = resultRelInfo->ri_junkFilter;
/*
* Fetch rows from subplan(s), and execute the required table modification
***************
*** 686,694 **** 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;
EvalPlanQualSetPlan(&node->mt_epqstate, subplanstate->plan);
continue;
}
--- 679,687 ----
node->mt_whichplan++;
if (node->mt_whichplan < node->mt_nplans)
{
subplanstate = node->mt_plans[node->mt_whichplan];
! resultRelInfo = node->resultRelInfo + node->mt_whichplan;
! junkfilter = resultRelInfo->ri_junkFilter;
EvalPlanQualSetPlan(&node->mt_epqstate, subplanstate->plan);
continue;
}
***************
*** 730,743 **** ExecModifyTable(ModifyTableState *node)
switch (operation)
{
case CMD_INSERT:
! slot = ExecInsert(slot, planSlot, estate);
break;
case CMD_UPDATE:
! slot = ExecUpdate(tupleid, slot, planSlot,
&node->mt_epqstate, estate);
break;
case CMD_DELETE:
! slot = ExecDelete(tupleid, planSlot,
&node->mt_epqstate, estate);
break;
default:
--- 723,739 ----
switch (operation)
{
case CMD_INSERT:
! slot = ExecInsert(resultRelInfo,
! slot, planSlot, estate);
break;
case CMD_UPDATE:
! slot = ExecUpdate(resultRelInfo,
! tupleid, slot, planSlot,
&node->mt_epqstate, estate);
break;
case CMD_DELETE:
! slot = ExecDelete(resultRelInfo,
! tupleid, planSlot,
&node->mt_epqstate, estate);
break;
default:
***************
*** 750,764 **** 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.
*/
--- 746,754 ----
***************
*** 805,829 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans);
mtstate->mt_nplans = nplans;
mtstate->operation = operation;
/* set up epqstate with dummy subplan pointer for the moment */
EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, node->epqParam);
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);
mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags);
! estate->es_result_relation_info++;
i++;
}
estate->es_result_relation_info = NULL;
--- 795,833 ----
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;
+
/* set up epqstate with dummy subplan pointer for the moment */
EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, node->epqParam);
mtstate->fireBSTriggers = true;
/*
* 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!
*/
i = 0;
+ resultRelInfo = mtstate->resultRelInfo;
foreach(l, node->plans)
{
subplan = (Plan *) lfirst(l);
+
+ /*
+ * If there are indices on the result relation, open them and save
+ * descriptors in the result relation info, so that we can add new index
+ * entries for the tuples we add/update. We need not do this for a
+ * DELETE, however, since deletion doesn't affect indexes.
+ */
+ if (resultRelInfo->ri_RelationDesc->rd_rel->relhasindex &&
+ operation != CMD_DELETE)
+ ExecOpenIndices(resultRelInfo);
+
+ estate->es_result_relation_info = resultRelInfo;
mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags);
!
! resultRelInfo++;
i++;
}
estate->es_result_relation_info = NULL;
***************
*** 860,867 **** 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;
foreach(l, node->returningLists)
{
List *rlist = (List *) lfirst(l);
--- 864,870 ----
/*
* Build a projection for each result rel.
*/
! resultRelInfo = mtstate->resultRelInfo;
foreach(l, node->returningLists)
{
List *rlist = (List *) lfirst(l);
***************
*** 960,966 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
if (junk_filter_needed)
{
! resultRelInfo = estate->es_result_relations;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
--- 963,969 ----
if (junk_filter_needed)
{
! resultRelInfo = mtstate->resultRelInfo;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
***************
*** 989,995 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
else
{
if (operation == CMD_INSERT)
! ExecCheckPlanOutput(estate->es_result_relations->ri_RelationDesc,
subplan->targetlist);
}
}
--- 992,998 ----
else
{
if (operation == CMD_INSERT)
! ExecCheckPlanOutput(mtstate->resultRelInfo->ri_RelationDesc,
subplan->targetlist);
}
}
*** a/src/backend/executor/nodeSubplan.c
--- b/src/backend/executor/nodeSubplan.c
***************
*** 668,673 **** ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
--- 668,675 ----
sstate->planstate = (PlanState *) list_nth(estate->es_subplanstates,
subplan->plan_id - 1);
+ Assert(sstate->planstate != NULL);
+
/* Initialize subexpressions */
sstate->testexpr = ExecInitExpr((Expr *) subplan->testexpr, parent);
sstate->args = (List *) ExecInitExpr((Expr *) subplan->args, parent);
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 84,89 **** _copyPlannedStmt(PlannedStmt *from)
--- 84,90 ----
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,177 **** _copyModifyTable(ModifyTable *from)
--- 173,179 ----
*/
COPY_SCALAR_FIELD(operation);
COPY_NODE_FIELD(resultRelations);
+ COPY_SCALAR_FIELD(resultRelIndex);
COPY_NODE_FIELD(plans);
COPY_NODE_FIELD(returningLists);
COPY_NODE_FIELD(rowMarks);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2383,2388 **** bool
--- 2383,2432 ----
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;
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 327,332 **** _outModifyTable(StringInfo str, ModifyTable *node)
--- 327,333 ----
WRITE_ENUM_FIELD(operation, CmdType);
WRITE_NODE_FIELD(resultRelations);
+ WRITE_INT_FIELD(resultRelIndex);
WRITE_NODE_FIELD(plans);
WRITE_NODE_FIELD(returningLists);
WRITE_NODE_FIELD(rowMarks);
***************
*** 1530,1535 **** _outPlannerGlobal(StringInfo str, PlannerGlobal *node)
--- 1531,1537 ----
WRITE_NODE_FIELD(finalrowmarks);
WRITE_NODE_FIELD(relationOids);
WRITE_NODE_FIELD(invalItems);
+ WRITE_NODE_FIELD(resultRelations);
WRITE_UINT_FIELD(lastPHId);
WRITE_BOOL_FIELD(transientPlan);
}
***************
*** 1544,1550 **** _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);
--- 1546,1551 ----
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3753,3762 **** 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.
*/
--- 3753,3758 ----
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 160,165 **** standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
--- 160,167 ----
glob->finalrowmarks = NIL;
glob->relationOids = NIL;
glob->invalItems = NIL;
+ glob->hasWritableCtes = false;
+ glob->resultRelations = NIL;
glob->lastPHId = 0;
glob->transientPlan = false;
***************
*** 237,245 **** 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->utilityStmt = parse->utilityStmt;
result->intoClause = parse->intoClause;
result->subplans = glob->subplans;
result->rewindPlanIDs = glob->rewindPlanIDs;
result->rowMarks = glob->finalrowmarks;
--- 239,248 ----
result->transientPlan = glob->transientPlan;
result->planTree = top_plan;
result->rtable = glob->finalrtable;
! 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;
***************
*** 541,547 **** subquery_planner(PlannerGlobal *glob, Query *parse,
rowMarks = root->rowMarks;
plan = (Plan *) make_modifytable(parse->commandType,
! copyObject(root->resultRelations),
list_make1(plan),
returningLists,
rowMarks,
--- 544,550 ----
rowMarks = root->rowMarks;
plan = (Plan *) make_modifytable(parse->commandType,
! list_make1_int(parse->resultRelation),
list_make1(plan),
returningLists,
rowMarks,
***************
*** 706,718 **** 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 *rowMarks;
List *tlist;
PlannerInfo subroot;
ListCell *l;
foreach(l, root->append_rel_list)
{
--- 709,721 ----
Query *parse = root->parse;
int parentRTindex = parse->resultRelation;
List *subplans = NIL;
List *returningLists = NIL;
List *rtable = NIL;
List *rowMarks;
List *tlist;
PlannerInfo subroot;
ListCell *l;
+ List *resultRelations = NIL;
foreach(l, root->append_rel_list)
{
***************
*** 772,779 **** inheritance_planner(PlannerInfo *root)
}
}
- root->resultRelations = resultRelations;
-
/* Mark result as unordered (probably unnecessary) */
root->query_pathkeys = NIL;
--- 775,780 ----
***************
*** 783,789 **** inheritance_planner(PlannerInfo *root)
*/
if (subplans == NIL)
{
- root->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,
--- 784,789 ----
***************
*** 818,824 **** 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),
subplans,
returningLists,
rowMarks,
--- 818,824 ----
/* And last, tack on a ModifyTable node to do the UPDATE/DELETE work */
return (Plan *) make_modifytable(parse->commandType,
! resultRelations,
subplans,
returningLists,
rowMarks,
***************
*** 1667,1678 **** grouping_planner(PlannerInfo *root, double tuple_fraction)
count_est);
}
- /* 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.
--- 1667,1672 ----
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
***************
*** 520,525 **** set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset)
--- 520,529 ----
(Plan *) lfirst(l),
rtoffset);
}
+
+ splan->resultRelIndex = list_length(glob->resultRelations);
+ glob->resultRelations = list_concat(glob->resultRelations,
+ splan->resultRelations);
}
break;
case T_Append:
*** a/src/backend/optimizer/plan/subselect.c
--- b/src/backend/optimizer/plan/subselect.c
***************
*** 873,888 **** SS_process_ctes(PlannerInfo *root)
Bitmapset *tmpset;
int paramid;
Param *prm;
/*
! * Ignore CTEs that are not actually referenced anywhere.
*/
! if (cte->cterefcount == 0)
{
/* Make a dummy entry in cte_plan_ids */
root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
continue;
}
/*
* Copy the source Query node. Probably not necessary, but let's keep
--- 873,905 ----
Bitmapset *tmpset;
int paramid;
Param *prm;
+ CmdType cmdType = ((Query *) cte->ctequery)->commandType;
/*
! * Ignore SELECT CTEs that are not actually referenced anywhere.
*/
! 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 (cmdType != CMD_SELECT)
+ {
+ /* We don't know reference counts until here */
+ if (cte->cterefcount > 0 &&
+ ((Query *) cte->ctequery)->returningList == NIL)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("INSERT/UPDATE/DELETE without RETURNING is only allowed inside a non-referenced
CTE")));
+ }
+
+ if (root->query_level > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("INSERT/UPDATE/DELETE inside a CTE is only allowed on the top level")));
+ }
/*
* Copy the source Query node. Probably not necessary, but let's keep
***************
*** 899,904 **** SS_process_ctes(PlannerInfo *root)
--- 916,924 ----
cte->cterecursive, 0.0,
&subroot);
+ 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.
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 7422,7427 **** common_table_expr: name opt_name_list AS select_with_parens
--- 7422,7454 ----
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:
*** a/src/backend/parser/parse_cte.c
--- b/src/backend/parser/parse_cte.c
***************
*** 18,23 ****
--- 18,24 ----
#include "nodes/nodeFuncs.h"
#include "parser/analyze.h"
#include "parser/parse_cte.h"
+ #include "nodes/plannodes.h"
#include "utils/builtins.h"
***************
*** 225,246 **** static void
analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
{
Query *query;
!
! /* Analysis not done already */
! Assert(IsA(cte->ctequery, SelectStmt));
query = parse_sub_analyze(cte->ctequery, pstate, cte, false);
cte->ctequery = (Node *) query;
/*
* 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.)
*/
! if (!IsA(query, Query) ||
! query->commandType != CMD_SELECT ||
! query->utilityStmt != NULL)
! elog(ERROR, "unexpected non-SELECT command in subquery in WITH");
if (query->intoClause)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
--- 226,250 ----
analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
{
Query *query;
! List *cteList;
query = parse_sub_analyze(cte->ctequery, pstate, cte, false);
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.
! * 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.
*/
! Assert(IsA(query, Query) && query->utilityStmt == NULL);
!
if (query->intoClause)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
***************
*** 251,257 **** analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
if (!cte->cterecursive)
{
/* Compute the output column names/types if not done yet */
! analyzeCTETargetList(pstate, cte, query->targetList);
}
else
{
--- 255,261 ----
if (!cte->cterecursive)
{
/* Compute the output column names/types if not done yet */
! analyzeCTETargetList(pstate, cte, cteList);
}
else
{
***************
*** 269,275 **** analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
lctyp = list_head(cte->ctecoltypes);
lctypmod = list_head(cte->ctecoltypmods);
varattno = 0;
! foreach(lctlist, query->targetList)
{
TargetEntry *te = (TargetEntry *) lfirst(lctlist);
Node *texpr;
--- 273,279 ----
lctyp = list_head(cte->ctecoltypes);
lctypmod = list_head(cte->ctecoltypmods);
varattno = 0;
! foreach(lctlist, cteList)
{
TargetEntry *te = (TargetEntry *) lfirst(lctlist);
Node *texpr;
*** a/src/backend/parser/parse_relation.c
--- b/src/backend/parser/parse_relation.c
***************
*** 24,29 ****
--- 24,30 ----
#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"
*** a/src/backend/parser/parse_target.c
--- b/src/backend/parser/parse_target.c
***************
*** 314,323 **** markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
! ste = get_tle_by_resno(((Query *) cte->ctequery)->targetList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
--- 314,333 ----
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
+ List *cteList;
+ Query *ctequery;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
!
! ctequery = (Query *) cte->ctequery;
!
! if (ctequery->commandType == CMD_SELECT)
! cteList = ctequery->targetList;
! else
! cteList = ctequery->returningList;
!
! ste = get_tle_by_resno(cteList,
attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
***************
*** 1345,1355 **** expandRecordVariable(ParseState *pstate, Var *var, int levelsup)
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
! 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);
--- 1355,1374 ----
{
CommonTableExpr *cte = GetCTEForRTE(pstate, rte, netlevelsup);
TargetEntry *ste;
+ List *cteList;
+ Query *ctequery;
/* should be analyzed by now */
Assert(IsA(cte->ctequery, Query));
!
! ctequery = (Query *) cte->ctequery;
!
! if (ctequery->commandType == CMD_SELECT)
! cteList = ctequery->targetList;
! else
! cteList = ctequery->returningList;
!
! ste = get_tle_by_resno(cteList, attnum);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
rte->eref->aliasname, attnum);
***************
*** 1372,1378 **** expandRecordVariable(ParseState *pstate, Var *var, int levelsup)
levelsup++)
pstate = pstate->parentParseState;
mypstate.parentParseState = pstate;
! mypstate.p_rtable = ((Query *) cte->ctequery)->rtable;
/* don't bother filling the rest of the fake pstate */
return expandRecordVariable(&mypstate, (Var *) expr, 0);
--- 1391,1397 ----
levelsup++)
pstate = pstate->parentParseState;
mypstate.parentParseState = pstate;
! mypstate.p_rtable = ctequery->rtable;
/* don't bother filling the rest of the fake pstate */
return expandRecordVariable(&mypstate, (Var *) expr, 0);
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
***************
*** 1632,1637 **** RewriteQuery(Query *parsetree, List *rewrite_events)
--- 1632,1641 ----
bool returning = false;
Query *qual_product = NULL;
List *rewritten = NIL;
+ ListCell *lc;
+ CommonTableExpr *cte;
+ Query *ctequery;
+ List *newstuff;
/*
* If the statement is an update, insert or delete - fire rules on it.
***************
*** 1749,1755 **** RewriteQuery(Query *parsetree, List *rewrite_events)
foreach(n, product_queries)
{
Query *pt = (Query *) lfirst(n);
- List *newstuff;
newstuff = RewriteQuery(pt, rewrite_events);
rewritten = list_concat(rewritten, newstuff);
--- 1753,1758 ----
***************
*** 1804,1809 **** RewriteQuery(Query *parsetree, List *rewrite_events)
--- 1807,1863 ----
}
/*
+ * Rewrite DML statements inside CTEs. If there are any
+ * DO ALSO rules, they are added to the top level (there
+ * won't be any non-top-level CTEs with DML).
+ */
+ foreach(lc, parsetree->cteList)
+ {
+ cte = lfirst(lc);
+
+ ctequery = (Query *) cte->ctequery;
+
+ if (ctequery->commandType == CMD_SELECT)
+ continue;
+
+ newstuff = RewriteQuery(ctequery, NIL);
+
+ if (list_length(newstuff) > 1 &&
+ ctequery->commandType != CMD_INSERT)
+ {
+ /*
+ * For UPDATE and DELETE, the actual query is
+ * at the end of the list.
+ */
+
+ ListCell *lc;
+ int n = 1;
+
+ foreach(lc, newstuff)
+ {
+ /*
+ * If this is the last one, don't add it to the results.
+ * Instead, update the query inside the CTE.
+ */
+ if (n == list_length(newstuff))
+ cte->ctequery = (Node *) lfirst(lc);
+ else
+ rewritten = lcons((void *) lfirst(lc), rewritten);
+
+ n++;
+ }
+
+ }
+ else
+ {
+ /* For INSERT the actual query is the first one. */
+ cte->ctequery = (Node *) linitial(newstuff);
+ rewritten = list_concat(rewritten,
+ list_delete_first(newstuff));
+ }
+ }
+
+ /*
* For INSERTs, the original query is done first; for UPDATE/DELETE, it is
* done last. This is needed because update and delete rule actions might
* not do anything if they are invoked after the update or delete is
*** a/src/backend/tcop/pquery.c
--- b/src/backend/tcop/pquery.c
***************
*** 293,298 **** ChoosePortalStrategy(List *stmts)
--- 293,299 ----
if (pstmt->canSetTag)
{
if (pstmt->commandType == CMD_SELECT &&
+ pstmt->hasWritableCtes == false &&
pstmt->utilityStmt == NULL &&
pstmt->intoClause == NULL)
return PORTAL_ONE_SELECT;
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 3909,3917 **** 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);
if (ste == NULL || ste->resjunk)
elog(ERROR, "subquery %s does not have attribute %d",
--- 3909,3924 ----
}
if (lc != NULL)
{
! 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",
*** a/src/include/executor/executor.h
--- b/src/include/executor/executor.h
***************
*** 160,166 **** extern void ExecutorRewind(QueryDesc *queryDesc);
extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- CmdType operation,
bool doInstrument);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids);
--- 160,165 ----
***************
*** 319,325 **** extern void ExecCloseScanRelation(Relation scanrel);
extern void ExecOpenIndices(ResultRelInfo *resultRelInfo);
extern void ExecCloseIndices(ResultRelInfo *resultRelInfo);
! extern List *ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid,
EState *estate, bool is_vacuum_full);
extern void RegisterExprContextCallback(ExprContext *econtext,
--- 318,325 ----
extern void ExecOpenIndices(ResultRelInfo *resultRelInfo);
extern void ExecCloseIndices(ResultRelInfo *resultRelInfo);
! extern List *ExecInsertIndexTuples(ResultRelInfo *resultRelInfo,
! TupleTableSlot *slot, ItemPointer tupleid,
EState *estate, bool is_vacuum_full);
extern void RegisterExprContextCallback(ExprContext *econtext,
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 1024,1029 **** typedef struct ModifyTableState
--- 1024,1031 ----
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;
EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */
bool fireBSTriggers; /* do we need to fire stmt triggers? */
} ModifyTableState;
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 55,60 **** typedef struct PlannedStmt
--- 55,62 ----
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,170 **** typedef struct ModifyTable
--- 167,173 ----
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 */
List *rowMarks; /* PlanRowMarks (non-locking only) */
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
***************
*** 80,85 **** typedef struct PlannerGlobal
--- 80,89 ----
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 */
bool transientPlan; /* redo plan when TransactionXmin changes? */
***************
*** 142,149 **** 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 */
--- 146,151 ----
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 1026,1028 **** SELECT * FROM t;
--- 1026,1110 ----
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)
+
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
***************
*** 500,502 **** WITH RECURSIVE t(j) AS (
--- 500,537 ----
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 by date: