Here is a new version of the patch with added tests, improved comments,
some minor code cleanup and most importantly slightly changed logic for
when we should inline.
The current strategy is to always inline unless the CTE is recursive or
it has side effects, i.e. it is a DML query, it contains calls it a
volatile function, or it contains FOR SHARE/FOR UPDATE. I feel this is a
conservative approach which prevents behavioral changes (other than
performance).
Current open issues:
1. Currently the patch will inline CTEs even when there are multiple
references to them. If this is a win or not depends on the specific
query so I am not sure what we should do here. One thing worth noting is
that our current documentation talks a lot about how CTEs are only
evaluated once.
"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.
Thus, expensive calculations that are needed in multiple places can be
placed within a <literal>WITH</literal> query to avoid redundant work.
Another
possible application is to prevent unwanted multiple evaluations of
functions with side-effects."
What do you think?
2. Feedback on the new syntax. I am personally fine with the current
syntax, but it was just something I just quickly hacked together to move
the patch forward and which also solved my personal uses cases.
3. Are we happy with how I modified query_tree_walker()? I feel the code
would be clearer if we could change the tree walker to treat the RTE as
the parent node of the subquery instead of a sibling, but this seems
like potentially a quite invasive change.
4. I need to update the user documentation.
Andreas
commit 60aab4af243cc93d504b99c99010593c02a5705c
Author: Andreas Karlsson <andr...@proxel.se>
Date: Mon Sep 17 03:42:40 2018 +0200
CTE inlining
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..92f180c5cf 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 inlining and <literal>MATERIALIZED</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..92ede4fc6c 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>
@@ -273,6 +273,8 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</para>
<para>
+ TODO: Update this for inlining and <literal>MATERIALIZED</literal>.
+
A key property of <literal>WITH</literal> queries is that they
are evaluated only once per execution of the primary query,
even if the primary query refers to them more than once.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 006a3d1772..19b21ab6c5 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 133df1b364..6779adfe8d 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 19b65f681d..03d085eda4 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 0fde876c77..764bf36674 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 ec6f2569ab..bd9086cb3f 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 fc7ba0dea2..a31c287c35 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 cb1ea86afb..019dce7f67 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,129 @@ 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);
+
+ /*
+ * Replace the CTE reference in the range table with a subquery
+ *
+ * A FOR UPDATE clause is treated as extending into views and
+ * subqueries, but not into CTEs. We preserve this distinction
+ * by not trying to push rowmarks into the new subquery.
+ */
+ 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);
+}
+
+static bool
+contain_row_marks_walker(Node *node, void *context)
+{
+ if (!node)
+ return false;
+
+ if (IsA(node, Query))
+ {
+ Query *query = castNode(Query, node);
+
+ if (query->rowMarks != NIL)
+ return true;
+
+ return query_tree_walker(query, contain_row_marks_walker, context, 0);
+ }
+ return expression_tree_walker(node, contain_row_marks_walker, context);
+}
+
+/*
+ * contain_row_marks: check the query or any subquery contains row marks
+ */
+static bool
+contain_row_marks(Node *node)
+{
+ return contain_row_marks_walker(node, NULL);
+}
/*
* 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)
@@ -1184,6 +1307,27 @@ SS_process_ctes(PlannerInfo *root)
}
/*
+ * We always inline the CTE as a subquery as long as it has no side
+ * effects, whch in this context means that it is a SELECT which does
+ * not call any volatile functions and does not have any rowmarks.
+ *
+ * The user may also have requested to not inline the CTE with the
+ * MATERIALIZED keyword.
+ */
+ if (!cte->ctematerialized &&
+ cmdType == CMD_SELECT &&
+ !cte->cterecursive &&
+ !contain_volatile_functions(cte->ctequery) &&
+ !contain_row_marks(cte->ctequery))
+ {
+ 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 c086235b25..623f582a57 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 acbe669294..fd7a751c9a 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 77811f6818..5b4c21e4f9 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 7739600db2..2774f15cc8 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 27782fed6c..42f226e459 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..d52dfe022a 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1154,3 +1154,92 @@ fetch backward all in c1;
(2 rows)
commit;
+-- Ensure that we inline the currect CTE when there multiple CTEs with the same name
+explain (verbose, costs off)
+with x as (select 1 as y)
+select * from (with x as (select 2 as y) select * from x) ss;
+ QUERY PLAN
+-------------
+ Result
+ Output: 2
+(2 rows)
+
+-- Ensure that we do not inline when there are side effects
+-- Basic subquery which can be inlined
+explain (verbose, costs off)
+with x as (select * from (select f1 from subselect_tbl) ss)
+select * from x where f1 = 1;
+ QUERY PLAN
+----------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: subselect_tbl.f1
+ Filter: (subselect_tbl.f1 = 1)
+(3 rows)
+
+-- Stable functions are safe to inline
+explain (verbose, costs off)
+with x as (select * from (select f1, now() from subselect_tbl) ss)
+select * from x where f1 = 1;
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: subselect_tbl.f1, now()
+ Filter: (subselect_tbl.f1 = 1)
+(3 rows)
+
+-- Volatile functions prevent inlining
+explain (verbose, costs off)
+with x as (select * from (select f1, random() from subselect_tbl) ss)
+select * from x where f1 = 1;
+ QUERY PLAN
+----------------------------------------------
+ CTE Scan on x
+ Output: x.f1, x.random
+ Filter: (x.f1 = 1)
+ CTE x
+ -> Seq Scan on public.subselect_tbl
+ Output: subselect_tbl.f1, random()
+(6 rows)
+
+-- Row marks prevent inlining
+explain (verbose, costs off)
+with x as (select * from (select f1 from subselect_tbl for update) ss)
+select * from x where f1 = 1;
+ QUERY PLAN
+--------------------------------------------------------------------
+ CTE Scan on x
+ Output: x.f1
+ Filter: (x.f1 = 1)
+ CTE x
+ -> Subquery Scan on ss
+ Output: ss.f1
+ -> LockRows
+ Output: subselect_tbl.f1, subselect_tbl.ctid
+ -> Seq Scan on public.subselect_tbl
+ Output: subselect_tbl.f1, subselect_tbl.ctid
+(10 rows)
+
+-- Explicitly request materialization
+explain (verbose, costs off)
+with x as materialized (select * from (select f1 from subselect_tbl) ss)
+select * from x where f1 = 1;
+ QUERY PLAN
+------------------------------------------
+ CTE Scan on x
+ Output: x.f1
+ Filter: (x.f1 = 1)
+ CTE x
+ -> Seq Scan on public.subselect_tbl
+ Output: subselect_tbl.f1
+(6 rows)
+
+-- Row marks are not pushed into CTEs
+explain (verbose, costs off)
+with x as (select * from subselect_tbl)
+select * from x for update;
+ QUERY PLAN
+----------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3
+(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..bdbba3838f 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -625,3 +625,42 @@ move forward all in c1;
fetch backward all in c1;
commit;
+
+-- Ensure that we inline the currect CTE when there multiple CTEs with the same name
+
+explain (verbose, costs off)
+with x as (select 1 as y)
+select * from (with x as (select 2 as y) select * from x) ss;
+
+-- Ensure that we do not inline when there are side effects
+
+-- Basic subquery which can be inlined
+explain (verbose, costs off)
+with x as (select * from (select f1 from subselect_tbl) ss)
+select * from x where f1 = 1;
+
+-- Stable functions are safe to inline
+explain (verbose, costs off)
+with x as (select * from (select f1, now() from subselect_tbl) ss)
+select * from x where f1 = 1;
+
+-- Volatile functions prevent inlining
+explain (verbose, costs off)
+with x as (select * from (select f1, random() from subselect_tbl) ss)
+select * from x where f1 = 1;
+
+-- Row marks prevent inlining
+explain (verbose, costs off)
+with x as (select * from (select f1 from subselect_tbl for update) ss)
+select * from x where f1 = 1;
+
+-- Explicitly request materialization
+explain (verbose, costs off)
+with x as materialized (select * from (select f1 from subselect_tbl) ss)
+select * from x where f1 = 1;
+
+-- Row marks are not pushed into CTEs
+
+explain (verbose, costs off)
+with x as (select * from subselect_tbl)
+select * from x for update;
