Thread: BUG #16978: Nested CTEs give ERROR in v13
The following bug has been logged on the website:
Bug reference: 16978
Logged by: Robins Tharakan
Email address: tharakan@gmail.com
PostgreSQL version: 13.2
Operating system: Ubuntu
Description:
This narrowed down SQL (from what SQLSmith generated) seems to error out
only with N levels of CTE nesting - no tables / views / 1-row output.
The SQL works fine with v12.4 but raises an ERROR in v13 which seems like a
regression. Is this owing a recent change in v13 - that could affect this?
(This was found during a larger investigation around a v13 SegFault but this
does seem worthy of its own bug-report).
→ psql -h localhost
psql (14devel, server 13.2)
Type "help" for help.
postgres=# --Having w_err in the SQL gives an error about UNION ALL
postgres=# WITH RECURSIVE w6(c6) AS
postgres-# (WITH w6(c6) AS
postgres(# (WITH w8(c8) AS
postgres(# (WITH w9(c9) AS
postgres(# (WITH w10(c10) AS
postgres(# (WITH w11(c11) AS
postgres(# (WITH w_err(c12) AS
postgres(# (SELECT 1)
postgres(# SELECT * FROM w_err)
postgres(# SELECT * FROM w11)
postgres(# SELECT * FROM w10)
postgres(# SELECT * FROM w9)
postgres(# SELECT * FROM w8)
postgres(# SELECT * FROM w6)
postgres-# SELECT * FROM w6;
ERROR: recursive query "w6" does not have the form non-recursive-term UNION
[ALL] recursive-term
LINE 1: WITH RECURSIVE w6(c6) AS
^
postgres=#
postgres=#
postgres=#
postgres=# -- Removing w_err it works
postgres=# WITH RECURSIVE w6(c6) AS
postgres-# (WITH w6(c6) AS
postgres(# (WITH w8(c8) AS
postgres(# (WITH w9(c9) AS
postgres(# (WITH w10(c10) AS
postgres(# (WITH w11(c11) AS
postgres(# -- (WITH w_err(c12) AS
postgres(# (SELECT 1)
postgres(# -- SELECT * FROM w_err)
postgres(# SELECT * FROM w11)
postgres(# SELECT * FROM w10)
postgres(# SELECT * FROM w9)
postgres(# SELECT * FROM w8)
postgres(# SELECT * FROM w6)
postgres-# SELECT * FROM w6;
c6
----
1
(1 row)
postgres=> \q
→ r.sh 12
psql (14devel, server 12.4)
Type "help" for help.
postgres=> -- No error on v12 with or without w_err
postgres=>
postgres=> WITH RECURSIVE w6(c6) AS
postgres-> (WITH w6(c6) AS
postgres(> (WITH w8(c8) AS
postgres(> (WITH w9(c9) AS
postgres(> (WITH w10(c10) AS
postgres(> (WITH w11(c11) AS
postgres(> (WITH w_err(c12) AS
postgres(> (SELECT 1)
postgres(> SELECT * FROM w_err)
postgres(> SELECT * FROM w11)
postgres(> SELECT * FROM w10)
postgres(> SELECT * FROM w9)
postgres(> SELECT * FROM w8)
postgres(> SELECT * FROM w6)
postgres-> SELECT * FROM w6;
c6
----
1
(1 row)
postgres=> -- No error on v12 with or without w_err
postgres=>
postgres=> WITH RECURSIVE w6(c6) AS
postgres-> (WITH w6(c6) AS
postgres(> (WITH w8(c8) AS
postgres(> (WITH w9(c9) AS
postgres(> (WITH w10(c10) AS
postgres(> (WITH w11(c11) AS
postgres(> -- (WITH w_err(c12) AS
postgres(> (SELECT 1)
postgres(> -- SELECT * FROM w_err)
postgres(> SELECT * FROM w11)
postgres(> SELECT * FROM w10)
postgres(> SELECT * FROM w9)
postgres(> SELECT * FROM w8)
postgres(> SELECT * FROM w6)
postgres-> SELECT * FROM w6;
c6
----
1
(1 row)
PG Bug reporting form <noreply@postgresql.org> writes:
> This narrowed down SQL (from what SQLSmith generated) seems to error out
> only with N levels of CTE nesting - no tables / views / 1-row output.
> The SQL works fine with v12.4 but raises an ERROR in v13 which seems like a
> regression. Is this owing a recent change in v13 - that could affect this?
FWIW, I don't see any error from this, either in HEAD or the
back branches.
$ cat recursive.sql
WITH RECURSIVE w6(c6) AS
(WITH w6(c6) AS
(WITH w8(c8) AS
(WITH w9(c9) AS
(WITH w10(c10) AS
(WITH w11(c11) AS
(WITH w_err(c12) AS
(SELECT 1)
SELECT * FROM w_err)
SELECT * FROM w11)
SELECT * FROM w10)
SELECT * FROM w9)
SELECT * FROM w8)
SELECT * FROM w6)
SELECT * FROM w6;
$ psql -f recursive.sql
c6
----
1
(1 row)
regards, tom lane
On Fri, Apr 23, 2021 at 10:08:06AM -0400, Tom Lane wrote: > PG Bug reporting form <noreply@postgresql.org> writes: > > This narrowed down SQL (from what SQLSmith generated) seems to error out > > only with N levels of CTE nesting - no tables / views / 1-row output. > > The SQL works fine with v12.4 but raises an ERROR in v13 which seems like a > > regression. Is this owing a recent change in v13 - that could affect this? > > FWIW, I don't see any error from this, either in HEAD or the > back branches. > > $ cat recursive.sql > WITH RECURSIVE w6(c6) AS > (WITH w6(c6) AS > (WITH w8(c8) AS > (WITH w9(c9) AS > (WITH w10(c10) AS > (WITH w11(c11) AS > (WITH w_err(c12) AS > (SELECT 1) > SELECT * FROM w_err) > SELECT * FROM w11) > SELECT * FROM w10) > SELECT * FROM w9) > SELECT * FROM w8) > SELECT * FROM w6) > SELECT * FROM w6; > $ psql -f recursive.sql > c6 > ---- > 1 > (1 row) 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. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
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.
regards, tom lane
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.