documentation bug - behave of NEW a OLD in plpgsql's triggers
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
[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.htmlNEW
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.
OLDData 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
Attachments:
plpgsql_triggers.patchtext/x-patch; charset=US-ASCII; name=plpgsql_triggers.patchDownload+8-8
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.htmlNEW
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.
OLDData 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 INSERTIf 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
Show quoted text
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
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.htmlNEW
? ?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 INSERTIf 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
Show quoted text
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 testpostgres=# 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 RAISEso 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. +