On Sun, Feb 21, 2010 at 10:32 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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 small correction here. I would have to have the client establish the record at the ID of the notifier's choosing (probably nextval() on a sequence) and then wait for it to be inserted. Obviously, you can't generate a record and wait around for it to be updated in the same transaction. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
