Subject: documentation bug - behave of NEW a OLD in plpgsql's triggers

From: Pavel Stehule <[email-masked]>
Date: Mon, 2 May 2011 18:00:48 +0200

Hello

one czech user reported a bug in documentation -
http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html

NEW

    Data type RECORD; variable holding the new database row for
INSERT/UPDATE operations in row-level triggers. This variable is NULL
in statement-level triggers and for DELETE operations.
OLD

    Data type RECORD; variable holding the old database row for
UPDATE/DELETE operations in row-level triggers. This variable is NULL
in statement-level triggers and for INSERT operations.

It isn't correct. NEW is not declared in DELETE trigger, OLD isn't
declared in INSERT

Regards

Pavel Stehule

---

From: Josh Kupershmidt <[email-masked]>
Date: Thu, 5 May 2011 20:56:36 -0400

[Moving to -docs]

> It isn't correct. NEW is not declared in DELETE trigger, OLD isn't

If I've understood you correctly, the problem is that the docs claim
that the variables are defined with a value of NULL, when in fact they
are undefined. For example, if you try to use variable NEW in a delete
trigger, you'll get an error message like:
|  ERROR:  record "new" is not assigned yet
|  DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.

How about a doc tweak like the attached?

Josh
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 1866e43..6b6cf37 100644
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** RAISE unique_violation USING MESSAGE = '
*** 3279,3286 ****
        <para>
         Data type <type>RECORD</type>; variable holding the new
         database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
!        triggers. This variable is <symbol>NULL</symbol> in statement-level triggers
!        and for <command>DELETE</command> operations.
        </para>
       </listitem>
      </varlistentry>
--- 3279,3286 ----
        <para>
         Data type <type>RECORD</type>; variable holding the new
         database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
!        triggers. This variable is not defined in statement-level triggers
!        or <command>DELETE</command> operations.
        </para>
       </listitem>
      </varlistentry>
*************** RAISE unique_violation USING MESSAGE = '
*** 3291,3298 ****
        <para>
         Data type <type>RECORD</type>; variable holding the old
         database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
!        triggers. This variable is <symbol>NULL</symbol> in statement-level triggers
!        and for <command>INSERT</command> operations.
        </para>
       </listitem>
      </varlistentry>
--- 3291,3298 ----
        <para>
         Data type <type>RECORD</type>; variable holding the old
         database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
!        triggers. This variable is not defined in statement-level triggers
!        or <command>INSERT</command> operations.
        </para>
       </listitem>
      </varlistentry>

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 6 May 2011 04:31:35 +0200

Hello

> How about a doc tweak like the attached?

it is correct

Regards

Pavel Stehule

---

From: Tom Lane <[email-masked]>
Date: Thu, 05 May 2011 22:36:35 -0400

>> It isn't correct. NEW is not declared in DELETE trigger, OLD isn't

That claim is flat out wrong.

> |  DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.

That is, in fact, exactly the behavior you get if you declare a RECORD
variable and set it to NULL.  If these variables were indeed not
declared, you'd get a complaint about "new" not being a known variable.
Observe:

regression=# create function foo(int) returns void as $$
regression$# begin
regression$#   new.x := $1;
regression$# end$$ language plpgsql;
ERROR:  "new.x" is not a known variable
LINE 3:   new.x := $1;
          ^

versus

regression=# create function foo(int) returns void as $$
regression$# declare new record;
regression$# begin
regression$#   new := null;
regression$#   new.x := $1;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select foo(1);
ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "foo" line 5 at assignment

			regards, tom lane

---

From: Josh Kupershmidt <[email-masked]>
Date: Thu, 5 May 2011 22:58:24 -0400

> declared, you'd get a complaint about "new" not being a known variable.

Hrm, guess I learned something. I tested with a trigger function which used:
...
     IF NEW IS NULL THEN
       RAISE NOTICE 'new is null.';
...

which was giving me 'ERROR:  record "new" is not assigned yet' when
used as an on-delete trigger. I am a little surprised that you can't
use IS NULL to test out a record-type variable which you've just
declared to be NULL, e.g. this function blows up:

