Postgres Pro
Downloads
Home   >   mailing lists

Thread: execute trigger after another one

execute trigger after another one

From
Carlos Carcamo
Date:
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"

Re: execute trigger after another one

From
David G Johnston
Date:
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.

Re: execute trigger after another one

From
Carlos Carcamo
Date:
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"

Re: execute trigger after another one

From
Stephen Cook
Date:
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

Re: execute trigger after another one

From
Adrian Klaver
Date:
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

Re: execute trigger after another one

From
Carlos Carcamo
Date:
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"