Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18

Tom Lane <tgl@sss.pgh.pa.us>

From: Tom Lane <tgl@sss.pgh.pa.us>

To: kamil@monicz.dev

Cc: pgsql-bugs@lists.postgresql.org

Date: 2025-11-09T15:53:56Z

Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Don't allow CTEs to determine semantic levels of aggregates.
- b85397481ce3 14.21 landed
- 9f5a58aacf65 15.16 landed
- 35b5c62c3ad9 19 (unreleased) landed
- 1c8c3206f4e0 16.12 landed
- 12bc3291772e 18.2 landed
- 075a763e2d42 17.8 landed
Calculate agglevelsup correctly when Aggref contains a CTE.
- b0cc0a71e0a0 19 (unreleased) cited

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