Skip site navigation (1) Skip section navigation (2)

Re: Bug in triggers

From: "Oleg Serov" <serovov(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug in triggers
Date: 2008-09-26 11:57:32
Message-ID: cec7c6df0809260457kc94f3cen5937d664a6a795d8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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!!!
>
> **/
>

In response to

Responses

pgsql-bugs by date

Next:From: Brendan JurdDate: 2008-09-26 14:04:57
Subject: Re: Incorrect "invalid AM/PM string" error from to_timestamp
Previous:From: Oleg SerovDate: 2008-09-26 10:27:46
Subject: Bug in triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group