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(at)monicz(dot)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.