Thread: BUG #18325: Possible bug with plpgsql function + ALTER TABLE DROP COLUMN

The following bug has been logged on the website:

Bug reference:      18325
Logged by:          Antti Risteli
Email address:      antti@kiinnost.us
PostgreSQL version: 16.1
Operating system:   Debian Linux
Description:

Hi,

we encountered a production problem after dropping a column that was used in
a
plpgsql function. While I am not 100% certain that this is a bug it was at
least
not mentioned in any documentation I found. While I'm not very familiar
with
PostgreSQL internals I believe the cause might be related to a plan being
cached
and not properly invalidated as the function call works from another
connection
initiated after the ALTER TABLE.

We initially encountered this on version 14.10 but it also happens on
16.1.
Here's a reproduction (which still might not be minimal but it is heavily
simplified from our actual case) when ran from a single connection:

    DROP TABLE IF EXISTS abc;
    CREATE TABLE abc (pk integer, test_id text);
    INSERT INTO abc VALUES (122,'ejife');

    CREATE OR REPLACE FUNCTION test_function() RETURNS integer
    AS $body$
    DECLARE
      abc_record abc;
      abc_records abc[] = array[]::abc[];
      abc_record2 abc;
    BEGIN
      FOR abc_record IN SELECT * FROM abc
      LOOP
        abc_records := abc_records || abc_record; 
        FOR abc_record2 IN SELECT * FROM unnest(abc_records)
        LOOP
          RETURN abc_record2.pk;
        END LOOP;
      END LOOP;
      RETURN 1;
    END
    $body$
    LANGUAGE plpgsql;

    -- this prints 122
    SELECT test_function();

    ALTER TABLE abc DROP COLUMN test_id;

    -- this should print 122
    SELECT test_function();

    -- but it actually prints
    ERROR:  attribute 2 of type record has been dropped
    CONTEXT:  PL/pgSQL function test_function() line 10 at FOR over SELECT
rows

At this point SELECT test_function() works normally on connections opened
after
ALTER TABLE.

Br,
antti