Re: External (asynchronous) notifications of database updates

On Mon, Jun 20, 2005 at 04:33:43AM +0200, Milan Krcmar wrote:
>
> I have a(n external) system driven by data in a Postgres database. I am
> looking for a functionality, which would asynchronously inform the
> system of any updates into the database, so the system could reflect the
> updates (without having to poll the database at regular basis).
>
> I have been skimming through various Postres' replication tools,
> becasuce replication needs the same functionality in general. All the
> tools I've found are based on, yes, triggers. But I don't know how to
> cope with transactions - strictly speaking with rollbacks:

Have you considered using LISTEN/NOTIFY? The listener should receive
notifications only if the notifying transaction commits.

http://www.postgresql.org/docs/8.0/static/sql-listen.html
http://www.postgresql.org/docs/8.0/static/sql-notify.html
http://www.postgresql.org/docs/8.0/static/libpq-notify.html

> As a row-level 'after insert/update/delete' trigger can create
> an external notification of the change, it will not see the reverse
> change if the current transaction is later rolled back.

Right -- that's a problem with using triggers to perform external
events.

> I don't know how the various replication tools solve this - do you?

Not sure; maybe some of the people who work on such tools can
comment.

> Thanks in advance for any piece of information, now I choose a terrible
> hack - when I get a notification, I wait for 10 seconds and then read
> the whole table... It works for me now, but might not work tomorrow.

What do you mean by "notification"? A trigger-based notification?
Or are you already using LISTEN/NOTIFY?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/