BUG #5503: error in trigger function with dropped columns
От | Maksym Boguk |
---|---|
Тема | BUG #5503: error in trigger function with dropped columns |
Дата | |
Msg-id | 201006130951.o5D9p1NE089091@wwwmaster.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #5503: error in trigger function with dropped columns
|
Список | 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.
В списке pgsql-bugs по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: BUG #5503: error in trigger function with dropped columns