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 <[EMAIL PROTECTED]>:
> 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!!!
>
> **/
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
