Thread: Trigger bug ?
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.
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 ?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
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