cached row type not invalidated after DDL change

From: Manuel Kniep <m(dot)kniep(at)web(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: cached row type not invalidated after DDL change
Date: 2015-06-12 16:24:43
Message-ID: etPan.557b07cb.66334873.147@Manuels-MacBook-Air-5.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Filipe Pina 2015-06-12 16:25:18 Re: database-level lockdown
Previous Message Holger.Friedrich-Fa-Trivadis 2015-06-12 15:37:35 FW: PostgreSQL and iptables