Re: NOTIFY/LISTEN in Postgresql

On Mon, Oct 15, 2012 at 6:44 AM, P. Broennimann <peter(dot)broennimann(at)gmail(dot)com
> wrote:

> Thx for the feedback I will take a look.
>
> Here some details. Basically what I'd like to achieve:
>
> Internet <-> AppliA <-> PostgreSQL <-> AppliB
>
> 1) AppliA receives a request from the internet and calls a Pg/SQL function.
> 2) The Pg/SQL function informs AppliB that there is some work waiting
> (NOTIFY).
> 3) AppliB does the work and posts the result back to PostgreSQL.
>
> ... and now here's the problem -> The initial Pg/SQL function should give
> back the result computed by AppliB -> It has to wait somehow and get
> informed when the work from AppliB is done.
>

two options:

1) advisory locks as Craig suggested. Beware of the fact that this will
result in delays however.

2) Return an interrim result to AppliA and the notify AppliA that the new
results are waiting using Listen/Notify (or maybe even pg_message_queue
with an xml payload).

Best wishes,
Chris Travers

>
> Thx & cheers,
> Peter
>
>
>
> 2012/10/15 Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
>
>> On 10/15/2012 08:54 PM, P. Broennimann wrote:
>>
>>> Hi there
>>>
>>> 1) Can a Pg/SQL function "listen" for a notification sent from an
>>> external instance?
>>>
>>
>> No, it's the other way around. A client can `LISTEN` for a `NOTIFY` sent
>> by another client, either directly or via a PL/PgSQL function.
>>
>> What you want is a NOTIFY callback or NOTIFY trigger, something that
>> invokes a function without any client action when a NOTIFY comes in. No
>> such feature exists.
>>
>> I would like my stored function to pause/wait and continue its execution
>>> once an external event (NOTIFY event) occurs.
>>>
>>
>> Use an advisory lock, they're ideal for that job:
>>
>> http://www.postgresql.org/**docs/current/static/explicit-**
>> locking.html#ADVISORY-LOCKS<http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS>
>>
>> 2) In Pg/SQL I can implement a loop (until something happens) to
>>> pause/wait. This costs CPU time -> Is there another solution?
>>>
>>
>> Depends on what you're waiting for. Details?
>>
>> Again, an advisory lock may be a candidate.
>>
>> --
>> Craig Ringer
>>
>
>