Thread

1. Do "after update" trigger block the current transaction?

   Clemens Eisserer <linuxhippy@gmail.com> — 2013-03-26T08:52:40Z

   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
   
   
   

2. Re: Do "after update" trigger block the current transaction?

   Richard Huxton <dev@archonet.com> — 2013-03-26T11:22:18Z

   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
   
   
   

3. Re: Do "after update" trigger block the current transaction?

   Clemens Eisserer <linuxhippy@gmail.com> — 2013-03-26T13:24:56Z

   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
   
   
   

4. Re: Do "after update" trigger block the current transaction?

   Richard Huxton <dev@archonet.com> — 2013-03-26T14:00:26Z

   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
   
   
   

5. Re: Do "after update" trigger block the current transaction?

   Clemens Eisserer <linuxhippy@gmail.com> — 2013-03-26T14:27:26Z

   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