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
