Re: cached row type not invalidated after DDL change

On 06/12/2015 09:24 AM, Manuel Kniep wrote:
> using a row type variable in a trigger function results in an error
> after changing the table structure
>
> running the following sequence of statements
> (https://gist.github.com/rapimo/accac676f7c8e3557a4d)
>
> CREATE TABLE foo(
> id integer
> );
>
> CREATE FUNCTION foo_trigger() RETURNS trigger LANGUAGE plpgsql AS $_$
> DECLARE
> r foo%rowtype;
> BEGIN
> SELECT NEW.* INTO r;
> RETURN r;
> END;
> $_$;
>
> CREATE TRIGGER foo AFTER INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE
> foo_trigger();
>
> INSERT INTO foo VALUES(1);
>
> ALTER TABLE foo ADD COLUMN flag boolean;
>
> INSERT INTO foo VALUES(2);
>
> results in an error:
>
> ERROR: returned row structure does not match the structure of the
> triggering table
> DETAIL: Number of returned columns (1) does not match expected column
> count (2).
> CONTEXT: PL/pgSQL function foo_trigger() during function exit
>
> for the last statement.
>
> For me it seems like the variable is cached for the open session after
> the first insert.
> Database sessions that didn’t issue an insert on the old structure don’t
> have that problem.
>
> I could reproduce this behavior in postgres 9.4.3, 9.4.1 and 9.4.0

See here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

and here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
>
> regards
>
> Manuel Kniep
>
>
>
>
>
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com