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

On Sun, Feb 21, 2010 at 10:32 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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

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