On Mon, Oct 15, 2012 at 8:44 AM, P. Broennimann
<peter.broennimann@gmail.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.
I have done this exact workload many times and it's 100% doable. It
relies on dblink and read committed hacks but it's very robust.
In step step 2, you write out a record that is going to hold the
response from AppliB. It can be a simple text field or whatever you
need. Then you spawn a dblink call and issue the notify (it has to be
dblink, because notifications are not delivered till end of
transaction. After you spawn, you start sleep looping, waiting for
AppliB to set the response field. You can see it because AppliB is
writing it in a different transaction, and in ReadCommitted mode you
are allowed to see other transactions that complete. Once the
response is set (make sure you have a timeout), you can return the
reponse or optionally grab more data.
merlin
