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: 2010-02-26 14:31:21
Message-ID: cec7c6df1002260631x214a0addi1fc6296aaed43860@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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!!!
> >
> > **/
> >
>



-- 
С уважением

Олег Серов

In response to

Responses

pgsql-bugs by date

Next:From: Oleg SerovDate: 2010-02-26 14:32:51
Subject: Re: Bug in PL/PgSQL "SELECT .. INTO" statement parser
Previous:From: Oleg SerovDate: 2010-02-26 14:28:49
Subject: Re: Bug in procedure When you modificate table

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