Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1la09J-00021b-VC for pgsql-bugs@arkaria.postgresql.org; Fri, 23 Apr 2021 18:03:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1la09I-0007xL-Sr for pgsql-bugs@arkaria.postgresql.org; Fri, 23 Apr 2021 18:03:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1la09I-0007xD-La for pgsql-bugs@lists.postgresql.org; Fri, 23 Apr 2021 18:03:28 +0000 Received: from momjian.us ([72.94.173.45]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1la09F-00086r-Tm for pgsql-bugs@lists.postgresql.org; Fri, 23 Apr 2021 18:03:28 +0000 Received: from bruce by momjian.us with local (Exim 4.92) (envelope-from ) id 1la09C-0007rr-CW; Fri, 23 Apr 2021 14:03:22 -0400 Date: Fri, 23 Apr 2021 14:03:22 -0400 From: Bruce Momjian To: Tom Lane Cc: tharakan@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16978: Nested CTEs give ERROR in v13 Message-ID: <20210423180322.GD7629@momjian.us> References: <16978-813ac0832f35db82@postgresql.org> <1313767.1619186886@sss.pgh.pa.us> <20210423145603.GC7629@momjian.us> <1356187.1619192255@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Nq2Wo0NMKNjxTN9z" Content-Disposition: inline In-Reply-To: <1356187.1619192255@sss.pgh.pa.us> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Nq2Wo0NMKNjxTN9z Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Fri, Apr 23, 2021 at 11:37:35AM -0400, Tom Lane wrote: > Bruce Momjian 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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion. --Nq2Wo0NMKNjxTN9z Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="cte-13.2.diff" diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index 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.out index 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.sql index 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 -- --Nq2Wo0NMKNjxTN9z--