Re: Bug in triggers - Mailing list pgsql-bugs

It's not obvious whether this is the same as one of the various other
problems you've complained about.  If it isn't, an English description
of what you think the problem is would probably improve your odds.
See also:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

...Robert

2010/2/26 Oleg Serov <serovov@gmail.com>:
> Up!, Anybody will answer on this bugreport?
>
> On Fri, Sep 26, 2008 at 2:57 PM, Oleg Serov <serovov@gmail.com> wrote:
>>
>> Sorry, bug is not in triggers, it is in PL/PGSQL =9Avar assign mechanism
>> here it is an example:
>> ROLLBACK;
>> BEGIN;
>>
>> CREATE TYPE "composite_type" AS (
>> =9A =9A =9A =9A"type" VARCHAR,
>> =9A =9A =9A =9A"type2" VARCHAR
>> );
>>
>>
>> CREATE TABLE "buggy" (
>> =9A =9A =9A =9A"id" BIGINT NOT NULL,
>> =9A =9A =9A =9A"bug" "composite_type",
>> =9A =9A =9A =9ACONSTRAINT "buggy_pkey" PRIMARY KEY("id")
>> ) WITH OIDS;
>>
>>
>> CREATE OR REPLACE FUNCTION "test_bug" () RETURNS pg_catalog.void AS
>> $body$
>> DECLARE
>> =9A =9Atmp_old buggy%rowtype;
>> BEGIN
>> =9A =9A =9A =9Atmp_old :=3D ROW(1, NULL)::buggy;
>> =9A =9A =9A =9AIF tmp_old::text <> ROW(1, NULL)::buggy::text THEN
>> =9A =9A =9A =9A =9A =9A =9A =9ARAISE EXCEPTION '% <> %', tmp_old, ROW(1,=
 NULL)::buggy;
>> =9A =9A =9A =9AEND IF;
>> END;
>> $body$
>> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>>
>> WILL THROW A EXCEPTION:
>> ERROR: =9A(1,"(,)") <> (1,)
>>
>>
>> SELECT test_bug();
>>
>>
>> 2008/9/26, Oleg Serov <serovov@gmail.com>:
>> > SQL code:
>> >
>> >
>> > ROLLBACK;
>> > BEGIN;
>> > CREATE TYPE "composite_type" AS (
>> > =9A =9A "typename" VARCHAR
>> > );
>> >
>> >
>> > CREATE TABLE "buggy" (
>> > =9A =9A "id" BIGINT NOT NULL,
>> > =9A =9A "bug" "composite_type",
>> > =9A =9A CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
>> > ) WITH OIDS;
>> >
>> > INSERT INTO buggy (id, bug) VALUES
>> > =9A =9A (100196418052926086, NULL);
>> >
>> > CREATE OR REPLACE FUNCTION "public"."test_bug" () RETURNS trigger AS
>> > $body$
>> > DECLARE
>> > =9A =9A tmp_old buggy%rowtype;
>> > =9A =9A tmp_new buggy%rowtype;
>> > BEGIN
>> > =9A =9A RAISE NOTICE 'OLD: %', OLD;
>> > =9A =9A RAISE NOTICE 'NEW: %', NEW;
>> >
>> > =9A =9A =9A =9A tmp_old :=3D OLD;
>> > =9A =9A =9A =9A RAISE NOTICE 'TMP OLD: %', tmp_old;
>> >
>> > =9A =9A RAISE NOTICE 'TMP OLD =3D OLD =3D> %', tmp_old::text =3D OLD::=
text;
>> >
>> > =9A =9A =9A =9A tmp_old.id :=3D NEW.id;
>> > =9A =9A =9A =9A tmp_new :=3D NEW;
>> >
>> > =9A =9A RAISE NOTICE 'TMP OLD: %', tmp_old;
>> > =9A =9A RAISE NOTICE 'TMP NEW: %', tmp_new;
>> >
>> > =9A =9A RAISE NOTICE 'TMP OLD =3D TMP NEW =3D> %', tmp_old::text =3D
>> > tmp_new::text;
>> > =9A =9A RAISE NOTICE 'TMP OLD =3D NEW =3D> %', tmp_old::text =3D NEW::=
text;
>> >
>> >
>> >
>> > =9A =9A =9A =9A IF (tmp_old::text <> tmp_new::text) <> (tmp_old::text =
<>
>> > NEW::text)
>> > THEN
>> > =9A =9A =9A =9A =9A =9A RAISE EXCEPTION 'PGSQL BUG!';
>> > =9A =9A =9A =9A END IF;
>> > =9A =9A RETURN OLD;
>> > END;
>> > $body$
>> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>> >
>> > CREATE TRIGGER "t_bug" BEFORE UPDATE
>> > ON buggy FOR EACH ROW
>> > EXECUTE PROCEDURE "test_bug"();
>> >
>> >
>> > UPDATE buggy SET id =3D =9A100112779830304388 =9AWHERE =9A100196418052=
926086 =9A=3D
>> > id;
>> > /**
>> > NOTICE: =9AOLD: (100196418052926086,)
>> > NOTICE: =9ANEW: (100112779830304388,)
>> > NOTICE: =9ATMP OLD: (100196418052926086,"()")
>> > NOTICE: =9ATMP OLD =3D OLD =3D> f
>> > NOTICE: =9ATMP OLD: (100112779830304388,"()")
>> > NOTICE: =9ATMP NEW: (100112779830304388,"()")
>> > NOTICE: =9ATMP OLD =3D TMP NEW =3D> t
>> > NOTICE: =9ATMP OLD =3D NEW =3D> f -- BUG!!!
>> >
>> > **/
>> >
>
>
>
> --
> =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD
>
> =EF=CC=C5=C7 =F3=C5=D2=CF=D7
>