Trigger bug ?

On 5/22/19 2:33 PM, PegoraroF10 wrote:

We created a usual trigger which seemed to be not firing or not working
properly. Then we put some raise notices on it and saw that it was returning
Null values. But why, it´s after insert and is a primary key, cannot be
null.

https://www.postgresql.org/docs/11/plpgsql-trigger.html
"The return value of a row-level trigger fired AFTER or a
statement-level trigger fired BEFORE or AFTER is always ignored; it
might as well be null. However, any of these types of triggers might
still abort the entire operation by raising an error."

More below.

create function MyFunction() returns trigger as $$
begin
raise '%', new.MyPK;
end$$ language plpgsql;

create trigger MyTrigger after insert on MyTable execute procedure
MyFunction();

We forgot FOR EACH ROW/STATEMENT when created our trigger.

On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why did
it accepted our command to create that trigger ?

https://www.postgresql.org/docs/11/sql-createtrigger.html
"FOR EACH ROW
FOR EACH STATEMENT

This specifies whether the trigger function should be fired once
for every row affected by the trigger event, or just once per SQL
statement. If neither is specified, FOR EACH STATEMENT is the default.
Constraint triggers can only be specified FOR EACH ROW.
"

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, May 22, 2019 at 2:33 PM PegoraroF10 <marcos@f10.com.br> wrote:

We forgot FOR EACH ROW/STATEMENT when created our trigger.

On docs it´s written {ROW/STATEMENT} between {}, so it´s needed. But why
did
it accepted our command to create that trigger ?

You only partially absorbed the syntax doc for that...in full it reads:

...
[ FOR [ EACH ] { ROW | STATEMENT } ]

You omitted the whole thing which is allowed.

David J.

sorry, I wasn´t clear, when I sad returning Null I tried to say that new.PK
was Null, not the function result.
Try to create exactly what I sent you and you´ll see null values on that pk.
And that occurs just because that trigger was created without EACH
ROW/STATEMENT.
So, my question is, PG should return an error and not create that trigger
because ROW/STATEMENT was not specified, correct ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On Wed, May 22, 2019 at 3:21 PM PegoraroF10 <marcos@f10.com.br> wrote:

Try to create exactly what I sent you and you´ll see null values on that
pk.

You should probably submit self-contained examples if you need the user to
do this.

And that occurs just because that trigger was created without EACH
ROW/STATEMENT.

So, my question is, PG should return an error and not create that trigger

because ROW/STATEMENT was not specified, correct ?

PostgreSQL doesn't inspect the called function to see if it is written
correctly for a given invocation of CREATE TRIGGER. So, no, given that
your command was syntactically valid PostgreSQL doesn't have any reason to
return an error.

David J.

I´m not saying it should inspect function code, but I think it should deny
when I try to create a trigger missing a needed argument.

When I do ...
create table MyTable(integer);
gives me an "syntax error at end of input" because I forgot field name.

why when I do ...
create trigger MyTrigger after insert on MyTable execute procedure
MyFunction();
It does not gives me something similar ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 5/22/19 3:21 PM, PegoraroF10 wrote:

sorry, I wasn´t clear, when I sad returning Null I tried to say that new.PK
was Null, not the function result > Try to create exactly what I sent you and you´ll see null values on

that pk.

Yes because FOR EACH STATEMENT may deal with multiple rows, so it is
undefined what NEW.pk is referring to.

More below.

And that occurs just because that trigger was created without EACH
ROW/STATEMENT.
So, my question is, PG should return an error and not create that trigger
because ROW/STATEMENT was not specified, correct ?

It was implicitly specified. Per the docs it will be FOR EACH STATEMENT
by default in the absence of user input.

If you just need the new PK why not?:

INSERT INTO some_table ... RETURNING pk;

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, May 22, 2019 at 3:41 PM PegoraroF10 <marcos@f10.com.br> wrote:

I´m not saying it should inspect function code, but I think it should deny
when I try to create a trigger missing a needed argument.

Guessing you missed my earlier response...

When I do ...
create table MyTable(integer);
gives me an "syntax error at end of input" because I forgot field name.

why when I do ...
create trigger MyTrigger after insert on MyTable execute procedure
MyFunction();
It does not gives me something similar ?

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event
[ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY
DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [
... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )

The part about { ROW | STATEMENT } is within an optional clause.

David J.

ok, you won. Sorry, I didn´t read that "If neither is specified, FOR EACH
STATEMENT is the default"

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html