Re: Asynchronous queries - processing listen (notify) in a procedural language

On Sun, Feb 21, 2010 at 9:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Sat, Feb 20, 2010 at 9:38 PM, Petr Chmelar <chmelarp(at)fit(dot)vutbr(dot)cz> 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