[postgis-devel] [BUGS] BUG #12869: PostGIS 2.2 can't compile against 9.5 dev branch

[Paragon Corporation]

Tom,


>> Here one  that come to mind

>> SELECT ST_GeometryN(newgeom,1) As geom1, ST_GeometryN(newgeom,2) As 
>> geom2 FROM ( SELECT ST_Simplify(geom,25)  As newgeom FROM sometable) 
>> As foo;

>> If I am not mistaken -- please correct me if I am wrong.  The above 
>> example would cause ST_Simplify to be called for each ST_GeometryN.

> Indeed, and that is exactly what happens in every PG release for time out
of mind, if that's actually how you're coding it without using any method
that would prevent flattening of the subquery.  Check it with 

> EXPLAIN VERBOSE if you doubt that.  The patch I committed two weeks ago
has no impact whatsoever on this example, because that's not a table-free
subquery.

> I suspect that (a) you are using OFFSET 0 in the subquery, (b) you already
have ST_Simplify marked as volatile, or (c) the performance hit from
multiple evaluations isn't as drastic as you think.

> regards, tom lane

Sorry for the false alarm.  Indeed you are right and the change is not as
much of a change as I thought.

This revised version of the query -- triggers a double call in older
versions of PostgreSQL too and since this would be the more common case not
much has changed in behavior.  I think for raster work most people wrap
these kind of calls in CTEs anyway.

DROP TABLE IF EXISTS t2;
CREATE TABLE t2(rast raster);
INSERT INTO t2(rast)
SELECT ST_SetValue(
			ST_SetValue(
				ST_AddBand(
					ST_MakeEmptyRaster(100, 100, 10, 10,
2, 2, 0, 0, 0),
					1, '32BF', 1, 0
				),
				1, 1, 1, 3.14159
			),
			1, 10, 10, 2.71828
		) As rast;

SELECT
	ST_Value(t.rast, 1, 1, 1),
	ST_Value(t.rast, 1, 10, 10),
	ST_BandNoDataValue(rast, 1)
FROM (
	SELECT ST_Reclass(
		rast,
		ROW(1,
'a-100]:50-1,(-100-1000]:150-50,(1000-10000]:254-150', '8BUI', 0)
	) AS rast  FROM t2
) AS t;


So I'll just put an OFFSET 0 on this regression query and call it a day.

Thanks,
Regina