I've been struggling with how we're going to upgrade launchpad.net to
PostgreSQL 12 or newer (we're currently on 10). We're one of those
applications that deliberately uses CTEs as optimization fences in a few
difficult places. The provision of the MATERIALIZED keyword in 12 is
great, but the fact that it doesn't exist in earlier versions is
awkward. We certainly don't want to upgrade our application code at the
same time as upgrading the database, and dealing with performance
degradation between the database upgrade and the application upgrade
doesn't seem great either (not to mention that it would be hard to
coordinate). That leaves us with hacking our query compiler to add the
MATERIALIZED keyword only if it's running on 12 or newer, which would be
possible but is pretty cumbersome.
However, an alternative would be to backport the new syntax to some
earlier versions. "WITH ... AS MATERIALIZED" can easily just be
synonymous with "WITH ... AS" in versions prior to 12; there's no need
to support "NOT MATERIALIZED" since that's explicitly requesting the new
query-folding feature that only exists in 12. Would something like the
attached patch against REL_11_STABLE be acceptable? I'd like to
backpatch it at least as far as PostgreSQL 10.
This compiles and passes regression tests.
Thanks,
--
Colin Watson [cjwat...@canonical.com]
>From 063186eb678ad9831961d6319f7a4279f1029358 Mon Sep 17 00:00:00 2001
From: Colin Watson <cjwat...@canonical.com>
Date: Fri, 18 Oct 2019 14:08:11 +0100
Subject: [PATCH] Backport "WITH ... AS MATERIALIZED" syntax
Applications that deliberately use CTEs as optimization fences need to
adjust their code to prepare for PostgreSQL 12. Unfortunately, the
MATERIALIZED keyword that they need to add isn't valid syntax in earlier
versions of PostgreSQL, so they're stuck with either upgrading the
application and the database simultaneously, accepting performance
degradation between the two parts of the upgrade, or doing complex query
compiler work to add MATERIALIZED conditionally.
It makes things much easier in these cases if the MATERIALIZED keyword
is accepted and ignored in earlier releases. Users can then upgrade to
a suitable point release, change their application code to add
MATERIALIZED, and then upgrade to PostgreSQL 12.
---
doc/src/sgml/queries.sgml | 12 ++++++++++
doc/src/sgml/ref/select.sgml | 18 +++++++++++++-
src/backend/parser/gram.y | 12 +++++++---
src/test/regress/expected/subselect.out | 31 +++++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 14 +++++++++++
5 files changed, 83 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 88bc189646..cc33d92133 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -2215,6 +2215,18 @@ SELECT n FROM t LIMIT 100;
rows.)
</para>
+ <para>
+ In some cases, <productname>PostgreSQL</productname> 12 folds
+ <literal>WITH</literal> queries into the parent query, allowing joint
+ optimization of the two query levels. You can override that decision by
+ specifying <literal>MATERIALIZED</literal> to force separate calculation
+ of the <literal>WITH</literal> query. While versions of
+ <productname>PostgreSQL</productname> before 12 do not support folding of
+ <literal>WITH</literal> queries, specifying
+ <literal>MATERIALIZED</literal> is permitted to ease application
+ upgrades.
+ </para>
+
<para>
The examples above only show <literal>WITH</literal> being used with
<command>SELECT</command>, but it can be attached in the same way to
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 4db8142afa..1bd711a3cb 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,17 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
row, the results are unspecified.
</para>
+ <para>
+ <productname>PostgreSQL</productname> 12 folds side-effect-free
+ <literal>WITH</literal> queries into the primary query in some cases.
+ To override this and retain the behaviour up to
+ <productname>PostgreSQL</productname> 11, mark the
+ <literal>WITH</literal> query as <literal>MATERIALIZED</literal>. That
+ might be useful, for example, if the <literal>WITH</literal> query is
+ being used as an optimization fence to prevent the planner from choosing
+ a bad plan.
+ </para>
+
<para>
See <xref linkend="queries-with"/> for additional information.
</para>
@@ -2087,6 +2098,11 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
<para>
<literal>ROWS FROM( ... )</literal> is an extension of the SQL standard.
</para>
+
+ <para>
+ The <literal>MATERIALIZED</literal> option of <literal>WITH</literal> is
+ an extension of the SQL standard.
+ </para>
</refsect2>
</refsect1>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index bc65319c2c..70df09f409 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -479,7 +479,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> row explicit_row implicit_row type_list array_expr_list
%type <node> case_expr case_arg when_clause case_default
%type <list> when_clause_list
-%type <ival> sub_type
+%type <ival> sub_type opt_materialized
%type <value> NumericOnly
%type <list> NumericOnly_list
%type <alias> alias_clause opt_alias_clause
@@ -11419,17 +11419,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->location = @1;
$$ = (Node *) n;
}
;
+/* Stub for forward-compatibility with PostgreSQL 12. */
+opt_materialized:
+ MATERIALIZED {}
+ | /*EMPTY*/ {}
+ ;
+
opt_with_clause:
with_clause { $$ = $1; }
| /*EMPTY*/ { $$ = NULL; }
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index a288c6d33b..85cd1e57cb 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1178,3 +1178,34 @@ fetch backward all in c1;
(2 rows)
commit;
+--
+-- Tests for CTE inlining behavior (forward-compatibility with PostgreSQL 12)
+--
+-- Basic subquery
+explain (verbose, costs off)
+with x as (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)
+
+-- 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)
+
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index eafd927e82..5a8ece180f 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -635,3 +635,17 @@ move forward all in c1;
fetch backward all in c1;
commit;
+
+--
+-- Tests for CTE inlining behavior (forward-compatibility with PostgreSQL 12)
+--
+
+-- Basic subquery
+explain (verbose, costs off)
+with x as (select * from (select f1 from subselect_tbl) 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;
--
2.17.1
