Tyler, Mark escribió:
I am almost sure you've defined a BEFORE trigger and you need and AFTER trigger, so it's fired after commiting.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_ <file://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?
<<attachment: smime.p7s>>
