Обсуждение: External (asynchronous) notifications of database updates
Hi List, 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: 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. I don't know how the various replication tools solve this - do you? 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. Milan
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/
Hello Mike, thanks a lot for your posting! > Have you considered using LISTEN/NOTIFY? The listener should receive > notifications only if the notifying transaction commits. Yes. I've seen them in the documentation _but_ missed the information about the notification being delivered after the notifier successfuly commits. Great! This is a (very basic) approach to my problem, although it requires me to create a mechanism exploring what row was exactly changed and how. I might readopt the triggers to ask them for some help, but with caution as race-conditions could easily appear. Thank you for the clue! > > 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. Still an unresolved issue. Those replication tools must have existed before the LISTEN/NOTIFY mechanism has been introduced to PG. Have they? > What do you mean by "notification"? A trigger-based notification? Or > are you already using LISTEN/NOTIFY? I meant a trigger-based notification, let's forget this for now. - Milan Krcmar
On Mon, Jun 20, 2005 at 08:35:46PM +0200, Milan Krcmar wrote: > > > 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. > > Still an unresolved issue. Those replication tools must have existed > before the LISTEN/NOTIFY mechanism has been introduced to PG. Have they? I don't know the history of the replication tools, but the Release Notes for Postgres95 0.03 from ten years ago mention LISTEN/NOTIFY: http://www.postgresql.org/docs/8.0/static/release-0-03.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/