From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: tharakan@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16978: Nested CTEs give ERROR in v13
Date: Fri, 23 Apr 2021 14:03:22 -0400
Message-ID: <20210423180322.GD7629@momjian.us> (raw)
In-Reply-To: <1356187.1619192255@sss.pgh.pa.us>
References: <16978-813ac0832f35db82@postgresql.org>
<1313767.1619186886@sss.pgh.pa.us>
<20210423145603.GC7629@momjian.us>
<1356187.1619192255@sss.pgh.pa.us>
On Fri, Apr 23, 2021 at 11:37:35AM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Fri, Apr 23, 2021 at 10:08:06AM -0400, Tom Lane wrote:
> >> FWIW, I don't see any error from this, either in HEAD or the
> >> back branches.
>
> > Uh, I don't see the failure in 13 head or master, but I do see it from a
> > 13.2 tree checkout. I will try to find the post-13.2 commit cause.
>
> Oh!
>
> [ checks commit log ]
>
> I bet it's 80ca8464f.
Confirmed. The bug was added by this commit in July 2019:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1cff1b95a
and was included in PG 13. It was fixed by this post-PG 13.2 commit on
February 26, 2021:
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=80ca8464f
The commit message is very clear:
makeDependencyGraphWalker and checkWellFormedRecursionWalker
thought they could hold onto a pointer to a list's first
cons cell while the list was modified by recursive calls.
That was okay when the cons cell was actually separately
--> palloc'd ... but since commit 1cff1b95a, it's quite unsafe,
leading to core dumps or incorrect complaints of faulty
WITH nesting.
--> In the field this'd require at least a seven-deep WITH nest
to cause an issue, but enabling DEBUG_LIST_MEMORY_USAGE
allows the bug to be seen with lesser nesting depths.
Per bug #16801 from Alexander Lakhin. Back-patch to v13.
It mentions the commit it fixed, and the fact that "this'd require at
least a seven-deep WITH nest to cause an issue", which is exactly what
you saw. :-)
I am attaching this later patch in a version that will cleanly apply to
PG 13.2, in case you need it. If not, the fix will appear in 13.3 which
will be released on May 13, 2021.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
Attachments:cte-13.2.diff (text/x-diff, 4.7K) downloaddiff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.cindex 1fca7485ca..b24989e417 100644--- a/src/backend/parser/parse_cte.c+++ b/src/backend/parser/parse_cte.c@@ -537,15 +537,15 @@ makeDependencyGraphWalker(Node *node, CteState *cstate)
* In the non-RECURSIVE case, query names are visible to the
* WITH items after them and to the main query.
*/
- ListCell *cell1;-
cstate->innerwiths = lcons(NIL, cstate->innerwiths);
- cell1 = list_head(cstate->innerwiths);
foreach(lc, stmt->withClause->ctes)
{
CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+ ListCell *cell1;
(void) makeDependencyGraphWalker(cte->ctequery, cstate);
+ /* note that recursion could mutate innerwiths list */+ cell1 = list_head(cstate->innerwiths);
lfirst(cell1) = lappend((List *) lfirst(cell1), cte);
}
(void) raw_expression_tree_walker(node,
@@ -813,15 +813,15 @@ checkWellFormedRecursionWalker(Node *node, CteState *cstate)
* In the non-RECURSIVE case, query names are visible to the
* WITH items after them and to the main query.
*/
- ListCell *cell1;-
cstate->innerwiths = lcons(NIL, cstate->innerwiths);
- cell1 = list_head(cstate->innerwiths);
foreach(lc, stmt->withClause->ctes)
{
CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);
+ ListCell *cell1;
(void) checkWellFormedRecursionWalker(cte->ctequery, cstate);
+ /* note that recursion could mutate innerwiths list */+ cell1 = list_head(cstate->innerwiths);
lfirst(cell1) = lappend((List *) lfirst(cell1), cte);
}
checkWellFormedSelectStmt(stmt, cstate);
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.outindex 67eaeb4f3e..0d506348e1 100644--- a/src/test/regress/expected/with.out+++ b/src/test/regress/expected/with.out@@ -167,6 +167,65 @@ ERROR: operator does not exist: text + integer
LINE 4: SELECT n+1 FROM t WHERE n < 10
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
+-- Deeply nested WITH caused a list-munging problem in v13+-- Detection of cross-references and self-references+WITH RECURSIVE w1(c1) AS+ (WITH w2(c2) AS+ (WITH w3(c3) AS+ (WITH w4(c4) AS+ (WITH w5(c5) AS+ (WITH RECURSIVE w6(c6) AS+ (WITH w6(c6) AS+ (WITH w8(c8) AS+ (SELECT 1)+ SELECT * FROM w8)+ SELECT * FROM w6)+ SELECT * FROM w6)+ SELECT * FROM w5)+ SELECT * FROM w4)+ SELECT * FROM w3)+ SELECT * FROM w2)+SELECT * FROM w1;+ c1 +----+ 1+(1 row)++-- Detection of invalid self-references+WITH RECURSIVE outermost(x) AS (+ SELECT 1+ UNION (WITH innermost1 AS (+ SELECT 2+ UNION (WITH innermost2 AS (+ SELECT 3+ UNION (WITH innermost3 AS (+ SELECT 4+ UNION (WITH innermost4 AS (+ SELECT 5+ UNION (WITH innermost5 AS (+ SELECT 6+ UNION (WITH innermost6 AS+ (SELECT 7)+ SELECT * FROM innermost6))+ SELECT * FROM innermost5))+ SELECT * FROM innermost4))+ SELECT * FROM innermost3))+ SELECT * FROM innermost2))+ SELECT * FROM outermost+ UNION SELECT * FROM innermost1)+ )+ SELECT * FROM outermost ORDER BY 1;+ x +---+ 1+ 2+ 3+ 4+ 5+ 6+ 7+(7 rows)+
--
-- Some examples with a tree
--
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sqlindex f85645efde..43cd2e10af 100644--- a/src/test/regress/sql/with.sql+++ b/src/test/regress/sql/with.sql@@ -87,6 +87,50 @@ UNION ALL
)
SELECT n, n IS OF (int) AS is_int FROM t;
+-- Deeply nested WITH caused a list-munging problem in v13+-- Detection of cross-references and self-references+WITH RECURSIVE w1(c1) AS+ (WITH w2(c2) AS+ (WITH w3(c3) AS+ (WITH w4(c4) AS+ (WITH w5(c5) AS+ (WITH RECURSIVE w6(c6) AS+ (WITH w6(c6) AS+ (WITH w8(c8) AS+ (SELECT 1)+ SELECT * FROM w8)+ SELECT * FROM w6)+ SELECT * FROM w6)+ SELECT * FROM w5)+ SELECT * FROM w4)+ SELECT * FROM w3)+ SELECT * FROM w2)+SELECT * FROM w1;+-- Detection of invalid self-references+WITH RECURSIVE outermost(x) AS (+ SELECT 1+ UNION (WITH innermost1 AS (+ SELECT 2+ UNION (WITH innermost2 AS (+ SELECT 3+ UNION (WITH innermost3 AS (+ SELECT 4+ UNION (WITH innermost4 AS (+ SELECT 5+ UNION (WITH innermost5 AS (+ SELECT 6+ UNION (WITH innermost6 AS+ (SELECT 7)+ SELECT * FROM innermost6))+ SELECT * FROM innermost5))+ SELECT * FROM innermost4))+ SELECT * FROM innermost3))+ SELECT * FROM innermost2))+ SELECT * FROM outermost+ UNION SELECT * FROM innermost1)+ )+ SELECT * FROM outermost ORDER BY 1;+
--
-- Some examples with a tree
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: bruce@momjian.us, tgl@sss.pgh.pa.us, tharakan@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16978: Nested CTEs give ERROR in v13
In-Reply-To: <20210423180322.GD7629@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox