pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
trigger Before or After 3+ messages / 3 participants [nested] [flat]
* trigger Before or After @ 2014-11-11 06:38 avpro avpro <avprowebeden@gmail.com> 0 siblings, 2 replies; 3+ messages in thread From: avpro avpro @ 2014-11-11 06:38 UTC (permalink / raw) To: pgsql-performance; pgsql-general@postgresql.org hi, in the pgsql documentation (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) i haven't seen anything referring to: how is affected the data inserted in the new table by a trigger Before Insert compared with a trigger After Insert? and anything related to performance for example: tables: actuals (summarize the total running hours), log (the functional hours are inserted in LOG as time) function: sum view: timeview (where running hours are calculated as a difference) -- Function: sum() -- DROP FUNCTION sum(); CREATE OR REPLACE FUNCTION sum() RETURNS trigger AS $BODY$begin update actuals set hours = hours + (select time from time_view where idlog = (select max(idlog) from timeview)) where actuals.idmac = (SELECT idmac FROM selectedmac) ; return new; end$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION sum() OWNER TO user; --trigger CREATE TRIGGER update_actuals_tg01 AFTER INSERT ON log FOR EACH ROW EXECUTE PROCEDURE sum(); I read somewhere (I don't find the link anymore) that if the trigger is After Insert, the data available in the table LOG might not be available anymore to run the trigger. is that correct? or I might understood wrong? what's the difference related to performance concerning a trigger Before Insert compared with a trigger After Insert? thank you have a sunny day ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [GENERAL] trigger Before or After @ 2014-11-11 08:19 Albe Laurenz <laurenz.albe@wien.gv.at> parent: avpro avpro <avprowebeden@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Albe Laurenz @ 2014-11-11 08:19 UTC (permalink / raw) To: avpro avpro *EXTERN* <avprowebeden@gmail.com>; pgsql-performance; pgsql-general@postgresql.org <pgsql-general@postgresql.org> avpro avpro wrote: > in the pgsql documentation > (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) > > > i haven't seen anything referring to: how is affected the data inserted in the new table by a trigger > Before Insert compared with a trigger After Insert? and anything related to performance In your example (the trigger updates a second table) it should make no difference if the trigger is BEFORE or AFTER INSERT. The difference is that in a BEFORE trigger you can modify the values that will be inserted before the INSERT actually happens. > I read somewhere (I don't find the link anymore) that if the trigger is After Insert, the data > available in the table LOG might not be available anymore to run the trigger. is that correct? or I > might understood wrong? I don't quite understand. You will have access to the OLD and NEW values in both BEFORE and AFTER triggers. In an AFTER trigger, the table row has already been modified. > what's the difference related to performance concerning a trigger Before Insert compared with a > trigger After Insert? I don't think that there is a big difference, but you can easily test it: Insert 100000 rows with a BEFORE trigger on the table and compare the time it takes to inserting 100000 rows with an AFTER trigger. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: trigger Before or After @ 2014-11-11 13:58 Adrian Klaver <adrian.klaver@aklaver.com> parent: avpro avpro <avprowebeden@gmail.com> 1 sibling, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2014-11-11 13:58 UTC (permalink / raw) To: avpro avpro <avprowebeden@gmail.com>; pgsql-performance; pgsql-general@postgresql.org On 11/10/2014 10:38 PM, avpro avpro wrote: > hi, > > in the pgsql documentation > (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) > > i haven't seen anything referring to: how is affected the data inserted > in the new table by a trigger Before Insert compared with a trigger > After Insert? and anything related to performance See bottom of above page and here: http://www.postgresql.org/docs/9.1/static/trigger-definition.html > > thank you > have a sunny day > -- Adrian Klaver adrian.klaver@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-11-11 13:58 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-11-11 06:38 trigger Before or After avpro avpro <avprowebeden@gmail.com> 2014-11-11 08:19 ` Albe Laurenz <laurenz.albe@wien.gv.at> 2014-11-11 13:58 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox