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.
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 |