Обсуждение: cached row type not invalidated after DDL change
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
regards
Manuel Kniep
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.klaver@aklaver.com