The following bug has been logged on the website:
Bug reference: 15276
Logged by: Gaspare Sganga
Email address: postgresql@gasparesganga.com
PostgreSQL version: 10.4
Operating system: Centos 7.5
Description:
PostgreSQL version: 10.4 (also tested back to 9.6 with same results)
I have a pl/pgSQL function to normalize PostGIS geometries that was relying
on set-returning functions inside CASE statements.
Updating to 10+ I had to get rid of them of course and using some LEFT JOIN
LATERAL ON [boolean] seemed like a good idea.
What it is happening is that a single execution of the funcion on one row
works just fine, as well as with a few rows.
But when executed on a whole table with hundreds, thousands or millions of
rows I get an error. But the stranges thing is that trying to re-execute the
same query on that single rows now triggers the error as well.
Here is a test function:
CREATE OR REPLACE FUNCTION test(PAR_geom geometry) RETURNS geometry AS $$
DECLARE
REC_linestrings record;
VAR_is_polygon boolean;
VAR_output geometry;
BEGIN
CASE ST_GeometryType(PAR_geom)
WHEN 'ST_LineString', 'ST_MultiLineString' THEN
VAR_is_polygon := false;
WHEN 'ST_Polygon', 'ST_MultiPolygon' THEN
VAR_is_polygon := true;
ELSE
RETURN PAR_geom;
END CASE;
-- This always shows the RIGHT boolean value, problem is not with this
variable!
RAISE NOTICE 'VAR_is_polygon value : %', VAR_is_polygon;
FOR REC_linestrings IN
SELECT array_agg(COALESCE(ext_rings, (rdump).geom) ORDER BY
(rdump).path[1]) AS geoms
FROM (
SELECT row_number() OVER (PARTITION BY rings) AS r,
COALESCE(rings, source) AS rdump
FROM ST_Dump(PAR_geom) AS
source
LEFT JOIN LATERAL ST_DumpRings(source.geom) AS rings
ON VAR_is_polygon -- Problem is here
) AS d
LEFT JOIN LATERAL ST_ExteriorRing((rdump).geom) AS ext_rings
ON VAR_is_polygon -- And probably will be here too
GROUP BY r
LOOP
/* Something here, we don't really care at the moment */
END LOOP;
/* Something else, who cares. We don't really care about return value
either. */
RETURN VAR_output;
END;
$$ LANGUAGE plpgsql;
Steps:
1) Execute the function on a single row:
SELECT test(ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'));
Ok
SELECT test(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 3)'));
Ok
2) Try to execute the function on a bigger rowset (with mixed geometry
types):
SELECT test(geom) FROM mytable;
ERROR: Input is not a polygon
SQL state: XX000
Context: funzione PL/pgSQL test(geometry) riga 19 a ciclo FOR su
righe SELECT
3) Keeping the same session and executing the previous queries, the result
now surprisingly is:
SELECT test(ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'));
Ok
SELECT test(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 3)'));
ERROR: Input is not a polygon
SQL state: XX000
Context: funzione PL/pgSQL test(geometry) riga 19 a ciclo FOR su
righe SELECT
The error is pretty clear, it's trying to execute a ST_DumpRings() on a
non-polygon geometry, ie. a Linestring.
It's like variable VAR_is_polygon always returned "true", but it actually
returns the right value (false) when the error is triggered (see the RAISE
NOTICE)!
I guess the planner must have cached a version of the query valid for a
large number of previuos cases (polygons).
I tried a second version with a simplified query, leaving just the innermost
LEFT JOIN LATERAL and the result is the same:
SELECT row_number() OVER (PARTITION BY rings) AS r, COALESCE(rings,
source) AS rdump
FROM ST_Dump(PAR_geom) AS source
LEFT JOIN LATERAL ST_DumpRings(source.geom) AS rings ON
VAR_is_polygon -- Problem is definitely here
I know I could write that query in a different way, but this behaviour bugs
me and I would like to understand if there is something I am missing here.
