Re: Problem with async notifications of table updates

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