Thread: Do "after update" trigger block the current transaction?
Hi, We are currently evaluating the feasibility of executing long-running scripts written in shell-script (plsh) called by triggers (after update/insert) to synchronize two databases. Will triggers (after update specifically) cause the execution of SQL-commands to pause until the trigger-function has returned (at statement execution time or commit)? The other possible approach would be to use polling on some trigger-updated timestamp-column, which is not pretty but should be fairly simple. Thank you in advance, Clemens
On 26/03/13 08:52, Clemens Eisserer wrote: > Hi, > > We are currently evaluating the feasibility of executing long-running > scripts written in shell-script (plsh) called by triggers (after > update/insert) to synchronize two databases. Will triggers (after > update specifically) cause the execution of SQL-commands to pause > until the trigger-function has returned (at statement execution time > or commit)? The trigger will block. If it didn't then it couldn't abort the transaction if it needed to. > The other possible approach would be to use polling on some > trigger-updated timestamp-column, which is not pretty but should be > fairly simple. Why not use one of the established trigger-based replication solutions? -- Richard Huxton Archonet Ltd
Hi Richard, >> Will triggers (after >> update specifically) cause the execution of SQL-commands to pause >> until the trigger-function has returned (at statement execution time >> or commit)? > > The trigger will block. If it didn't then it couldn't abort the transaction > if it needed to. Thanks for the clarification. > Why not use one of the established trigger-based replication solutions? Because the "other" database which I would like to keep in sync is a MySQL db. Furthermore I do not need a 1:1 replica, but instead just update a few columns in different tables there. My inital plan was to add a timestamp-column which is updated at every Update and to poll for changes every 5-10s. However, the word "polling" seems to cause an allergic reaction for some poeple ;) Thanks, Clemens
On 26/03/13 13:24, Clemens Eisserer wrote: > Hi Richard, > >>> Will triggers (after >>> update specifically) cause the execution of SQL-commands to pause >>> until the trigger-function has returned (at statement execution time >>> or commit)? >> >> The trigger will block. If it didn't then it couldn't abort the transaction >> if it needed to. > > Thanks for the clarification. > >> Why not use one of the established trigger-based replication solutions? > > Because the "other" database which I would like to keep in sync is a > MySQL db. Furthermore I do not need a 1:1 replica, but instead just > update a few columns in different tables there. > > My inital plan was to add a timestamp-column which is updated at every > Update and to poll for changes every 5-10s. However, the word > "polling" seems to cause an allergic reaction for some poeple ;) Might be worth looking at PgQ - a queueing system underlying Londiste. That would handle tracking the changes in PostgreSQL leaving you to just handle the MySQL end. Timestamps will do the job as long as you are careful to allow enough slack to deal with clock updates. -- Richard Huxton Archonet Ltd
Hi Richard, > Might be worth looking at PgQ - a queueing system underlying Londiste. That > would handle tracking the changes in PostgreSQL leaving you to just handle > the MySQL end. Timestamps will do the job as long as you are careful to > allow enough slack to deal with clock updates. Thanks a lot, PgQ seems to be exactly what I was looking for :-) Another solution that came to my mind is notifying the "replication daemon" using NOTIFY/LISTEN. Thanks again, Clemens