Re: Bug in triggers

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!!!

**/

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!!!
>
> **/
>

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!!!
> >
> > **/
> >
>

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

Олег Серов

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

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

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

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

Олег Серов

2010/3/3 Oleg Serov <serovov(at)gmail(dot)com>:
>
>
> 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?

It does seem weird that assigning NEW to var changes the value; I'm
not sure why that happens. Is that what you're asking about?

...Robert

I'm asking to fix this =)

On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 2010/3/3 Oleg Serov <serovov(at)gmail(dot)com>:
> >
> >
> > 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?
>
> It does seem weird that assigning NEW to var changes the value; I'm
> not sure why that happens. Is that what you're asking about?
>
> ...Robert
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Олег Серов

2010/3/3 Oleg Serov <serovov(at)gmail(dot)com>:
> I'm asking to fix this =)
>
> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> It does seem weird that assigning NEW to var changes the value; I'm
>> not sure why that happens.  Is that what you're asking about?

Anyone else have an opinion on whether this is a bug?

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> It does seem weird that assigning NEW to var changes the value; I'm
>> not sure why that happens. Is that what you're asking about?

> Anyone else have an opinion on whether this is a bug?

It's arguably a bug, but since we lack consensus on whether NULL and
ROW(NULL,NULL,...) are the same thing, it's difficult to make a
bulletproof case either way. In any case nothing is likely to get done
about it in the near term because it's wired into plpgsql's
implementation. Changing from row to record representation of such
variables is possible but would probably have side effects, ie, it would
create new compatibility issues of unknown seriousness. I'm not too
optimistic about the performance implications either.

I think it might be material for a TODO item, but I don't want to
consider any immediate fixes.

regards, tom lane

On Fri, Mar 5, 2010 at 2:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> It does seem weird that assigning NEW to var changes the value; I'm
>>> not sure why that happens.  Is that what you're asking about?
>
>> Anyone else have an opinion on whether this is a bug?
>
> It's arguably a bug, but since we lack consensus on whether NULL and
> ROW(NULL,NULL,...) are the same thing, it's difficult to make a
> bulletproof case either way.  In any case nothing is likely to get done
> about it in the near term because it's wired into plpgsql's
> implementation.  Changing from row to record representation of such
> variables is possible but would probably have side effects, ie, it would
> create new compatibility issues of unknown seriousness.  I'm not too
> optimistic about the performance implications either.

I don't know if it is a bug. Different textual representations could
easily happen due to intermediate conversions of datatypes....

For example: I wouldn't expect timestamp::date::text to equal
timestamp::text. Textual representations are not necessarily
consistent.

I guess a better question for Oleg might be:

"Why is it important to you to get this fixed? What are you trying to
do that you can't do without fixing this?"

Best Wishes,
Chris Travers

On Fri, Mar 5, 2010 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> It does seem weird that assigning NEW to var changes the value; I'm
>>> not sure why that happens.  Is that what you're asking about?
>
>> Anyone else have an opinion on whether this is a bug?
>
> It's arguably a bug, but since we lack consensus on whether NULL and
> ROW(NULL,NULL,...) are the same thing, it's difficult to make a
> bulletproof case either way.  In any case nothing is likely to get done
> about it in the near term because it's wired into plpgsql's
> implementation.  Changing from row to record representation of such
> variables is possible but would probably have side effects, ie, it would
> create new compatibility issues of unknown seriousness.  I'm not too
> optimistic about the performance implications either.
>
> I think it might be material for a TODO item, but I don't want to
> consider any immediate fixes.

Have we or can we somehow document why this happens?

...Robert

On Sat, Mar 6, 2010 at 2:12 AM, Chris Travers <chris(at)metatrontech(dot)com>wrote:

> On Fri, Mar 5, 2010 at 2:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >>> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> >>> It does seem weird that assigning NEW to var changes the value; I'm
> >>> not sure why that happens. Is that what you're asking about?
> >
> >> Anyone else have an opinion on whether this is a bug?
> >
> > It's arguably a bug, but since we lack consensus on whether NULL and
> > ROW(NULL,NULL,...) are the same thing, it's difficult to make a
> > bulletproof case either way. In any case nothing is likely to get done
> > about it in the near term because it's wired into plpgsql's
> > implementation. Changing from row to record representation of such
> > variables is possible but would probably have side effects, ie, it would
> > create new compatibility issues of unknown seriousness. I'm not too
> > optimistic about the performance implications either.
>
> I don't know if it is a bug. Different textual representations could
> easily happen due to intermediate conversions of datatypes....
>
> For example: I wouldn't expect timestamp::date::text to equal
> timestamp::text. Textual representations are not necessarily
> consistent.
>
> I guess a better question for Oleg might be:
>
> "Why is it important to you to get this fixed? What are you trying to
> do that you can't do without fixing this?"
>

