Up!, Anybody will answer on this bugreport? On Fri, Sep 26, 2008 at 2:57 PM, Oleg Serov <sero...@gmail.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 <sero...@gmail.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!!! > > > > **/ > > > -- С уважением Олег Серов