Re: Bug in triggers

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(at)gmail(dot)com>:
> Up!, Anybody will answer on this bugreport?
>
> On Fri, Sep 26, 2008 at 2:57 PM, Oleg Serov <serovov(at)gmail(dot)com> wrote:
>>
>> Sorry, bug is not in triggers, it is in PL/PGSQL  var assign mechanism
>> here it is an example:
>> ROLLBACK;
>> BEGIN;
>>
>> CREATE TYPE "composite_type" AS (
>>        "type" VARCHAR,
>>        "type2" VARCHAR
>> );
>>
>>
>> CREATE TABLE "buggy" (
>>        "id" BIGINT NOT NULL,
>>        "bug" "composite_type",
>>        CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
>> ) WITH OIDS;
>>
>>
>> CREATE OR REPLACE FUNCTION "test_bug" () RETURNS pg_catalog.void AS
>> $body$
>> DECLARE
>>    tmp_old buggy%rowtype;
>> BEGIN
>>        tmp_old := ROW(1, NULL)::buggy;
>>        IF tmp_old::text <> ROW(1, NULL)::buggy::text THEN
>>                RAISE EXCEPTION '% <> %', tmp_old, ROW(1, NULL)::buggy;
>>        END IF;
>> END;
>> $body$
>> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>>
>> WILL THROW A EXCEPTION:
>> ERROR:  (1,"(,)") <> (1,)
>>
>>
>> SELECT test_bug();
>>
>>
>> 2008/9/26, Oleg Serov <serovov(at)gmail(dot)com>:
>> > SQL code:
>> >
>> >
>> > ROLLBACK;
>> > BEGIN;
>> > CREATE TYPE "composite_type" AS (
>> >     "typename" VARCHAR
>> > );
>> >
>> >
>> > CREATE TABLE "buggy" (
>> >     "id" BIGINT NOT NULL,
>> >     "bug" "composite_type",
>> >     CONSTRAINT "buggy_pkey" PRIMARY KEY("id")
>> > ) WITH OIDS;
>> >
>> > INSERT INTO buggy (id, bug) VALUES
>> >     (100196418052926086, NULL);
>> >
>> > CREATE OR REPLACE FUNCTION "public"."test_bug" () RETURNS trigger AS
>> > $body$
>> > DECLARE
>> >     tmp_old buggy%rowtype;
>> >     tmp_new buggy%rowtype;
>> > BEGIN
>> >     RAISE NOTICE 'OLD: %', OLD;
>> >     RAISE NOTICE 'NEW: %', NEW;
>> >
>> >         tmp_old := OLD;
>> >         RAISE NOTICE 'TMP OLD: %', tmp_old;
>> >
>> >     RAISE NOTICE 'TMP OLD = OLD => %', tmp_old::text = OLD::text;
>> >
>> >         tmp_old.id := NEW.id;
>> >         tmp_new := NEW;
>> >
>> >     RAISE NOTICE 'TMP OLD: %', tmp_old;
>> >     RAISE NOTICE 'TMP NEW: %', tmp_new;
>> >
>> >     RAISE NOTICE 'TMP OLD = TMP NEW => %', tmp_old::text =
>> > tmp_new::text;
>> >     RAISE NOTICE 'TMP OLD = NEW => %', tmp_old::text = NEW::text;
>> >
>> >
>> >
>> >         IF (tmp_old::text <> tmp_new::text) <> (tmp_old::text <>
>> > NEW::text)
>> > THEN
>> >             RAISE EXCEPTION 'PGSQL BUG!';
>> >         END IF;
>> >     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 =  100112779830304388  WHERE  100196418052926086  =
>> > id;
>> > /**
>> > NOTICE:  OLD: (100196418052926086,)
>> > NOTICE:  NEW: (100112779830304388,)
>> > NOTICE:  TMP OLD: (100196418052926086,"()")
>> > NOTICE:  TMP OLD = OLD => f
>> > NOTICE:  TMP OLD: (100112779830304388,"()")
>> > NOTICE:  TMP NEW: (100112779830304388,"()")
>> > NOTICE:  TMP OLD = TMP NEW => t
>> > NOTICE:  TMP OLD = NEW => f -- BUG!!!
>> >
>> > **/
>> >
>
>
>
> --
> С уважением
>
> Олег Серов
>