From: Vik Fearing <vik@postgresfriends.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kamil Monicz <kamil@monicz.dev>
Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter@eisentraut.org>
Subject: Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
Date: Tue, 11 Nov 2025 17:53:45 +0100
Message-ID: <f2171b06-8970-4946-9092-72b4668c58e4@postgresfriends.org> (raw)
In-Reply-To: <2184532.1762874669@sss.pgh.pa.us>
References: <19106-9dd3668a0734cd72@postgresql.org>
<1650412.1762703636@sss.pgh.pa.us>
<b23b48b7-0222-4b5e-a986-1d79920a7298@app.fastmail.com>
<1886977.1762794560@sss.pgh.pa.us>
<1980046.1762808758@sss.pgh.pa.us>
<36c1f631-f65f-4bb8-90af-9fafdb683547@postgresfriends.org>
<2184532.1762874669@sss.pgh.pa.us>
On 11/11/2025 16:24, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> On 10/11/2025 22:05, Tom Lane wrote:
>>> I looked at the SQL standard for possible guidance and found none:
>>> they disallow subqueries altogether within aggregate arguments,
>>> so they need not consider such cases.
>> I am not seeing that restriction in the standard.
> Maybe I'm misunderstanding what I read, but in SQL:2021
> 6.9 <set function specification> SR1 says
>
> If <aggregate function> specifies a <general set function>, then
> the <value expression> simply contained in the <general set
> function> shall not contain a <set function specification>
> or a <query expression>.
>
> The predecessor text in SQL99 says
>
> 4) The <value expression> simply contained in <set function
> specification> shall not contain a <set function specification>
> or a <subquery>.
>
> I don't think replacing <subquery> with <query expression> moved the
> goalposts at all, but maybe I'm missing something.
I don't think you are. I was missing that you can't get to <aggregate
function> without going through <set function specification> (or a
window) so I did not see that rule.
I had a rummage through the archives but couldn't easily find the paper
introducing aggregates so I can't see what the justification for that
rule was. This language was not in 1989 but is in 1992. It may just be a
case of "this is what we've implemented so this is what we are specifying."
>> ... MATERIALIZEDing either or both CTEs
>> has no effect, which I find strange.
> The fundamental problem is that the parser is mis-assigning
> agglevelsup; given that, the planner is very likely to get
> confused no matter what other details there are.
Thank you for the explanation.
--
Vik Fearing
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: vik@postgresfriends.org, tgl@sss.pgh.pa.us, kamil@monicz.dev, peter@eisentraut.org
Subject: Re: BUG #19106: Potential regression with CTE materialization planning in Postgres 18
In-Reply-To: <f2171b06-8970-4946-9092-72b4668c58e4@postgresfriends.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