Обсуждение: 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. +