This bug is not critical, i'm comparing two rows with single structure. and
i cast it to text and compare.

>
> Best Wishes,
> Chris Travers
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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

Олег Серов

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Mar 5, 2010 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It's arguably a bug, but since we lack consensus on whether NULL and
>> ROW(NULL,NULL,...) are the same thing, it's difficult to make a
>> bulletproof case either way.

> Have we or can we somehow document why this happens?

The reason it happens is that the assignment target is a "row" variable,
meaning that it doesn't have concrete existence as a tuple but is just
an alias for a list of scalar variables. So there is no way for it to
represent an atomic NULL; setting each of the individual scalars to NULL
is possible but the result acts more like ROW(NULL,NULL,...).

I'm not sure about documenting that. It seems like an implementation
detail. If we had consensus that the two cases either should or should
not be distinguishable, we could work towards making that happen; but
lacking such consensus I'm hesitant to touch it at all.

regards, tom lane

Accidentally replied to Tom directly. Sending to the list now.

On Sun, Mar 7, 2010 at 9:08 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Mar 5, 2010 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> It's arguably a bug, but since we lack consensus on whether NULL and
>>> ROW(NULL,NULL,...) are the same thing, it's difficult to make a
>>> bulletproof case either way.
>
>> Have we or can we somehow document why this happens?
>
> The reason it happens is that the assignment target is a "row" variable,
> meaning that it doesn't have concrete existence as a tuple but is just
> an alias for a list of scalar variables. So there is no way for it to
> represent an atomic NULL; setting each of the individual scalars to NULL
> is possible but the result acts more like ROW(NULL,NULL,...).

I am going to offer a slightly different perspective here. Oleg is
putting casting both the record and row to text before comparing them.
I personally wouldn't necessarily expect this to be safe across
datatypes. Regardless of whether NULL is the same as ROW(NULL), it
seems that it is inherently questionable to rely on textual
representations of different datatypes in such comparisons.

>
> I'm not sure about documenting that. It seems like an implementation
> detail. If we had consensus that the two cases either should or should
> not be distinguishable, we could work towards making that happen; but
> lacking such consensus I'm hesitant to touch it at all.

I am not sure about that. If we have a ROW variable, how do we know,
when we cast it to text, whether or not a given NULL is really a
single NULL or rather a ROW(NULL,NULL....) variable? Absent such
information, how can you be sure that textual representations will be
equal?

It seems to me the fundamental issue here (which might be worth
documenting) is that NEW is not currently a tuple, so textual
representations of NEW and the tuple cannot be guaranteed to be
identical (because the amount of information in the record is greater
than in the row). This seems to be separate from the question of
whether ROW(NULL...) and NULL are the same from a row comparison
viewpoint.

Hope this adds something to the discussion.

Best Wishes,
Chris Travers

On Sun, Mar 7, 2010 at 12:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Mar 5, 2010 at 5:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> It's arguably a bug, but since we lack consensus on whether NULL and
>>> ROW(NULL,NULL,...) are the same thing, it's difficult to make a
>>> bulletproof case either way.
>
>> Have we or can we somehow document why this happens?
>
> The reason it happens is that the assignment target is a "row" variable,
> meaning that it doesn't have concrete existence as a tuple but is just
> an alias for a list of scalar variables.  So there is no way for it to
> represent an atomic NULL; setting each of the individual scalars to NULL
> is possible but the result acts more like ROW(NULL,NULL,...).
>
> I'm not sure about documenting that.  It seems like an implementation
> detail.  If we had consensus that the two cases either should or should
> not be distinguishable, we could work towards making that happen; but
> lacking such consensus I'm hesitant to touch it at all.

What seems odd to me is that NEW is apparently some other kind of
thing that is not the same kind of thing as the row variable.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> What seems odd to me is that NEW is apparently some other kind of
> thing that is not the same kind of thing as the row variable.

NEW is a record variable, not a row variable. In this context that's
sensible because its actual rowtype is unspecified by the function text.
The implications for row-null handling aren't obvious though :-(

regards, tom lane

2010/3/9 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> What seems odd to me is that NEW is apparently some other kind of
>> thing that is not the same kind of thing as the row variable.
>
> NEW is a record variable, not a row variable.  In this context that's
> sensible because its actual rowtype is unspecified by the function text.
> The implications for row-null handling aren't obvious though :-(
>

