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

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