Hi,
I am trying to set up a PostGreSQL database to send asynchronous notifications when certain inserts or updates are performed on the tables. The idea is I want to have publish / subscirbe model with the database in the centre as the information hub. An application will insert a record into a table and then a notification message is sent to all registered subscribers telling them that record number X has been added to table Y. Each subscriber can then chose to retrieve the record or ignore the notification. This should be near real-time (< 0.5 sec from insert / update to notification reception).
To do the notification I have ported the Spread (www.spread.org) interface for MySQL to Postgres (actually only the send_mesg() part of it). I then have a trigger function which calls the send_mesg() function on an insert or update to the table. All good -- except that when another application receives the message and queries the table the record that caused the notification is not there. It would appear that it only becomes available AFTER the trigger function that fired the message returns.
So the question is - how can I get my trigger function to flush the row so that I can be sure it is available for use prior to the return of the trigger function? Alternatively - how can I tell the trigger function to only execute the send_mesg() after the row is available?
I have not used the LISTEN / NOTIFY interface because:
a) It does not easily support sending any information (yes I know you can set up another table and insert a pointer to the record however that is far from ideal)
b) There is no guarantee on message delivery. Spread allows you to ensure that message is delivered.
Thanks in advance - Mark
IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email.
