Re: Bug in triggers

2010/3/1 Robert Haas <robertmhaas(at)gmail(dot)com>
>
> 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

Thanks! This was long time ago, so i reposted it due empty responses.
i think this problem already discussed by Tom Lane, it is about "Row of
nulls OR null row", but i couldn't find this thread in archive.

So if you have null row in plpgsql and assign it to plpgsql var it will be
translated to row of nulls instead null row.
Here it is an example:
It is assign with direct function call:

> CREATE TYPE "type_subrow" AS (
> "typename" VARCHAR
> );
> CREATE TYPE "type_row" AS (
> "typename" VARCHAR,
> "subrow" type_subrow
> );
>
> CREATE OR REPLACE FUNCTION "test_bug"(in_row type_row) RETURNS void AS
> $body$
> DECLARE
> var type_row%rowtype;
> BEGIN
> var := in_row;
> RAISE NOTICE 'Original value: %', in_row;
> RAISE NOTICE 'Assigned value: %', var;
>
> IF var::TEXT <> in_row::TEXT THEN
> RAISE EXCEPTION 'var is not equals in_row';
> END IF;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> SELECT test_bug('("Test",)'::type_row);
>

Will output:

NOTICE: Original value: (Test,"()")
> NOTICE: Assigned value: (Test,"()")
>

As you see - subrow of type row is not null, it is ROW(NULL).

Now see how it will be in trigger:

ROLLBACK;
> BEGIN;
>
> CREATE TYPE "type_subrow" AS (
> "typename" VARCHAR
> );
> CREATE TABLE "type_row" (
> "typename" VARCHAR,
> "subrow" type_subrow
> );
>
> CREATE OR REPLACE FUNCTION "test_bug"() RETURNS trigger AS
> $body$
> DECLARE
> var type_row%rowtype;
> BEGIN
> var := NEW;
> RAISE NOTICE 'Original value: %', NEW;
> RAISE NOTICE 'Assigned value: %', var;
>
> IF var::TEXT <> NEW::TEXT THEN
> RAISE NOTICE 'var is not equals NEW';
> END IF;
>
> RETURN NEW;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> CREATE TRIGGER "t_bug" BEFORE INSERT
> ON type_row FOR EACH ROW
> EXECUTE PROCEDURE "test_bug"();
>
> INSERT INTO type_row VALUES('Test', NULL);
>
Will output:

NOTICE: Original value: (Test,)
> NOTICE: Assigned value: (Test,"()")
> NOTICE: var is not equals NEW
>

As you see - NEW.subrow is null.
But var.subrow is not null, it is ROW(NULL).

Do you understand what is the problem?

>
> 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!!!
> >> >
> >> > **/
> >> >
> >
> >
> >
> > --
> > С уважением
> >
> > Олег Серов
> >

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

Олег Серов