diff options
Diffstat (limited to 'src')
38 files changed, 1323 insertions, 161 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 5789a39ba3d..e76ce2ceb13 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -995,7 +995,7 @@ ExecuteTruncate(TruncateStmt *stmt) /* * To fire triggers, we'll need an EState as well as a ResultRelInfo for - * each relation. + * each relation. We don't need to call ExecOpenIndices, though. */ estate = CreateExecutorState(); resultRelInfos = (ResultRelInfo *) @@ -1008,7 +1008,6 @@ ExecuteTruncate(TruncateStmt *stmt) InitResultRelInfo(resultRelInfo, rel, 0, /* dummy rangetable index */ - CMD_DELETE, /* don't need any index info */ 0); resultRelInfo++; } diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 1f418e907ea..5576ea259f4 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -419,6 +419,20 @@ DefineView(ViewStmt *stmt, const char *queryString) elog(ERROR, "unexpected parse analysis result"); /* + * Check for unsupported cases. These tests are redundant with ones in + * DefineQueryRewrite(), but that function will complain about a bogus + * ON SELECT rule, and we'd rather the message complain about a view. + */ + if (viewParse->intoClause != NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("views must not contain SELECT INTO"))); + if (viewParse->hasModifyingCTE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("views must not contain data-modifying statements in WITH"))); + + /* * If a list of column names was given, run through and insert these into * the actual query tree. - thomas 2000-03-08 */ diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 68509fbfc6e..845fac1ae1c 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -68,6 +68,7 @@ ExecutorCheckPerms_hook_type ExecutorCheckPerms_hook = NULL; /* decls for local routines only used within this module */ static void InitPlan(QueryDesc *queryDesc, int eflags); +static void ExecPostprocessPlan(EState *estate); static void ExecEndPlan(PlanState *planstate, EState *estate); static void ExecutePlan(EState *estate, PlanState *planstate, CmdType operation, @@ -161,9 +162,13 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags) switch (queryDesc->operation) { case CMD_SELECT: - /* SELECT INTO and SELECT FOR UPDATE/SHARE need to mark tuples */ + /* + * SELECT INTO, SELECT FOR UPDATE/SHARE and modifying CTEs need to + * mark tuples + */ if (queryDesc->plannedstmt->intoClause != NULL || - queryDesc->plannedstmt->rowMarks != NIL) + queryDesc->plannedstmt->rowMarks != NIL || + queryDesc->plannedstmt->hasModifyingCTE) estate->es_output_cid = GetCurrentCommandId(true); break; @@ -307,13 +312,19 @@ standard_ExecutorRun(QueryDesc *queryDesc, * * We provide a function hook variable that lets loadable plugins * get control when ExecutorEnd is called. Such a plugin would - * normally call standard_ExecutorEnd(). + * normally call standard_ExecutorEnd(). Because such hooks expect + * to execute after all plan execution is done, we run + * ExecPostprocessPlan before invoking the hook. * * ---------------------------------------------------------------- */ void ExecutorEnd(QueryDesc *queryDesc) { + /* Let plan nodes do any final processing required */ + ExecPostprocessPlan(queryDesc->estate); + + /* Now close down */ if (ExecutorEnd_hook) (*ExecutorEnd_hook) (queryDesc); else @@ -681,7 +692,6 @@ InitPlan(QueryDesc *queryDesc, int eflags) InitResultRelInfo(resultRelInfo, resultRelation, resultRelationIndex, - operation, estate->es_instrument); resultRelInfo++; } @@ -873,24 +883,18 @@ InitPlan(QueryDesc *queryDesc, int eflags) } /* - * Initialize ResultRelInfo data for one result relation + * Check that a proposed result relation is a legal target for the operation + * + * In most cases parser and/or planner should have noticed this already, but + * let's make sure. In the view case we do need a test here, because if the + * view wasn't rewritten by a rule, it had better have an INSTEAD trigger. */ void -InitResultRelInfo(ResultRelInfo *resultRelInfo, - Relation resultRelationDesc, - Index resultRelationIndex, - CmdType operation, - int instrument_options) +CheckValidResultRel(Relation resultRel, CmdType operation) { - TriggerDesc *trigDesc = resultRelationDesc->trigdesc; + TriggerDesc *trigDesc = resultRel->trigdesc; - /* - * Check valid relkind ... in most cases parser and/or planner should have - * noticed this already, but let's make sure. In the view case we do need - * a test here, because if the view wasn't rewritten by a rule, it had - * better have an INSTEAD trigger. - */ - switch (resultRelationDesc->rd_rel->relkind) + switch (resultRel->rd_rel->relkind) { case RELKIND_RELATION: /* OK */ @@ -899,13 +903,13 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot change sequence \"%s\"", - RelationGetRelationName(resultRelationDesc)))); + RelationGetRelationName(resultRel)))); break; case RELKIND_TOASTVALUE: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot change TOAST relation \"%s\"", - RelationGetRelationName(resultRelationDesc)))); + RelationGetRelationName(resultRel)))); break; case RELKIND_VIEW: switch (operation) @@ -915,7 +919,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("cannot insert into view \"%s\"", - RelationGetRelationName(resultRelationDesc)), + RelationGetRelationName(resultRel)), errhint("You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger."))); break; case CMD_UPDATE: @@ -923,7 +927,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("cannot update view \"%s\"", - RelationGetRelationName(resultRelationDesc)), + RelationGetRelationName(resultRel)), errhint("You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger."))); break; case CMD_DELETE: @@ -931,7 +935,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("cannot delete from view \"%s\"", - RelationGetRelationName(resultRelationDesc)), + RelationGetRelationName(resultRel)), errhint("You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger."))); break; default: @@ -943,17 +947,30 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot change foreign table \"%s\"", - RelationGetRelationName(resultRelationDesc)))); + RelationGetRelationName(resultRel)))); break; default: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot change relation \"%s\"", - RelationGetRelationName(resultRelationDesc)))); + RelationGetRelationName(resultRel)))); break; } +} - /* OK, fill in the node */ +/* + * Initialize ResultRelInfo data for one result relation + * + * Caution: before Postgres 9.1, this function included the relkind checking + * that's now in CheckValidResultRel, and it also did ExecOpenIndices if + * appropriate. Be sure callers cover those needs. + */ +void +InitResultRelInfo(ResultRelInfo *resultRelInfo, + Relation resultRelationDesc, + Index resultRelationIndex, + int instrument_options) +{ MemSet(resultRelInfo, 0, sizeof(ResultRelInfo)); resultRelInfo->type = T_ResultRelInfo; resultRelInfo->ri_RangeTableIndex = resultRelationIndex; @@ -962,7 +979,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_IndexRelationDescs = NULL; resultRelInfo->ri_IndexRelationInfo = NULL; /* make a copy so as not to depend on relcache info not changing... */ - resultRelInfo->ri_TrigDesc = CopyTriggerDesc(trigDesc); + resultRelInfo->ri_TrigDesc = CopyTriggerDesc(resultRelationDesc->trigdesc); if (resultRelInfo->ri_TrigDesc) { int n = resultRelInfo->ri_TrigDesc->numtriggers; @@ -983,16 +1000,6 @@ 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); } /* @@ -1042,26 +1049,29 @@ ExecGetTriggerResultRel(EState *estate, Oid relid) /* * Open the target relation's relcache entry. We assume that an * appropriate lock is still held by the backend from whenever the trigger - * event got queued, so we need take no new lock here. + * event got queued, so we need take no new lock here. Also, we need + * not recheck the relkind, so no need for CheckValidResultRel. */ rel = heap_open(relid, NoLock); /* - * 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. + * Make the new entry in the right context. */ 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); MemoryContextSwitchTo(oldcontext); + /* + * Currently, we don't need any index information in ResultRelInfos used + * only for triggers, so no need to call ExecOpenIndices. + */ + return rInfo; } @@ -1123,6 +1133,54 @@ ExecContextForcesOids(PlanState *planstate, bool *hasoids) } /* ---------------------------------------------------------------- + * ExecPostprocessPlan + * + * Give plan nodes a final chance to execute before shutdown + * ---------------------------------------------------------------- + */ +static void +ExecPostprocessPlan(EState *estate) +{ + MemoryContext oldcontext; + ListCell *lc; + + /* + * Switch into per-query memory context + */ + oldcontext = MemoryContextSwitchTo(estate->es_query_cxt); + + /* + * Make sure nodes run forward. + */ + estate->es_direction = ForwardScanDirection; + + /* + * Run any secondary ModifyTable nodes to completion, in case the main + * query did not fetch all rows from them. (We do this to ensure that + * such nodes have predictable results.) + */ + foreach(lc, estate->es_auxmodifytables) + { + PlanState *ps = (PlanState *) lfirst(lc); + + for (;;) + { + TupleTableSlot *slot; + + /* Reset the per-output-tuple exprcontext each time */ + ResetPerTupleExprContext(estate); + + slot = ExecProcNode(ps); + + if (TupIsNull(slot)) + break; + } + } + + MemoryContextSwitchTo(oldcontext); +} + +/* ---------------------------------------------------------------- * ExecEndPlan * * Cleans up the query plan -- closes files and frees up storage @@ -2026,6 +2084,7 @@ EvalPlanQualStart(EPQState *epqstate, EState *parentestate, Plan *planTree) estate->es_instrument = parentestate->es_instrument; estate->es_select_into = parentestate->es_select_into; estate->es_into_oids = parentestate->es_into_oids; + estate->es_auxmodifytables = NIL; /* * The external param list is simply shared from parent. The internal @@ -2080,7 +2139,11 @@ 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. + * all. (However, if the subplan is headed by a ModifyTable node, then + * it must be a data-modifying CTE, which we will certainly not need to + * re-run, so we can skip initializing it. This is just an efficiency + * hack; it won't skip data-modifying CTEs for which the ModifyTable node + * is not at the top.) */ Assert(estate->es_subplanstates == NIL); foreach(l, parentestate->es_plannedstmt->subplans) @@ -2088,7 +2151,11 @@ EvalPlanQualStart(EPQState *epqstate, EState *parentestate, Plan *planTree) Plan *subplan = (Plan *) lfirst(l); PlanState *subplanstate; - subplanstate = ExecInitNode(subplan, estate, 0); + /* Don't initialize ModifyTable subplans, per comment above */ + if (IsA(subplan, ModifyTable)) + subplanstate = NULL; + else + subplanstate = ExecInitNode(subplan, estate, 0); estate->es_subplanstates = lappend(estate->es_subplanstates, subplanstate); diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index 582c7ca667d..511c74eeafd 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -145,6 +145,8 @@ CreateExecutorState(void) estate->es_subplanstates = NIL; + estate->es_auxmodifytables = NIL; + estate->es_per_tuple_exprcontext = NULL; estate->es_epqTuple = NULL; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 12a5b2a8953..cf32dc56903 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -160,7 +160,8 @@ ExecProcessReturning(ProjectionInfo *projectReturning, static TupleTableSlot * ExecInsert(TupleTableSlot *slot, TupleTableSlot *planSlot, - EState *estate) + EState *estate, + bool canSetTag) { HeapTuple tuple; ResultRelInfo *resultRelInfo; @@ -247,9 +248,12 @@ ExecInsert(TupleTableSlot *slot, estate); } - (estate->es_processed)++; - estate->es_lastoid = newId; - setLastTid(&(tuple->t_self)); + if (canSetTag) + { + (estate->es_processed)++; + estate->es_lastoid = newId; + setLastTid(&(tuple->t_self)); + } /* AFTER ROW INSERT Triggers */ ExecARInsertTriggers(estate, resultRelInfo, tuple, recheckIndexes); @@ -283,7 +287,8 @@ ExecDelete(ItemPointer tupleid, HeapTupleHeader oldtuple, TupleTableSlot *planSlot, EPQState *epqstate, - EState *estate) + EState *estate, + bool canSetTag) { ResultRelInfo *resultRelInfo; Relation resultRelationDesc; @@ -393,7 +398,8 @@ ldelete:; */ } - (estate->es_processed)++; + if (canSetTag) + (estate->es_processed)++; /* AFTER ROW DELETE Triggers */ ExecARDeleteTriggers(estate, resultRelInfo, tupleid); @@ -467,7 +473,8 @@ ExecUpdate(ItemPointer tupleid, TupleTableSlot *slot, TupleTableSlot *planSlot, EPQState *epqstate, - EState *estate) + EState *estate, + bool canSetTag) { HeapTuple tuple; ResultRelInfo *resultRelInfo; @@ -621,7 +628,8 @@ lreplace:; estate); } - (estate->es_processed)++; + if (canSetTag) + (estate->es_processed)++; /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple, @@ -647,16 +655,13 @@ fireBSTriggers(ModifyTableState *node) switch (node->operation) { case CMD_INSERT: - ExecBSInsertTriggers(node->ps.state, - node->ps.state->es_result_relations); + ExecBSInsertTriggers(node->ps.state, node->resultRelInfo); break; case CMD_UPDATE: - ExecBSUpdateTriggers(node->ps.state, - node->ps.state->es_result_relations); + ExecBSUpdateTriggers(node->ps.state, node->resultRelInfo); break; case CMD_DELETE: - ExecBSDeleteTriggers(node->ps.state, - node->ps.state->es_result_relations); + ExecBSDeleteTriggers(node->ps.state, node->resultRelInfo); break; default: elog(ERROR, "unknown operation"); @@ -673,16 +678,13 @@ fireASTriggers(ModifyTableState *node) switch (node->operation) { case CMD_INSERT: - ExecASInsertTriggers(node->ps.state, - node->ps.state->es_result_relations); + ExecASInsertTriggers(node->ps.state, node->resultRelInfo); break; case CMD_UPDATE: - ExecASUpdateTriggers(node->ps.state, - node->ps.state->es_result_relations); + ExecASUpdateTriggers(node->ps.state, node->resultRelInfo); break; case CMD_DELETE: - ExecASDeleteTriggers(node->ps.state, - node->ps.state->es_result_relations); + ExecASDeleteTriggers(node->ps.state, node->resultRelInfo); break; default: elog(ERROR, "unknown operation"); @@ -703,6 +705,8 @@ ExecModifyTable(ModifyTableState *node) { EState *estate = node->ps.state; CmdType operation = node->operation; + ResultRelInfo *saved_resultRelInfo; + ResultRelInfo *resultRelInfo; PlanState *subplanstate; JunkFilter *junkfilter; TupleTableSlot *slot; @@ -712,6 +716,15 @@ ExecModifyTable(ModifyTableState *node) HeapTupleHeader oldtuple = NULL; /* + * If we've already completed processing, don't try to do more. We need + * this test because ExecPostprocessPlan might call us an extra time, and + * our subplan's nodes aren't necessarily robust against being called + * extra times. + */ + if (node->mt_done) + return NULL; + + /* * On first call, fire BEFORE STATEMENT triggers before proceeding. */ if (node->fireBSTriggers) @@ -720,17 +733,21 @@ ExecModifyTable(ModifyTableState *node) node->fireBSTriggers = false; } + /* Preload local variables */ + resultRelInfo = node->resultRelInfo + node->mt_whichplan; + subplanstate = node->mt_plans[node->mt_whichplan]; + junkfilter = resultRelInfo->ri_junkFilter; + /* * 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. + * relation while we are within this ModifyTable node. Even though + * ModifyTable nodes can't be nested statically, they can be nested + * dynamically (since our subplan could include a reference to a modifying + * CTE). So we have to save and restore the caller's value. */ - estate->es_result_relation_info = - estate->es_result_relations + node->mt_whichplan; + saved_resultRelInfo = estate->es_result_relation_info; - /* Preload local variables */ - subplanstate = node->mt_plans[node->mt_whichplan]; - junkfilter = estate->es_result_relation_info->ri_junkFilter; + estate->es_result_relation_info = resultRelInfo; /* * Fetch rows from subplan(s), and execute the required table modification @@ -754,9 +771,10 @@ ExecModifyTable(ModifyTableState *node) node->mt_whichplan++; if (node->mt_whichplan < node->mt_nplans) { - estate->es_result_relation_info++; + resultRelInfo++; subplanstate = node->mt_plans[node->mt_whichplan]; - junkfilter = estate->es_result_relation_info->ri_junkFilter; + junkfilter = resultRelInfo->ri_junkFilter; + estate->es_result_relation_info = resultRelInfo; EvalPlanQualSetPlan(&node->mt_epqstate, subplanstate->plan, node->mt_arowmarks[node->mt_whichplan]); continue; @@ -778,7 +796,7 @@ ExecModifyTable(ModifyTableState *node) Datum datum; bool isNull; - if (estate->es_result_relation_info->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION) + if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_RELATION) { datum = ExecGetJunkAttribute(slot, junkfilter->jf_junkAttNo, @@ -814,15 +832,15 @@ ExecModifyTable(ModifyTableState *node) switch (operation) { case CMD_INSERT: - slot = ExecInsert(slot, planSlot, estate); + slot = ExecInsert(slot, planSlot, estate, node->canSetTag); break; case CMD_UPDATE: slot = ExecUpdate(tupleid, oldtuple, slot, planSlot, - &node->mt_epqstate, estate); + &node->mt_epqstate, estate, node->canSetTag); break; case CMD_DELETE: slot = ExecDelete(tupleid, oldtuple, planSlot, - &node->mt_epqstate, estate); + &node->mt_epqstate, estate, node->canSetTag); break; default: elog(ERROR, "unknown operation"); @@ -835,19 +853,21 @@ ExecModifyTable(ModifyTableState *node) */ if (slot) { - estate->es_result_relation_info = NULL; + estate->es_result_relation_info = saved_resultRelInfo; return slot; } } - /* Reset es_result_relation_info before exiting */ - estate->es_result_relation_info = NULL; + /* Restore es_result_relation_info before exiting */ + estate->es_result_relation_info = saved_resultRelInfo; /* * We're done, but fire AFTER STATEMENT triggers before exiting. */ fireASTriggers(node); + node->mt_done = true; + return NULL; } @@ -861,6 +881,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) ModifyTableState *mtstate; CmdType operation = node->operation; int nplans = list_length(node->plans); + ResultRelInfo *saved_resultRelInfo; ResultRelInfo *resultRelInfo; TupleDesc tupDesc; Plan *subplan; @@ -886,32 +907,59 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->ps.state = estate; mtstate->ps.targetlist = NIL; /* not actually used */ + mtstate->operation = operation; + mtstate->canSetTag = node->canSetTag; + mtstate->mt_done = false; + mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans); + mtstate->resultRelInfo = estate->es_result_relations + node->resultRelIndex; mtstate->mt_arowmarks = (List **) palloc0(sizeof(List *) * nplans); mtstate->mt_nplans = nplans; - mtstate->operation = operation; + /* set up epqstate with dummy subplan data for the moment */ EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, NIL, 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 + * results into the array "mt_plans". This is also a convenient place + * to verify that the proposed target relations are valid and open their + * indexes for insertion of new index entries. 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; + saved_resultRelInfo = estate->es_result_relation_info; + + resultRelInfo = mtstate->resultRelInfo; i = 0; foreach(l, node->plans) { subplan = (Plan *) lfirst(l); + + /* + * Verify result relation is a valid target for the current operation + */ + CheckValidResultRel(resultRelInfo->ri_RelationDesc, operation); + + /* + * 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); + + /* Now init the plan for this result rel */ + estate->es_result_relation_info = resultRelInfo; mtstate->mt_plans[i] = ExecInitNode(subplan, estate, eflags); - estate->es_result_relation_info++; + + resultRelInfo++; i++; } - estate->es_result_relation_info = NULL; + + estate->es_result_relation_info = saved_resultRelInfo; /* * Initialize RETURNING projections if needed. @@ -940,8 +988,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); @@ -1045,7 +1092,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; @@ -1083,7 +1130,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); } } @@ -1096,6 +1143,16 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) if (estate->es_trig_tuple_slot == NULL) estate->es_trig_tuple_slot = ExecInitExtraTupleSlot(estate); + /* + * Lastly, if this is not the primary (canSetTag) ModifyTable node, add it + * to estate->es_auxmodifytables so that it will be run to completion by + * ExecPostprocessPlan. (It'd actually work fine to add the primary + * ModifyTable node too, but there's no need.) + */ + if (!mtstate->canSetTag) + estate->es_auxmodifytables = lappend(estate->es_auxmodifytables, + mtstate); + return mtstate; } diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 04763d44ebb..7a1bc1562fd 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -80,6 +80,7 @@ _copyPlannedStmt(PlannedStmt *from) COPY_SCALAR_FIELD(commandType); COPY_SCALAR_FIELD(hasReturning); + COPY_SCALAR_FIELD(hasModifyingCTE); COPY_SCALAR_FIELD(canSetTag); COPY_SCALAR_FIELD(transientPlan); COPY_NODE_FIELD(planTree); @@ -174,7 +175,9 @@ _copyModifyTable(ModifyTable *from) * copy remainder of node */ COPY_SCALAR_FIELD(operation); + COPY_SCALAR_FIELD(canSetTag); COPY_NODE_FIELD(resultRelations); + COPY_SCALAR_FIELD(resultRelIndex); COPY_NODE_FIELD(plans); COPY_NODE_FIELD(returningLists); COPY_NODE_FIELD(rowMarks); @@ -2384,6 +2387,7 @@ _copyQuery(Query *from) COPY_SCALAR_FIELD(hasSubLinks); COPY_SCALAR_FIELD(hasDistinctOn); COPY_SCALAR_FIELD(hasRecursive); + COPY_SCALAR_FIELD(hasModifyingCTE); COPY_SCALAR_FIELD(hasForUpdate); COPY_NODE_FIELD(cteList); COPY_NODE_FIELD(rtable); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index c896f49ff6f..d43c51e2751 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -893,6 +893,7 @@ _equalQuery(Query *a, Query *b) COMPARE_SCALAR_FIELD(hasSubLinks); COMPARE_SCALAR_FIELD(hasDistinctOn); COMPARE_SCALAR_FIELD(hasRecursive); + COMPARE_SCALAR_FIELD(hasModifyingCTE); COMPARE_SCALAR_FIELD(hasForUpdate); COMPARE_NODE_FIELD(cteList); COMPARE_NODE_FIELD(rtable); diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d4b92429171..c3c5d8e6e5c 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2588,6 +2588,56 @@ 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; + if (walker(stmt->withClause, 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; + if (walker(stmt->withClause, 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; + if (walker(stmt->withClause, context)) + return true; + } + break; case T_SelectStmt: { SelectStmt *stmt = (SelectStmt *) node; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 706b2425cf2..4aae2b33a6d 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -244,6 +244,7 @@ _outPlannedStmt(StringInfo str, PlannedStmt *node) WRITE_ENUM_FIELD(commandType, CmdType); WRITE_BOOL_FIELD(hasReturning); + WRITE_BOOL_FIELD(hasModifyingCTE); WRITE_BOOL_FIELD(canSetTag); WRITE_BOOL_FIELD(transientPlan); WRITE_NODE_FIELD(planTree); @@ -328,7 +329,9 @@ _outModifyTable(StringInfo str, ModifyTable *node) _outPlanInfo(str, (Plan *) node); WRITE_ENUM_FIELD(operation, CmdType); + WRITE_BOOL_FIELD(canSetTag); WRITE_NODE_FIELD(resultRelations); + WRITE_INT_FIELD(resultRelIndex); WRITE_NODE_FIELD(plans); WRITE_NODE_FIELD(returningLists); WRITE_NODE_FIELD(rowMarks); @@ -1639,6 +1642,7 @@ _outPlannerGlobal(StringInfo str, PlannerGlobal *node) WRITE_BITMAPSET_FIELD(rewindPlanIDs); WRITE_NODE_FIELD(finalrtable); WRITE_NODE_FIELD(finalrowmarks); + WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(relationOids); WRITE_NODE_FIELD(invalItems); WRITE_UINT_FIELD(lastPHId); @@ -1657,7 +1661,6 @@ _outPlannerInfo(StringInfo str, PlannerInfo *node) WRITE_UINT_FIELD(query_level); WRITE_NODE_FIELD(join_rel_list); WRITE_INT_FIELD(join_cur_level); - WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(init_plans); WRITE_NODE_FIELD(cte_plan_ids); WRITE_NODE_FIELD(eq_classes); @@ -2163,6 +2166,7 @@ _outQuery(StringInfo str, Query *node) WRITE_BOOL_FIELD(hasSubLinks); WRITE_BOOL_FIELD(hasDistinctOn); WRITE_BOOL_FIELD(hasRecursive); + WRITE_BOOL_FIELD(hasModifyingCTE); WRITE_BOOL_FIELD(hasForUpdate); WRITE_NODE_FIELD(cteList); WRITE_NODE_FIELD(rtable); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index c76884e991f..09c5e25012c 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -203,6 +203,7 @@ _readQuery(void) READ_BOOL_FIELD(hasSubLinks); READ_BOOL_FIELD(hasDistinctOn); READ_BOOL_FIELD(hasRecursive); + READ_BOOL_FIELD(hasModifyingCTE); READ_BOOL_FIELD(hasForUpdate); READ_NODE_FIELD(cteList); READ_NODE_FIELD(rtable); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 4895858df60..8a0135c9a74 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -4284,7 +4284,8 @@ make_result(PlannerInfo *root, * to make it look better sometime. */ ModifyTable * -make_modifytable(CmdType operation, List *resultRelations, +make_modifytable(CmdType operation, bool canSetTag, + List *resultRelations, List *subplans, List *returningLists, List *rowMarks, int epqParam) { @@ -4334,7 +4335,9 @@ make_modifytable(CmdType operation, List *resultRelations, node->plan.targetlist = NIL; node->operation = operation; + node->canSetTag = canSetTag; node->resultRelations = resultRelations; + node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; node->returningLists = returningLists; node->rowMarks = rowMarks; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index ee09673051f..38112f1501b 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -163,6 +163,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) glob->rewindPlanIDs = NULL; glob->finalrtable = NIL; glob->finalrowmarks = NIL; + glob->resultRelations = NIL; glob->relationOids = NIL; glob->invalItems = NIL; glob->lastPHId = 0; @@ -214,6 +215,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) /* final cleanup of the plan */ Assert(glob->finalrtable == NIL); Assert(glob->finalrowmarks == NIL); + Assert(glob->resultRelations == NIL); top_plan = set_plan_references(glob, top_plan, root->parse->rtable, root->rowMarks); @@ -239,11 +241,12 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) result->commandType = parse->commandType; result->hasReturning = (parse->returningList != NIL); + result->hasModifyingCTE = parse->hasModifyingCTE; result->canSetTag = parse->canSetTag; 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->subplans = glob->subplans; @@ -571,7 +574,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse, rowMarks = root->rowMarks; plan = (Plan *) make_modifytable(parse->commandType, - copyObject(root->resultRelations), + parse->canSetTag, + list_make1_int(parse->resultRelation), list_make1(plan), returningLists, rowMarks, @@ -787,7 +791,7 @@ inheritance_planner(PlannerInfo *root) /* Make sure any initplans from this rel get into the outer list */ root->init_plans = list_concat(root->init_plans, subroot.init_plans); - /* Build target-relations list for the executor */ + /* Build list of target-relation RT indexes */ resultRelations = lappend_int(resultRelations, appinfo->child_relid); /* Build list of per-relation RETURNING targetlists */ @@ -803,8 +807,6 @@ inheritance_planner(PlannerInfo *root) } } - root->resultRelations = resultRelations; - /* Mark result as unordered (probably unnecessary) */ root->query_pathkeys = NIL; @@ -814,7 +816,6 @@ 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, @@ -849,7 +850,8 @@ 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), + parse->canSetTag, + resultRelations, subplans, returningLists, rowMarks, @@ -1725,12 +1727,6 @@ 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. diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index b1c181a1cc5..432d6483be1 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -173,8 +173,9 @@ static bool extract_query_dependencies_walker(Node *node, * The return value is normally the same Plan node passed in, but can be * different when the passed-in Plan is a SubqueryScan we decide isn't needed. * - * The flattened rangetable entries are appended to glob->finalrtable, - * and we also append rowmarks entries to glob->finalrowmarks. + * The flattened rangetable entries are appended to glob->finalrtable. + * Also, rowmarks entries are appended to glob->finalrowmarks, and the + * RT indexes of ModifyTable result relations to glob->resultRelations. * Plan dependencies are appended to glob->relationOids (for relations) * and glob->invalItems (for everything else). * @@ -552,6 +553,17 @@ set_plan_refs(PlannerGlobal *glob, Plan *plan, int rtoffset) (Plan *) lfirst(l), rtoffset); } + + /* + * Append this ModifyTable node's final result relation RT + * index(es) to the global list for the plan, and set its + * resultRelIndex to reflect their starting position in the + * global list. + */ + splan->resultRelIndex = list_length(glob->resultRelations); + glob->resultRelations = + list_concat(glob->resultRelations, + list_copy(splan->resultRelations)); } break; case T_Append: diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 96a257f6afa..a9649212f20 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -930,6 +930,7 @@ SS_process_ctes(PlannerInfo *root) foreach(lc, root->parse->cteList) { CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc); + CmdType cmdType = ((Query *) cte->ctequery)->commandType; Query *subquery; Plan *plan; PlannerInfo *subroot; @@ -939,9 +940,9 @@ SS_process_ctes(PlannerInfo *root) Param *prm; /* - * 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); @@ -1332,14 +1333,16 @@ simplify_EXISTS_query(Query *query) { /* * We don't try to simplify at all if the query uses set operations, - * aggregates, HAVING, LIMIT/OFFSET, or FOR UPDATE/SHARE; none of these - * seem likely in normal usage and their possible effects are complex. + * aggregates, modifying CTEs, HAVING, LIMIT/OFFSET, or FOR UPDATE/SHARE; + * none of these seem likely in normal usage and their possible effects + * are complex. */ if (query->commandType != CMD_SELECT || query->intoClause || query->setOperations || query->hasAggs || query->hasWindowFuncs || + query->hasModifyingCTE || query->havingQual || query->limitOffset || query->limitCount || diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 7f28d9df4f5..1d1690d3757 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -288,6 +288,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { qry->hasRecursive = stmt->withClause->recursive; qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } /* set up range table with just the result rel */ @@ -358,6 +359,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) { qry->hasRecursive = stmt->withClause->recursive; qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } /* @@ -853,6 +855,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) { qry->hasRecursive = stmt->withClause->recursive; qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } /* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */ @@ -999,6 +1002,7 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt) { qry->hasRecursive = stmt->withClause->recursive; qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } /* @@ -1220,6 +1224,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) { qry->hasRecursive = stmt->withClause->recursive; qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } /* @@ -1816,6 +1821,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { qry->hasRecursive = stmt->withClause->recursive; qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } qry->resultRelation = setTargetTable(pstate, stmt->relation, @@ -2043,6 +2049,16 @@ transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt) parser_errposition(pstate, exprLocation((Node *) result->intoClause)))); + /* + * We also disallow data-modifying WITH in a cursor. (This could be + * allowed, but the semantics of when the updates occur might be + * surprising.) + */ + if (result->hasModifyingCTE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("DECLARE CURSOR must not contain data-modifying statements in WITH"))); + /* FOR UPDATE and WITH HOLD are not compatible */ if (result->rowMarks != NIL && (stmt->options & CURSOR_OPT_HOLD)) ereport(ERROR, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index cbfacec4495..ee4dbd3c8ff 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -8426,12 +8426,12 @@ cte_list: | cte_list ',' common_table_expr { $$ = lappend($1, $3); } ; -common_table_expr: name opt_name_list AS select_with_parens +common_table_expr: name opt_name_list AS '(' PreparableStmt ')' { CommonTableExpr *n = makeNode(CommonTableExpr); n->ctename = $1; n->aliascolnames = $2; - n->ctequery = $4; + n->ctequery = $5; n->location = @1; $$ = (Node *) n; } diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index d250e0c8598..4c5a6fe0b01 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -458,7 +458,7 @@ transformCTEReference(ParseState *pstate, RangeVar *r, { RangeTblEntry *rte; - rte = addRangeTableEntryForCTE(pstate, cte, levelsup, r->alias, true); + rte = addRangeTableEntryForCTE(pstate, cte, levelsup, r, true); return rte; } diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index 4d3d33eb079..23b72b245b2 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -115,7 +115,7 @@ transformWithClause(ParseState *pstate, WithClause *withClause) * list. Check this right away so we needn't worry later. * * Also, tentatively mark each CTE as non-recursive, and initialize its - * reference count to zero. + * reference count to zero, and set pstate->p_hasModifyingCTE if needed. */ foreach(lc, withClause->ctes) { @@ -136,6 +136,16 @@ transformWithClause(ParseState *pstate, WithClause *withClause) cte->cterecursive = false; cte->cterefcount = 0; + + if (!IsA(cte->ctequery, SelectStmt)) + { + /* must be a data-modifying statement */ + Assert(IsA(cte->ctequery, InsertStmt) || + IsA(cte->ctequery, UpdateStmt) || + IsA(cte->ctequery, DeleteStmt)); + + pstate->p_hasModifyingCTE = true; + } } if (withClause->recursive) @@ -229,20 +239,20 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) Query *query; /* Analysis not done already */ - Assert(IsA(cte->ctequery, SelectStmt)); + Assert(!IsA(cte->ctequery, Query)); 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.) + * Check that we got something reasonable. These first two cases should + * be prevented by the grammar. */ - if (!IsA(query, Query) || - query->commandType != CMD_SELECT || - query->utilityStmt != NULL) - elog(ERROR, "unexpected non-SELECT command in subquery in WITH"); + if (!IsA(query, Query)) + elog(ERROR, "unexpected non-Query statement in WITH"); + if (query->utilityStmt != NULL) + elog(ERROR, "unexpected utility statement in WITH"); + if (query->intoClause) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -250,10 +260,28 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) parser_errposition(pstate, exprLocation((Node *) query->intoClause)))); + /* + * We disallow data-modifying WITH except at the top level of a query, + * because it's not clear when such a modification should be executed. + */ + if (query->commandType != CMD_SELECT && + pstate->parentParseState != NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("WITH clause containing a data-modifying statement must be at the top level"), + parser_errposition(pstate, cte->location))); + + /* + * CTE queries are always marked not canSetTag. (Currently this only + * matters for data-modifying statements, for which the flag will be + * propagated to the ModifyTable plan node.) + */ + query->canSetTag = false; + if (!cte->cterecursive) { /* Compute the output column names/types if not done yet */ - analyzeCTETargetList(pstate, cte, query->targetList); + analyzeCTETargetList(pstate, cte, GetCTETargetList(cte)); } else { @@ -273,7 +301,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) lctypmod = list_head(cte->ctecoltypmods); lccoll = list_head(cte->ctecolcollations); varattno = 0; - foreach(lctlist, query->targetList) + foreach(lctlist, GetCTETargetList(cte)) { TargetEntry *te = (TargetEntry *) lfirst(lctlist); Node *texpr; @@ -613,12 +641,20 @@ checkWellFormedRecursion(CteState *cstate) CommonTableExpr *cte = cstate->items[i].cte; SelectStmt *stmt = (SelectStmt *) cte->ctequery; - Assert(IsA(stmt, SelectStmt)); /* not analyzed yet */ + Assert(!IsA(stmt, Query)); /* not analyzed yet */ /* Ignore items that weren't found to be recursive */ if (!cte->cterecursive) continue; + /* Must be a SELECT statement */ + if (!IsA(stmt, SelectStmt)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_RECURSION), + errmsg("recursive query \"%s\" must not contain data-modifying statements", + cte->ctename), + parser_errposition(cstate->pstate, cte->location))); + /* Must have top-level UNION */ if (stmt->op != SETOP_UNION) ereport(ERROR, diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 033ed411fde..c7000b99153 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -1363,10 +1363,11 @@ RangeTblEntry * addRangeTableEntryForCTE(ParseState *pstate, CommonTableExpr *cte, Index levelsup, - Alias *alias, + RangeVar *rv, bool inFromCl) { RangeTblEntry *rte = makeNode(RangeTblEntry); + Alias *alias = rv->alias; char *refname = alias ? alias->aliasname : cte->ctename; Alias *eref; int numaliases; @@ -1384,6 +1385,24 @@ addRangeTableEntryForCTE(ParseState *pstate, if (!rte->self_reference) cte->cterefcount++; + /* + * We throw error if the CTE is INSERT/UPDATE/DELETE without RETURNING. + * This won't get checked in case of a self-reference, but that's OK + * because data-modifying CTEs aren't allowed to be recursive anyhow. + */ + if (IsA(cte->ctequery, Query)) + { + Query *ctequery = (Query *) cte->ctequery; + + if (ctequery->commandType != CMD_SELECT && + ctequery->returningList == NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("WITH query \"%s\" does not have a RETURNING clause", + cte->ctename), + parser_errposition(pstate, rv->location))); + } + rte->ctecoltypes = cte->ctecoltypes; rte->ctecoltypmods = cte->ctecoltypmods; rte->ctecolcollations = cte->ctecolcollations; diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index e9ace37e2d8..c0eaea71a66 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -324,10 +324,7 @@ 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); + ste = get_tle_by_resno(GetCTETargetList(cte), attnum); if (ste == NULL || ste->resjunk) elog(ERROR, "subquery %s does not have attribute %d", rte->eref->aliasname, attnum); @@ -1415,10 +1412,7 @@ 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); + ste = get_tle_by_resno(GetCTETargetList(cte), 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/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c index fecc4e27fa3..a405dbfe8ee 100644 --- a/src/backend/rewrite/rewriteDefine.c +++ b/src/backend/rewrite/rewriteDefine.c @@ -330,6 +330,14 @@ DefineQueryRewrite(char *rulename, errmsg("rules on SELECT must have action INSTEAD SELECT"))); /* + * ... it cannot contain data-modifying WITH ... + */ + if (query->hasModifyingCTE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("rules on SELECT must not contain data-modifying statements in WITH"))); + + /* * ... there can be no rule qual, ... */ if (event_qual != NULL) diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index c0d25b15c60..87d39174a44 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1801,6 +1801,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) bool returning = false; Query *qual_product = NULL; List *rewritten = NIL; + ListCell *lc1; /* * If the statement is an insert, update, or delete, adjust its targetlist @@ -1981,6 +1982,67 @@ RewriteQuery(Query *parsetree, List *rewrite_events) } /* + * Recursively process any insert/update/delete statements in WITH clauses + */ + foreach(lc1, parsetree->cteList) + { + CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc1); + Query *ctequery = (Query *) cte->ctequery; + List *newstuff; + + Assert(IsA(ctequery, Query)); + + if (ctequery->commandType == CMD_SELECT) + continue; + + newstuff = RewriteQuery(ctequery, rewrite_events); + + /* + * Currently we can only handle unconditional, single-statement DO + * INSTEAD rules correctly; we have to get exactly one Query out of + * the rewrite operation to stuff back into the CTE node. + */ + if (list_length(newstuff) == 1) + { + /* Push the single Query back into the CTE node */ + ctequery = (Query *) linitial(newstuff); + Assert(IsA(ctequery, Query)); + /* WITH queries should never be canSetTag */ + Assert(!ctequery->canSetTag); + cte->ctequery = (Node *) ctequery; + } + else if (newstuff == NIL) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("DO INSTEAD NOTHING rules are not supported for data-modifying statements in WITH"))); + } + else + { + ListCell *lc2; + + /* examine queries to determine which error message to issue */ + foreach(lc2, newstuff) + { + Query *q = (Query *) lfirst(lc2); + + if (q->querySource == QSRC_QUAL_INSTEAD_RULE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("conditional DO INSTEAD rules are not supported for data-modifying statements in WITH"))); + if (q->querySource == QSRC_NON_INSTEAD_RULE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("DO ALSO rules are not supported for data-modifying statements in WITH"))); + } + + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("multi-statement DO INSTEAD rules are not supported for data-modifying statements in WITH"))); + } + } + + /* * 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 @@ -2034,6 +2096,12 @@ QueryRewrite(Query *parsetree) Query *lastInstead; /* + * This function is only applied to top-level original queries + */ + Assert(parsetree->querySource == QSRC_ORIGINAL); + Assert(parsetree->canSetTag); + + /* * Step 1 * * Apply all non-SELECT rules possibly getting 0 or many queries diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 0b108ac1348..e7f240e298f 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -143,8 +143,8 @@ FreeQueryDesc(QueryDesc *qdesc) /* * ProcessQuery - * Execute a single plannable query within a PORTAL_MULTI_QUERY - * or PORTAL_ONE_RETURNING portal + * Execute a single plannable query within a PORTAL_MULTI_QUERY, + * PORTAL_ONE_RETURNING, or PORTAL_ONE_MOD_WITH portal * * plan: the plan tree for the query * sourceText: the source text of the query @@ -263,6 +263,7 @@ ChoosePortalStrategy(List *stmts) * PORTAL_ONE_SELECT and PORTAL_UTIL_SELECT need only consider the * single-statement case, since there are no rewrite rules that can add * auxiliary queries to a SELECT or a utility command. + * PORTAL_ONE_MOD_WITH likewise allows only one top-level statement. */ if (list_length(stmts) == 1) { @@ -277,7 +278,12 @@ ChoosePortalStrategy(List *stmts) if (query->commandType == CMD_SELECT && query->utilityStmt == NULL && query->intoClause == NULL) - return PORTAL_ONE_SELECT; + { + if (query->hasModifyingCTE) + return PORTAL_ONE_MOD_WITH; + else + return PORTAL_ONE_SELECT; + } if (query->commandType == CMD_UTILITY && query->utilityStmt != NULL) { @@ -297,7 +303,12 @@ ChoosePortalStrategy(List *stmts) if (pstmt->commandType == CMD_SELECT && pstmt->utilityStmt == NULL && pstmt->intoClause == NULL) - return PORTAL_ONE_SELECT; + { + if (pstmt->hasModifyingCTE) + return PORTAL_ONE_MOD_WITH; + else + return PORTAL_ONE_SELECT; + } } } else @@ -562,6 +573,7 @@ PortalStart(Portal portal, ParamListInfo params, Snapshot snapshot) break; case PORTAL_ONE_RETURNING: + case PORTAL_ONE_MOD_WITH: /* * We don't start the executor until we are told to run the @@ -572,7 +584,6 @@ PortalStart(Portal portal, ParamListInfo params, Snapshot snapshot) pstmt = (PlannedStmt *) PortalGetPrimaryStmt(portal); Assert(IsA(pstmt, PlannedStmt)); - Assert(pstmt->hasReturning); portal->tupDesc = ExecCleanTypeFromTL(pstmt->planTree->targetlist, false); @@ -780,12 +791,13 @@ PortalRun(Portal portal, long count, bool isTopLevel, { case PORTAL_ONE_SELECT: case PORTAL_ONE_RETURNING: + case PORTAL_ONE_MOD_WITH: case PORTAL_UTIL_SELECT: /* * If we have not yet run the command, do so, storing its - * results in the portal's tuplestore. Do this only for the - * PORTAL_ONE_RETURNING and PORTAL_UTIL_SELECT cases. + * results in the portal's tuplestore. But we don't do that + * for the PORTAL_ONE_SELECT case. */ if (portal->strategy != PORTAL_ONE_SELECT && !portal->holdStore) FillPortalStore(portal, isTopLevel); @@ -879,8 +891,8 @@ PortalRun(Portal portal, long count, bool isTopLevel, /* * PortalRunSelect * Execute a portal's query in PORTAL_ONE_SELECT mode, and also - * when fetching from a completed holdStore in PORTAL_ONE_RETURNING - * and PORTAL_UTIL_SELECT cases. + * when fetching from a completed holdStore in PORTAL_ONE_RETURNING, + * PORTAL_ONE_MOD_WITH, and PORTAL_UTIL_SELECT cases. * * This handles simple N-rows-forward-or-backward cases. For more complex * nonsequential access to a portal, see PortalRunFetch. @@ -1031,7 +1043,8 @@ PortalRunSelect(Portal portal, * FillPortalStore * Run the query and load result tuples into the portal's tuple store. * - * This is used for PORTAL_ONE_RETURNING and PORTAL_UTIL_SELECT cases only. + * This is used for PORTAL_ONE_RETURNING, PORTAL_ONE_MOD_WITH, and + * PORTAL_UTIL_SELECT cases only. */ static void FillPortalStore(Portal portal, bool isTopLevel) @@ -1051,6 +1064,7 @@ FillPortalStore(Portal portal, bool isTopLevel) switch (portal->strategy) { case PORTAL_ONE_RETURNING: + case PORTAL_ONE_MOD_WITH: /* * Run the portal to completion just as for the default @@ -1392,6 +1406,7 @@ PortalRunFetch(Portal portal, break; case PORTAL_ONE_RETURNING: + case PORTAL_ONE_MOD_WITH: case PORTAL_UTIL_SELECT: /* @@ -1455,6 +1470,7 @@ DoPortalRunFetch(Portal portal, Assert(portal->strategy == PORTAL_ONE_SELECT || portal->strategy == PORTAL_ONE_RETURNING || + portal->strategy == PORTAL_ONE_MOD_WITH || portal->strategy == PORTAL_UTIL_SELECT); switch (fdirection) diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 67aa5e2ab63..8e130962469 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -123,6 +123,8 @@ CommandIsReadOnly(Node *parsetree) return false; /* SELECT INTO */ else if (stmt->rowMarks != NIL) return false; /* SELECT FOR UPDATE/SHARE */ + else if (stmt->hasModifyingCTE) + return false; /* data-modifying CTE */ else return true; case CMD_UPDATE: diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index d9b359465a2..025edf08386 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -4138,7 +4138,7 @@ 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, + TargetEntry *ste = get_tle_by_resno(GetCTETargetList(cte), attnum); if (ste == NULL || ste->resjunk) diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index dc911e00aca..949608001ea 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -922,6 +922,7 @@ PlanCacheComputeResultDesc(List *stmt_list) switch (ChoosePortalStrategy(stmt_list)) { case PORTAL_ONE_SELECT: + case PORTAL_ONE_MOD_WITH: node = (Node *) linitial(stmt_list); if (IsA(node, Query)) { diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 0a0544bc38a..106c1eed2fa 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201102222 +#define CATALOG_VERSION_NO 201102251 #endif diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 018b14ac843..2ed54d0a5cc 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -163,10 +163,10 @@ extern void ExecutorEnd(QueryDesc *queryDesc); extern void standard_ExecutorEnd(QueryDesc *queryDesc); extern void ExecutorRewind(QueryDesc *queryDesc); extern bool ExecCheckRTPerms(List *rangeTable, bool ereport_on_violation); +extern void CheckValidResultRel(Relation resultRel, CmdType operation); extern void InitResultRelInfo(ResultRelInfo *resultRelInfo, Relation resultRelationDesc, Index resultRelationIndex, - CmdType operation, int instrument_options); extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid); extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 0a6b829de4f..d9aec4c26a3 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -346,7 +346,7 @@ typedef struct EState /* If query can insert/delete tuples, the command ID to mark them with */ CommandId es_output_cid; - /* Info about target table for insert/update/delete queries: */ + /* Info about target table(s) for insert/update/delete queries: */ ResultRelInfo *es_result_relations; /* array of ResultRelInfos */ int es_num_result_relations; /* length of array */ ResultRelInfo *es_result_relation_info; /* currently active array elt */ @@ -378,6 +378,8 @@ typedef struct EState List *es_subplanstates; /* List of PlanState for SubPlans */ + List *es_auxmodifytables; /* List of secondary ModifyTableStates */ + /* * this ExprContext is for per-output-tuple operations, such as constraint * checks and index-value computations. It will be reset for each output @@ -1041,10 +1043,13 @@ typedef struct ResultState typedef struct ModifyTableState { PlanState ps; /* its first field is NodeTag */ - CmdType operation; + CmdType operation; /* INSERT, UPDATE, or DELETE */ + bool canSetTag; /* do we set the command tag/es_processed? */ + bool mt_done; /* are we done? */ 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) */ + ResultRelInfo *resultRelInfo; /* per-subplan target relations */ List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */ EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks */ bool fireBSTriggers; /* do we need to fire stmt triggers? */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 536c03245e3..824403c69b3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -118,6 +118,7 @@ typedef struct Query bool hasSubLinks; /* has subquery SubLink */ bool hasDistinctOn; /* distinctClause is from DISTINCT ON */ bool hasRecursive; /* WITH RECURSIVE was specified */ + bool hasModifyingCTE; /* has INSERT/UPDATE/DELETE in WITH */ bool hasForUpdate; /* FOR UPDATE or FOR SHARE was specified */ List *cteList; /* WITH list (of CommonTableExpr's) */ @@ -884,7 +885,8 @@ typedef struct CommonTableExpr NodeTag type; char *ctename; /* query name (never qualified) */ List *aliascolnames; /* optional list of column names */ - Node *ctequery; /* subquery (SelectStmt or Query) */ + /* SelectStmt/InsertStmt/etc before parse analysis, Query afterwards: */ + Node *ctequery; /* the CTE's subquery */ int location; /* token location, or -1 if unknown */ /* These fields are set during parse analysis: */ bool cterecursive; /* is this CTE actually recursive? */ @@ -896,6 +898,14 @@ typedef struct CommonTableExpr List *ctecolcollations; /* OID list of column collation OIDs */ } CommonTableExpr; +/* Convenience macro to get the output tlist of a CTE's query */ +#define GetCTETargetList(cte) \ + (AssertMacro(IsA((cte)->ctequery, Query)), \ + ((Query *) (cte)->ctequery)->commandType == CMD_SELECT ? \ + ((Query *) (cte)->ctequery)->targetList : \ + ((Query *) (cte)->ctequery)->returningList) + + /***************************************************************************** * Optimizable Statements *****************************************************************************/ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 90d61256e9c..efc79186f95 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -40,6 +40,8 @@ typedef struct PlannedStmt bool hasReturning; /* is it insert|update|delete RETURNING? */ + bool hasModifyingCTE; /* has insert|update|delete in WITH? */ + bool canSetTag; /* do I set the command result tag? */ bool transientPlan; /* redo plan when TransactionXmin changes? */ @@ -167,7 +169,9 @@ typedef struct ModifyTable { Plan plan; CmdType operation; /* INSERT, UPDATE, or DELETE */ + bool canSetTag; /* do we set the command tag/es_processed? */ List *resultRelations; /* integer list of RT indexes */ + int resultRelIndex; /* index of first resultRel in plan's list */ List *plans; /* plan(s) producing source data */ List *returningLists; /* per-target-table RETURNING tlists */ List *rowMarks; /* PlanRowMarks (non-locking only) */ diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index ab708351ed7..8bcc4006a1a 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -76,6 +76,8 @@ typedef struct PlannerGlobal List *finalrowmarks; /* "flat" list of PlanRowMarks */ + List *resultRelations; /* "flat" list of integer RT indexes */ + List *relationOids; /* OIDs of relations the plan depends on */ List *invalItems; /* other dependencies, as PlanInvalItems */ @@ -154,8 +156,6 @@ typedef struct PlannerInfo List **join_rel_level; /* lists of join-relation RelOptInfos */ int join_cur_level; /* index of list being extended */ - 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/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index 9ddd5c183e3..7e03bc924ed 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -78,8 +78,8 @@ extern SetOp *make_setop(SetOpCmd cmd, SetOpStrategy strategy, Plan *lefttree, long numGroups, double outputRows); extern Result *make_result(PlannerInfo *root, List *tlist, Node *resconstantqual, Plan *subplan); -extern ModifyTable *make_modifytable(CmdType operation, List *resultRelations, - List *subplans, List *returningLists, +extern ModifyTable *make_modifytable(CmdType operation, bool canSetTag, + List *resultRelations, List *subplans, List *returningLists, List *rowMarks, int epqParam); extern bool is_projection_capable_plan(Plan *plan); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index a1511cbe64c..a68f7cf5087 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -103,6 +103,7 @@ struct ParseState bool p_hasAggs; bool p_hasWindowFuncs; bool p_hasSubLinks; + bool p_hasModifyingCTE; bool p_is_insert; bool p_is_update; bool p_locked_from_parent; diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h index 41f482c8df9..50ee4dacd28 100644 --- a/src/include/parser/parse_relation.h +++ b/src/include/parser/parse_relation.h @@ -74,7 +74,7 @@ extern RangeTblEntry *addRangeTableEntryForJoin(ParseState *pstate, extern RangeTblEntry *addRangeTableEntryForCTE(ParseState *pstate, CommonTableExpr *cte, Index levelsup, - Alias *alias, + RangeVar *rv, bool inFromCl); extern bool isLockedRefname(ParseState *pstate, const char *refname); extern void addRTEtoQuery(ParseState *pstate, RangeTblEntry *rte, diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h index 639ccc45469..51e2bc99843 100644 --- a/src/include/utils/portal.h +++ b/src/include/utils/portal.h @@ -71,6 +71,11 @@ * can't cope, and also because we don't want to risk failing to execute * all the auxiliary queries.) * + * PORTAL_ONE_MOD_WITH: the portal contains one single SELECT query, but + * it has data-modifying CTEs. This is currently treated the same as the + * PORTAL_ONE_RETURNING case because of the possibility of needing to fire + * triggers. It may act more like PORTAL_ONE_SELECT in future. + * * PORTAL_UTIL_SELECT: the portal contains a utility statement that returns * a SELECT-like result (for example, EXPLAIN or SHOW). On first execution, * we run the statement and dump its results into the portal tuplestore; @@ -83,6 +88,7 @@ typedef enum PortalStrategy { PORTAL_ONE_SELECT, PORTAL_ONE_RETURNING, + PORTAL_ONE_MOD_WITH, PORTAL_UTIL_SELECT, PORTAL_MULTI_QUERY } PortalStrategy; diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 93b67e3b74d..a82ae137977 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -738,7 +738,7 @@ WITH RECURSIVE (54 rows) -- --- Test WITH attached to a DML statement +-- Test WITH attached to a data-modifying statement -- CREATE TEMPORARY TABLE y (a INTEGER); INSERT INTO y SELECT generate_series(1, 10); @@ -1159,3 +1159,564 @@ SELECT * FROM t; 10 (55 rows) +-- +-- Data-modifying statements in WITH +-- +-- INSERT ... 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) + +SELECT * FROM y; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 + 14 + 15 + 16 + 17 + 18 + 19 + 20 +(20 rows) + +-- UPDATE ... RETURNING +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) + +SELECT * FROM y; + a +---- + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 + 14 + 15 + 16 + 17 + 18 + 19 + 20 + 21 +(20 rows) + +-- DELETE ... RETURNING +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) + +SELECT * FROM y; + a +---- + 11 + 12 + 13 + 14 + 15 + 16 + 17 + 18 + 19 + 20 + 21 +(11 rows) + +-- forward reference +WITH RECURSIVE t AS ( + INSERT INTO y + SELECT a+5 FROM t2 WHERE a > 5 + RETURNING * +), t2 AS ( + UPDATE y SET a=a-11 RETURNING * +) +SELECT * FROM t +UNION ALL +SELECT * FROM t2; + a +---- + 11 + 12 + 13 + 14 + 15 + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(16 rows) + +SELECT * FROM y; + a +---- + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 11 + 7 + 12 + 8 + 13 + 9 + 14 + 10 + 15 +(16 rows) + +-- unconditional DO INSTEAD rule +CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD + INSERT INTO y VALUES(42) RETURNING *; +WITH t AS ( + DELETE FROM y RETURNING * +) +SELECT * FROM t; + a +---- + 42 +(1 row) + +SELECT * FROM y; + a +---- + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 11 + 7 + 12 + 8 + 13 + 9 + 14 + 10 + 15 + 42 +(17 rows) + +DROP RULE y_rule ON y; +-- a truly recursive CTE in the same list +WITH RECURSIVE t(a) AS ( + SELECT 0 + UNION ALL + SELECT a+1 FROM t WHERE a+1 < 5 +), t2 as ( + INSERT INTO y + SELECT * FROM t RETURNING * +) +SELECT * FROM t2 JOIN y USING (a) ORDER BY a; + a +--- + 0 + 1 + 2 + 3 + 4 +(5 rows) + +SELECT * FROM y; + a +---- + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 11 + 7 + 12 + 8 + 13 + 9 + 14 + 10 + 15 + 42 + 0 + 1 + 2 + 3 + 4 +(22 rows) + +-- data-modifying WITH in a modifying statement +WITH t AS ( + DELETE FROM y + WHERE a <= 10 + RETURNING * +) +INSERT INTO y SELECT -a FROM t RETURNING *; + a +----- + 0 + -1 + -2 + -3 + -4 + -5 + -6 + -7 + -8 + -9 + -10 + 0 + -1 + -2 + -3 + -4 +(16 rows) + +SELECT * FROM y; + a +----- + 11 + 12 + 13 + 14 + 15 + 42 + 0 + -1 + -2 + -3 + -4 + -5 + -6 + -7 + -8 + -9 + -10 + 0 + -1 + -2 + -3 + -4 +(22 rows) + +-- check that WITH query is run to completion even if outer query isn't +WITH t AS ( + UPDATE y SET a = a * 100 RETURNING * +) +SELECT * FROM t LIMIT 10; + a +------ + 1100 + 1200 + 1300 + 1400 + 1500 + 4200 + 0 + -100 + -200 + -300 +(10 rows) + +SELECT * FROM y; + a +------- + 1100 + 1200 + 1300 + 1400 + 1500 + 4200 + 0 + -100 + -200 + -300 + -400 + -500 + -600 + -700 + -800 + -900 + -1000 + 0 + -100 + -200 + -300 + -400 +(22 rows) + +-- triggers +TRUNCATE TABLE y; +INSERT INTO y SELECT generate_series(1, 10); +CREATE FUNCTION y_trigger() RETURNS trigger AS $$ +begin + raise notice 'y_trigger: a = %', new.a; + return new; +end; +$$ LANGUAGE plpgsql; +CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW + EXECUTE PROCEDURE y_trigger(); +WITH t AS ( + INSERT INTO y + VALUES + (21), + (22), + (23) + RETURNING * +) +SELECT * FROM t; +NOTICE: y_trigger: a = 21 +NOTICE: y_trigger: a = 22 +NOTICE: y_trigger: a = 23 + a +---- + 21 + 22 + 23 +(3 rows) + +SELECT * FROM y; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 21 + 22 + 23 +(13 rows) + +DROP TRIGGER y_trig ON y; +CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW + EXECUTE PROCEDURE y_trigger(); +WITH t AS ( + INSERT INTO y + VALUES + (31), + (32), + (33) + RETURNING * +) +SELECT * FROM t; +NOTICE: y_trigger: a = 31 +NOTICE: y_trigger: a = 32 +NOTICE: y_trigger: a = 33 + a +---- + 31 + 32 + 33 +(3 rows) + +SELECT * FROM y; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 21 + 22 + 23 + 31 + 32 + 33 +(16 rows) + +DROP TRIGGER y_trig ON y; +CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$ +begin + raise notice 'y_trigger'; + return null; +end; +$$ LANGUAGE plpgsql; +CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT + EXECUTE PROCEDURE y_trigger(); +WITH t AS ( + INSERT INTO y + VALUES + (41), + (42), + (43) + RETURNING * +) +SELECT * FROM t; +NOTICE: y_trigger + a +---- + 41 + 42 + 43 +(3 rows) + +SELECT * FROM y; + a +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 21 + 22 + 23 + 31 + 32 + 33 + 41 + 42 + 43 +(19 rows) + +DROP TRIGGER y_trig ON y; +DROP FUNCTION y_trigger(); +-- error cases +-- data-modifying WITH tries to use its own output +WITH RECURSIVE t AS ( + INSERT INTO y + SELECT * FROM t +) +VALUES(FALSE); +ERROR: recursive query "t" must not contain data-modifying statements +LINE 1: WITH RECURSIVE t AS ( + ^ +-- no RETURNING in a referenced data-modifying WITH +WITH t AS ( + INSERT INTO y VALUES(0) +) +SELECT * FROM t; +ERROR: WITH query "t" does not have a RETURNING clause +LINE 4: SELECT * FROM t; + ^ +-- data-modifying WITH allowed only at the top level +SELECT * FROM ( + WITH t AS (UPDATE y SET a=a+1 RETURNING *) + SELECT * FROM t +) ss; +ERROR: WITH clause containing a data-modifying statement must be at the top level +LINE 2: WITH t AS (UPDATE y SET a=a+1 RETURNING *) + ^ +-- most variants of rules aren't allowed +CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y; +WITH t AS ( + INSERT INTO y VALUES(0) +) +VALUES(FALSE); +ERROR: conditional DO INSTEAD rules are not supported for data-modifying statements in WITH +DROP RULE y_rule ON y; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 1878eb65b23..f5d5ebe1594 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -339,7 +339,7 @@ WITH RECURSIVE SELECT * FROM z; -- --- Test WITH attached to a DML statement +-- Test WITH attached to a data-modifying statement -- CREATE TEMPORARY TABLE y (a INTEGER); @@ -538,3 +538,205 @@ WITH RECURSIVE t(j) AS ( SELECT j+1 FROM t WHERE j < 10 ) SELECT * FROM t; + +-- +-- Data-modifying statements in WITH +-- + +-- INSERT ... RETURNING +WITH t AS ( + INSERT INTO y + VALUES + (11), + (12), + (13), + (14), + (15), + (16), + (17), + (18), + (19), + (20) + RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +-- UPDATE ... RETURNING +WITH t AS ( + UPDATE y + SET a=a+1 + RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +-- DELETE ... RETURNING +WITH t AS ( + DELETE FROM y + WHERE a <= 10 + RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +-- forward reference +WITH RECURSIVE t AS ( + INSERT INTO y + SELECT a+5 FROM t2 WHERE a > 5 + RETURNING * +), t2 AS ( + UPDATE y SET a=a-11 RETURNING * +) +SELECT * FROM t +UNION ALL +SELECT * FROM t2; + +SELECT * FROM y; + +-- unconditional DO INSTEAD rule +CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD + INSERT INTO y VALUES(42) RETURNING *; + +WITH t AS ( + DELETE FROM y RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +DROP RULE y_rule ON y; + +-- a truly recursive CTE in the same list +WITH RECURSIVE t(a) AS ( + SELECT 0 + UNION ALL + SELECT a+1 FROM t WHERE a+1 < 5 +), t2 as ( + INSERT INTO y + SELECT * FROM t RETURNING * +) +SELECT * FROM t2 JOIN y USING (a) ORDER BY a; + +SELECT * FROM y; + +-- data-modifying WITH in a modifying statement +WITH t AS ( + DELETE FROM y + WHERE a <= 10 + RETURNING * +) +INSERT INTO y SELECT -a FROM t RETURNING *; + +SELECT * FROM y; + +-- check that WITH query is run to completion even if outer query isn't +WITH t AS ( + UPDATE y SET a = a * 100 RETURNING * +) +SELECT * FROM t LIMIT 10; + +SELECT * FROM y; + +-- triggers + +TRUNCATE TABLE y; +INSERT INTO y SELECT generate_series(1, 10); + +CREATE FUNCTION y_trigger() RETURNS trigger AS $$ +begin + raise notice 'y_trigger: a = %', new.a; + return new; +end; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER y_trig BEFORE INSERT ON y FOR EACH ROW + EXECUTE PROCEDURE y_trigger(); + +WITH t AS ( + INSERT INTO y + VALUES + (21), + (22), + (23) + RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +DROP TRIGGER y_trig ON y; + +CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH ROW + EXECUTE PROCEDURE y_trigger(); + +WITH t AS ( + INSERT INTO y + VALUES + (31), + (32), + (33) + RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +DROP TRIGGER y_trig ON y; + +CREATE OR REPLACE FUNCTION y_trigger() RETURNS trigger AS $$ +begin + raise notice 'y_trigger'; + return null; +end; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER y_trig AFTER INSERT ON y FOR EACH STATEMENT + EXECUTE PROCEDURE y_trigger(); + +WITH t AS ( + INSERT INTO y + VALUES + (41), + (42), + (43) + RETURNING * +) +SELECT * FROM t; + +SELECT * FROM y; + +DROP TRIGGER y_trig ON y; +DROP FUNCTION y_trigger(); + +-- error cases + +-- data-modifying WITH tries to use its own output +WITH RECURSIVE t AS ( + INSERT INTO y + SELECT * FROM t +) +VALUES(FALSE); + +-- no RETURNING in a referenced data-modifying WITH +WITH t AS ( + INSERT INTO y VALUES(0) +) +SELECT * FROM t; + +-- data-modifying WITH allowed only at the top level +SELECT * FROM ( + WITH t AS (UPDATE y SET a=a+1 RETURNING *) + SELECT * FROM t +) ss; + +-- most variants of rules aren't allowed +CREATE RULE y_rule AS ON INSERT TO y WHERE a=0 DO INSTEAD DELETE FROM y; +WITH t AS ( + INSERT INTO y VALUES(0) +) +VALUES(FALSE); +DROP RULE y_rule ON y; |