BUG #15276: pl/pgSQL function caches wrong plan - Mailing list pgsql-bugs

From PG Bug reporting form
Subject BUG #15276: pl/pgSQL function caches wrong plan
Date
Msg-id 153133115170.1402.14759411348166936516@wrigleys.postgresql.org
Whole thread Raw
List pgsql-bugs
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.


pgsql-bugs by date:

Previous
From: Andres Freund
Date:
Subject: Re: BUG #15275: Trigger don't take supperuser role into account tocreate role
Next
From: Tom Lane
Date:
Subject: Re: BUG #15273: Lexer bug with UESCAPE