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

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.