On Sun, Feb 21, 2010 at 9:22 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Merlin Moncure <mmoncure@xxxxxxxxx> writes: >> On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp@xxxxxxxxxxxx> wrote: >>> Is there a way how to listen and trigger the notify messages in the >>> database (+-)immediately and/or to execute additional (trigger) queries >>> in other transactions? > >> The only way that I know of to send notify 'in-transaction' is via >> dblink...you just send 'notify x' as the query which commits and fires >> the action. It doesn't make sense to do this if your outer >> transaction is very short in duration. > > It's not clear that it makes sense to do that in a long transaction, > either. What are you notifying other sessions *about*? Not your own > changes --- they won't be able to see those till you commit. There's > a reason why NOTIFY is delayed till commit ... Heh...I almost mentioned this on the listen/notify thread. There is actually a case for mid transaction notify that I rely on quite a bit: when you need to request information from some client that is attached to your database. The database needs to signal the client and go get the information and return it, preferably _inside_ the notifying transaction so that you can have the information come back as a result to the function that set up the notification. The way I currently do this currently is via dblink establish a receiving record that the client stores it's response data with and block for it in the transaction that set up the dblink, Since it's read committed I can block and wait for the data or a timeout. With immediate notification and payloads, the dblink approach wouldn't be needed. I could establish the receiving record, and notify the client with the id of the record I want the response data in as a payload. It's mainly a parlor trick, but I like being able fetch data from a client in a single transaction based on an event. So, I have to basically state that while I can work around the current state affairs quite nicely, I think that the assertion that you have to necessarily wait for the txn to end before dispatching notify is only_mostly_ true. I'm pretty happy with the way things work now though...the new notification system is awesome. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
