Do "after update" trigger block the current transaction?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general