On 11/16/18 10:15 PM, Tom Lane wrote:
> I took a little bit of a look through this. Some thoughts:
Thanks for the review! I have decided to pick up this patch and work on
it since nothing has happened in a while. Here is a new version with
most of the feedback fixed.
* This is not the idiomatic way to declare an expression tree walker:
+static bool inline_cte_walker(Node *node, void *ctxp)
+{
+ struct inline_cte_walker_ctx *ctx = ctxp;
* I have no faith in the idea that we can skip doing a copyObject on the
inlined subquery, except maybe in the case where we know there's exactly
one reference.
* In "here's where we do the actual substitution", if we're going to
scribble on the RTE rather than make a new one, we must take pains
to zero out the RTE_CTE-specific fields so that the RTE looks the
same as if it had been a RTE_SUBQUERY all along; cf db1071d4e.
* The lack of comments about what conditions we inline under
(at subselect.c:1318) is distressing. I'm not particularly
in love with the way that inline_cte_walker is commented, either.
And dare I mention that this falsifies the intro comment for
SS_process_ctes?
* Speaking of the comments, I'm not convinced that view rewrite is
a good comparison point; I think this is more like subquery pullup.
I believe I have fixed these except for the comment on the conditions
for when we inline.
Andrew Gierth: Why did you chose to not inline on FOR UPDATE but inline
volatile functions? I feel that this might be inconsistent since in both
cases the query in the CTE can change behavior if the planner pushes a
WHERE clause into the subquery, but maybe I am missing something.
* I wonder whether we should make range_table_walker more friendly
to the needs of this patch. The fact that it doesn't work for this usage
suggests that it might not work for others, too. I could see replacing
the QTW_EXAMINE_RTES flag with QTW_EXAMINE_RTES_BEFORE and
QTW_EXAMINE_RTES_AFTER so that callers could say which order of operations
they want (ie, visit RTE itself before or after its substructure). Then
we could get rid of the double traversal of the RTE list.
I did as suggested and the code is now much cleaner, but I feel while
RTE walking business would become cleaner if we could change from having
the range table walker yield both RTEs and the contents of the RTEs to
having it just yeild the RTEs and then the walker callback can call
expression_tree_walker() with the RTE so RTEs are treated like any other
node in the tree.
I might look into how big impact such a change would have and if it is
worth the churn.
* I think a large fraction of the regression test cases that this
changes are actually broken by the patch, in the sense that we meant
to test the behavior with a CTE and now we're not getting that.
So we'd need to add MATERIALIZED in many more places than this has
done. Somebody else (Stephen?) would need to opine on whether that's
true for the CTEs in rowsecurity.sql, but it's definitely true for
the one in rowtypes.sql, where the point is to test what happens
with a whole-row Var.
Agreed, fixed.
* Which will mean we need some new test cases showing that this patch does
anything. It'd be a good idea to add a test case showing that this gets
things right for conflicting CTE names at different levels, eg
explain verbose
with x as (select 1 as y)
select * from (with x as (select 2 as y) select * from x) ss;
Added this test case, but more are needed. Any suggestion for what file
these tests belong (right now I just added it to subselect.sql)? Or
should I add a new called cte.sql?
* ruleutils.c needs adjustments for the new syntax, if we keep that.
Thanks, fixed!
* And of course the documentation needs much more work than this.
Yeah, I was waiting for there to be more agreement on when CTEs should
be inlined, but maybe I should start writing anyway.
Andreas
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bb92d9d37a..8c26dd1f26 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -1888,7 +1888,7 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
-- join in CTE
EXPLAIN (VERBOSE, COSTS OFF)
-WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit
@@ -1905,7 +1905,7 @@ WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
-WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
c1_1 | c2_1
------+------
101 | 101
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index f438165650..56602a164c 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -493,8 +493,8 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
-- join in CTE
EXPLAIN (VERBOSE, COSTS OFF)
-WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
-WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
+WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
-- ctid with whole-row reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 88bc189646..fe47ba0d1f 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -2199,6 +2199,8 @@ SELECT n FROM t LIMIT 100;
</para>
<para>
+ TODO: Update this for inlinding and <literal>MATERILIZED</literal>.
+
A useful property of <literal>WITH</literal> queries is that they are evaluated
only once per execution of the parent query, even if they are referred to
more than once by the parent query or sibling <literal>WITH</literal> queries.
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 4db8142afa..e344b7e49a 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -72,7 +72,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
<phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
- <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
+ <replaceable class="parameter">with_query_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] AS [ MATERIALIZED ] ( <replaceable class="parameter">select</replaceable> | <replaceable class="parameter">values</replaceable> | <replaceable class="parameter">insert</replaceable> | <replaceable class="parameter">update</replaceable> | <replaceable class="parameter">delete</replaceable> )
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
@@ -290,6 +290,10 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
row, the results are unspecified.
</para>
+ <para>
+ TODO: Write something about <literal>MATERIALIZED</literal>.
+ </para>
+
<para>
See <xref linkend="queries-with"/> for additional information.
</para>
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 144cd7a047..fad7a0b3c2 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2539,6 +2539,7 @@ _copyCommonTableExpr(const CommonTableExpr *from)
COPY_NODE_FIELD(ctequery);
COPY_LOCATION_FIELD(location);
COPY_SCALAR_FIELD(cterecursive);
+ COPY_SCALAR_FIELD(ctematerialized);
COPY_SCALAR_FIELD(cterefcount);
COPY_NODE_FIELD(ctecolnames);
COPY_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index c4579fbcc6..0f9aa03ac5 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2793,6 +2793,7 @@ _equalCommonTableExpr(const CommonTableExpr *a, const CommonTableExpr *b)
COMPARE_NODE_FIELD(ctequery);
COMPARE_LOCATION_FIELD(location);
COMPARE_SCALAR_FIELD(cterecursive);
+ COMPARE_SCALAR_FIELD(ctematerialized);
COMPARE_SCALAR_FIELD(cterefcount);
COMPARE_NODE_FIELD(ctecolnames);
COMPARE_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index a813e38791..9497cb67cf 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2314,8 +2314,7 @@ range_table_walker(List *rtable,
{
RangeTblEntry *rte = (RangeTblEntry *) lfirst(rt);
- /* For historical reasons, visiting RTEs is not the default */
- if (flags & QTW_EXAMINE_RTES)
+ if (flags & QTW_EXAMINE_RTES_BEFORE)
if (walker(rte, context))
return true;
@@ -2353,6 +2352,10 @@ range_table_walker(List *rtable,
break;
}
+ if (flags & QTW_EXAMINE_RTES_AFTER)
+ if (walker(rte, context))
+ return true;
+
if (walker(rte->securityQuals, context))
return true;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 2dd8ed3a46..e9b70856d3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2983,6 +2983,7 @@ _outCommonTableExpr(StringInfo str, const CommonTableExpr *node)
WRITE_NODE_FIELD(ctequery);
WRITE_LOCATION_FIELD(location);
WRITE_BOOL_FIELD(cterecursive);
+ WRITE_BOOL_FIELD(ctematerialized);
WRITE_INT_FIELD(cterefcount);
WRITE_NODE_FIELD(ctecolnames);
WRITE_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index fa27f37d6f..a7054fa044 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -421,6 +421,7 @@ _readCommonTableExpr(void)
READ_NODE_FIELD(ctequery);
READ_LOCATION_FIELD(location);
READ_BOOL_FIELD(cterecursive);
+ READ_BOOL_FIELD(ctematerialized);
READ_INT_FIELD(cterefcount);
READ_NODE_FIELD(ctecolnames);
READ_NODE_FIELD(ctecoltypes);
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 920a99d0d9..0fed2f07c7 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -340,7 +340,7 @@ flatten_unplanned_rtes(PlannerGlobal *glob, RangeTblEntry *rte)
(void) query_tree_walker(rte->subquery,
flatten_rtes_walker,
(void *) glob,
- QTW_EXAMINE_RTES);
+ QTW_EXAMINE_RTES_BEFORE);
}
static bool
@@ -363,7 +363,7 @@ flatten_rtes_walker(Node *node, PlannerGlobal *glob)
return query_tree_walker((Query *) node,
flatten_rtes_walker,
(void *) glob,
- QTW_EXAMINE_RTES);
+ QTW_EXAMINE_RTES_BEFORE);
}
return expression_tree_walker(node, flatten_rtes_walker,
(void *) glob);
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 83008d7661..eebc968fde 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -53,6 +53,14 @@ typedef struct finalize_primnode_context
Bitmapset *paramids; /* Non-local PARAM_EXEC paramids found */
} finalize_primnode_context;
+typedef struct inline_cte_walker_context
+{
+ const char *ctename;
+ int levelsup;
+ int refcount;
+ Query *ctequery;
+ CommonTableExpr *cte;
+} inline_cte_walker_context;
static Node *build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
List *plan_params,
@@ -1145,14 +1153,93 @@ hash_ok_operator(OpExpr *expr)
}
}
+/*
+ * inline_cte_walker: recursively walk the query and inline CTE
+ *
+ * While walking the expression tree with take care to examine the RTEs and
+ * potentially replace a CTE RTE with a subquery RTE after recursing into the
+ * RTE to avoid recursing into the newly inlined CTE.
+ */
+static bool inline_cte_walker(Node *node, inline_cte_walker_context *context)
+{
+ if (!node)
+ return false;
+
+ if (IsA(node, Query))
+ {
+ Query *query = castNode(Query, node);
+
+ context->levelsup++;
+
+ /*
+ * Visit the actual RTE nodes after their contents so we can modify
+ * the RTE node without having to visit the newly inlined contents.
+ */
+ query_tree_walker(query, inline_cte_walker, context,
+ QTW_EXAMINE_RTES_AFTER);
+
+ context->levelsup--;
+
+ return false;
+ }
+ else if (IsA(node, RangeTblEntry))
+ {
+ RangeTblEntry *rte = castNode(RangeTblEntry, node);
+
+ if (rte->rtekind == RTE_CTE &&
+ strcmp(rte->ctename, context->ctename) == 0 &&
+ rte->ctelevelsup == context->levelsup)
+ {
+ Query *newquery = copyObject(context->ctequery);
+
+ /* Preserve outer references, for example to other CTEs */
+ if (context->levelsup > 0)
+ IncrementVarSublevelsUp((Node *) newquery, context->levelsup, 1);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = newquery;
+ rte->security_barrier = false;
+
+ /* Zero out CTE specific fields */
+ rte->ctename = NULL;
+ rte->ctelevelsup = 0;
+ rte->self_reference = false;
+ rte->coltypes = NIL;
+ rte->coltypmods = NIL;
+ rte->colcollations = NIL;
+
+ context->refcount--;
+ }
+
+ return false;
+ }
+
+ return expression_tree_walker(node, inline_cte_walker, context);
+}
+
+/*
+ * inline_cte: replace all refernces to CTE with subqueries
+ */
+static void inline_cte(PlannerInfo *root, CommonTableExpr *cte)
+{
+ struct inline_cte_walker_context context;
+ context.ctequery = castNode(Query, cte->ctequery);
+ context.ctename = cte->ctename;
+ context.refcount = cte->cterefcount;
+ context.levelsup = -1;
+ context.cte = cte;
+ inline_cte_walker((Node *) root->parse, &context);
+ /* we must replace all references */
+ Assert(context.refcount == 0);
+}
/*
* SS_process_ctes: process a query's WITH list
*
- * We plan each interesting WITH item and convert it to an initplan.
- * A side effect is to fill in root->cte_plan_ids with a list that
- * parallels root->parse->cteList and provides the subplan ID for
- * each CTE's initplan.
+ * We plan each interesting WITH item and either convert it to an initplan, or
+ * if allowed inline it as a subquery. A side effect is to fill in
+ * root->cte_plan_ids with a list that parallels root->parse->cteList and
+ * provides the subplan ID for each CTE's initplan, or a dummy ID when inlined.
*/
void
SS_process_ctes(PlannerInfo *root)
@@ -1183,6 +1270,24 @@ SS_process_ctes(PlannerInfo *root)
continue;
}
+ /*
+ * Consider inlining the CTE rather than planning it separately.
+ *
+ * TODO: Check with Gierth why rowMarks should not be inlined.
+ */
+ if (cmdType == CMD_SELECT &&
+ !cte->ctematerialized &&
+ !cte->cterecursive &&
+ !((cte->cterefcount > 1) && contain_volatile_functions(cte->ctequery)) &&
+ castNode(Query, cte->ctequery)->rowMarks == NIL)
+ {
+ inline_cte(root, cte);
+
+ /* 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
* this similar to make_subplan.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 5bcaf42205..0cef2737d5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -518,7 +518,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
Bit ConstBit BitWithLength BitWithoutLength
%type <str> character
%type <str> extract_arg
-%type <boolean> opt_varying opt_timezone opt_no_inherit
+%type <boolean> opt_varying opt_timezone opt_no_inherit opt_materialized
%type <ival> Iconst SignedIconst
%type <str> Sconst comment_text notify_payload
@@ -11368,17 +11368,23 @@ cte_list:
| cte_list ',' common_table_expr { $$ = lappend($1, $3); }
;
-common_table_expr: name opt_name_list AS '(' PreparableStmt ')'
+common_table_expr: name opt_name_list AS opt_materialized '(' PreparableStmt ')'
{
CommonTableExpr *n = makeNode(CommonTableExpr);
n->ctename = $1;
n->aliascolnames = $2;
- n->ctequery = $5;
+ n->ctequery = $6;
+ n->ctematerialized = $4;
n->location = @1;
$$ = (Node *) n;
}
;
+opt_materialized:
+ MATERIALIZED { $$ = true; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
+
opt_with_clause:
with_clause { $$ = $1; }
| /*EMPTY*/ { $$ = NULL; }
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index f1f4212b5d..5a71d691ce 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -761,7 +761,7 @@ IncrementVarSublevelsUp_walker(Node *node,
result = query_tree_walker((Query *) node,
IncrementVarSublevelsUp_walker,
(void *) context,
- QTW_EXAMINE_RTES);
+ QTW_EXAMINE_RTES_BEFORE);
context->min_sublevels_up--;
return result;
}
@@ -785,7 +785,7 @@ IncrementVarSublevelsUp(Node *node, int delta_sublevels_up,
query_or_expression_tree_walker(node,
IncrementVarSublevelsUp_walker,
(void *) &context,
- QTW_EXAMINE_RTES);
+ QTW_EXAMINE_RTES_BEFORE);
}
/*
@@ -804,7 +804,7 @@ IncrementVarSublevelsUp_rtable(List *rtable, int delta_sublevels_up,
range_table_walker(rtable,
IncrementVarSublevelsUp_walker,
(void *) &context,
- QTW_EXAMINE_RTES);
+ QTW_EXAMINE_RTES_BEFORE);
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 4857caecaa..b4030a111e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5165,7 +5165,10 @@ get_with_clause(Query *query, deparse_context *context)
}
appendStringInfoChar(buf, ')');
}
- appendStringInfoString(buf, " AS (");
+ appendStringInfoString(buf, " AS ");
+ if (cte->ctematerialized)
+ appendStringInfoString(buf, "MATERIALIZED ");
+ appendStringInfoChar(buf, '(');
if (PRETTY_INDENT(context))
appendContextKeyword(context, "", 0, 0, 0);
get_query_def((Query *) cte->ctequery, buf, context->namespaces, NULL,
diff --git a/src/include/nodes/nodeFuncs.h b/src/include/nodes/nodeFuncs.h
index 849f34d2a8..d4783cd61f 100644
--- a/src/include/nodes/nodeFuncs.h
+++ b/src/include/nodes/nodeFuncs.h
@@ -22,8 +22,10 @@
#define QTW_IGNORE_RC_SUBQUERIES 0x03 /* both of above */
#define QTW_IGNORE_JOINALIASES 0x04 /* JOIN alias var lists */
#define QTW_IGNORE_RANGE_TABLE 0x08 /* skip rangetable entirely */
-#define QTW_EXAMINE_RTES 0x10 /* examine RTEs */
+#define QTW_EXAMINE_RTES_BEFORE 0x10 /* examine RTE nodes before their contents */
#define QTW_DONT_COPY_QUERY 0x20 /* do not copy top Query */
+#define QTW_EXAMINE_RTES_AFTER 0x40 /* examine RTE nodes after their contents */
+
/* callback function for check_functions_in_node */
typedef bool (*check_function_callback) (Oid func_id, void *context);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d3dd3d0339..7d4fbffcbf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1409,6 +1409,7 @@ typedef struct CommonTableExpr
int location; /* token location, or -1 if unknown */
/* These fields are set during parse analysis: */
bool cterecursive; /* is this CTE actually recursive? */
+ bool ctematerialized; /* is this an optimization fence? */
int cterefcount; /* number of RTEs referencing this CTE
* (excluding internal self-references) */
List *ctecolnames; /* list of output column names */
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 1d12b01068..1a0932d863 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2179,7 +2179,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test;
Filter: (((a % 2) = 0) AND f_leak(b))
(2 rows)
-PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
+PREPARE plancache_test2 AS WITH q AS MATERIALIZED (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
QUERY PLAN
-------------------------------------------------
@@ -2192,7 +2192,7 @@ EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
-> Seq Scan on z2
(7 rows)
-PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
+PREPARE plancache_test3 AS WITH q AS MATERIALIZED (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
QUERY PLAN
-----------------------------------------------------
@@ -2853,7 +2853,7 @@ NOTICE: f_leak => 98f13708210194c475687be6106a3b84
20 | 98f13708210194c475687be6106a3b84
(11 rows)
-EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
+EXPLAIN (COSTS OFF) WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
QUERY PLAN
-------------------------------------------------
CTE Scan on cte1
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index d6a1a3331e..14757b40f6 100644
--- a/src/test/regress/expected/rowtypes.out
+++ b/src/test/regress/expected/rowtypes.out
@@ -1037,7 +1037,7 @@ from (values (1,row(1,2)), (1,row(null,null)), (1,null),
(6 rows)
explain (verbose, costs off)
-with r(a,b) as
+with r(a,b) as materialized
(values (1,row(1,2)), (1,row(null,null)), (1,null),
(null,row(1,2)), (null,row(null,null)), (null,null) )
select r, r is null as isnull, r is not null as isnotnull from r;
@@ -1050,7 +1050,7 @@ select r, r is null as isnull, r is not null as isnotnull from r;
Output: "*VALUES*".column1, "*VALUES*".column2
(5 rows)
-with r(a,b) as
+with r(a,b) as materialized
(values (1,row(1,2)), (1,row(null,null)), (1,null),
(null,row(1,2)), (null,row(null,null)), (null,null) )
select r, r is null as isnull, r is not null as isnotnull from r;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e384cd2279..32dfab94b2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3108,7 +3108,7 @@ explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
(5 rows)
-- ensure upserting into a rule, with a CTE (different offsets!) works
-WITH data(hat_name, hat_color) AS (
+WITH data(hat_name, hat_color) AS MATERIALIZED (
VALUES ('h8', 'green'),
('h9', 'blue'),
('h7', 'forbidden')
@@ -3122,7 +3122,7 @@ RETURNING *;
h9 | blue
(2 rows)
-EXPLAIN (costs off) WITH data(hat_name, hat_color) AS (
+EXPLAIN (costs off) WITH data(hat_name, hat_color) AS MATERIALIZED (
VALUES ('h8', 'green'),
('h9', 'blue'),
('h7', 'forbidden')
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 588d069589..812ed92c64 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1154,3 +1154,12 @@ fetch backward all in c1;
(2 rows)
commit;
+explain verbose
+with x as (select 1 as y)
+select * from (with x as (select 2 as y) select * from x) ss;
+ QUERY PLAN
+------------------------------------------
+ Result (cost=0.00..0.01 rows=1 width=4)
+ Output: 2
+(2 rows)
+
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 38e9b38bc4..4c9e846fab 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -840,10 +840,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b);
EXPLAIN (COSTS OFF) EXECUTE plancache_test;
-PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
+PREPARE plancache_test2 AS WITH q AS MATERIALIZED (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
-PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
+PREPARE plancache_test3 AS WITH q AS MATERIALIZED (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
SET ROLE regress_rls_group1;
@@ -1072,7 +1072,7 @@ INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
SET SESSION AUTHORIZATION regress_rls_bob;
WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
-EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
+EXPLAIN (COSTS OFF) WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index e6d389805c..b3b07746c3 100644
--- a/src/test/regress/sql/rowtypes.sql
+++ b/src/test/regress/sql/rowtypes.sql
@@ -428,12 +428,12 @@ from (values (1,row(1,2)), (1,row(null,null)), (1,null),
(null,row(1,2)), (null,row(null,null)), (null,null) ) r(a,b);
explain (verbose, costs off)
-with r(a,b) as
+with r(a,b) as materialized
(values (1,row(1,2)), (1,row(null,null)), (1,null),
(null,row(1,2)), (null,row(null,null)), (null,null) )
select r, r is null as isnull, r is not null as isnotnull from r;
-with r(a,b) as
+with r(a,b) as materialized
(values (1,row(1,2)), (1,row(null,null)), (1,null),
(null,row(1,2)), (null,row(null,null)), (null,null) )
select r, r is null as isnull, r is not null as isnotnull from r;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index f4ee30ec8f..82750b8e6f 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1132,7 +1132,7 @@ SELECT tablename, rulename, definition FROM pg_rules
explain (costs off) INSERT INTO hats VALUES ('h8', 'forbidden') RETURNING *;
-- ensure upserting into a rule, with a CTE (different offsets!) works
-WITH data(hat_name, hat_color) AS (
+WITH data(hat_name, hat_color) AS MATERIALIZED (
VALUES ('h8', 'green'),
('h9', 'blue'),
('h7', 'forbidden')
@@ -1140,7 +1140,7 @@ WITH data(hat_name, hat_color) AS (
INSERT INTO hats
SELECT * FROM data
RETURNING *;
-EXPLAIN (costs off) WITH data(hat_name, hat_color) AS (
+EXPLAIN (costs off) WITH data(hat_name, hat_color) AS MATERIALIZED (
VALUES ('h8', 'green'),
('h9', 'blue'),
('h7', 'forbidden')
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 843f511b3d..a1f9a7d890 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -625,3 +625,7 @@ move forward all in c1;
fetch backward all in c1;
commit;
+
+explain verbose
+with x as (select 1 as y)
+select * from (with x as (select 2 as y) select * from x) ss;