is it necessary definition there? This is defined well from context.

regards
Pavel Stehule

>                        regards, tom lane
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

On Tue, Mar 9, 2010 at 7:31 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2010/3/9 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> What seems odd to me is that NEW is apparently some other kind of
>>> thing that is not the same kind of thing as the row variable.
>>
>> NEW is a record variable, not a row variable.  In this context that's
>> sensible because its actual rowtype is unspecified by the function text.
>> The implications for row-null handling aren't obvious though :-(
>>
>
> is it necessary definition there? This is defined well from context.
>
I am assuming that Tom's previous objections may have to do with
C-language triggers as well but I couldn't tell from reading the docs.
This may because I am no C-guru.

I think this behavior is unexpected, but not a bug. The best fix is
documenting the datatype better. Something like adding a paragraph to
chapter 38.9 just above the examples (going off the 8.4 docs):

Please note, NEW and OLD records are not guaranteed to follow the full
internal representation of the tuple in question. In some cases (such
as casting to text) this can create subtle differences which make
comparisons problematic. In some cases you may need to properly cast
NEW and OLD prior to making comparisons.

Best Wishes,
Chris Travers

Chris Travers <chris(at)metatrontech(dot)com> writes:
> I think this behavior is unexpected, but not a bug. The best fix is
> documenting the datatype better. Something like adding a paragraph to
> chapter 38.9 just above the examples (going off the 8.4 docs):

> Please note, NEW and OLD records are not guaranteed to follow the full
> internal representation of the tuple in question. In some cases (such
> as casting to text) this can create subtle differences which make
> comparisons problematic. In some cases you may need to properly cast
> NEW and OLD prior to making comparisons.

We may need to document it, but not like that; it's (a) incorrect and
(b) unhelpful to the reader, who is left without any clear idea of what
to avoid. I think that the real issue here doesn't have anything to do
with NEW/OLD as such, but is related to the representational difference
between record and row variables.

regards, tom lane

I wrote:
> I think that the real issue here doesn't have anything to do
> with NEW/OLD as such, but is related to the representational difference
> between record and row variables.

BTW, just to reinforce that it's not NEW/OLD that's the issue, here's
a simplified version of Oleg's non-trigger example:

CREATE TYPE composite_type AS (
c1 bigint,
c2 bigint
);

CREATE TABLE buggy (
id bigint,
bug composite_type
);

CREATE OR REPLACE FUNCTION test_bug () RETURNS void AS
$body$
DECLARE
tmp_row buggy;
tmp_rec record;
BEGIN
tmp_rec := ROW(1, NULL)::buggy;
tmp_row := tmp_rec;
IF tmp_row::text <> tmp_rec::text THEN
RAISE EXCEPTION '% <> %', tmp_row, tmp_rec;
END IF;
END;
$body$
LANGUAGE plpgsql;

select test_bug();

The issue here isn't even with the declared variables themselves,
it's with the sub-record or sub-row for the composite-type column "bug".
In the value assigned to tmp_rec that's an atomic NULL, but there's no
such representation available when that's copied to tmp_row's sub-row,
so you get ROW(NULL,NULL) instead.

regards, tom lane

On Tue, Mar 9, 2010 at 11:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We may need to document it, but not like that; it's (a) incorrect and
> (b) unhelpful to the reader, who is left without any clear idea of what
> to avoid.  I think that the real issue here doesn't have anything to do
> with NEW/OLD as such, but is related to the representational difference
> between record and row variables.

I agree. That's precisely what I'm confused about.

...Robert

On Wed, Mar 10, 2010 at 8:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Mar 9, 2010 at 11:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We may need to document it, but not like that; it's (a) incorrect and
>> (b) unhelpful to the reader, who is left without any clear idea of what
>> to avoid.  I think that the real issue here doesn't have anything to do
>> with NEW/OLD as such, but is related to the representational difference
>> between record and row variables.
>
> I agree.  That's precisely what I'm confused about.
>

- Show quoted text -
On Wed, Mar 10, 2010 at 8:20 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Mar 9, 2010 at 11:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We may need to document it, but not like that; it's (a) incorrect and
>> (b) unhelpful to the reader, who is left without any clear idea of what
>> to avoid. I think that the real issue here doesn't have anything to do
>> with NEW/OLD as such, but is related to the representational difference
>> between record and row variables.
>
> I agree. That's precisely what I'm confused about.

Additionally, plpgsql uses "record" seemingly to refer to row
variables, so pointing folks to this conversation may not necessarily
clear up confusion....