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

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.