Thread: Insert with query
By doing insert into a table using a query, it seems that all records of consultation were included bypassing the AFTER INSERT triggers and as few as after all the records already included, the TRIGGER is fired for each record, I wonder if this occurrence is normal.
I created an example that demonstrates my doubt, where the 1st case works properly. The 2nd case is restricted by TRIGGER when I think, that could not be
---- EXEMPLE ----
drop table if exists test;
create table test(chave serial primary key, campo1 integer, campo2 integer, campo3 integer);
CREATE OR REPLACE FUNCTION ft_block()
RETURNS trigger AS
$BODY$
declare
vMax integer;
begin
select max(coalesce(campo3, campo2)) from test where campo1 = new.campo1 into vMax;
if vMax > coalesce(new.campo3, new.campo2) then
raise 'Erro na inclusão: %, %, %, %', new.campo1, new.campo2, new.campo3, vMax;
end if;
return null;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER t_block
AFTER INSERT
ON test
FOR EACH ROW
EXECUTE PROCEDURE ft_block();
create or replace function f_insert_in_test()
returns boolean as
$BODY$
declare
r record;
begin
for r in (select * from (values (1, 1, null), (1, 2, 3), (1, 4, null), (1, 5, null)) as x) loop
insert into test(campo1, campo2, campo3) values(r.column1, r.column2, r.column3);
end loop;
return true;
end;
$BODY$
language plpgsql;
--- 1º CASE -- OK
delete from test;
select f_insert_in_test();
--- 2º CASE --- BLOCK
delete from test;
insert into test(campo1, campo2, campo3) (select * from (values (1, 1, null), (1, 2, 3), (1, 4, null), (1, 5, null)) as x);
select * from test;
Juliano Amaral Chaves <juliano.amaral@hotmail.com> wrote: > By doing insert into a table using a query, it seems that all > records of consultation were included bypassing the AFTER INSERT > triggers and as few as after all the records already included, > the TRIGGER is fired for each record, I wonder if this occurrence > is normal. > > I created an example that demonstrates my doubt, where the 1st > case works properly. The 2nd case is restricted by TRIGGER when I > think, that could not be > [ example showing that an AFTER INSERT trigger sees the modified > table as of the state after the INSERT statement completes, > rather than the state after the insert of the individual row ] I can see where that would be confusing, but things are operating as designed, AFAICS. The trigger fires after the statement, not after each affected row is processed. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 05/31/2013 06:32 AM, Kevin Grittner wrote: > Juliano Amaral Chaves <juliano.amaral@hotmail.com> wrote: > >> By doing insert into a table using a query, it seems that all >> records of consultation were included bypassing the AFTER INSERT >> triggers and as few as after all the records already included, >> the TRIGGER is fired for each record, I wonder if this occurrence >> is normal. >> >> I created an example that demonstrates my doubt, where the 1st >> case works properly. The 2nd case is restricted by TRIGGER when I >> think, that could not be > >> [ example showing that an AFTER INSERT trigger sees the modified >> table as of the state after the INSERT statement completes, >> rather than the state after the insert of the individual row ] > > I can see where that would be confusing, but things are operating > as designed, AFAICS. The trigger fires after the statement, not > after each affected row is processed. But why? The OP specified FOR EACH ROW in the trigger statement. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 05/31/2013 06:32 AM, Kevin Grittner wrote: >> Juliano Amaral Chaves <juliano.amaral@hotmail.com> wrote: >> >>> By doing insert into a table using a query, it seems that all >>> records of consultation were included bypassing the AFTER INSERT >>> triggers and as few as after all the records already included, >>> the TRIGGER is fired for each record, I wonder if this occurrence >>> is normal. >>> >>> I created an example that demonstrates my doubt, where the 1st >>> case works properly. The 2nd case is restricted by TRIGGER when I >>> think, that could not be >> >>> [ example showing that an AFTER INSERT trigger sees the modified >>> table as of the state after the INSERT statement completes, >>> rather than the state after the insert of the individual row ] >> >> I can see where that would be confusing, but things are operating >> as designed, AFAICS. The trigger fires after the statement, not >> after each affected row is processed. > > But why? The OP specified FOR EACH ROW in the trigger statement. Hmm. I went to the SQL spec, and the behavior expected by Juliano seems to be what it mandated by the spec. To quote a couple brief snippets from the spec, the trigger should fire "either immediately before the triggering event or immediately after it, according to its specified trigger action time" and "Every trigger event arises as a consequence of executing some SQL-data change statement." It is clear from the spec that a MERGE *statement*, for example, can cause different types of triggers (INSERT and UPDATE, for example) to fire. The distinction between a "triggering event" and the "data change statement" causing the event is pretty explicit. At least, that's how I interpret the spec. That said, it would be a big behavior change to make it work that way. Such a change could only be considered for a major release, and the benefits of conforming to the standard in this respect would need to be balanced against the cost of making the change and the inevitable breakage of currently-working applications which would certainly result. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 05/31/2013 08:15 AM, Kevin Grittner wrote: > Adrian Klaver <adrian.klaver@gmail.com> wrote: >> On 05/31/2013 06:32 AM, Kevin Grittner wrote: >> >> But why? The OP specified FOR EACH ROW in the trigger statement. > > Hmm. I went to the SQL spec, and the behavior expected by Juliano > seems to be what it mandated by the spec. To quote a couple brief > snippets from the spec, the trigger should fire "either immediately > before the triggering event or immediately after it, according to > its specified trigger action time" and "Every trigger event arises > as a consequence of executing some SQL-data change statement." It > is clear from the spec that a MERGE *statement*, for example, can > cause different types of triggers (INSERT and UPDATE, for example) > to fire. The distinction between a "triggering event" and the > "data change statement" causing the event is pretty explicit. > > At least, that's how I interpret the spec. > > That said, it would be a big behavior change to make it work that > way. Such a change could only be considered for a major release, > and the benefits of conforming to the standard in this respect > would need to be balanced against the cost of making the change and > the inevitable breakage of currently-working applications which > would certainly result. The thing is I thought it was working to spec and the docs would seem to be saying it does: http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html " FOR EACH ROW FOR EACH STATEMENT This specifies whether the trigger procedure 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." Now it is entirely possible I am reading the above wrong and if that is the case I would welcome an explanation of where I am misinterpreting it. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 05/31/2013 08:15 AM, Kevin Grittner wrote: >> Adrian Klaver <adrian.klaver@gmail.com> wrote: >>> On 05/31/2013 06:32 AM, Kevin Grittner wrote: >>> But why? The OP specified FOR EACH ROW in the trigger >>> statement. >> >> I went to the SQL spec, >> To quote [...], the trigger should fire "either immediately >> before the triggering event or immediately after it, [...]" >> The distinction between a "triggering event" and the >> "data change statement" causing the event is pretty explicit. >> it would be a big behavior change to make it work that way. > The thing is I thought it was working to spec and the docs would > seem to be saying it does: > > http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html > " > FOR EACH ROW > FOR EACH STATEMENT > This specifies whether the trigger procedure 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." > > Now it is entirely possible I am reading the above wrong and if > that is the case I would welcome an explanation of where I am > misinterpreting it. Currently on an AFTER ... FOR EACH ROW we fire the trigger once *for* each affected row, that's true. But we don't do it immediately after the *triggering event* -- we do it immediately after the *data change statement*. The issue isn't how many times we execute the trigger, or with what parameters, but *when* it runs. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 06/01/2013 06:47 AM, Kevin Grittner wrote: > Adrian Klaver <adrian.klaver@gmail.com> wrote: >> On 05/31/2013 08:15 AM, Kevin Grittner wrote: >>> Adrian Klaver <adrian.klaver@gmail.com> wrote: >>>> On 05/31/2013 06:32 AM, Kevin Grittner wrote: > >>>> But why? The OP specified FOR EACH ROW in the trigger >>>> statement. >>> > >> The thing is I thought it was working to spec and the docs would >> seem to be saying it does: >> >> http://www.postgresql.org/docs/9.2/interactive/sql-createtrigger.html >> " >> FOR EACH ROW >> FOR EACH STATEMENT >> This specifies whether the trigger procedure 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." >> >> Now it is entirely possible I am reading the above wrong and if >> that is the case I would welcome an explanation of where I am >> misinterpreting it. > > Currently on an AFTER ... FOR EACH ROW we fire the trigger once > *for* each affected row, that's true. But we don't do it > immediately after the *triggering event* -- we do it immediately > after the *data change statement*. The issue isn't how many times > we execute the trigger, or with what parameters, but *when* it > runs. Aah, that was the part I was missing. So to see if I understand, in the OPs case: 1) The first case worked as Juliano expected because the INSERTs where done in a loop where each INSERT was a discrete statement and there was a 1:1 correspondence between statement and triggering event. 2) The second case did not work as expected because the INSERTs where wrapped up in a single statement and the AFTER triggers ran for each row after all the rows where inserted not after each row was inserted. -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 06/01/2013 06:47 AM, Kevin Grittner wrote: >> Currently on an AFTER ... FOR EACH ROW we fire the trigger once >> *for* each affected row, that's true. But we don't do it >> immediately after the *triggering event* -- we do it immediately >> after the *data change statement*. The issue isn't how many times >> we execute the trigger, or with what parameters, but *when* it >> runs. > > Aah, that was the part I was missing. So to see if I understand, in the > OPs case: > > 1) The first case worked as Juliano expected because the INSERTs where > done in a loop where each INSERT was a discrete statement and there was > a 1:1 correspondence between statement and triggering event. > > 2) The second case did not work as expected because the INSERTs where > wrapped up in a single statement and the AFTER triggers ran for each row > after all the rows where inserted not after each row was inserted. Exactly. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 06/01/2013 09:22 AM, Kevin Grittner wrote: > Adrian Klaver <adrian.klaver@gmail.com> wrote: >> On 06/01/2013 06:47 AM, Kevin Grittner wrote: > >>> Currently on an AFTER ... FOR EACH ROW we fire the trigger once >>> *for* each affected row, that's true. But we don't do it >>> immediately after the *triggering event* -- we do it immediately >>> after the *data change statement*. The issue isn't how many times >>> we execute the trigger, or with what parameters, but *when* it >>> runs. >> >> Aah, that was the part I was missing. So to see if I understand, in the >> OPs case: >> >> 1) The first case worked as Juliano expected because the INSERTs where >> done in a loop where each INSERT was a discrete statement and there was >> a 1:1 correspondence between statement and triggering event. >> >> 2) The second case did not work as expected because the INSERTs where >> wrapped up in a single statement and the AFTER triggers ran for each row >> after all the rows where inserted not after each row was inserted. > > Exactly. Hmm. I am going to have to pay more attention to how I move data when using AFTER triggers, for instance using the single row vs multirow forms of INSERT. Thanks for the explanation it was enlightening. > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@gmail.com