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; In prior versions this raster test would return one notice: NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API Now it returns 10 notices because the call is being done 10 times (1 for each column) NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API NOTICE: Raster has default geotransform. Adjusting metadata for use of GDAL Warp API 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 know there is a new MATERIALIZED keyword to get the old behavior, but people are not going to be able to change their apps to introduce new keywords, especially ones meant to be deployed by many versions of PostgreSQL. That said IS THERE or can there be a GUC like set cte_materialized = on; to get the old behavior? Thanks, Regina PostGIS PSC member
