Обсуждение: BUG #19106: Potential regression with CTE materialization planning in Postgres 18

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/app/services/element_spatial_service.py#L82-L215

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.

PG Bug reporting form <noreply@postgresql.org> writes:
> After upgrading from Postgres 17 to 18, one of my queries started raising an
> error:
> "unexpected outer reference in CTE query"

I agree that sounds like a bug ...

> The problematic query is:
>
https://github.com/openstreetmap-ng/openstreetmap-ng/blob/eb805d8766fb4b359b96eb6b50acc8c2a835a165/app/services/element_spatial_service.py#L82-L215

... but I am not going to spend time trying to reproduce it given
this amount of detail.  There's too much missing context, like what
data you were running the query on.  I could spend all day, not
see the failure, and be left no wiser than before as to whether
it's already fixed or I just didn't duplicate your context closely
enough.  Please see if you can reduce the problem case to a
self-contained SQL script.

            regards, tom lane

On Sun, Nov 9, 2025, at 16:53, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > After upgrading from Postgres 17 to 18, one of my queries started raising an
> > error:
> > "unexpected outer reference in CTE query"
> 
> I agree that sounds like a bug ...
> 
> > The problematic query is:
> >
https://github.com/openstreetmap-ng/openstreetmap-ng/blob/eb805d8766fb4b359b96eb6b50acc8c2a835a165/app/services/element_spatial_service.py#L82-L215
> 
> ... but I am not going to spend time trying to reproduce it given
> this amount of detail.  There's too much missing context, like what
> data you were running the query on.  I could spend all day, not
> see the failure, and be left no wiser than before as to whether
> it's already fixed or I just didn't duplicate your context closely
> enough.  Please see if you can reduce the problem case to a
> self-contained SQL script.
> 
> regards, tom lane
> 

Hello Tom,

It's my first time here (and realistically on a proper mailing list), so please excuse me. Here's the small,
self-containedreproduction:
 

```
EXPLAIN
SELECT *
FROM (
    SELECT ARRAY[1, 2] AS arr
) r
CROSS JOIN LATERAL (
    WITH a AS (
        SELECT
            CASE
                WHEN id = 1 THEN ST_GeomFromText('LINESTRING(0 0,1 0,1 1)')
                ELSE ST_GeomFromText('POINT(0 0)')
            END AS geom
        FROM unnest(r.arr) AS id
    ),
    b AS (
        SELECT ST_Polygonize(
            (SELECT ST_UnaryUnion(ST_Collect(geom)) FROM a)
        ) AS st_polygonize
    )
    SELECT
        (SELECT st_polygonize FROM b),
        (SELECT st_polygonize FROM b)
) s;
```
```
ERROR:  unexpected outer reference in CTE query 

SQL state: XX000
```

It depends on PostGIS being installed and loaded. In my case, it's version 3.6.0. I tried to make it work without it,
butI couldn't figure it out.
 

-Kamil Monicz