Thread: BUG #5503: error in trigger function with dropped columns
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.
"Maksym Boguk" <Maxim.Boguk@gmail.com> writes: > This bug hard to describe. But in general if a table contained dropped > columns you cannot use return record variable in trigger function. This is fixed for 9.0 ... or at least the specific test case you provide doesn't fail. We have not risked back-porting the change though, because there are other aspects of what the new code does that might cause people problems, eg http://archives.postgresql.org/pgsql-hackers/2010-03/msg00444.php http://archives.postgresql.org/message-id/6645.1267926354@sss.pgh.pa.us regards, tom lane
I see... but anyway this bug does not allow use return record value from a trigger in table contained at least one dropped column, and even worse trigger will work on fresh loaded copy of production database and would pass all possible tests, but on production database it is stop working. Moreover, full functional system can become broken if single column dropped from table contained such trigger. E.g. functionality of such trigger depends of dropped column history of the table, which is wrong (IMHO). I was tried another test trigger on table with dropped column, and get even more funny results (trigger awaiting return record contained all rows from table include dropped so I tried construct such record): CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$ DECLARE _row record; BEGIN RAISE NOTICE 'NEW record =3D %', NEW; SELECT *,2,3 INTO _row FROM test limit 1; RAISE NOTICE '_row record =3D %', _row; RETURN _row; END; $$ LANGUAGE plpgsql; postgres=3D# insert into test values (1); NOTICE: NEW record =3D (1) NOTICE: _row record =3D (1,2,3) ERROR: returned row structure does not match the structure of the triggering table DETAIL: Returned type integer does not match expected type N/A (dropped column) in column "........pg.dropped.2........". CONTEXT: PL/pgSQL function "test_function" during function exit I think changes in 9.0 now mask actual bug instead of fix it. If I was wrong, still would be useful to know how to use return record from trigger function in that case, because I can't make a working version at all. On Mon, Jun 14, 2010 at 4:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Maksym Boguk" <Maxim.Boguk@gmail.com> writes: >> This bug hard to describe. But in general if a table contained dropped >> columns you cannot use return record variable in trigger function. > > This is fixed for 9.0 ... or at least the specific test case you provide > doesn't fail. =C2=A0We have not risked back-porting the change though, > because there are other aspects of what the new code does that might > cause people problems, eg > http://archives.postgresql.org/pgsql-hackers/2010-03/msg00444.php > http://archives.postgresql.org/message-id/6645.1267926354@sss.pgh.pa.us > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane > --=20 Maxim Boguk Senior Postgresql DBA. Skype: maxim.boguk Jabber: maxim.boguk@gmail.com LinkedIn profile: http://nz.linkedin.com/in/maximboguk =D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/ =D0=A1=D0=B8=D0=BB=D0=B0 =D1=81=D0=BE=D0=BB=D0=BE=D0=BC=D1=83 =D0=BB=D0=BE= =D0=BC=D0=B8=D1=82, =D0=BD=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5 =D0=B2 =D0= =BD=D0=B0=D1=88=D0=B5=D0=B9 =D0=B6=D0=B8=D0=B7=D0=BD=D0=B8 - =D1=81=D0=BE= =D0=BB=D0=BE=D0=BC=D0=B0, =D0=B4=D0=B0 =D0=B8 =D1=81=D0=B8=D0=BB=D0=B0 =D0= =B4=D0=B0=D0=BB=D0=B5=D0=BA=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5.
HI all. Look like no one think this behavior is bug. Then need change documentation probably, because in http://www.postgresql.org/docs/8.4/interactive/plpgsql-trigger.html specified: "To alter the row to be stored, it is possible to replace single values directly in NEW and return the modified NEW, or to build a complete new record/row to return." But in reality returning record or row doesn't work in insert trigger at all in case of target table contained dropped columns. Another interesting test: CREATE TABLE test1 as select * from test; now test1 table have the same structure as test and try construct row instead of record: CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$ DECLARE _row test1%ROWTYPE; BEGIN RAISE NOTICE 'NEW record =3D %', NEW; SELECT * INTO _row FROM test1 limit 1; RAISE NOTICE '_row record =3D %', _row; RETURN _row; END; $$ LANGUAGE plpgsql; CREATE TRIGGER test_trigger before insert on test for each row EXECUTE PROCEDURE test_function(); INSERT INTO test values (1); NOTICE: NEW record =3D (1) NOTICE: _row record =3D (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 So i can't return record, and i can return row from table of the same structure. And that all because trigger function somehow think need return all columns of table including dropped column. If this behavior is not a bug, than documentation should be changed (because "or to build a complete new record/row to return" will never work if table contained dropped columns). On Mon, Jun 14, 2010 at 11:20 AM, Maxim Boguk <maxim.boguk@gmail.com> wrote: > I see... but anyway this bug does not allow use return record value > from a trigger in table contained at least one dropped column, and > even worse trigger will work on fresh loaded copy of production > database and would pass all possible tests, but on production database > it is stop working. Moreover, full functional system can become broken > if single column dropped from table contained such trigger. > E.g. functionality of such trigger depends of dropped column history > of the table, which is wrong (IMHO). > > I was tried another test trigger on table with dropped column, and get > even more funny results (trigger awaiting return record contained all > rows from table include dropped so I tried construct such record): > > CREATE OR REPLACE FUNCTION test_function() RETURNS trigger AS $$ > =C2=A0DECLARE > =C2=A0 _row =C2=A0 record; > =C2=A0BEGIN > =C2=A0 RAISE NOTICE 'NEW record =3D %', NEW; > =C2=A0 SELECT *,2,3 INTO _row FROM test limit 1; > =C2=A0 RAISE NOTICE '_row record =3D %', _row; > =C2=A0 RETURN _row; > =C2=A0END; > $$ LANGUAGE plpgsql; > > postgres=3D# insert into test values (1); > NOTICE: =C2=A0NEW record =3D (1) > NOTICE: =C2=A0_row record =3D (1,2,3) > ERROR: =C2=A0returned row structure does not match the structure of the > triggering table > DETAIL: =C2=A0Returned type integer does not match expected type N/A > (dropped column) in column "........pg.dropped.2........". > CONTEXT: =C2=A0PL/pgSQL function "test_function" during function exit > > I think changes in 9.0 now mask actual bug instead of fix it. If I was > wrong, still would be useful to know how to use return record from > trigger function in that case, because I can't make a working version > at all. > > On Mon, Jun 14, 2010 at 4:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> "Maksym Boguk" <Maxim.Boguk@gmail.com> writes: >>> This bug hard to describe. But in general if a table contained dropped >>> columns you cannot use return record variable in trigger function. >> >> This is fixed for 9.0 ... or at least the specific test case you provide >> doesn't fail. =C2=A0We have not risked back-porting the change though, >> because there are other aspects of what the new code does that might >> cause people problems, eg >> http://archives.postgresql.org/pgsql-hackers/2010-03/msg00444.php >> http://archives.postgresql.org/message-id/6645.1267926354@sss.pgh.pa.us >> >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0regards, tom lane >> > > > > -- > Maxim Boguk > Senior Postgresql DBA. > > Skype: maxim.boguk > Jabber: maxim.boguk@gmail.com > > LinkedIn profile: http://nz.linkedin.com/in/maximboguk > =D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/ > > =D0=A1=D0=B8=D0=BB=D0=B0 =D1=81=D0=BE=D0=BB=D0=BE=D0=BC=D1=83 =D0=BB=D0= =BE=D0=BC=D0=B8=D1=82, =D0=BD=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5 =D0=B2 = =D0=BD=D0=B0=D1=88=D0=B5=D0=B9 =D0=B6=D0=B8=D0=B7=D0=BD=D0=B8 - =D1=81=D0= =BE=D0=BB=D0=BE=D0=BC=D0=B0, =D0=B4=D0=B0 =D0=B8 =D1=81=D0=B8=D0=BB=D0=B0 = =D0=B4=D0=B0=D0=BB=D0=B5=D0=BA=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5. > --=20 Maxim Boguk Senior Postgresql DBA. Skype: maxim.boguk Jabber: maxim.boguk@gmail.com LinkedIn profile: http://nz.linkedin.com/in/maximboguk =D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/ =D0=A1=D0=B8=D0=BB=D0=B0 =D1=81=D0=BE=D0=BB=D0=BE=D0=BC=D1=83 =D0=BB=D0=BE= =D0=BC=D0=B8=D1=82, =D0=BD=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5 =D0=B2 =D0= =BD=D0=B0=D1=88=D0=B5=D0=B9 =D0=B6=D0=B8=D0=B7=D0=BD=D0=B8 - =D1=81=D0=BE= =D0=BB=D0=BE=D0=BC=D0=B0, =D0=B4=D0=B0 =D0=B8 =D1=81=D0=B8=D0=BB=D0=B0 =D0= =B4=D0=B0=D0=BB=D0=B5=D0=BA=D0=BE =D0=BD=D0=B5 =D0=B2=D1=81=D0=B5.
On Sun, Jun 13, 2010 at 7:20 PM, Maxim Boguk <maxim.boguk@gmail.com> wrote: > I think changes in 9.0 now mask actual bug instead of fix it. If I was > wrong, still would be useful to know how to use return record from > trigger function in that case, because I can't make a working version > at all. Why do you think that? I tried both the example you gave here and the example from your followup email on the 15th in 9.0beta, and the behavior seems correct there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company