CREATE OR REPLACE FUNCTION test_trg() RETURNS TRIGGER AS $$
  DECLARE SOMEVAR record;
  BEGIN
     SOMEVAR := NULL;
     IF SOMEVAR IS NULL THEN
       RAISE NOTICE 'somevar is null.';
     END IF;
  RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

with the same error message.

Josh

---

From: Tom Lane <[email-masked]>
Date: Thu, 05 May 2011 23:32:54 -0400

> used as an on-delete trigger.

Hmm ... I wonder whether we couldn't make that case work, since IS NULL
shouldn't particularly care whether the record has a known tuple
structure or not.  Still, it's probably not worth spending effort on ...

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 Sep 2011 22:54:39 -0400 (EDT)

Josh Kupershmidt wrote:
> > ? ?Data type RECORD; variable holding the new database row for
> > ? ?Data type RECORD; variable holding the old database row for

Perfect.  Applied to 9.0, 9.1, and head.  Thanks.  Sorry for the delay.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

---

From: Josh Kupershmidt <[email-masked]>
Date: Wed, 7 Sep 2011 09:21:06 -0400

> Perfect.  Applied to 9.0, 9.1, and head.  Thanks.  Sorry for the delay.

Err, as Tom's first comment in this thread explains, Pavel and I were
both wrong: the variables in question are indeed NULL, not undefined.
I think the docs were fine the way they were.

Josh

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 7 Sep 2011 09:24:27 -0400 (EDT)

Josh Kupershmidt wrote:
> > Perfect. ?Applied to 9.0, 9.1, and head. ?Thanks. ?Sorry for the delay.
> 

OK, reverted.  I did not see Tom's comment.

---

From: Pavel Stehule <[email-masked]>
Date: Wed, 7 Sep 2011 15:40:19 +0200

> I think the docs were fine the way they were.

There is maybe bug - these variables are defined, but they has not
assigned tupledesc, so there is not possible do any test

postgres=# create table omega (a int, b int);
CREATE TABLE
postgres=# create or replace function foo_trig()
postgres-# returns trigger as $$
postgres$# begin
postgres$#   raise notice '%', new;
postgres$#   return null;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# create trigger xxx after delete on omega for each row
execute procedure foo_trig();
CREATE TRIGGER
postgres=# insert into omega values(20);
INSERT 0 1
postgres=# delete from omega;
ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  PL/pgSQL function "foo_trig" line 3 at RAISE

so current text in documentation is not correct too.

Regards

Pavel Stehule

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 15 Aug 2012 20:31:07 -0400

> so current text in documentation is not correct too.

I used your queries to test NEW/OLD on DELETE/INSERT, respectively, and
for statement-level triggers, and you are right that they are
unassigned, not NULL.

The attached patch fixes our documentation for PG 9.3.

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index ab40845..07fba57
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** RAISE unique_violation USING MESSAGE = '
*** 3403,3409 ****
        <para>
         Data type <type>RECORD</type>; variable holding the new
         database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
!        triggers. This variable is <symbol>NULL</symbol> in statement-level triggers
         and for <command>DELETE</command> operations.
        </para>
       </listitem>
--- 3403,3409 ----
        <para>
         Data type <type>RECORD</type>; variable holding the new
         database row for <command>INSERT</>/<command>UPDATE</> operations in row-level
!        triggers. This variable is unassigned in statement-level triggers
         and for <command>DELETE</command> operations.
        </para>
       </listitem>
*************** RAISE unique_violation USING MESSAGE = '
*** 3415,3421 ****
        <para>
         Data type <type>RECORD</type>; variable holding the old
         database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
!        triggers. This variable is <symbol>NULL</symbol> in statement-level triggers
         and for <command>INSERT</command> operations.
        </para>
       </listitem>
--- 3415,3421 ----
        <para>
         Data type <type>RECORD</type>; variable holding the old
         database row for <command>UPDATE</>/<command>DELETE</> operations in row-level
!        triggers. This variable is unassigned in statement-level triggers
         and for <command>INSERT</command> operations.
        </para>
       </listitem>