From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: kamil@monicz.dev
Subject: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
Date: Sun, 09 Nov 2025 13:52:01 +0000
Message-ID: <19106-9dd3668a0734cd72@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19106
Logged by: Kamil Monicz
Email address: kamil@monicz.dev
PostgreSQL version: 18.0
Operating system: NixOS unstable ffcdcf99d65c61956d882df249a9be53e59
Description:
After upgrading from Postgres 17 to 18, one of my queries started raising an
error:
"unexpected outer reference in CTE query"
The problematic query is:
https://github.com/openstreetmap-ng/openstreetmap-ng/blob/eb805d8766fb4b359b96eb6b50acc8c2a835a165/a...
Specifically, the `WITH member_geoms ...` part inside the `LEFT JOIN
LATERAL`.
I was able to resolve the issue by forcing the LATERAL CTEs as NOT
MATERIALIZED:
--- app/services/element_spatial_service.py
+++ app/services/element_spatial_service.py
@@ -155,3 +155,3 @@ rels_computed AS (
LEFT JOIN LATERAL (
- WITH member_geoms AS (
+ WITH member_geoms AS NOT MATERIALIZED (
SELECT ST_Collect(geom_val) AS geom
@@ -179,3 +179,3 @@ rels_computed AS (
),
- noded_geoms AS (
+ noded_geoms AS NOT MATERIALIZED (
SELECT ST_UnaryUnion(ST_Collect(
@@ -186,3 +186,3 @@ rels_computed AS (
),
- polygon_geoms AS (
+ polygon_geoms AS NOT MATERIALIZED (
SELECT ST_UnaryUnion(ST_Collect(
This seems like a regression because in cases where a CTE has an outer
reference, it simply shouldn't be materialized (I don't really know the
Postgres internals). I never expected these CTEs to be materialized. I
simply use them for improved readability.
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, kamil@monicz.dev
Subject: Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
In-Reply-To: <19106-9dd3668a0734cd72@postgresql.org>
* 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