From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Regina Obe <lr(at)pcorp(dot)us> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: CTE Changes in PostgreSQL 12, can we have a GUC to get old behavior |
Date: | 2019-02-22 20:39:45 |
Message-ID: | 20190222203945.t53vg2ftwtx2l6ua@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 2019-02-22 15:33:08 -0500, Regina Obe wrote:
> The CTE change in PostgreSQL 12 broke several of PostGIS regression tests
> because many of our tests are negative tests that test to confirm we get
> warnings in certain cases. In the past, these would output 1 notice because
> the CTE was materialized, now they output 1 for each column.
>
> An example is as follows:
>
> WITH data AS ( SELECT '#2911' l, ST_Metadata(ST_Rescale( ST_AddBand(
> ST_MakeEmptyRaster(10, 10, 0, 0, 1, -1, 0, 0, 0), 1, '8BUI', 0, 0 ),
> 2.0, -2.0 )) m ) SELECT l, (m).* FROM data;
> The regression errors are easy enough to fix with OFFSET or subquery. What
> I'm more concerned about is that I expect we'll have performance
> degradation.
>
> Historically PostGIS functions haven't been costed right and can't be
> because they rely on INLINING of sql functions which gets broken when too
> high of cost is put on functions. We have a ton of functions like these
> that return composite objects and this above function is particularly
> expensive so to have it call that 10 times is almost guaranteed to be a
> performance killer.
I think there's a fair argument that we shouldn't inline in a way that
increases the number of function calls due to (foo).*. In fact, I'm
mildly surprised that we do that?
> That said IS THERE or can there be a GUC like
>
> set cte_materialized = on;
>
> to get the old behavior?
-incredibly many. That'll just make it harder to understand what SQL
means.
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2019-02-22 21:02:34 | Re: [HACKERS] CLUSTER command progress monitor |
Previous Message | Robbie Harwood | 2019-02-22 20:37:24 | Re: [PATCH v20] GSSAPI encryption support |