BUG #19106: Potential regression with CTE materialization planning in Postgres 18 - Mailing list pgsql-bugs

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.