BUG #5503: error in trigger function with dropped columns

From: "Maksym Boguk" <Maxim(dot)Boguk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5503: error in trigger function with dropped columns
Date: 2010-06-13 09:51:01
Message-ID: 201006130951.o5D9p1NE089091@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5503
Logged by: Maksym Boguk
Email address: Maxim(dot)Boguk(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Hartmut Goebel 2010-06-13 15:43:48 Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Previous Message xMoMx 2010-06-13 09:05:33 BUG #5502: Preparing an array return Bug