BUG #15276: pl/pgSQL function caches wrong plan

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: postgresql(at)gasparesganga(dot)com
Subject: BUG #15276: pl/pgSQL function caches wrong plan
Date: 2018-07-11 17:45:51
Message-ID: 153133115170.1402.14759411348166936516@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15276
Logged by: Gaspare Sganga
Email address: postgresql(at)gasparesganga(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2018-07-11 21:05:05 Re: BUG #15273: Lexer bug with UESCAPE
Previous Message Andres Freund 2018-07-11 17:21:16 Re: BUG #15275: Trigger don't take supperuser role into account to create role