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