Обсуждение: Re: [BUGS] documentation bug - behave of NEW a OLD in plpgsql's triggers
[Moving to -docs] On Mon, May 2, 2011 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 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 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
Вложения
Josh Kupershmidt <schmiddy@gmail.com> writes:
> [Moving to -docs]
> On Mon, May 2, 2011 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> It isn't correct. NEW is not declared in DELETE trigger, OLD isn't
>> declared in INSERT
That claim is flat out wrong.
> 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.
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
On Thu, May 5, 2011 at 10:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.
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
Josh Kupershmidt <schmiddy@gmail.com> writes:
> 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.
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
Hello 2011/5/6 Josh Kupershmidt <schmiddy@gmail.com>: > [Moving to -docs] > > On Mon, May 2, 2011 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 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 > > 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? it is correct Regards Pavel Stehule > > Josh >
Josh Kupershmidt wrote: > [Moving to -docs] > > On Mon, May 2, 2011 at 12:00 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > 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 > > 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? Perfect. Applied to 9.0, 9.1, and head. Thanks. Sorry for the delay. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Tue, Sep 6, 2011 at 10:54 PM, Bruce Momjian <bruce@momjian.us> wrote: > Josh Kupershmidt wrote: >> How about a doc tweak like the attached? > > 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
Josh Kupershmidt wrote: > On Tue, Sep 6, 2011 at 10:54 PM, Bruce Momjian <bruce@momjian.us> wrote: > > Josh Kupershmidt wrote: > >> How about a doc tweak like the attached? > > > > 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. OK, reverted. I did not see Tom's comment. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
2011/9/7 Josh Kupershmidt <schmiddy@gmail.com>: > On Tue, Sep 6, 2011 at 10:54 PM, Bruce Momjian <bruce@momjian.us> wrote: >> Josh Kupershmidt wrote: >>> How about a doc tweak like the attached? >> >> 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. 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 > > Josh >
On Wed, Sep 7, 2011 at 03:40:19PM +0200, Pavel Stehule wrote: > 2011/9/7 Josh Kupershmidt <schmiddy@gmail.com>: > > On Tue, Sep 6, 2011 at 10:54 PM, Bruce Momjian <bruce@momjian.us> wrote: > >> Josh Kupershmidt wrote: > >>> How about a doc tweak like the attached? > >> > >> 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. > > 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. 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. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +