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