BUG #5503: error in trigger function with dropped columns - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5503
Logged by:          Maksym Boguk
Email address:      Maxim.Boguk@gmail.com
PostgreSQL version: 8.4.3
Operating system:   Linux 2.6.18-164
Description:        error in trigger function with dropped columns
Details:

This bug hard to describe. But in general if a table contained dropped
columns you cannot use return record variable in trigger function. Because
you get error like:
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
(3).

Test case:

postgres=# CREATE TABLE test (f1 text, f2 text, f3 text);
CREATE TABLE

postgres=# insert into test values (1,2,3);
INSERT 0 1

CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$
 DECLARE
   _row   record;
 BEGIN
   RAISE NOTICE 'NEW record = %', NEW;
   SELECT * INTO _row FROM test limit 1;
   RAISE NOTICE '_row record = %', _row;
   RETURN _row;
 END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION

postgres=# CREATE TRIGGER test_trigger before insert on test for each row
EXECUTE PROCEDURE test_function();
CREATE TRIGGER

postgres=# insert into test values (1,2,3);
NOTICE:  NEW record = (1,2,3)
NOTICE:  _row record = (1,2,3)
INSERT 0 1

Ok until now all looks good... now lets drop one column from test:

postgres=# ALTER TABLE test drop column f3;
ALTER TABLE
postgres=# insert into test values (1,2);
NOTICE:  NEW record = (1,2)
NOTICE:  _row record = (1,2)
ERROR:  returned row structure does not match the structure of the
triggering table
DETAIL:  Number of returned columns (2) does not match expected column count
(3).
CONTEXT:  PL/pgSQL function "test_function" during function exit

OOPS!

Recreating function doesn't help.
Drop/create trigger again doesn't help too:
postgres=# DROP TRIGGER test_trigger on test;
DROP TRIGGER
postgres=# CREATE TRIGGER test_trigger before insert on test for each row
EXECUTE PROCEDURE test_function();
CREATE TRIGGER
postgres=# insert into test values (1,2);
NOTICE:  NEW record = (1,2)
NOTICE:  _row record = (1,2)
ERROR:  returned row structure does not match the structure of the
triggering table
DETAIL:  Number of returned columns (2) does not match expected column count
(3).
CONTEXT:  PL/pgSQL function "test_function" during function exit

If I drop one more column I start getting next error:
postgres=# ALTER TABLE test drop column f2;
ALTER TABLE
postgres=# insert into test values (1);
NOTICE:  NEW record = (1)
NOTICE:  _row record = (1)
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
(3).
CONTEXT:  PL/pgSQL function "test_function" during function exit

In the same defining _row test%ROWTYPE; producing no errors in both cases.

Thank you very much for your attention.