BUG #16978: Nested CTEs give ERROR in v13
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t70702psql -h localhost -U postgresBuilt from patchset v5 (message #5), July 28, 2026 at 02:21 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t70702_5 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t70702_5 && git checkout t70702_5Patchset v5 (message #5) is on t70702_5
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.