Review of Writeable CTE Patch
Started by Merlin Moncurealmost 16 years ago20 messages
Marko,
Submission Review
-----------------
*) patch applies clean to HEADwever
*) applied tests ran ok
*) there is some documentation adjustments in the patch. possibly a
little underweight, but sufficient.
*) A couple of very minor things aside, I think this should be
accepted and passed for 8.5 although it could use another set of eyes
from someone more comfortable with this part of the code.
Usability Review
----------------
*) Works as advertised...'feels right'. Found only one small issue
which Marko was already aware of and had adjusted for.
*) No, we don't already have it. This is maybe the #2 most requested
feature, after in-core replication.
*) Yes it follows community-agreed behavior.
Feature Test
------------
*) No build issues.
*) The feature appears to work. Aside from the supplied tests, I
tested with much larger tables (million records) and tables with
triggers on them, sometimes in wacky combination:
with f as (delete from foo returning *) insert into foo select * from f;
with f as (insert into foo returning *) insert into foo select * from f;
This threw an assertion failure:
with recursive t as (insert into foo select * from t) values(true);
TRAP: FailedAssertion("!(((((Node*)(stmt))->type) == T_SelectStmt))",
File: "parse_cte.c", Line: 606)
Marko was already aware of it and has a fix ready.
*) I tested most of the error conditions and got them to fire. No
unpleasant surprises. The cursor error ("Non-SELECT cursors are not
implemented") is a little misleading. Perhaps it should read
something like "Writeable CTE are not supported in cusor declaration"
Performance Review
------------------
*) Everything ran exactly as it should. Huge updates rewritten as
writeable CTE delete + insert are slightly slower than raw insert but
this is expected.
Coding Review
-------------
*) A lot of the code is sgml/test/grammar changes that should be
relatively uncontroversial. This is a small patch for what it does.
*) Should canSetTag be passed as the first agument to (for example) in
ExecInsert? Is this the proper way to be passing this information?
*) execMain.c Line 1224
There is a blank code comment here. IMO this section needs to be
documented better: the
CommandCounterIncrement is a critical part of how this works.
*) execMain.c Line 2033
The adjusted comment is confusing and seems to contradict itself. I
would have wriiten: initialize them if they are not run in
EvalPlanQual(). Aside: is this an optimization?
*) CopySnapshot was promoted from static. Is this legal/good idea?
Is a wrapper more appropriate?
Architecture Review
-------------------
*) Nothing jumped out at me. The changes are small, so it really
comes down to if they were done properly/right spot.
Review Review
-------------
merlin
Re: Review of Writeable CTE Patch
On Tue, Jan 26, 2010 at 9:13 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
*) Works as advertised...'feels right'. Found only one small issue
which Marko was already aware of and had adjusted for.
[...]
Marko was already aware of it and has a fix ready.
So it sounds like we should expect an updated patch shortly?
...Robert
Re: Review of Writeable CTE Patch
On 2010-01-26 16:54, Robert Haas wrote:
On Tue, Jan 26, 2010 at 9:13 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
*) Works as advertised...'feels right'. Found only one small issue
which Marko was already aware of and had adjusted for.[...]
Marko was already aware of it and has a fix ready.
So it sounds like we should expect an updated patch shortly?
Yes. I'm adding more comments and documentation, expect a patch within
a couple of days.
Regards,
Marko Tiikkaja
Re: Review of Writeable CTE Patch
Merlin Moncure escribi�:
*) CopySnapshot was promoted from static. Is this legal/good idea?
Is a wrapper more appropriate?
Hmm ... I wonder why isn't the patch doing RegisterSnapshot with the
passed snapshot directly -- why is it necessary to create a new copy of
it? (I notice that only one of the arms in that "if" creates a copy;
if that is correct, I think it warrants a comment explaining why).
If the copy is necessary (e.g. because the snapshot must not be modified
externally, and there's actual risk that it is), then maybe it would be
better to create a new function RegisterSnapshotCopy instead?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Re: Review of Writeable CTE Patch
On 2010-01-26 17:11, Alvaro Herrera wrote:
Merlin Moncure escribi�:
*) CopySnapshot was promoted from static. Is this legal/good idea?
Is a wrapper more appropriate?Hmm ... I wonder why isn't the patch doing RegisterSnapshot with the
passed snapshot directly -- why is it necessary to create a new copy of
it? (I notice that only one of the arms in that "if" creates a copy;
if that is correct, I think it warrants a comment explaining why).
Per discussion here:
http://archives.postgresql.org/pgsql-hackers/2009-11/msg01964.php the
executor copies the snapshot if it plans on modifying it. A comment
explaining this might be in order.
If the copy is necessary (e.g. because the snapshot must not be modified
externally, and there's actual risk that it is), then maybe it would be
better to create a new function RegisterSnapshotCopy instead?
Sounds reasonable.
Regards,
Marko Tiikkaja
Re: Review of Writeable CTE Patch
Hi,
Here's an updated patch. This includes the fix mentioned earlier, some
comment improvements and making CopySnapshot() static again. I also
changed all references to this feature to "DML WITH" for consistency.
I'm not sure if we want to keep it, but it should now be easier to
change in the future.
On 2010-01-26 16:13, Merlin Moncure wrote:
Marko was already aware of it and has a fix ready.
This one includes it.
*) I tested most of the error conditions and got them to fire. No
unpleasant surprises. The cursor error ("Non-SELECT cursors are not
implemented") is a little misleading. Perhaps it should read
something like "Writeable CTE are not supported in cusor declaration"
Ok, changed this one.
*) execMain.c Line 1224
There is a blank code comment here. IMO this section needs to be
documented better: the
CommandCounterIncrement is a critical part of how this works.
Added some more comments.
*) execMain.c Line 2033
The adjusted comment is confusing and seems to contradict itself. I
would have wriiten: initialize them if they are not run in
EvalPlanQual(). Aside: is this an optimization?
I tried, but I can't think of a better wording for that comment :-(
This is not really an optimization, just doing the right thing. The
performance difference here is negligible.
*) CopySnapshot was promoted from static. Is this legal/good idea?
Is a wrapper more appropriate?
Added new function RegisterSnapshotCopy() per Alvaro's suggestion.
Thanks a lot for reviewing!
Regards,
Marko Tiikkaja
Attachments:
dmlwith6.patchtext/plain; charset=iso-8859-1; name=dmlwith6.patchDownload
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 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,1588 ----
</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 *
+ )
+ INSERT INTO foo_log_200910
+ SELECT * FROM rows
+ </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/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
*** 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> | (<replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> [ RETURNING...]))
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable class="parameter">with_query_name</replaceable> }
</synopsis>
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
*** 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->hasDmlWith)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DML WITH is not allowed in a cursor declaration")));
+
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
***************
*** 933,939 **** ExecuteTruncate(TruncateStmt *stmt)
InitResultRelInfo(resultRelInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE, /* don't need any index info */
0);
resultRelInfo++;
}
--- 933,938 ----
*** 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 WITH */
+ 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("DML WITH is 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
***************
*** 72,77 **** static void ExecutePlan(EState *estate, PlanState *planstate,
--- 72,78 ----
DestReceiver *dest);
static void ExecCheckRTPerms(List *rangeTable);
static void ExecCheckRTEPerms(RangeTblEntry *rte);
+ static bool ExecTopLevelStmtIsReadOnly(PlannedStmt *stmt);
static void ExecCheckXactReadOnly(PlannedStmt *plannedstmt);
static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate,
Plan *planTree);
***************
*** 152,184 **** standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
palloc0(queryDesc->plannedstmt->nParamExec * sizeof(ParamExecData));
/*
! * If non-read-only query, set the command ID to mark output tuples with
*/
! switch (queryDesc->operation)
{
! case CMD_SELECT:
! /* SELECT INTO and SELECT FOR UPDATE/SHARE need to mark tuples */
! if (queryDesc->plannedstmt->intoClause != NULL ||
! queryDesc->plannedstmt->rowMarks != NIL)
! estate->es_output_cid = GetCurrentCommandId(true);
! break;
!
! case CMD_INSERT:
! case CMD_DELETE:
! case CMD_UPDATE:
! estate->es_output_cid = GetCurrentCommandId(true);
! break;
!
! default:
! elog(ERROR, "unrecognized operation code: %d",
! (int) queryDesc->operation);
! break;
}
- /*
- * Copy other important information into the EState
- */
- estate->es_snapshot = RegisterSnapshot(queryDesc->snapshot);
estate->es_crosscheck_snapshot = RegisterSnapshot(queryDesc->crosscheck_snapshot);
estate->es_instrument = queryDesc->instrument_options;
--- 153,174 ----
palloc0(queryDesc->plannedstmt->nParamExec * sizeof(ParamExecData));
/*
! * If there are DML WITH statements, we always need to use the CID and copy
! * the snapshot. Otherwise we can safely use the snapshot provided to us and
! * determine whether we use the CID or not with ExecTopLevelStmtIsReadOnly().
*/
! if (queryDesc->plannedstmt->hasDmlWith)
{
! estate->es_output_cid = GetCurrentCommandId(true);
! estate->es_snapshot = RegisterSnapshotCopy(queryDesc->snapshot);
! }
! else
! {
! estate->es_output_cid = GetCurrentCommandId(!ExecTopLevelStmtIsReadOnly
! (queryDesc->plannedstmt));
! estate->es_snapshot = RegisterSnapshot(queryDesc->snapshot);
}
estate->es_crosscheck_snapshot = RegisterSnapshot(queryDesc->crosscheck_snapshot);
estate->es_instrument = queryDesc->instrument_options;
***************
*** 566,571 **** ExecCheckRTEPerms(RangeTblEntry *rte)
--- 556,578 ----
}
/*
+ * Is the top level statement read-only?
+ *
+ * Ehm. This is more like "would this statement be read-only
+ * if it didn't have DML WITH in it?"
+ */
+ static bool ExecTopLevelStmtIsReadOnly(PlannedStmt *stmt)
+ {
+ if (stmt->commandType == CMD_SELECT &&
+ stmt->intoClause == NULL &&
+ stmt->rowMarks == NULL)
+ return true;
+ else
+ return false;
+ }
+
+
+ /*
* Check that the query does not imply any writes to non-temp tables.
*/
static void
***************
*** 665,671 **** InitPlan(QueryDesc *queryDesc, int eflags)
InitResultRelInfo(resultRelInfo,
resultRelation,
resultRelationIndex,
- operation,
estate->es_instrument);
resultRelInfo++;
}
--- 672,677 ----
***************
*** 796,801 **** InitPlan(QueryDesc *queryDesc, int eflags)
--- 802,860 ----
*/
planstate = ExecInitNode(plan, estate, eflags);
+ /* Add any DML WITH statements into estate */
+ estate->es_prescanstates = NIL;
+
+ if (plannedstmt->hasDmlWith)
+ {
+ foreach(l, plannedstmt->planTree->initPlan)
+ {
+ SubPlan *sp;
+ int cte_param_id;
+ ParamExecData *prmdata;
+ CteScanState *leader;
+ PlanState *ps;
+
+ sp = (SubPlan *) lfirst(l);
+ if (sp->subLinkType != CTE_SUBLINK)
+ continue;
+
+ /*
+ * Any CTE referenced in the query will have a leader CteScanState. We add
+ * the leader scanstate of those to the list. Not having a leader means the
+ * CTE is not referenced anywhere, so we just add the ModifyTable node. The
+ * executor will ignore its output.
+ */
+
+ cte_param_id = linitial_int(sp->setParam);
+ prmdata = &(estate->es_param_exec_vals[cte_param_id]);
+ leader = (CteScanState *) DatumGetPointer(prmdata->value);
+
+ if (leader)
+ {
+ ps = leader->cteplanstate;
+
+ /* ignore SELECT CTEs */
+ if (!IsA(ps, ModifyTableState))
+ continue;
+
+ estate->es_prescanstates = lappend(estate->es_prescanstates,
+ leader);
+ }
+ else
+ {
+ ps = (PlanState *) list_nth(estate->es_subplanstates,
+ sp->plan_id - 1);
+
+ /* must be DML (see comment above) */
+ Assert(IsA(ps, ModifyTableState));
+
+ estate->es_prescanstates = lappend(estate->es_prescanstates,
+ ps);
+ }
+ }
+ }
+
/*
* Get the tuple descriptor describing the type of tuples to return. (this
* is especially important if we are creating a relation with "SELECT
***************
*** 858,864 **** void
InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- CmdType operation,
int instrument_options)
{
/*
--- 917,922 ----
***************
*** 926,941 **** 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);
}
/*
--- 984,989 ----
***************
*** 991,1005 **** 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);
--- 1039,1051 ----
/*
* 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);
***************
*** 1165,1170 **** ExecutePlan(EState *estate,
--- 1211,1217 ----
{
TupleTableSlot *slot;
long current_tuple_count;
+ ListCell *lc;
/*
* initialize local variables
***************
*** 1176,1181 **** ExecutePlan(EState *estate,
--- 1223,1261 ----
*/
estate->es_direction = direction;
+ /* If there are any DML WITH statements, process those first */
+ foreach(lc, estate->es_prescanstates)
+ {
+ TupleTableSlot *slot;
+ PlanState *ps = (PlanState *) lfirst(lc);
+
+ for (;;)
+ {
+ slot = ExecProcNode(ps);
+ if (TupIsNull(slot))
+ break;
+ }
+
+ /* Need to rewind the CTE */
+ if (!IsA(ps, ModifyTableState))
+ ExecReScan(ps, NULL);
+
+ /*
+ * To avoid different anomalies, we need to bump CID after every DML
+ * statement we process.
+ */
+
+ CommandCounterIncrement();
+
+ /* If this was the last one, don't use a CID unless necessary. */
+ if (!lnext(lc) && ExecTopLevelStmtIsReadOnly(estate->es_plannedstmt))
+ estate->es_output_cid = GetCurrentCommandId(false);
+ else
+ estate->es_output_cid = GetCurrentCommandId(true);
+
+ estate->es_snapshot->curcid = estate->es_output_cid;
+ }
+
/*
* Loop until we've processed the proper number of tuples from the plan.
*/
***************
*** 1957,1963 **** 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)
--- 2037,2044 ----
* 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)
***************
*** 1965,1971 **** 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);
--- 2046,2056 ----
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/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 158,164 **** ExecProcessReturning(ProjectionInfo *projectReturning,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
--- 158,165 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(bool canSetTag,
! TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
***************
*** 240,246 **** ExecInsert(TupleTableSlot *slot,
newId = heap_insert(resultRelationDesc, tuple,
estate->es_output_cid, 0, NULL);
! (estate->es_processed)++;
estate->es_lastoid = newId;
setLastTid(&(tuple->t_self));
--- 241,249 ----
newId = heap_insert(resultRelationDesc, tuple,
estate->es_output_cid, 0, NULL);
! if (canSetTag)
! (estate->es_processed)++;
!
estate->es_lastoid = newId;
setLastTid(&(tuple->t_self));
***************
*** 272,278 **** ExecInsert(TupleTableSlot *slot,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
--- 275,282 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(bool canSetTag,
! ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
***************
*** 354,360 **** ldelete:;
return NULL;
}
! (estate->es_processed)++;
/*
* Note: Normally one would think that we have to delete index tuples
--- 358,365 ----
return NULL;
}
! if (canSetTag)
! (estate->es_processed)++;
/*
* Note: Normally one would think that we have to delete index tuples
***************
*** 415,421 **** ldelete:;
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
--- 420,427 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(bool canSetTag,
! ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
***************
*** 544,550 **** lreplace:;
return NULL;
}
! (estate->es_processed)++;
/*
* Note: instead of having to update the old index tuples associated with
--- 550,557 ----
return NULL;
}
! if (canSetTag)
! (estate->es_processed)++;
/*
* Note: instead of having to update the old index tuples associated with
***************
*** 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");
--- 596,610 ----
{
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");
--- 622,636 ----
{
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)
--- 652,658 ----
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
--- 668,677 ----
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;
}
--- 687,695 ----
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;
}
***************
*** 726,743 **** ExecModifyTable(ModifyTableState *node)
if (operation != CMD_DELETE)
slot = ExecFilterJunk(junkfilter, slot);
}
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:
--- 727,751 ----
if (operation != CMD_DELETE)
slot = ExecFilterJunk(junkfilter, slot);
}
+
+ /*
+ * es_result_relation_info must point to the currently active result
+ * relation. We want it to be NULL whenever we're not within
+ * ModifyTable, though.
+ */
+ estate->es_result_relation_info = resultRelInfo;
switch (operation)
{
case CMD_INSERT:
! slot = ExecInsert(node->canSetTag, slot, planSlot, estate);
break;
case CMD_UPDATE:
! slot = ExecUpdate(node->canSetTag, tupleid, slot, planSlot,
&node->mt_epqstate, estate);
break;
case CMD_DELETE:
! slot = ExecDelete(node->canSetTag, tupleid, planSlot,
&node->mt_epqstate, estate);
break;
default:
***************
*** 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;
--- 813,852 ----
mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans);
mtstate->mt_nplans = nplans;
mtstate->operation = operation;
+ mtstate->canSetTag = node->canSetTag;
+ 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);
--- 883,889 ----
/*
* 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;
--- 982,988 ----
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);
}
}
--- 1011,1017 ----
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(hasDmlWith);
COPY_NODE_FIELD(subplans);
COPY_BITMAPSET_FIELD(rewindPlanIDs);
COPY_NODE_FIELD(rowMarks);
***************
*** 171,177 **** _copyModifyTable(ModifyTable *from)
--- 172,180 ----
* 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);
***************
*** 2260,2265 **** _copyInsertStmt(InsertStmt *from)
--- 2263,2269 ----
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2273,2278 **** _copyDeleteStmt(DeleteStmt *from)
--- 2277,2283 ----
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2287,2292 **** _copyUpdateStmt(UpdateStmt *from)
--- 2292,2298 ----
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 888,893 **** _equalInsertStmt(InsertStmt *a, InsertStmt *b)
--- 888,894 ----
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 899,904 **** _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
--- 900,906 ----
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 911,916 **** _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
--- 913,919 ----
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2394,2399 **** bool
--- 2394,2449 ----
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_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_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_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);
***************
*** 1533,1538 **** _outPlannerGlobal(StringInfo str, PlannerGlobal *node)
--- 1534,1540 ----
WRITE_NODE_FIELD(finalrowmarks);
WRITE_NODE_FIELD(relationOids);
WRITE_NODE_FIELD(invalItems);
+ WRITE_NODE_FIELD(resultRelations);
WRITE_UINT_FIELD(lastPHId);
WRITE_BOOL_FIELD(transientPlan);
}
***************
*** 1548,1554 **** _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);
--- 1550,1555 ----
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3754,3760 **** make_result(PlannerInfo *root,
* to make it look better sometime.
*/
ModifyTable *
! make_modifytable(CmdType operation, List *resultRelations,
List *subplans, List *returningLists,
List *rowMarks, int epqParam)
{
--- 3754,3761 ----
* to make it look better sometime.
*/
ModifyTable *
! make_modifytable(CmdType operation, bool canSetTag,
! List *resultRelations,
List *subplans, List *returningLists,
List *rowMarks, int epqParam)
{
***************
*** 3763,3772 **** 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.
*/
--- 3764,3769 ----
***************
*** 3804,3809 **** make_modifytable(CmdType operation, List *resultRelations,
--- 3801,3807 ----
node->plan.targetlist = NIL;
node->operation = operation;
+ node->canSetTag = canSetTag;
node->resultRelations = resultRelations;
node->plans = subplans;
node->returningLists = returningLists;
*** 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->hasDmlWith = 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->hasDmlWith = glob->hasDmlWith;
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,551 ----
rowMarks = root->rowMarks;
plan = (Plan *) make_modifytable(parse->commandType,
! parse->canSetTag,
! 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)
{
--- 710,722 ----
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;
--- 776,781 ----
***************
*** 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,
--- 785,790 ----
***************
*** 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,
--- 819,826 ----
/* And last, tack on a ModifyTable node to do the UPDATE/DELETE work */
return (Plan *) make_modifytable(parse->commandType,
! parse->canSetTag,
! resultRelations,
subplans,
returningLists,
rowMarks,
***************
*** 1668,1679 **** 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.
--- 1670,1675 ----
*** 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,906 ----
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;
}
+
+ 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("DML WITH without RETURNING is only allowed inside an unreferenced CTE")));
+
+ if (root->query_level > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DML WITH is only allowed at the top level")));
+
+ root->glob->hasDmlWith = true;
+ }
/*
* Copy the source Query node. Probably not necessary, but let's keep
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 290,295 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
--- 290,302 ----
qry->distinctClause = NIL;
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* The USING clause is non-standard SQL syntax, and is equivalent in
* functionality to the FROM list that can be specified for UPDATE. The
***************
*** 342,347 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 349,361 ----
qry->commandType = CMD_INSERT;
pstate->p_is_insert = true;
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
* VALUES list, or general SELECT input. We special-case VALUES, both for
***************
*** 366,373 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
pstate->p_relnamespace = NIL;
sub_varnamespace = pstate->p_varnamespace;
pstate->p_varnamespace = NIL;
- /* There can't be any outer WITH to worry about */
- Assert(pstate->p_ctenamespace == NIL);
}
else
{
--- 380,385 ----
***************
*** 1735,1740 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1747,1759 ----
true,
ACL_UPDATE);
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* the FROM clause is non-standard SQL syntax. We used to be able to do
* this with REPLACE in POSTQUEL so we keep the feature.
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 428,434 **** static TypeName *TableFuncTypeName(List *columns);
%type <boolean> xml_whitespace_option
%type <node> common_table_expr
! %type <with> with_clause
%type <list> cte_list
%type <list> window_clause window_definition_list opt_partition_clause
--- 428,434 ----
%type <boolean> xml_whitespace_option
%type <node> common_table_expr
! %type <with> with_clause opt_with_clause
%type <list> cte_list
%type <list> window_clause window_definition_list opt_partition_clause
***************
*** 7042,7052 **** DeallocateStmt: DEALLOCATE name
*****************************************************************************/
InsertStmt:
! INSERT INTO qualified_name insert_rest returning_clause
{
! $4->relation = $3;
! $4->returningList = $5;
! $$ = (Node *) $4;
}
;
--- 7042,7053 ----
*****************************************************************************/
InsertStmt:
! opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
{
! $5->relation = $4;
! $5->returningList = $6;
! $5->withClause = $1;
! $$ = (Node *) $5;
}
;
***************
*** 7102,7115 **** returning_clause:
*
*****************************************************************************/
! DeleteStmt: DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relation = $3;
! n->usingClause = $4;
! n->whereClause = $5;
! n->returningList = $6;
$$ = (Node *)n;
}
;
--- 7103,7117 ----
*
*****************************************************************************/
! DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relation = $4;
! n->usingClause = $5;
! n->whereClause = $6;
! n->returningList = $7;
! n->withClause = $1;
$$ = (Node *)n;
}
;
***************
*** 7164,7181 **** opt_nowait: NOWAIT { $$ = TRUE; }
*
*****************************************************************************/
! UpdateStmt: UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relation = $2;
! n->targetList = $4;
! n->fromClause = $5;
! n->whereClause = $6;
! n->returningList = $7;
$$ = (Node *)n;
}
;
--- 7166,7184 ----
*
*****************************************************************************/
! UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relation = $3;
! n->targetList = $5;
! n->fromClause = $6;
! n->whereClause = $7;
! n->returningList = $8;
! n->withClause = $1;
$$ = (Node *)n;
}
;
***************
*** 7501,7506 **** with_clause:
--- 7504,7513 ----
}
;
+ opt_with_clause:
+ with_clause { $$ = $1; }
+ | /*EMPTY*/ { $$ = NULL; }
+
cte_list:
common_table_expr { $$ = list_make1($1); }
| cte_list ',' common_table_expr { $$ = lappend($1, $3); }
***************
*** 7515,7520 **** common_table_expr: name opt_name_list AS select_with_parens
--- 7522,7554 ----
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;
***************
*** 595,606 **** checkWellFormedRecursion(CteState *cstate)
CommonTableExpr *cte = cstate->items[i].cte;
SelectStmt *stmt = (SelectStmt *) cte->ctequery;
- Assert(IsA(stmt, SelectStmt)); /* not analyzed yet */
-
/* Ignore items that weren't found to be recursive */
if (!cte->cterecursive)
continue;
/* Must have top-level UNION */
if (stmt->op != SETOP_UNION)
ereport(ERROR,
--- 599,621 ----
CommonTableExpr *cte = cstate->items[i].cte;
SelectStmt *stmt = (SelectStmt *) cte->ctequery;
/* Ignore items that weren't found to be recursive */
if (!cte->cterecursive)
continue;
+ /* Must be a SELECT statement */
+ if (!IsA(stmt, SelectStmt))
+ {
+ Assert(IsA(stmt, InsertStmt) ||
+ IsA(stmt, UpdateStmt) ||
+ IsA(stmt, DeleteStmt)); /* not analyzed yet */
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Recursive DML WITH statements are not supported"),
+ parser_errposition(cstate->pstate, cte->location)));
+ }
+
/* Must have top-level UNION */
if (stmt->op != SETOP_UNION)
ereport(ERROR,
*** 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 *lc1;
+ 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,1868 ----
}
/*
+ * Rewrite DML WITH statements.
+ */
+ foreach(lc1, parsetree->cteList)
+ {
+ cte = lfirst(lc1);
+
+ ctequery = (Query *) cte->ctequery;
+
+ if (ctequery->commandType == CMD_SELECT)
+ continue;
+
+ newstuff = RewriteQuery(ctequery, NIL);
+
+ /* Currently we can only handle unconditional DO INSTEAD rules correctly. */
+ if (list_length(newstuff) > 1)
+ {
+ ListCell *lc2;
+
+ foreach(lc2, newstuff)
+ {
+ QuerySource qsrc = ((Query *) lfirst(lc2))->querySource;
+
+ if (qsrc == QSRC_QUAL_INSTEAD_RULE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Conditional DO INSTEAD rules aren't supported in DML WITH statements")));
+ }
+ else if (qsrc == QSRC_NON_INSTEAD_RULE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO ALSO rules aren't supported in DML WITH statements")));
+ }
+ }
+
+ elog(ERROR, "unknown rewrite result");
+ }
+ else if (list_length(newstuff) == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO INSTEAD NOTHING rules aren't supported in DML WITH statements")));
+ }
+ else
+ {
+ Assert(list_length(newstuff) == 1);
+
+ cte->ctequery = (Node *) linitial(newstuff);
+
+ /* this query won't set the command tag */
+ ((Query *) cte->ctequery)->canSetTag = false;
+ }
+ }
+
+ /*
* 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->hasDmlWith &&
pstmt->utilityStmt == NULL &&
pstmt->intoClause == NULL)
return PORTAL_ONE_SELECT;
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 3980,3988 **** 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",
--- 3980,3995 ----
}
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/backend/utils/time/snapmgr.c
--- b/src/backend/utils/time/snapmgr.c
***************
*** 375,380 **** RegisterSnapshot(Snapshot snapshot)
--- 375,401 ----
}
/*
+ * RegisterSnapshot
+ * Copies a snapshot and registers the copy as being in use by the current
+ * resource owner
+ *
+ * If InvalidSnapshot is passed, it is not registered.
+ */
+ Snapshot
+ RegisterSnapshotCopy(Snapshot snapshot)
+ {
+ Snapshot snap;
+
+ if (snapshot == InvalidSnapshot)
+ return InvalidSnapshot;
+
+ snap = CopySnapshot(snapshot);
+
+ return RegisterSnapshotOnOwner(snap, CurrentResourceOwner);
+ }
+
+
+ /*
* RegisterSnapshotOnOwner
* As above, but use the specified resource owner
*/
*** 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,
int instrument_options);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids);
--- 160,165 ----
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 376,381 **** typedef struct EState
--- 376,382 ----
List *es_exprcontexts; /* List of ExprContexts within EState */
+ List *es_prescanstates; /* List of PlanStates to be scanned before the main plan */
List *es_subplanstates; /* List of PlanState for SubPlans */
/*
***************
*** 1026,1034 **** typedef struct ModifyTableState
--- 1027,1038 ----
{
PlanState ps; /* its first field is NodeTag */
CmdType operation;
+ bool canSetTag; /* do we set the command tag? */
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/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 849,855 **** typedef struct CommonTableExpr
NodeTag type;
char *ctename; /* query name (never qualified) */
List *aliascolnames; /* optional list of column names */
! Node *ctequery; /* subquery (SelectStmt or Query) */
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
--- 849,855 ----
NodeTag type;
char *ctename; /* query name (never qualified) */
List *aliascolnames; /* optional list of column names */
! Node *ctequery; /* subquery (Stmt or Query) */
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
***************
*** 879,884 **** typedef struct InsertStmt
--- 879,885 ----
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} InsertStmt;
/* ----------------------
***************
*** 892,897 **** typedef struct DeleteStmt
--- 893,899 ----
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} DeleteStmt;
/* ----------------------
***************
*** 906,911 **** typedef struct UpdateStmt
--- 908,914 ----
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} UpdateStmt;
/* ----------------------
*** 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 hasDmlWith; /* are there any DML WITH statements? */
+
List *subplans; /* Plan trees for SubPlan expressions */
Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */
***************
*** 164,170 **** typedef struct ModifyTable
--- 166,174 ----
{
Plan plan;
CmdType operation; /* INSERT, UPDATE, or DELETE */
+ bool canSetTag; /* do we set the command tag? */
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 hasDmlWith; /* are there any DML WITH statements? */
+
+ List *resultRelations;/* list of result relations */
+
Index lastPHId; /* highest PlaceHolderVar ID assigned */
bool transientPlan; /* redo plan when TransactionXmin changes? */
***************
*** 152,159 **** 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 */
--- 156,161 ----
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 77,83 **** 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,
List *rowMarks, int epqParam);
extern bool is_projection_capable_plan(Plan *plan);
--- 77,84 ----
long numGroups, double outputRows);
extern Result *make_result(PlannerInfo *root, List *tlist,
Node *resconstantqual, Plan *subplan);
! 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);
*** a/src/include/utils/snapmgr.h
--- b/src/include/utils/snapmgr.h
***************
*** 34,39 **** extern Snapshot GetActiveSnapshot(void);
--- 34,40 ----
extern bool ActiveSnapshotSet(void);
extern Snapshot RegisterSnapshot(Snapshot snapshot);
+ extern Snapshot RegisterSnapshotCopy(Snapshot snapshot);
extern void UnregisterSnapshot(Snapshot snapshot);
extern Snapshot RegisterSnapshotOnOwner(Snapshot snapshot, ResourceOwner owner);
extern void UnregisterSnapshotFromOwner(Snapshot snapshot, ResourceOwner owner);
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 1026,1028 **** SELECT * FROM t;
--- 1026,1159 ----
10
(55 rows)
+ --
+ -- DML WITH
+ --
+ 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)
+
+ WITH t AS (
+ UPDATE y SET a = a-11
+ ), t2 AS (
+ DELETE FROM y WHERE a <= 5
+ )
+ UPDATE y SET a=a+1 RETURNING *;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ (5 rows)
+
+ WITH t AS (
+ UPDATE y SET a=a-100
+ ), t2 AS (
+ UPDATE y SET a=a+94
+ )
+ SELECT * FROM y;
+ a
+ ---
+ 1
+ 2
+ 3
+ 4
+ 5
+ (5 rows)
+
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t2 WHERE a < 10
+ ), t2 AS (
+ UPDATE y SET a=a+6 RETURNING *
+ )
+ SELECT * FROM y;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ 7
+ 8
+ 9
+ (8 rows)
+
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
***************
*** 500,502 **** WITH RECURSIVE t(j) AS (
--- 500,559 ----
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
+
+ --
+ -- DML WITH
+ --
+
+ 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;
+
+ WITH t AS (
+ UPDATE y SET a = a-11
+ ), t2 AS (
+ DELETE FROM y WHERE a <= 5
+ )
+ UPDATE y SET a=a+1 RETURNING *;
+
+ WITH t AS (
+ UPDATE y SET a=a-100
+ ), t2 AS (
+ UPDATE y SET a=a+94
+ )
+ SELECT * FROM y;
+
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t2 WHERE a < 10
+ ), t2 AS (
+ UPDATE y SET a=a+6 RETURNING *
+ )
+ SELECT * FROM y;
Re: Review of Writeable CTE Patch
Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:
Here's an updated patch. This includes the fix mentioned earlier, some
comment improvements and making CopySnapshot() static again. I also
changed all references to this feature to "DML WITH" for consistency.
I'm not sure if we want to keep it, but it should now be easier to
change in the future.
Hi, I'm reviewing the writable CTE patch. The code logic seems to be
pretty good, but I have a couple of comments about error cases:
* Did we have a consensus about user-visible "DML WITH" messages?
The term is used in error messages in many places, for example:
"DML WITH without RETURNING is only allowed inside an unreferenced CTE"
Since we don't use "DML WITH" nor "CTE" in documentation,
I'd like to avoid such technical acronyms in logs if we had better names,
or we should have a section to explain them in docs.
* What can I do to get "Recursive DML WITH statements are not supported"
message? I get syntax errors before I get the message because We don't
support UNIONs with RETURNING queries. Am I missing something?
=# UPDATE tbl SET i = i + 1 WHERE i = 1
UNION ALL
UPDATE tbl SET i = i + 1 WHERE i = 2;
ERROR: syntax error at or near "UNION"
* The patch includes regression tests, but no error cases in it.
More test cases are needed for stupid queries.
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
Re: Review of Writeable CTE Patch
Hi,
On 2010-02-03 11:04 UTC+2, Takahiro Itagaki wrote:
Hi, I'm reviewing the writable CTE patch. The code logic seems to be
pretty good, but I have a couple of comments about error cases:* Did we have a consensus about user-visible "DML WITH" messages?
The term is used in error messages in many places, for example:
"DML WITH without RETURNING is only allowed inside an unreferenced CTE"
Since we don't use "DML WITH" nor "CTE" in documentation,
I'd like to avoid such technical acronyms in logs if we had better names,
or we should have a section to explain them in docs.
We have yet to reach a consensus on the name for this feature. I don't
think we have any really good candidates, but I like "DML WITH" best so far.
* What can I do to get "Recursive DML WITH statements are not supported"
message? I get syntax errors before I get the message because We don't
support UNIONs with RETURNING queries. Am I missing something?=# UPDATE tbl SET i = i + 1 WHERE i = 1
UNION ALL
UPDATE tbl SET i = i + 1 WHERE i = 2;
ERROR: syntax error at or near "UNION"
WITH RECURSIVE t AS (INSERT INTO foo SELECT * FROM t) VALUES(true);
would do that. You can do the same with UPDATE .. FROM and DELETE .. USING.
* The patch includes regression tests, but no error cases in it.
More test cases are needed for stupid queries.
Ok, I'll add these and send an updated patch in a few hours.
Regards,
Marko Tiikkaja
Re: Review of Writeable CTE Patch
Hi,
On 2010-02-03 11:04, Takahiro Itagaki wrote:
* The patch includes regression tests, but no error cases in it.
More test cases are needed for stupid queries.
Here's an updated patch.
Regards,
Marko Tiikkaja
Attachments:
dmlwith7.patchtext/plain; charset=iso-8859-1; name=dmlwith7.patchDownload
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***************
*** 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,1588 ----
</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 *
+ )
+ INSERT INTO foo_log_200910
+ SELECT * FROM rows
+ </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/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
*** 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> | (<replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> [ RETURNING...]))
TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <replaceable class="parameter">with_query_name</replaceable> }
</synopsis>
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 21,26 **** PostgreSQL documentation
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
+ [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
*** 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->hasDmlWith)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DML WITH is not allowed in a cursor declaration")));
+
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
***************
*** 940,946 **** ExecuteTruncate(TruncateStmt *stmt)
InitResultRelInfo(resultRelInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE, /* don't need any index info */
0);
resultRelInfo++;
}
--- 940,945 ----
*** 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 WITH */
+ 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("DML WITH is 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
***************
*** 72,77 **** static void ExecutePlan(EState *estate, PlanState *planstate,
--- 72,78 ----
DestReceiver *dest);
static void ExecCheckRTPerms(List *rangeTable);
static void ExecCheckRTEPerms(RangeTblEntry *rte);
+ static bool ExecTopLevelStmtIsReadOnly(PlannedStmt *stmt);
static void ExecCheckXactReadOnly(PlannedStmt *plannedstmt);
static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate,
Plan *planTree);
***************
*** 152,184 **** standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
palloc0(queryDesc->plannedstmt->nParamExec * sizeof(ParamExecData));
/*
! * If non-read-only query, set the command ID to mark output tuples with
*/
! switch (queryDesc->operation)
{
! case CMD_SELECT:
! /* SELECT INTO and SELECT FOR UPDATE/SHARE need to mark tuples */
! if (queryDesc->plannedstmt->intoClause != NULL ||
! queryDesc->plannedstmt->rowMarks != NIL)
! estate->es_output_cid = GetCurrentCommandId(true);
! break;
!
! case CMD_INSERT:
! case CMD_DELETE:
! case CMD_UPDATE:
! estate->es_output_cid = GetCurrentCommandId(true);
! break;
!
! default:
! elog(ERROR, "unrecognized operation code: %d",
! (int) queryDesc->operation);
! break;
}
- /*
- * Copy other important information into the EState
- */
- estate->es_snapshot = RegisterSnapshot(queryDesc->snapshot);
estate->es_crosscheck_snapshot = RegisterSnapshot(queryDesc->crosscheck_snapshot);
estate->es_instrument = queryDesc->instrument_options;
--- 153,174 ----
palloc0(queryDesc->plannedstmt->nParamExec * sizeof(ParamExecData));
/*
! * If there are DML WITH statements, we always need to use the CID and copy
! * the snapshot. Otherwise we can safely use the snapshot provided to us and
! * determine whether we use the CID or not with ExecTopLevelStmtIsReadOnly().
*/
! if (queryDesc->plannedstmt->hasDmlWith)
{
! estate->es_output_cid = GetCurrentCommandId(true);
! estate->es_snapshot = RegisterSnapshotCopy(queryDesc->snapshot);
! }
! else
! {
! estate->es_output_cid = GetCurrentCommandId(!ExecTopLevelStmtIsReadOnly
! (queryDesc->plannedstmt));
! estate->es_snapshot = RegisterSnapshot(queryDesc->snapshot);
}
estate->es_crosscheck_snapshot = RegisterSnapshot(queryDesc->crosscheck_snapshot);
estate->es_instrument = queryDesc->instrument_options;
***************
*** 566,571 **** ExecCheckRTEPerms(RangeTblEntry *rte)
--- 556,578 ----
}
/*
+ * Is the top level statement read-only?
+ *
+ * Ehm. This is more like "would this statement be read-only
+ * if it didn't have DML WITH in it?"
+ */
+ static bool ExecTopLevelStmtIsReadOnly(PlannedStmt *stmt)
+ {
+ if (stmt->commandType == CMD_SELECT &&
+ stmt->intoClause == NULL &&
+ stmt->rowMarks == NULL)
+ return true;
+ else
+ return false;
+ }
+
+
+ /*
* Check that the query does not imply any writes to non-temp tables.
*/
static void
***************
*** 665,671 **** InitPlan(QueryDesc *queryDesc, int eflags)
InitResultRelInfo(resultRelInfo,
resultRelation,
resultRelationIndex,
- operation,
estate->es_instrument);
resultRelInfo++;
}
--- 672,677 ----
***************
*** 796,801 **** InitPlan(QueryDesc *queryDesc, int eflags)
--- 802,860 ----
*/
planstate = ExecInitNode(plan, estate, eflags);
+ /* Add any DML WITH statements into estate */
+ estate->es_prescanstates = NIL;
+
+ if (plannedstmt->hasDmlWith)
+ {
+ foreach(l, plannedstmt->planTree->initPlan)
+ {
+ SubPlan *sp;
+ int cte_param_id;
+ ParamExecData *prmdata;
+ CteScanState *leader;
+ PlanState *ps;
+
+ sp = (SubPlan *) lfirst(l);
+ if (sp->subLinkType != CTE_SUBLINK)
+ continue;
+
+ /*
+ * Any CTE referenced in the query will have a leader CteScanState. We add
+ * the leader scanstate of those to the list. Not having a leader means the
+ * CTE is not referenced anywhere, so we just add the ModifyTable node. The
+ * executor will ignore its output.
+ */
+
+ cte_param_id = linitial_int(sp->setParam);
+ prmdata = &(estate->es_param_exec_vals[cte_param_id]);
+ leader = (CteScanState *) DatumGetPointer(prmdata->value);
+
+ if (leader)
+ {
+ ps = leader->cteplanstate;
+
+ /* ignore SELECT CTEs */
+ if (!IsA(ps, ModifyTableState))
+ continue;
+
+ estate->es_prescanstates = lappend(estate->es_prescanstates,
+ leader);
+ }
+ else
+ {
+ ps = (PlanState *) list_nth(estate->es_subplanstates,
+ sp->plan_id - 1);
+
+ /* must be DML (see comment above) */
+ Assert(IsA(ps, ModifyTableState));
+
+ estate->es_prescanstates = lappend(estate->es_prescanstates,
+ ps);
+ }
+ }
+ }
+
/*
* Get the tuple descriptor describing the type of tuples to return. (this
* is especially important if we are creating a relation with "SELECT
***************
*** 858,864 **** void
InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- CmdType operation,
int instrument_options)
{
/*
--- 917,922 ----
***************
*** 926,941 **** 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);
}
/*
--- 984,989 ----
***************
*** 991,1005 **** 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);
--- 1039,1051 ----
/*
* 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);
***************
*** 1165,1170 **** ExecutePlan(EState *estate,
--- 1211,1217 ----
{
TupleTableSlot *slot;
long current_tuple_count;
+ ListCell *lc;
/*
* initialize local variables
***************
*** 1176,1181 **** ExecutePlan(EState *estate,
--- 1223,1261 ----
*/
estate->es_direction = direction;
+ /* If there are any DML WITH statements, process those first */
+ foreach(lc, estate->es_prescanstates)
+ {
+ TupleTableSlot *slot;
+ PlanState *ps = (PlanState *) lfirst(lc);
+
+ for (;;)
+ {
+ slot = ExecProcNode(ps);
+ if (TupIsNull(slot))
+ break;
+ }
+
+ /* Need to rewind the CTE */
+ if (!IsA(ps, ModifyTableState))
+ ExecReScan(ps, NULL);
+
+ /*
+ * To avoid different anomalies, we need to bump CID after every DML
+ * statement we process.
+ */
+
+ CommandCounterIncrement();
+
+ /* If this was the last one, don't use a CID unless necessary. */
+ if (!lnext(lc) && ExecTopLevelStmtIsReadOnly(estate->es_plannedstmt))
+ estate->es_output_cid = GetCurrentCommandId(false);
+ else
+ estate->es_output_cid = GetCurrentCommandId(true);
+
+ estate->es_snapshot->curcid = estate->es_output_cid;
+ }
+
/*
* Loop until we've processed the proper number of tuples from the plan.
*/
***************
*** 1957,1963 **** 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)
--- 2037,2044 ----
* 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)
***************
*** 1965,1971 **** 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);
--- 2046,2056 ----
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/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 158,164 **** ExecProcessReturning(ProjectionInfo *projectReturning,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
--- 158,165 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(bool canSetTag,
! TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
***************
*** 240,246 **** ExecInsert(TupleTableSlot *slot,
newId = heap_insert(resultRelationDesc, tuple,
estate->es_output_cid, 0, NULL);
! (estate->es_processed)++;
estate->es_lastoid = newId;
setLastTid(&(tuple->t_self));
--- 241,249 ----
newId = heap_insert(resultRelationDesc, tuple,
estate->es_output_cid, 0, NULL);
! if (canSetTag)
! (estate->es_processed)++;
!
estate->es_lastoid = newId;
setLastTid(&(tuple->t_self));
***************
*** 274,280 **** ExecInsert(TupleTableSlot *slot,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
--- 277,284 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(bool canSetTag,
! ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
***************
*** 356,362 **** ldelete:;
return NULL;
}
! (estate->es_processed)++;
/*
* Note: Normally one would think that we have to delete index tuples
--- 360,367 ----
return NULL;
}
! if (canSetTag)
! (estate->es_processed)++;
/*
* Note: Normally one would think that we have to delete index tuples
***************
*** 417,423 **** ldelete:;
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
--- 422,429 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(bool canSetTag,
! ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
***************
*** 546,552 **** lreplace:;
return NULL;
}
! (estate->es_processed)++;
/*
* Note: instead of having to update the old index tuples associated with
--- 552,559 ----
return NULL;
}
! if (canSetTag)
! (estate->es_processed)++;
/*
* Note: instead of having to update the old index tuples associated with
***************
*** 593,607 **** 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");
--- 600,614 ----
{
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");
***************
*** 619,633 **** 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");
--- 626,640 ----
{
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");
***************
*** 649,654 **** ExecModifyTable(ModifyTableState *node)
--- 656,662 ----
EState *estate = node->ps.state;
CmdType operation = node->operation;
PlanState *subplanstate;
+ ResultRelInfo *resultRelInfo;
JunkFilter *junkfilter;
TupleTableSlot *slot;
TupleTableSlot *planSlot;
***************
*** 664,680 **** 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
--- 672,681 ----
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
***************
*** 690,698 **** 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;
}
--- 691,699 ----
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,747 **** ExecModifyTable(ModifyTableState *node)
if (operation != CMD_DELETE)
slot = ExecFilterJunk(junkfilter, slot);
}
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:
--- 731,755 ----
if (operation != CMD_DELETE)
slot = ExecFilterJunk(junkfilter, slot);
}
+
+ /*
+ * es_result_relation_info must point to the currently active result
+ * relation. We want it to be NULL whenever we're not within
+ * ModifyTable, though.
+ */
+ estate->es_result_relation_info = resultRelInfo;
switch (operation)
{
case CMD_INSERT:
! slot = ExecInsert(node->canSetTag, slot, planSlot, estate);
break;
case CMD_UPDATE:
! slot = ExecUpdate(node->canSetTag, tupleid, slot, planSlot,
&node->mt_epqstate, estate);
break;
case CMD_DELETE:
! slot = ExecDelete(node->canSetTag, tupleid, planSlot,
&node->mt_epqstate, estate);
break;
default:
***************
*** 809,833 **** 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;
--- 817,856 ----
mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans);
mtstate->mt_nplans = nplans;
mtstate->operation = operation;
+ mtstate->canSetTag = node->canSetTag;
+ 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;
***************
*** 864,871 **** 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);
--- 887,893 ----
/*
* Build a projection for each result rel.
*/
! resultRelInfo = mtstate->resultRelInfo;
foreach(l, node->returningLists)
{
List *rlist = (List *) lfirst(l);
***************
*** 964,970 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
if (junk_filter_needed)
{
! resultRelInfo = estate->es_result_relations;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
--- 986,992 ----
if (junk_filter_needed)
{
! resultRelInfo = mtstate->resultRelInfo;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
***************
*** 993,999 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
else
{
if (operation == CMD_INSERT)
! ExecCheckPlanOutput(estate->es_result_relations->ri_RelationDesc,
subplan->targetlist);
}
}
--- 1015,1021 ----
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(hasDmlWith);
COPY_NODE_FIELD(subplans);
COPY_BITMAPSET_FIELD(rewindPlanIDs);
COPY_NODE_FIELD(rowMarks);
***************
*** 171,177 **** _copyModifyTable(ModifyTable *from)
--- 172,180 ----
* 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);
***************
*** 2260,2265 **** _copyInsertStmt(InsertStmt *from)
--- 2263,2269 ----
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2273,2278 **** _copyDeleteStmt(DeleteStmt *from)
--- 2277,2283 ----
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2287,2292 **** _copyUpdateStmt(UpdateStmt *from)
--- 2292,2298 ----
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 888,893 **** _equalInsertStmt(InsertStmt *a, InsertStmt *b)
--- 888,894 ----
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 899,904 **** _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
--- 900,906 ----
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 911,916 **** _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
--- 913,919 ----
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2394,2399 **** bool
--- 2394,2449 ----
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_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_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_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);
***************
*** 1533,1538 **** _outPlannerGlobal(StringInfo str, PlannerGlobal *node)
--- 1534,1540 ----
WRITE_NODE_FIELD(finalrowmarks);
WRITE_NODE_FIELD(relationOids);
WRITE_NODE_FIELD(invalItems);
+ WRITE_NODE_FIELD(resultRelations);
WRITE_UINT_FIELD(lastPHId);
WRITE_BOOL_FIELD(transientPlan);
}
***************
*** 1548,1554 **** _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);
--- 1550,1555 ----
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3754,3760 **** make_result(PlannerInfo *root,
* to make it look better sometime.
*/
ModifyTable *
! make_modifytable(CmdType operation, List *resultRelations,
List *subplans, List *returningLists,
List *rowMarks, int epqParam)
{
--- 3754,3761 ----
* to make it look better sometime.
*/
ModifyTable *
! make_modifytable(CmdType operation, bool canSetTag,
! List *resultRelations,
List *subplans, List *returningLists,
List *rowMarks, int epqParam)
{
***************
*** 3763,3772 **** 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.
*/
--- 3764,3769 ----
***************
*** 3804,3809 **** make_modifytable(CmdType operation, List *resultRelations,
--- 3801,3807 ----
node->plan.targetlist = NIL;
node->operation = operation;
+ node->canSetTag = canSetTag;
node->resultRelations = resultRelations;
node->plans = subplans;
node->returningLists = returningLists;
*** 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->hasDmlWith = 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->hasDmlWith = glob->hasDmlWith;
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,551 ----
rowMarks = root->rowMarks;
plan = (Plan *) make_modifytable(parse->commandType,
! parse->canSetTag,
! 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)
{
--- 710,722 ----
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;
--- 776,781 ----
***************
*** 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,
--- 785,790 ----
***************
*** 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,
--- 819,826 ----
/* And last, tack on a ModifyTable node to do the UPDATE/DELETE work */
return (Plan *) make_modifytable(parse->commandType,
! parse->canSetTag,
! resultRelations,
subplans,
returningLists,
rowMarks,
***************
*** 1668,1679 **** 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.
--- 1670,1675 ----
*** 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,906 ----
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;
}
+
+ 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("DML WITH without RETURNING is only allowed inside an unreferenced CTE")));
+
+ if (root->query_level > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DML WITH is only allowed at the top level")));
+
+ root->glob->hasDmlWith = true;
+ }
/*
* Copy the source Query node. Probably not necessary, but let's keep
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 290,295 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
--- 290,302 ----
qry->distinctClause = NIL;
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* The USING clause is non-standard SQL syntax, and is equivalent in
* functionality to the FROM list that can be specified for UPDATE. The
***************
*** 342,347 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 349,361 ----
qry->commandType = CMD_INSERT;
pstate->p_is_insert = true;
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
* VALUES list, or general SELECT input. We special-case VALUES, both for
***************
*** 366,373 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
pstate->p_relnamespace = NIL;
sub_varnamespace = pstate->p_varnamespace;
pstate->p_varnamespace = NIL;
- /* There can't be any outer WITH to worry about */
- Assert(pstate->p_ctenamespace == NIL);
}
else
{
--- 380,385 ----
***************
*** 1735,1740 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1747,1759 ----
true,
ACL_UPDATE);
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* the FROM clause is non-standard SQL syntax. We used to be able to do
* this with REPLACE in POSTQUEL so we keep the feature.
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 429,435 **** static TypeName *TableFuncTypeName(List *columns);
%type <boolean> xml_whitespace_option
%type <node> common_table_expr
! %type <with> with_clause
%type <list> cte_list
%type <list> window_clause window_definition_list opt_partition_clause
--- 429,435 ----
%type <boolean> xml_whitespace_option
%type <node> common_table_expr
! %type <with> with_clause opt_with_clause
%type <list> cte_list
%type <list> window_clause window_definition_list opt_partition_clause
***************
*** 7072,7082 **** DeallocateStmt: DEALLOCATE name
*****************************************************************************/
InsertStmt:
! INSERT INTO qualified_name insert_rest returning_clause
{
! $4->relation = $3;
! $4->returningList = $5;
! $$ = (Node *) $4;
}
;
--- 7072,7083 ----
*****************************************************************************/
InsertStmt:
! opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
{
! $5->relation = $4;
! $5->returningList = $6;
! $5->withClause = $1;
! $$ = (Node *) $5;
}
;
***************
*** 7132,7145 **** returning_clause:
*
*****************************************************************************/
! DeleteStmt: DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relation = $3;
! n->usingClause = $4;
! n->whereClause = $5;
! n->returningList = $6;
$$ = (Node *)n;
}
;
--- 7133,7147 ----
*
*****************************************************************************/
! DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relation = $4;
! n->usingClause = $5;
! n->whereClause = $6;
! n->returningList = $7;
! n->withClause = $1;
$$ = (Node *)n;
}
;
***************
*** 7194,7211 **** opt_nowait: NOWAIT { $$ = TRUE; }
*
*****************************************************************************/
! UpdateStmt: UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relation = $2;
! n->targetList = $4;
! n->fromClause = $5;
! n->whereClause = $6;
! n->returningList = $7;
$$ = (Node *)n;
}
;
--- 7196,7214 ----
*
*****************************************************************************/
! UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relation = $3;
! n->targetList = $5;
! n->fromClause = $6;
! n->whereClause = $7;
! n->returningList = $8;
! n->withClause = $1;
$$ = (Node *)n;
}
;
***************
*** 7531,7536 **** with_clause:
--- 7534,7543 ----
}
;
+ opt_with_clause:
+ with_clause { $$ = $1; }
+ | /*EMPTY*/ { $$ = NULL; }
+
cte_list:
common_table_expr { $$ = list_make1($1); }
| cte_list ',' common_table_expr { $$ = lappend($1, $3); }
***************
*** 7545,7550 **** common_table_expr: name opt_name_list AS select_with_parens
--- 7552,7584 ----
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;
***************
*** 595,606 **** checkWellFormedRecursion(CteState *cstate)
CommonTableExpr *cte = cstate->items[i].cte;
SelectStmt *stmt = (SelectStmt *) cte->ctequery;
- Assert(IsA(stmt, SelectStmt)); /* not analyzed yet */
-
/* Ignore items that weren't found to be recursive */
if (!cte->cterecursive)
continue;
/* Must have top-level UNION */
if (stmt->op != SETOP_UNION)
ereport(ERROR,
--- 599,621 ----
CommonTableExpr *cte = cstate->items[i].cte;
SelectStmt *stmt = (SelectStmt *) cte->ctequery;
/* Ignore items that weren't found to be recursive */
if (!cte->cterecursive)
continue;
+ /* Must be a SELECT statement */
+ if (!IsA(stmt, SelectStmt))
+ {
+ Assert(IsA(stmt, InsertStmt) ||
+ IsA(stmt, UpdateStmt) ||
+ IsA(stmt, DeleteStmt)); /* not analyzed yet */
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Recursive DML WITH statements are not supported"),
+ parser_errposition(cstate->pstate, cte->location)));
+ }
+
/* Must have top-level UNION */
if (stmt->op != SETOP_UNION)
ereport(ERROR,
*** 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 *lc1;
+ 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,1868 ----
}
/*
+ * Rewrite DML WITH statements.
+ */
+ foreach(lc1, parsetree->cteList)
+ {
+ cte = lfirst(lc1);
+
+ ctequery = (Query *) cte->ctequery;
+
+ if (ctequery->commandType == CMD_SELECT)
+ continue;
+
+ newstuff = RewriteQuery(ctequery, NIL);
+
+ /* Currently we can only handle unconditional DO INSTEAD rules correctly. */
+ if (list_length(newstuff) > 1)
+ {
+ ListCell *lc2;
+
+ foreach(lc2, newstuff)
+ {
+ QuerySource qsrc = ((Query *) lfirst(lc2))->querySource;
+
+ if (qsrc == QSRC_QUAL_INSTEAD_RULE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Conditional DO INSTEAD rules aren't supported in DML WITH statements")));
+ }
+ else if (qsrc == QSRC_NON_INSTEAD_RULE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO ALSO rules aren't supported in DML WITH statements")));
+ }
+ }
+
+ elog(ERROR, "unknown rewrite result");
+ }
+ else if (list_length(newstuff) == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO INSTEAD NOTHING rules aren't supported in DML WITH statements")));
+ }
+ else
+ {
+ Assert(list_length(newstuff) == 1);
+
+ cte->ctequery = (Node *) linitial(newstuff);
+
+ /* this query won't set the command tag */
+ ((Query *) cte->ctequery)->canSetTag = false;
+ }
+ }
+
+ /*
* 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->hasDmlWith &&
pstmt->utilityStmt == NULL &&
pstmt->intoClause == NULL)
return PORTAL_ONE_SELECT;
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 3980,3988 **** 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",
--- 3980,3995 ----
}
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/backend/utils/time/snapmgr.c
--- b/src/backend/utils/time/snapmgr.c
***************
*** 375,380 **** RegisterSnapshot(Snapshot snapshot)
--- 375,401 ----
}
/*
+ * RegisterSnapshot
+ * Copies a snapshot and registers the copy as being in use by the current
+ * resource owner
+ *
+ * If InvalidSnapshot is passed, it is not registered.
+ */
+ Snapshot
+ RegisterSnapshotCopy(Snapshot snapshot)
+ {
+ Snapshot snap;
+
+ if (snapshot == InvalidSnapshot)
+ return InvalidSnapshot;
+
+ snap = CopySnapshot(snapshot);
+
+ return RegisterSnapshotOnOwner(snap, CurrentResourceOwner);
+ }
+
+
+ /*
* RegisterSnapshotOnOwner
* As above, but use the specified resource owner
*/
*** 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,
int instrument_options);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids);
--- 160,165 ----
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 376,381 **** typedef struct EState
--- 376,382 ----
List *es_exprcontexts; /* List of ExprContexts within EState */
+ List *es_prescanstates; /* List of PlanStates to be scanned before the main plan */
List *es_subplanstates; /* List of PlanState for SubPlans */
/*
***************
*** 1026,1034 **** typedef struct ModifyTableState
--- 1027,1038 ----
{
PlanState ps; /* its first field is NodeTag */
CmdType operation;
+ bool canSetTag; /* do we set the command tag? */
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/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 850,856 **** typedef struct CommonTableExpr
NodeTag type;
char *ctename; /* query name (never qualified) */
List *aliascolnames; /* optional list of column names */
! Node *ctequery; /* subquery (SelectStmt or Query) */
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
--- 850,856 ----
NodeTag type;
char *ctename; /* query name (never qualified) */
List *aliascolnames; /* optional list of column names */
! Node *ctequery; /* subquery (Stmt or Query) */
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
***************
*** 880,885 **** typedef struct InsertStmt
--- 880,886 ----
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} InsertStmt;
/* ----------------------
***************
*** 893,898 **** typedef struct DeleteStmt
--- 894,900 ----
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} DeleteStmt;
/* ----------------------
***************
*** 907,912 **** typedef struct UpdateStmt
--- 909,915 ----
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} UpdateStmt;
/* ----------------------
*** 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 hasDmlWith; /* are there any DML WITH statements? */
+
List *subplans; /* Plan trees for SubPlan expressions */
Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */
***************
*** 164,170 **** typedef struct ModifyTable
--- 166,174 ----
{
Plan plan;
CmdType operation; /* INSERT, UPDATE, or DELETE */
+ bool canSetTag; /* do we set the command tag? */
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 hasDmlWith; /* are there any DML WITH statements? */
+
+ List *resultRelations;/* list of result relations */
+
Index lastPHId; /* highest PlaceHolderVar ID assigned */
bool transientPlan; /* redo plan when TransactionXmin changes? */
***************
*** 152,159 **** 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 */
--- 156,161 ----
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 77,83 **** 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,
List *rowMarks, int epqParam);
extern bool is_projection_capable_plan(Plan *plan);
--- 77,84 ----
long numGroups, double outputRows);
extern Result *make_result(PlannerInfo *root, List *tlist,
Node *resconstantqual, Plan *subplan);
! 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);
*** a/src/include/utils/snapmgr.h
--- b/src/include/utils/snapmgr.h
***************
*** 34,39 **** extern Snapshot GetActiveSnapshot(void);
--- 34,40 ----
extern bool ActiveSnapshotSet(void);
extern Snapshot RegisterSnapshot(Snapshot snapshot);
+ extern Snapshot RegisterSnapshotCopy(Snapshot snapshot);
extern void UnregisterSnapshot(Snapshot snapshot);
extern Snapshot RegisterSnapshotOnOwner(Snapshot snapshot, ResourceOwner owner);
extern void UnregisterSnapshotFromOwner(Snapshot snapshot, ResourceOwner owner);
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 1026,1028 **** SELECT * FROM t;
--- 1026,1241 ----
10
(55 rows)
+ --
+ -- DML 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)
+
+ -- 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)
+
+ -- 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)
+
+ -- the top level statement can update rows updated by DML WITH
+ WITH t AS (
+ UPDATE y SET a = a-11
+ ), t2 AS (
+ DELETE FROM y WHERE a <= 5
+ )
+ UPDATE y SET a=a+1 RETURNING *;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ (5 rows)
+
+ -- t2 can update tuples updated by t
+ WITH t AS (
+ UPDATE y SET a=a-100
+ ), t2 AS (
+ UPDATE y SET a=a+94
+ )
+ SELECT * FROM y;
+ a
+ ---
+ 1
+ 2
+ 3
+ 4
+ 5
+ (5 rows)
+
+ -- forward reference
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t2 WHERE a < 10
+ ), t2 AS (
+ UPDATE y SET a=a+6 RETURNING *
+ )
+ SELECT * FROM y;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ 7
+ 8
+ 9
+ (8 rows)
+
+ -- unconditional DO INSTEAD rule
+ CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD INSERT INTO y VALUES(0);
+ WITH t AS (
+ DELETE FROM y
+ )
+ SELECT * FROM y;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ 7
+ 8
+ 9
+ 0
+ (9 rows)
+
+ DROP RULE y_rule ON y;
+ -- error cases
+ -- DML WITH tries to use its own output
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t
+ )
+ VALUES(FALSE);
+ ERROR: Recursive DML WITH statements are not supported
+ LINE 1: WITH RECURSIVE t AS (
+ ^
+ -- no RETURNING in a references DML WITH
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ SELECT * FROM t;
+ ERROR: DML WITH without RETURNING is only allowed inside an unreferenced CTE
+ -- DML WITH only at the top level
+ SELECT * FROM (
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t
+ ) ss;
+ ERROR: DML WITH is only allowed at the top level
+ -- no DML WITH in VIEWs
+ CREATE VIEW yv AS
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+ ERROR: DML WITH is not allowed in a view definition
+ -- no DML WITH in CURSORs
+ DECLARE f CURSOR FOR
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+ ERROR: DML WITH is not allowed in a cursor declaration
+ -- no DO INSTEAD NOTHING rules
+ CREATE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTHING;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ ERROR: DO INSTEAD NOTHING rules aren't supported in DML WITH statements
+ DROP RULE y_rule ON y;
+ -- no DO ALSO rules
+ CREATE RULE y_rule AS ON INSERT TO y DO ALSO DELETE FROM y;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ ERROR: DO ALSO rules aren't supported in DML WITH statements
+ DROP RULE y_rule ON y;
+ -- no conditional DO INSTEAD rules
+ 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 aren't supported in DML WITH statements
+ DROP RULE y_rule ON y;
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
***************
*** 500,502 **** WITH RECURSIVE t(j) AS (
--- 500,628 ----
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
+
+ --
+ -- DML WITH
+ --
+
+ -- INSERT .. RETURNING
+ WITH t AS (
+ INSERT INTO y
+ VALUES
+ (11),
+ (12),
+ (13),
+ (14),
+ (15),
+ (16),
+ (17),
+ (18),
+ (19),
+ (20)
+ RETURNING *
+ )
+ SELECT * FROM t;
+
+ -- UPDATE .. RETURNING
+ WITH t AS (
+ UPDATE y
+ SET a=a+1
+ RETURNING *
+ )
+ SELECT * FROM t;
+
+ -- DELETE .. RETURNING
+ WITH t AS (
+ DELETE FROM y
+ WHERE a <= 10
+ RETURNING *
+ )
+ SELECT * FROM t;
+
+ -- the top level statement can update rows updated by DML WITH
+ WITH t AS (
+ UPDATE y SET a = a-11
+ ), t2 AS (
+ DELETE FROM y WHERE a <= 5
+ )
+ UPDATE y SET a=a+1 RETURNING *;
+
+ -- t2 can update tuples updated by t
+ WITH t AS (
+ UPDATE y SET a=a-100
+ ), t2 AS (
+ UPDATE y SET a=a+94
+ )
+ SELECT * FROM y;
+
+ -- forward reference
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t2 WHERE a < 10
+ ), t2 AS (
+ UPDATE y SET a=a+6 RETURNING *
+ )
+ SELECT * FROM y;
+
+ -- unconditional DO INSTEAD rule
+ CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD INSERT INTO y VALUES(0);
+ WITH t AS (
+ DELETE FROM y
+ )
+ SELECT * FROM y;
+ DROP RULE y_rule ON y;
+
+ -- error cases
+
+ -- DML WITH tries to use its own output
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t
+ )
+ VALUES(FALSE);
+
+ -- no RETURNING in a references DML WITH
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ SELECT * FROM t;
+
+ -- DML WITH only at the top level
+ SELECT * FROM (
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t
+ ) ss;
+
+ -- no DML WITH in VIEWs
+ CREATE VIEW yv AS
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+
+ -- no DML WITH in CURSORs
+ DECLARE f CURSOR FOR
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+
+ -- no DO INSTEAD NOTHING rules
+ CREATE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTHING;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ DROP RULE y_rule ON y;
+
+ -- no DO ALSO rules
+ CREATE RULE y_rule AS ON INSERT TO y DO ALSO DELETE FROM y;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ DROP RULE y_rule ON y;
+
+ -- no conditional DO INSTEAD rules
+ 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;
Re: Review of Writeable CTE Patch
On Wed, Feb 3, 2010 at 4:09 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
Hi,
On 2010-02-03 11:04 UTC+2, Takahiro Itagaki wrote:
Hi, I'm reviewing the writable CTE patch. The code logic seems to be
pretty good, but I have a couple of comments about error cases:* Did we have a consensus about user-visible "DML WITH" messages?
The term is used in error messages in many places, for example:
"DML WITH without RETURNING is only allowed inside an unreferenced CTE"
Since we don't use "DML WITH" nor "CTE" in documentation,
I'd like to avoid such technical acronyms in logs if we had better names,
or we should have a section to explain them in docs.We have yet to reach a consensus on the name for this feature. I don't
think we have any really good candidates, but I like "DML WITH" best so far.
Why can't we complain about the actual SQL statement the user issued?
Like, say:
INSERT requires RETURNING when used within a referenced CTE
...Robert
Re: Review of Writeable CTE Patch
On Wed, Feb 3, 2010 at 5:31 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
On 2010-02-03 11:04, Takahiro Itagaki wrote:
* The patch includes regression tests, but no error cases in it.
More test cases are needed for stupid queries.Here's an updated patch.
Some thoughts:
The comments in standard_ExecutorStart() don't do a good job of
explaining WHY the code does what it does - they just recapitulate
what you can already see from reading the code. You say "If there are
DML WITH statements, we always need to use the CID and copy the
snapshot." That's self-evident from the following code. What's not
clear is why this is necessary, and the comment doesn't make any
attempt to explain it. The second half of the if statement has the
same problem.
In ExecTopLevelStmtIsReadOnly, you might perhaps want to rephase the
comment in a way that doesn't use the word "Ehm." Like maybe: "Even
if this function returns true, the statement might still contain
INSERT,
UPDATE, or DELETE statements within a CTE; we only check the top-level
statement." Also, there should be a newline immediately before the
function name, per our usual style conventions.
InitPlan makes some references to "leader" scan states, but there's no
explanation of what exactly those are.
The comment in analyzeCTE that says "Many of these conditions are
impossible given restrictions of the grammar, but check 'em anyway."
makes less sense with this patch than it did formerly and may need to
be rethought... and I'm not sure there's any reason to change this
elog() an Assert.
In both analyzeCTE() and checkWellFormedRecursion(), I don't like just
removing the assertions there; we should try to assert something a bit
more sensible, like maybe !IsA(cte->ctequery, Query). This patch
removes a number of other assertions as well, but I don't know enough
about those other spots to judge whether all of those cases are
sensible.
The only change to parse_relation.c is the addition of a #include; is
this needed?
The documentation changes for INSERT, UPDATE, and DELETE seem
inadequate, because they add a reference to with_query with no
corresponding explanation of what a with_query might be.
The limitations of INSERT/UPDATE/DELETE-within-WITH should be
documented somewhere: top level CTE only, and no DO ALSO or
conditional DO INSTEAD rules. If we don't intend to remove this
limitation in a future release, we should probably also document that.
I believe there are some other caveats that we've discussed before,
too, though I'm not sure if they're still true. Stuff like:
- CTEs will be executed to completion in sequential order before the
main statement begins execution
- each CTE will see the results of CTEs already executed, and the main
statement will see the results of all CTEs
- but queries within each CTE still won't see their own updates (a
reference to whatever section of the manual we talk about this in
would probably be good)
- possible pitfalls of CTEs not being pipelined
...Robert
Re: Review of Writeable CTE Patch
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2010 at 4:09 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:We have yet to reach a consensus on the name for this feature. �I don't
think we have any really good candidates, but I like "DML WITH" best so far.
Why can't we complain about the actual SQL statement the user issued?
Like, say:
INSERT requires RETURNING when used within a referenced CTE
We could probably make that work for error messages, but what about
documentation? It's going to be awkward to write something like
"INSERT/UPDATE/DELETE RETURNING" every time we need to make a general
statement about the behavior of all three.
regards, tom lane
Re: Review of Writeable CTE Patch
Hi,
On 2010-02-03 16:09 UTC+2, Robert Haas wrote:
Why can't we complain about the actual SQL statement the user issued?
Like, say:INSERT requires RETURNING when used within a referenced CTE
The SELECT equivalent of this query looks like this:
=> with recursive t as (select * from t) values(true);
ERROR: recursive query "t" does not have the form non-recursive-term
UNION [ALL] recursive-term
but I didn't want to throw people off to think that they can use
INSERT/UPDATE/RETURNING in a RECURSIVE CTE, just to get complaints about
syntax error.
Regards,
Marko Tiikkaja
Re: Review of Writeable CTE Patch
On Wed, Feb 3, 2010 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2010 at 4:09 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:We have yet to reach a consensus on the name for this feature. I don't
think we have any really good candidates, but I like "DML WITH" best so far.Why can't we complain about the actual SQL statement the user issued?
Like, say:
INSERT requires RETURNING when used within a referenced CTEWe could probably make that work for error messages, but what about
documentation? It's going to be awkward to write something like
"INSERT/UPDATE/DELETE RETURNING" every time we need to make a general
statement about the behavior of all three.
The current patch includes a total of 5 lines of text documenting this
new feature (plus one example), so the issue doesn't really arise.
If, as I believe, more documentation is needed, then we may need to
think about how to handle this, but it's hard to speculate without a
bit more context.
...Robert
Re: Review of Writeable CTE Patch
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2010 at 10:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We could probably make that work for error messages, but what about
documentation? �It's going to be awkward to write something like
"INSERT/UPDATE/DELETE RETURNING" every time we need to make a general
statement about the behavior of all three.
The current patch includes a total of 5 lines of text documenting this
new feature (plus one example), so the issue doesn't really arise.
Well, that's certainly not going to be nearly sufficient. I think what
you meant is "Marko hasn't bothered with documentation". There is going
to need to be discussion in the RULES chapter, in the page describing
returned command tags, and probably six other places that aren't coming
to me in the time it takes to type this sentence.
regards, tom lane
Re: Review of Writeable CTE Patch
On 2010-02-03 16:53 UTC+2, Robert Haas wrote:
Some thoughts:
The comments in standard_ExecutorStart() don't do a good job of
explaining WHY the code does what it does - they just recapitulate
what you can already see from reading the code. You say "If there are
DML WITH statements, we always need to use the CID and copy the
snapshot." That's self-evident from the following code. What's not
clear is why this is necessary, and the comment doesn't make any
attempt to explain it. The second half of the if statement has the
same problem.
Ok, I'll try to make this more clear.
In ExecTopLevelStmtIsReadOnly, you might perhaps want to rephase the
comment in a way that doesn't use the word "Ehm." Like maybe: "Even
if this function returns true, the statement might still contain
INSERT,
UPDATE, or DELETE statements within a CTE; we only check the top-level
statement." Also, there should be a newline immediately before the
function name, per our usual style conventions.
That comment tries to emphasize the fact that I can't think of any
reasonable name for that particular function. If the name looks OK, I
can update the comment.
The comment in analyzeCTE that says "Many of these conditions are
impossible given restrictions of the grammar, but check 'em anyway."
makes less sense with this patch than it did formerly and may need to
be rethought... and I'm not sure there's any reason to change this
elog() an Assert.
Ok, I'll look at this.
In both analyzeCTE() and checkWellFormedRecursion(), I don't like just
removing the assertions there; we should try to assert something a bit
more sensible, like maybe !IsA(cte->ctequery, Query). This patch
removes a number of other assertions as well, but I don't know enough
about those other spots to judge whether all of those cases are
sensible.
I'll look through these again.
The only change to parse_relation.c is the addition of a #include; is
this needed?
No, I thought I had removed that long time ago. Will remove.
The documentation changes for INSERT, UPDATE, and DELETE seem
inadequate, because they add a reference to with_query with no
corresponding explanation of what a with_query might be.
Ok, I'll add this.
The limitations of INSERT/UPDATE/DELETE-within-WITH should be
documented somewhere: top level CTE only, and no DO ALSO or
conditional DO INSTEAD rules. If we don't intend to remove this
limitation in a future release, we should probably also document that.
I believe there are some other caveats that we've discussed before,
too, though I'm not sure if they're still true. Stuff like:- CTEs will be executed to completion in sequential order before the
main statement begins execution
- each CTE will see the results of CTEs already executed, and the main
statement will see the results of all CTEs
- but queries within each CTE still won't see their own updates (a
reference to whatever section of the manual we talk about this in
would probably be good)
- possible pitfalls of CTEs not being pipelined
Right. The documentation in its current state is definitely lacking.
I've tried to focus all the time I have in making this patch technically
good.
Regards,
Marko Tiikkaja
Re: Review of Writeable CTE Patch
On Wed, Feb 3, 2010 at 11:18 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
On 2010-02-03 16:53 UTC+2, Robert Haas wrote:
Some thoughts:
The comments in standard_ExecutorStart() don't do a good job of
explaining WHY the code does what it does - they just recapitulate
what you can already see from reading the code. You say "If there are
DML WITH statements, we always need to use the CID and copy the
snapshot." That's self-evident from the following code. What's not
clear is why this is necessary, and the comment doesn't make any
attempt to explain it. The second half of the if statement has the
same problem.Right. The documentation in its current state is definitely lacking.
I've tried to focus all the time I have in making this patch technically
good.
Outside of documentation issues, where do we stand? Do you need help
with the documentation?
merlin
Re: Review of Writeable CTE Patch
On 2010-02-03 18:41 UTC+2, Merlin Moncure wrote:
On Wed, Feb 3, 2010 at 11:18 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:Right. The documentation in its current state is definitely lacking.
I've tried to focus all the time I have in making this patch technically
good.
Do you need help with the documentation?
I'm going to work on the documentation tonight, but it will probably
need some work from a native English speaker after I'm done.
Regards,
Marko Tiikkaja
Re: Review of Writeable CTE Patch
On Wed, Feb 3, 2010 at 11:18 AM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
In ExecTopLevelStmtIsReadOnly, you might perhaps want to rephase the
comment in a way that doesn't use the word "Ehm." Like maybe: "Even
if this function returns true, the statement might still contain
INSERT,
UPDATE, or DELETE statements within a CTE; we only check the top-level
statement." Also, there should be a newline immediately before the
function name, per our usual style conventions.That comment tries to emphasize the fact that I can't think of any
reasonable name for that particular function. If the name looks OK, I
can update the comment.
Name seems fine. Just fix the comment.
The limitations of INSERT/UPDATE/DELETE-within-WITH should be
documented somewhere: top level CTE only, and no DO ALSO or
conditional DO INSTEAD rules. If we don't intend to remove this
limitation in a future release, we should probably also document that.
I believe there are some other caveats that we've discussed before,
too, though I'm not sure if they're still true. Stuff like:- CTEs will be executed to completion in sequential order before the
main statement begins execution
- each CTE will see the results of CTEs already executed, and the main
statement will see the results of all CTEs
- but queries within each CTE still won't see their own updates (a
reference to whatever section of the manual we talk about this in
would probably be good)
- possible pitfalls of CTEs not being pipelinedRight. The documentation in its current state is definitely lacking.
I've tried to focus all the time I have in making this patch technically
good.
Well, technically good is certainly a good place to start. :-) Of
course, we need the docs, too. Thanks for your work on this.
...Robert
Re: Review of Writeable CTE Patch
Hi,
Attached is an updated patch. I'm now going to start working on the
documentation and I'll send it in a separate patch a bit later.
On 2010-02-03 16:53 UTC+2, Robert Haas wrote:
Some thoughts:
This patch
removes a number of other assertions as well, but I don't know enough
about those other spots to judge whether all of those cases are
sensible.
I put back the Asserts in make_modifytable(). The one in
ExecInitModifyTable() is not true any more and neither is the Assert in
transformInsertStatement().
Regards,
Marko Tiikkaja
Attachments:
dmlwith8.patchtext/plain; charset=iso-8859-1; name=dmlwith8.patchDownload
*** 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->hasDmlWith)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DML WITH is not allowed in a cursor declaration")));
+
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
***************
*** 940,946 **** ExecuteTruncate(TruncateStmt *stmt)
InitResultRelInfo(resultRelInfo,
rel,
0, /* dummy rangetable index */
- CMD_DELETE, /* don't need any index info */
0);
resultRelInfo++;
}
--- 940,945 ----
*** 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 WITH */
+ 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("DML WITH is 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
***************
*** 72,77 **** static void ExecutePlan(EState *estate, PlanState *planstate,
--- 72,78 ----
DestReceiver *dest);
static void ExecCheckRTPerms(List *rangeTable);
static void ExecCheckRTEPerms(RangeTblEntry *rte);
+ static bool ExecTopLevelStmtIsReadOnly(PlannedStmt *stmt);
static void ExecCheckXactReadOnly(PlannedStmt *plannedstmt);
static void EvalPlanQualStart(EPQState *epqstate, EState *parentestate,
Plan *planTree);
***************
*** 152,184 **** standard_ExecutorStart(QueryDesc *queryDesc, int eflags)
palloc0(queryDesc->plannedstmt->nParamExec * sizeof(ParamExecData));
/*
! * If non-read-only query, set the command ID to mark output tuples with
*/
! switch (queryDesc->operation)
{
! case CMD_SELECT:
! /* SELECT INTO and SELECT FOR UPDATE/SHARE need to mark tuples */
! if (queryDesc->plannedstmt->intoClause != NULL ||
! queryDesc->plannedstmt->rowMarks != NIL)
! estate->es_output_cid = GetCurrentCommandId(true);
! break;
!
! case CMD_INSERT:
! case CMD_DELETE:
! case CMD_UPDATE:
! estate->es_output_cid = GetCurrentCommandId(true);
! break;
!
! default:
! elog(ERROR, "unrecognized operation code: %d",
! (int) queryDesc->operation);
! break;
}
- /*
- * Copy other important information into the EState
- */
- estate->es_snapshot = RegisterSnapshot(queryDesc->snapshot);
estate->es_crosscheck_snapshot = RegisterSnapshot(queryDesc->crosscheck_snapshot);
estate->es_instrument = queryDesc->instrument_options;
--- 153,181 ----
palloc0(queryDesc->plannedstmt->nParamExec * sizeof(ParamExecData));
/*
! * To provide sane and predictable behaviour for DML WITH statements, we need
! * to increase the CID and update the snapshot to reflect that after every
! * statement. This allows a statement to see a previously ran statement's
! * tuples. We don't want the executor to be scribbling on its input so we
! * modify a copy of the snapshot. Also we always use at least one CID.
*/
! if (queryDesc->plannedstmt->hasDmlWith)
{
! estate->es_output_cid = GetCurrentCommandId(true);
! estate->es_snapshot = RegisterSnapshotCopy(queryDesc->snapshot);
! }
! else
! {
! /*
! * If there are no DML WITH statements, the top-level statement tells us
! * whether or not we need to use a CID. We can also use the provided
! * snapshot since we're not going to modify it.
! */
! estate->es_output_cid = GetCurrentCommandId(!ExecTopLevelStmtIsReadOnly
! (queryDesc->plannedstmt));
! estate->es_snapshot = RegisterSnapshot(queryDesc->snapshot);
}
estate->es_crosscheck_snapshot = RegisterSnapshot(queryDesc->crosscheck_snapshot);
estate->es_instrument = queryDesc->instrument_options;
***************
*** 566,571 **** ExecCheckRTEPerms(RangeTblEntry *rte)
--- 563,587 ----
}
/*
+ * Is the top-level statement read-only?
+ *
+ * Even if this function returns true, the statement might still contain
+ * INSERT, UPDATE or DELETE statements within a CTE; we only check the
+ * top-level statement.
+ */
+ static bool
+ ExecTopLevelStmtIsReadOnly(PlannedStmt *stmt)
+ {
+ if (stmt->commandType == CMD_SELECT &&
+ stmt->intoClause == NULL &&
+ stmt->rowMarks == NULL)
+ return true;
+ else
+ return false;
+ }
+
+
+ /*
* Check that the query does not imply any writes to non-temp tables.
*/
static void
***************
*** 665,671 **** InitPlan(QueryDesc *queryDesc, int eflags)
InitResultRelInfo(resultRelInfo,
resultRelation,
resultRelationIndex,
- operation,
estate->es_instrument);
resultRelInfo++;
}
--- 681,686 ----
***************
*** 796,801 **** InitPlan(QueryDesc *queryDesc, int eflags)
--- 811,877 ----
*/
planstate = ExecInitNode(plan, estate, eflags);
+ /* Add any DML WITH statements into estate */
+ estate->es_prescanstates = NIL;
+
+ if (plannedstmt->hasDmlWith)
+ {
+ /*
+ * We need to know the "leader" CteScanState (see comment below) so we need
+ * to look in initPlan.
+ */
+ foreach(l, plannedstmt->planTree->initPlan)
+ {
+ SubPlan *sp;
+ int cte_param_id;
+ ParamExecData *prmdata;
+ CteScanState *leader;
+ PlanState *ps;
+
+ sp = (SubPlan *) lfirst(l);
+ if (sp->subLinkType != CTE_SUBLINK)
+ continue;
+
+ /*
+ * Any CTE referenced in the query will have a "leader" CteScanState. All
+ * other CteScanStates associated with that CTE will use the leader's
+ * tuplestore, so we only need to make sure the leader has all the rows from
+ * the RETURNING. The executor will make that happen as long as we add the
+ * leader CteScanState to es_prescanstates. Not having a leader means the
+ * CTE is not referenced anywhere, so we just add the ModifyTable node and
+ * the executor will ignore its output. This avoids storing RETURNING
+ * tuples for unreferenced CTEs.
+ */
+
+ cte_param_id = linitial_int(sp->setParam);
+ prmdata = &(estate->es_param_exec_vals[cte_param_id]);
+ leader = (CteScanState *) DatumGetPointer(prmdata->value);
+
+ if (leader)
+ {
+ ps = leader->cteplanstate;
+
+ /* ignore SELECT CTEs */
+ if (!IsA(ps, ModifyTableState))
+ continue;
+
+ estate->es_prescanstates = lappend(estate->es_prescanstates,
+ leader);
+ }
+ else
+ {
+ ps = (PlanState *) list_nth(estate->es_subplanstates,
+ sp->plan_id - 1);
+
+ /* must be DML (see comment above) */
+ Assert(IsA(ps, ModifyTableState));
+
+ estate->es_prescanstates = lappend(estate->es_prescanstates,
+ ps);
+ }
+ }
+ }
+
/*
* Get the tuple descriptor describing the type of tuples to return. (this
* is especially important if we are creating a relation with "SELECT
***************
*** 858,864 **** void
InitResultRelInfo(ResultRelInfo *resultRelInfo,
Relation resultRelationDesc,
Index resultRelationIndex,
- CmdType operation,
int instrument_options)
{
/*
--- 934,939 ----
***************
*** 926,941 **** 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);
}
/*
--- 1001,1006 ----
***************
*** 991,1005 **** 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);
--- 1056,1068 ----
/*
* 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);
***************
*** 1165,1170 **** ExecutePlan(EState *estate,
--- 1228,1234 ----
{
TupleTableSlot *slot;
long current_tuple_count;
+ ListCell *lc;
/*
* initialize local variables
***************
*** 1176,1181 **** ExecutePlan(EState *estate,
--- 1240,1281 ----
*/
estate->es_direction = direction;
+ /* If there are any DML WITH statements, process those first */
+ foreach(lc, estate->es_prescanstates)
+ {
+ TupleTableSlot *slot;
+ PlanState *ps = (PlanState *) lfirst(lc);
+
+ for (;;)
+ {
+ slot = ExecProcNode(ps);
+ if (TupIsNull(slot))
+ break;
+ }
+
+ /* Need to rewind the CTE */
+ if (!IsA(ps, ModifyTableState))
+ ExecReScan(ps, NULL);
+
+ /*
+ * To allow a statement see a previously ran statement's tuples we need to
+ * increment CID and update the snapshot.
+ */
+
+ CommandCounterIncrement();
+
+ /*
+ * If this was the last DML WITH statement, don't use a CID unless the
+ * top-level statement requires it.
+ */
+ if (!lnext(lc) && ExecTopLevelStmtIsReadOnly(estate->es_plannedstmt))
+ estate->es_output_cid = GetCurrentCommandId(false);
+ else
+ estate->es_output_cid = GetCurrentCommandId(true);
+
+ estate->es_snapshot->curcid = estate->es_output_cid;
+ }
+
/*
* Loop until we've processed the proper number of tuples from the plan.
*/
***************
*** 1957,1963 **** 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)
--- 2057,2064 ----
* 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)
***************
*** 1965,1971 **** 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);
--- 2066,2076 ----
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/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 158,164 **** ExecProcessReturning(ProjectionInfo *projectReturning,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
--- 158,165 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecInsert(bool canSetTag,
! TupleTableSlot *slot,
TupleTableSlot *planSlot,
EState *estate)
{
***************
*** 240,246 **** ExecInsert(TupleTableSlot *slot,
newId = heap_insert(resultRelationDesc, tuple,
estate->es_output_cid, 0, NULL);
! (estate->es_processed)++;
estate->es_lastoid = newId;
setLastTid(&(tuple->t_self));
--- 241,249 ----
newId = heap_insert(resultRelationDesc, tuple,
estate->es_output_cid, 0, NULL);
! if (canSetTag)
! (estate->es_processed)++;
!
estate->es_lastoid = newId;
setLastTid(&(tuple->t_self));
***************
*** 274,280 **** ExecInsert(TupleTableSlot *slot,
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
--- 277,284 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecDelete(bool canSetTag,
! ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate)
***************
*** 356,362 **** ldelete:;
return NULL;
}
! (estate->es_processed)++;
/*
* Note: Normally one would think that we have to delete index tuples
--- 360,367 ----
return NULL;
}
! if (canSetTag)
! (estate->es_processed)++;
/*
* Note: Normally one would think that we have to delete index tuples
***************
*** 417,423 **** ldelete:;
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
--- 422,429 ----
* ----------------------------------------------------------------
*/
static TupleTableSlot *
! ExecUpdate(bool canSetTag,
! ItemPointer tupleid,
TupleTableSlot *slot,
TupleTableSlot *planSlot,
EPQState *epqstate,
***************
*** 546,552 **** lreplace:;
return NULL;
}
! (estate->es_processed)++;
/*
* Note: instead of having to update the old index tuples associated with
--- 552,559 ----
return NULL;
}
! if (canSetTag)
! (estate->es_processed)++;
/*
* Note: instead of having to update the old index tuples associated with
***************
*** 593,607 **** 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");
--- 600,614 ----
{
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");
***************
*** 619,633 **** 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");
--- 626,640 ----
{
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");
***************
*** 649,654 **** ExecModifyTable(ModifyTableState *node)
--- 656,662 ----
EState *estate = node->ps.state;
CmdType operation = node->operation;
PlanState *subplanstate;
+ ResultRelInfo *resultRelInfo;
JunkFilter *junkfilter;
TupleTableSlot *slot;
TupleTableSlot *planSlot;
***************
*** 664,680 **** 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
--- 672,681 ----
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
***************
*** 690,698 **** 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;
}
--- 691,699 ----
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,747 **** ExecModifyTable(ModifyTableState *node)
if (operation != CMD_DELETE)
slot = ExecFilterJunk(junkfilter, slot);
}
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:
--- 731,755 ----
if (operation != CMD_DELETE)
slot = ExecFilterJunk(junkfilter, slot);
}
+
+ /*
+ * es_result_relation_info must point to the currently active result
+ * relation. We want it to be NULL whenever we're not within
+ * ModifyTable, though.
+ */
+ estate->es_result_relation_info = resultRelInfo;
switch (operation)
{
case CMD_INSERT:
! slot = ExecInsert(node->canSetTag, slot, planSlot, estate);
break;
case CMD_UPDATE:
! slot = ExecUpdate(node->canSetTag, tupleid, slot, planSlot,
&node->mt_epqstate, estate);
break;
case CMD_DELETE:
! slot = ExecDelete(node->canSetTag, tupleid, planSlot,
&node->mt_epqstate, estate);
break;
default:
***************
*** 809,833 **** 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;
--- 817,856 ----
mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) * nplans);
mtstate->mt_nplans = nplans;
mtstate->operation = operation;
+ mtstate->canSetTag = node->canSetTag;
+ 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;
***************
*** 864,871 **** 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);
--- 887,893 ----
/*
* Build a projection for each result rel.
*/
! resultRelInfo = mtstate->resultRelInfo;
foreach(l, node->returningLists)
{
List *rlist = (List *) lfirst(l);
***************
*** 964,970 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
if (junk_filter_needed)
{
! resultRelInfo = estate->es_result_relations;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
--- 986,992 ----
if (junk_filter_needed)
{
! resultRelInfo = mtstate->resultRelInfo;
for (i = 0; i < nplans; i++)
{
JunkFilter *j;
***************
*** 993,999 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
else
{
if (operation == CMD_INSERT)
! ExecCheckPlanOutput(estate->es_result_relations->ri_RelationDesc,
subplan->targetlist);
}
}
--- 1015,1021 ----
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(hasDmlWith);
COPY_NODE_FIELD(subplans);
COPY_BITMAPSET_FIELD(rewindPlanIDs);
COPY_NODE_FIELD(rowMarks);
***************
*** 171,177 **** _copyModifyTable(ModifyTable *from)
--- 172,180 ----
* 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);
***************
*** 2260,2265 **** _copyInsertStmt(InsertStmt *from)
--- 2263,2269 ----
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2273,2278 **** _copyDeleteStmt(DeleteStmt *from)
--- 2277,2283 ----
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
***************
*** 2287,2292 **** _copyUpdateStmt(UpdateStmt *from)
--- 2292,2298 ----
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
COPY_NODE_FIELD(returningList);
+ COPY_NODE_FIELD(withClause);
return newnode;
}
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 888,893 **** _equalInsertStmt(InsertStmt *a, InsertStmt *b)
--- 888,894 ----
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 899,904 **** _equalDeleteStmt(DeleteStmt *a, DeleteStmt *b)
--- 900,906 ----
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
***************
*** 911,916 **** _equalUpdateStmt(UpdateStmt *a, UpdateStmt *b)
--- 913,919 ----
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_NODE_FIELD(withClause);
return true;
}
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2394,2399 **** bool
--- 2394,2449 ----
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_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_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_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);
***************
*** 1533,1538 **** _outPlannerGlobal(StringInfo str, PlannerGlobal *node)
--- 1534,1540 ----
WRITE_NODE_FIELD(finalrowmarks);
WRITE_NODE_FIELD(relationOids);
WRITE_NODE_FIELD(invalItems);
+ WRITE_NODE_FIELD(resultRelations);
WRITE_UINT_FIELD(lastPHId);
WRITE_BOOL_FIELD(transientPlan);
}
***************
*** 1548,1554 **** _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);
--- 1550,1555 ----
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 3754,3760 **** make_result(PlannerInfo *root,
* to make it look better sometime.
*/
ModifyTable *
! make_modifytable(CmdType operation, List *resultRelations,
List *subplans, List *returningLists,
List *rowMarks, int epqParam)
{
--- 3754,3761 ----
* to make it look better sometime.
*/
ModifyTable *
! make_modifytable(CmdType operation, bool canSetTag,
! List *resultRelations,
List *subplans, List *returningLists,
List *rowMarks, int epqParam)
{
***************
*** 3804,3809 **** make_modifytable(CmdType operation, List *resultRelations,
--- 3805,3811 ----
node->plan.targetlist = NIL;
node->operation = operation;
+ node->canSetTag = canSetTag;
node->resultRelations = resultRelations;
node->plans = subplans;
node->returningLists = returningLists;
*** 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->hasDmlWith = 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->hasDmlWith = glob->hasDmlWith;
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,551 ----
rowMarks = root->rowMarks;
plan = (Plan *) make_modifytable(parse->commandType,
! parse->canSetTag,
! 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)
{
--- 710,722 ----
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;
--- 776,781 ----
***************
*** 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,
--- 785,790 ----
***************
*** 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,
--- 819,826 ----
/* And last, tack on a ModifyTable node to do the UPDATE/DELETE work */
return (Plan *) make_modifytable(parse->commandType,
! parse->canSetTag,
! resultRelations,
subplans,
returningLists,
rowMarks,
***************
*** 1668,1679 **** 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.
--- 1670,1675 ----
*** 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,906 ----
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;
}
+
+ 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("DML WITH without RETURNING is only allowed inside an unreferenced CTE")));
+
+ if (root->query_level > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DML WITH is only allowed at the top level")));
+
+ root->glob->hasDmlWith = true;
+ }
/*
* Copy the source Query node. Probably not necessary, but let's keep
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 290,295 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
--- 290,302 ----
qry->distinctClause = NIL;
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* The USING clause is non-standard SQL syntax, and is equivalent in
* functionality to the FROM list that can be specified for UPDATE. The
***************
*** 342,347 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 349,361 ----
qry->commandType = CMD_INSERT;
pstate->p_is_insert = true;
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
* VALUES list, or general SELECT input. We special-case VALUES, both for
***************
*** 366,373 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
pstate->p_relnamespace = NIL;
sub_varnamespace = pstate->p_varnamespace;
pstate->p_varnamespace = NIL;
- /* There can't be any outer WITH to worry about */
- Assert(pstate->p_ctenamespace == NIL);
}
else
{
--- 380,385 ----
***************
*** 1735,1740 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1747,1759 ----
true,
ACL_UPDATE);
+ /* process the WITH clause */
+ if (stmt->withClause)
+ {
+ qry->hasRecursive = stmt->withClause->recursive;
+ qry->cteList = transformWithClause(pstate, stmt->withClause);
+ }
+
/*
* the FROM clause is non-standard SQL syntax. We used to be able to do
* this with REPLACE in POSTQUEL so we keep the feature.
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 429,435 **** static TypeName *TableFuncTypeName(List *columns);
%type <boolean> xml_whitespace_option
%type <node> common_table_expr
! %type <with> with_clause
%type <list> cte_list
%type <list> window_clause window_definition_list opt_partition_clause
--- 429,435 ----
%type <boolean> xml_whitespace_option
%type <node> common_table_expr
! %type <with> with_clause opt_with_clause
%type <list> cte_list
%type <list> window_clause window_definition_list opt_partition_clause
***************
*** 7072,7082 **** DeallocateStmt: DEALLOCATE name
*****************************************************************************/
InsertStmt:
! INSERT INTO qualified_name insert_rest returning_clause
{
! $4->relation = $3;
! $4->returningList = $5;
! $$ = (Node *) $4;
}
;
--- 7072,7083 ----
*****************************************************************************/
InsertStmt:
! opt_with_clause INSERT INTO qualified_name insert_rest returning_clause
{
! $5->relation = $4;
! $5->returningList = $6;
! $5->withClause = $1;
! $$ = (Node *) $5;
}
;
***************
*** 7132,7145 **** returning_clause:
*
*****************************************************************************/
! DeleteStmt: DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relation = $3;
! n->usingClause = $4;
! n->whereClause = $5;
! n->returningList = $6;
$$ = (Node *)n;
}
;
--- 7133,7147 ----
*
*****************************************************************************/
! DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
! n->relation = $4;
! n->usingClause = $5;
! n->whereClause = $6;
! n->returningList = $7;
! n->withClause = $1;
$$ = (Node *)n;
}
;
***************
*** 7194,7211 **** opt_nowait: NOWAIT { $$ = TRUE; }
*
*****************************************************************************/
! UpdateStmt: UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relation = $2;
! n->targetList = $4;
! n->fromClause = $5;
! n->whereClause = $6;
! n->returningList = $7;
$$ = (Node *)n;
}
;
--- 7196,7214 ----
*
*****************************************************************************/
! UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
! n->relation = $3;
! n->targetList = $5;
! n->fromClause = $6;
! n->whereClause = $7;
! n->returningList = $8;
! n->withClause = $1;
$$ = (Node *)n;
}
;
***************
*** 7531,7536 **** with_clause:
--- 7534,7543 ----
}
;
+ opt_with_clause:
+ with_clause { $$ = $1; }
+ | /*EMPTY*/ { $$ = NULL; }
+
cte_list:
common_table_expr { $$ = list_make1($1); }
| cte_list ',' common_table_expr { $$ = lappend($1, $3); }
***************
*** 7545,7550 **** common_table_expr: name opt_name_list AS select_with_parens
--- 7552,7584 ----
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,241 ----
analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
{
Query *query;
! List *cteList;
query = parse_sub_analyze(cte->ctequery, pstate, cte, false);
cte->ctequery = (Node *) query;
! /* Check that we got something reasonable. */
! if (!IsA(query, Query))
! elog(ERROR, "unexpected non-Query as subquery in WITH");
! if (query->utilityStmt != NULL)
! elog(ERROR, "unexpected utility statement in subquery in WITH");
if (query->intoClause)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
***************
*** 248,257 **** analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
parser_errposition(pstate,
exprLocation((Node *) query->intoClause))));
if (!cte->cterecursive)
{
/* Compute the output column names/types if not done yet */
! analyzeCTETargetList(pstate, cte, query->targetList);
}
else
{
--- 243,257 ----
parser_errposition(pstate,
exprLocation((Node *) query->intoClause))));
+ if (query->commandType == CMD_SELECT)
+ cteList = query->targetList;
+ else
+ cteList = query->returningList;
+
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;
--- 269,275 ----
lctyp = list_head(cte->ctecoltypes);
lctypmod = list_head(cte->ctecoltypmods);
varattno = 0;
! foreach(lctlist, cteList)
{
TargetEntry *te = (TargetEntry *) lfirst(lctlist);
Node *texpr;
***************
*** 595,606 **** checkWellFormedRecursion(CteState *cstate)
CommonTableExpr *cte = cstate->items[i].cte;
SelectStmt *stmt = (SelectStmt *) cte->ctequery;
- Assert(IsA(stmt, SelectStmt)); /* not analyzed yet */
-
/* Ignore items that weren't found to be recursive */
if (!cte->cterecursive)
continue;
/* Must have top-level UNION */
if (stmt->op != SETOP_UNION)
ereport(ERROR,
--- 595,613 ----
CommonTableExpr *cte = cstate->items[i].cte;
SelectStmt *stmt = (SelectStmt *) cte->ctequery;
/* Ignore items that weren't found to be recursive */
if (!cte->cterecursive)
continue;
+ Assert(!IsA(stmt, Query)); /* not analyzed yet */
+
+ /* Must be a SELECT statement */
+ if (!IsA(stmt, SelectStmt))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Recursive DML WITH statements are not supported"),
+ parser_errposition(cstate->pstate, cte->location)));
+
/* Must have top-level UNION */
if (stmt->op != SETOP_UNION)
ereport(ERROR,
*** 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 *lc1;
+ 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,1871 ----
}
/*
+ * Rewrite DML WITH statements.
+ */
+ foreach(lc1, parsetree->cteList)
+ {
+ cte = lfirst(lc1);
+
+ ctequery = (Query *) cte->ctequery;
+
+ if (ctequery->commandType == CMD_SELECT)
+ continue;
+
+ newstuff = RewriteQuery(ctequery, NIL);
+
+ /* Currently we can only handle unconditional DO INSTEAD rules correctly. */
+ if (list_length(newstuff) > 1)
+ {
+ ListCell *lc2;
+
+ foreach(lc2, newstuff)
+ {
+ QuerySource qsrc = ((Query *) lfirst(lc2))->querySource;
+
+ if (qsrc == QSRC_QUAL_INSTEAD_RULE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Conditional DO INSTEAD rules aren't supported in DML WITH statements")));
+ }
+ else if (qsrc == QSRC_NON_INSTEAD_RULE)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO ALSO rules aren't supported in DML WITH statements")));
+ }
+ }
+
+ elog(ERROR, "unknown rewrite result");
+ }
+ else if (list_length(newstuff) == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DO INSTEAD NOTHING rules aren't supported in DML WITH statements")));
+ }
+ else
+ {
+ Assert(list_length(newstuff) == 1);
+
+ cte->ctequery = (Node *) linitial(newstuff);
+
+ /*
+ * Only the top-level statement sets the command tag.
+ */
+ /* this query won't set the command tag */
+ ((Query *) cte->ctequery)->canSetTag = false;
+ }
+ }
+
+ /*
* 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->hasDmlWith &&
pstmt->utilityStmt == NULL &&
pstmt->intoClause == NULL)
return PORTAL_ONE_SELECT;
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 3980,3988 **** 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",
--- 3980,3995 ----
}
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/backend/utils/time/snapmgr.c
--- b/src/backend/utils/time/snapmgr.c
***************
*** 375,380 **** RegisterSnapshot(Snapshot snapshot)
--- 375,401 ----
}
/*
+ * RegisterSnapshot
+ * Copies a snapshot and registers the copy as being in use by the current
+ * resource owner
+ *
+ * If InvalidSnapshot is passed, it is not registered.
+ */
+ Snapshot
+ RegisterSnapshotCopy(Snapshot snapshot)
+ {
+ Snapshot snap;
+
+ if (snapshot == InvalidSnapshot)
+ return InvalidSnapshot;
+
+ snap = CopySnapshot(snapshot);
+
+ return RegisterSnapshotOnOwner(snap, CurrentResourceOwner);
+ }
+
+
+ /*
* RegisterSnapshotOnOwner
* As above, but use the specified resource owner
*/
*** 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,
int instrument_options);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
extern bool ExecContextForcesOids(PlanState *planstate, bool *hasoids);
--- 160,165 ----
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 376,381 **** typedef struct EState
--- 376,382 ----
List *es_exprcontexts; /* List of ExprContexts within EState */
+ List *es_prescanstates; /* List of PlanStates to be scanned before the main plan */
List *es_subplanstates; /* List of PlanState for SubPlans */
/*
***************
*** 1026,1034 **** typedef struct ModifyTableState
--- 1027,1038 ----
{
PlanState ps; /* its first field is NodeTag */
CmdType operation;
+ bool canSetTag; /* do we set the command tag? */
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/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 850,856 **** typedef struct CommonTableExpr
NodeTag type;
char *ctename; /* query name (never qualified) */
List *aliascolnames; /* optional list of column names */
! Node *ctequery; /* subquery (SelectStmt or Query) */
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
--- 850,856 ----
NodeTag type;
char *ctename; /* query name (never qualified) */
List *aliascolnames; /* optional list of column names */
! Node *ctequery; /* subquery (Stmt or Query) */
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
***************
*** 880,885 **** typedef struct InsertStmt
--- 880,886 ----
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} InsertStmt;
/* ----------------------
***************
*** 893,898 **** typedef struct DeleteStmt
--- 894,900 ----
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} DeleteStmt;
/* ----------------------
***************
*** 907,912 **** typedef struct UpdateStmt
--- 909,915 ----
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
List *returningList; /* list of expressions to return */
+ WithClause *withClause; /* WITH clause */
} UpdateStmt;
/* ----------------------
*** 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 hasDmlWith; /* are there any DML WITH statements? */
+
List *subplans; /* Plan trees for SubPlan expressions */
Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */
***************
*** 164,170 **** typedef struct ModifyTable
--- 166,174 ----
{
Plan plan;
CmdType operation; /* INSERT, UPDATE, or DELETE */
+ bool canSetTag; /* do we set the command tag? */
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 hasDmlWith; /* are there any DML WITH statements? */
+
+ List *resultRelations;/* list of result relations */
+
Index lastPHId; /* highest PlaceHolderVar ID assigned */
bool transientPlan; /* redo plan when TransactionXmin changes? */
***************
*** 152,159 **** 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 */
--- 156,161 ----
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 77,83 **** 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,
List *rowMarks, int epqParam);
extern bool is_projection_capable_plan(Plan *plan);
--- 77,84 ----
long numGroups, double outputRows);
extern Result *make_result(PlannerInfo *root, List *tlist,
Node *resconstantqual, Plan *subplan);
! 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);
*** a/src/include/utils/snapmgr.h
--- b/src/include/utils/snapmgr.h
***************
*** 34,39 **** extern Snapshot GetActiveSnapshot(void);
--- 34,40 ----
extern bool ActiveSnapshotSet(void);
extern Snapshot RegisterSnapshot(Snapshot snapshot);
+ extern Snapshot RegisterSnapshotCopy(Snapshot snapshot);
extern void UnregisterSnapshot(Snapshot snapshot);
extern Snapshot RegisterSnapshotOnOwner(Snapshot snapshot, ResourceOwner owner);
extern void UnregisterSnapshotFromOwner(Snapshot snapshot, ResourceOwner owner);
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
***************
*** 1026,1028 **** SELECT * FROM t;
--- 1026,1241 ----
10
(55 rows)
+ --
+ -- DML 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)
+
+ -- 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)
+
+ -- 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)
+
+ -- the top level statement can update rows updated by DML WITH
+ WITH t AS (
+ UPDATE y SET a = a-11
+ ), t2 AS (
+ DELETE FROM y WHERE a <= 5
+ )
+ UPDATE y SET a=a+1 RETURNING *;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ (5 rows)
+
+ -- t2 can update tuples updated by t
+ WITH t AS (
+ UPDATE y SET a=a-100
+ ), t2 AS (
+ UPDATE y SET a=a+94
+ )
+ SELECT * FROM y;
+ a
+ ---
+ 1
+ 2
+ 3
+ 4
+ 5
+ (5 rows)
+
+ -- forward reference
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t2 WHERE a < 10
+ ), t2 AS (
+ UPDATE y SET a=a+6 RETURNING *
+ )
+ SELECT * FROM y;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ 7
+ 8
+ 9
+ (8 rows)
+
+ -- unconditional DO INSTEAD rule
+ CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD INSERT INTO y VALUES(0);
+ WITH t AS (
+ DELETE FROM y
+ )
+ SELECT * FROM y;
+ a
+ ----
+ 7
+ 8
+ 9
+ 10
+ 11
+ 7
+ 8
+ 9
+ 0
+ (9 rows)
+
+ DROP RULE y_rule ON y;
+ -- error cases
+ -- DML WITH tries to use its own output
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t
+ )
+ VALUES(FALSE);
+ ERROR: Recursive DML WITH statements are not supported
+ LINE 1: WITH RECURSIVE t AS (
+ ^
+ -- no RETURNING in a references DML WITH
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ SELECT * FROM t;
+ ERROR: DML WITH without RETURNING is only allowed inside an unreferenced CTE
+ -- DML WITH only at the top level
+ SELECT * FROM (
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t
+ ) ss;
+ ERROR: DML WITH is only allowed at the top level
+ -- no DML WITH in VIEWs
+ CREATE VIEW yv AS
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+ ERROR: DML WITH is not allowed in a view definition
+ -- no DML WITH in CURSORs
+ DECLARE f CURSOR FOR
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+ ERROR: DML WITH is not allowed in a cursor declaration
+ -- no DO INSTEAD NOTHING rules
+ CREATE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTHING;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ ERROR: DO INSTEAD NOTHING rules aren't supported in DML WITH statements
+ DROP RULE y_rule ON y;
+ -- no DO ALSO rules
+ CREATE RULE y_rule AS ON INSERT TO y DO ALSO DELETE FROM y;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ ERROR: DO ALSO rules aren't supported in DML WITH statements
+ DROP RULE y_rule ON y;
+ -- no conditional DO INSTEAD rules
+ 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 aren't supported in DML WITH statements
+ DROP RULE y_rule ON y;
*** a/src/test/regress/sql/with.sql
--- b/src/test/regress/sql/with.sql
***************
*** 500,502 **** WITH RECURSIVE t(j) AS (
--- 500,628 ----
SELECT j+1 FROM t WHERE j < 10
)
SELECT * FROM t;
+
+ --
+ -- DML WITH
+ --
+
+ -- INSERT .. RETURNING
+ WITH t AS (
+ INSERT INTO y
+ VALUES
+ (11),
+ (12),
+ (13),
+ (14),
+ (15),
+ (16),
+ (17),
+ (18),
+ (19),
+ (20)
+ RETURNING *
+ )
+ SELECT * FROM t;
+
+ -- UPDATE .. RETURNING
+ WITH t AS (
+ UPDATE y
+ SET a=a+1
+ RETURNING *
+ )
+ SELECT * FROM t;
+
+ -- DELETE .. RETURNING
+ WITH t AS (
+ DELETE FROM y
+ WHERE a <= 10
+ RETURNING *
+ )
+ SELECT * FROM t;
+
+ -- the top level statement can update rows updated by DML WITH
+ WITH t AS (
+ UPDATE y SET a = a-11
+ ), t2 AS (
+ DELETE FROM y WHERE a <= 5
+ )
+ UPDATE y SET a=a+1 RETURNING *;
+
+ -- t2 can update tuples updated by t
+ WITH t AS (
+ UPDATE y SET a=a-100
+ ), t2 AS (
+ UPDATE y SET a=a+94
+ )
+ SELECT * FROM y;
+
+ -- forward reference
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t2 WHERE a < 10
+ ), t2 AS (
+ UPDATE y SET a=a+6 RETURNING *
+ )
+ SELECT * FROM y;
+
+ -- unconditional DO INSTEAD rule
+ CREATE RULE y_rule AS ON DELETE TO y DO INSTEAD INSERT INTO y VALUES(0);
+ WITH t AS (
+ DELETE FROM y
+ )
+ SELECT * FROM y;
+ DROP RULE y_rule ON y;
+
+ -- error cases
+
+ -- DML WITH tries to use its own output
+ WITH RECURSIVE t AS (
+ INSERT INTO y
+ SELECT * FROM t
+ )
+ VALUES(FALSE);
+
+ -- no RETURNING in a references DML WITH
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ SELECT * FROM t;
+
+ -- DML WITH only at the top level
+ SELECT * FROM (
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t
+ ) ss;
+
+ -- no DML WITH in VIEWs
+ CREATE VIEW yv AS
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+
+ -- no DML WITH in CURSORs
+ DECLARE f CURSOR FOR
+ WITH t AS (UPDATE y SET a=a+1 RETURNING *)
+ SELECT * FROM t;
+
+ -- no DO INSTEAD NOTHING rules
+ CREATE RULE y_rule AS ON INSERT TO y DO INSTEAD NOTHING;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ DROP RULE y_rule ON y;
+
+ -- no DO ALSO rules
+ CREATE RULE y_rule AS ON INSERT TO y DO ALSO DELETE FROM y;
+ WITH t AS (
+ INSERT INTO y VALUES(0)
+ )
+ VALUES(FALSE);
+ DROP RULE y_rule ON y;
+
+ -- no conditional DO INSTEAD rules
+ 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;