using a row type variable in a trigger function results in an error after changing the table structure
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