Обсуждение: execute trigger after another one
Hello list, hope you're well. I need some help with postgres. I have two triggers that are executed after insert, update and delete. One trigger is with plpgsql language and the other one is with pl/python, the first one just update some tables and last one calls a *.sh that is executed to perform some stuff, both triggers works great, the problem is that I need that the triggers execute in order, first the plpgsql and then the plpython one. I read about order of execution of triggers, is supposed that postgres executes triggers in alphabetical order, so I called the plpgsql a_trigger and the second one b_trigger (as an example), but it seems that the second one always executes first. Is there any way to make triggers execute in a specific order? Thanks in advance. -- "El desarrollo no es material es un estado de conciencia mental"
Carlos Carcamo wrote > I read about order of execution of triggers, is supposed that postgres > executes triggers in alphabetical order, so I called the plpgsql > a_trigger and the second one b_trigger (as an example), but it seems > that the second one always executes first. > > Is there any way to make triggers execute in a specific order? If two triggers would otherwise fire at the same time then alphabetical order is used to break ties. But in all situations before triggers will always fire before after triggers. But since you haven't show us the exact CREATE TRIGGER statements you are using whether that is why yours are not behaving is impossible to tell. Also, you say "it seems" - can you put forth specific proof that one is firing before the other? David J. -- View this message in context: http://postgresql.nabble.com/execute-trigger-after-another-one-tp5829308p5829311.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
2014-12-04 16:46 GMT-06:00 David G Johnston <david.g.johnston@gmail.com>: > Carlos Carcamo wrote >> I read about order of execution of triggers, is supposed that postgres >> executes triggers in alphabetical order, so I called the plpgsql >> a_trigger and the second one b_trigger (as an example), but it seems >> that the second one always executes first. >> >> Is there any way to make triggers execute in a specific order? > > If two triggers would otherwise fire at the same time then alphabetical > order is used to break ties. But in all situations before triggers will > always fire before after triggers. > > But since you haven't show us the exact CREATE TRIGGER statements you are > using whether that is why yours are not behaving is impossible to tell. sorry for that, here some code: -- Trigger #1 CREATE OR REPLACE FUNCTION tgfn_kardex() RETURNS trigger AS $BODY$ BEGIN IF (TG_OP = 'INSERT') THEN --logic here END IF; --more code RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE -- then CREATE TRIGGER tgfn_kardex AFTER INSERT OR UPDATE OR DELETE ON in_kardex FOR EACH ROW EXECUTE PROCEDURE tgfn_kardex(); -- Trigger #2 CREATE OR REPLACE FUNCTION update_remote() RETURNS trigger AS $BODY$ import os os.system('./var/www/update_remote.sh') $BODY$ LANGUAGE plpython3u VOLATILE -- then CREATE TRIGGER update_remote AFTER INSERT OR UPDATE OR DELETE ON in_kardex FOR EACH ROW EXECUTE PROCEDURE update_remote(); > Also, you say "it seems" - can you put forth specific proof that one is > firing before the other? Yes because my update_remote.sh file calls a php file to update a table in mysql, and it is updated after I perform another query to in_kardex, so the mysql table is one query behind postgres... Any thoughts? -- "El desarrollo no es material es un estado de conciencia mental"
On 12/4/2014 6:11 PM, Carlos Carcamo wrote: > Yes because my update_remote.sh file calls a php file to update a > table in mysql, and it is updated after I perform another query to > in_kardex, so the mysql table is one query behind postgres... Any > thoughts? My bet is that the query run from your shell script can't see your changes because the original transaction is still open. -- Stephen
On 12/04/2014 03:11 PM, Carlos Carcamo wrote: > 2014-12-04 16:46 GMT-06:00 David G Johnston <david.g.johnston@gmail.com>: >> Carlos Carcamo wrote >>> I read about order of execution of triggers, is supposed that postgres >>> executes triggers in alphabetical order, so I called the plpgsql >>> a_trigger and the second one b_trigger (as an example), but it seems >>> that the second one always executes first. >>> >>> Is there any way to make triggers execute in a specific order? >> >> If two triggers would otherwise fire at the same time then alphabetical >> order is used to break ties. But in all situations before triggers will >> always fire before after triggers. >> >> But since you haven't show us the exact CREATE TRIGGER statements you are >> using whether that is why yours are not behaving is impossible to tell. > > sorry for that, here some code: > > -- Trigger #1 > CREATE OR REPLACE FUNCTION tgfn_kardex() > RETURNS trigger AS > $BODY$ > BEGIN > IF (TG_OP = 'INSERT') THEN > --logic here > END IF; > --more code > RETURN NULL; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > > -- then > > CREATE TRIGGER tgfn_kardex > AFTER INSERT OR UPDATE OR DELETE > ON in_kardex > FOR EACH ROW > EXECUTE PROCEDURE tgfn_kardex(); > > > -- Trigger #2 > CREATE OR REPLACE FUNCTION update_remote() > RETURNS trigger AS > $BODY$ > import os > os.system('./var/www/update_remote.sh') > $BODY$ > LANGUAGE plpython3u VOLATILE > > -- then > > CREATE TRIGGER update_remote > AFTER INSERT OR UPDATE OR DELETE > ON in_kardex > FOR EACH ROW > EXECUTE PROCEDURE update_remote(); > >> Also, you say "it seems" - can you put forth specific proof that one is >> firing before the other? > > Yes because my update_remote.sh file calls a php file to update a > table in mysql, and it is updated after I perform another query to > in_kardex, so the mysql table is one query behind postgres... > > Any thoughts? You do not say what version of Postgres you are using, but if 9.3+ then you might want to look at the MySQL FDW: https://github.com/EnterpriseDB/mysql_fdw > -- Adrian Klaver adrian.klaver@aklaver.com
2014-12-04 17:31 GMT-06:00 Adrian Klaver <adrian.klaver@aklaver.com>: > On 12/04/2014 03:11 PM, Carlos Carcamo wrote: >> >> 2014-12-04 16:46 GMT-06:00 David G Johnston <david.g.johnston@gmail.com>: >>> >>> Carlos Carcamo wrote >>>> >>>> I read about oGlenrder of execution of triggers, is supposed that postgres >>>> executes triggers in alphabetical order, so I called the plpgsql >>>> a_trigger and the second one b_trigger (as an example), but it seems >>>> that the second one always executes first. >>>> >>>> Is there any way to make triggers execute in a specific order? >>> >>> >>> If two triggers would otherwise fire at the same time then alphabetical >>> order is used to break ties. But in all situations before triggers will >>> always fire before after triggers. >>> >>> But since you haven't show us the exact CREATE TRIGGER statements you are >>> using whether that is why yours are not behaving is impossible to tell. >> >> >> sorry for that, here some code: >> >> -- Trigger #1 >> CREATE OR REPLACE FUNCTION tgfn_kardex() >> RETURNS trigger AS >> $BODY$ >> BEGIN >> IF (TG_OP = 'INSERT') THEN >> --logic here >> END IF; >> --more code >> RETURN NULL; >> END; >> $BODY$ >> LANGUAGE plpgsql VOLATILE >> >> -- then >> >> CREATE TRIGGER tgfn_kardex >> AFTER INSERT OR UPDATE OR DELETE >> ON in_kardex >> FOR EACH ROW >> EXECUTE PROCEDURE tgfn_kardex(); >> >> >> -- Trigger #2 >> CREATE OR REPLACE FUNCTION update_remote() >> RETURNS trigger AS >> $BODY$ >> import os >> os.system('./var/www/update_remote.sh') >> $BODY$ >> LANGUAGE plpython3u VOLATILE >> >> -- then >> >> CREATE TRIGGER update_remote >> AFTER INSERT OR UPDATE OR DELETE >> ON in_kardex >> FOR EACH ROW >> EXECUTE PROCEDURE update_remote(); >> >>> Also, you say "it seems" - can you put forth specific proof that one is >>> firing before the other? >> >> >> Yes because my update_remote.sh file calls a php file to update a >> table in mysql, and it is updated after I perform another query to >> in_kardex, so the mysql table is one query behind postgres... >> >> Any thoughts? > My bet is that the query run from your shell script can't see your changes because the original transaction is still open. --Stephen Cook Good point, I hadn't thought about it... > You do not say what version of Postgres you are using, but if 9.3+ then you > might want to look at the MySQL FDW: I'm using 9.1 > https://github.com/EnterpriseDB/mysql_fdw -- "El desarrollo no es material es un estado de conciencia mental"