Content-Type: text/plain;
  charset="us-ascii"
From: Damien Dougan <damien.dougan@mobilecohesion.com>
Organization: Mobile Cohesion
To: pgsql-interfaces@postgresql.org
Subject: Asynchronous Communication across two independent components
Date: Mon, 24 Mar 2003 15:35:25 +0000
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303241535.25156.damien.dougan@mobilecohesion.com>

Hi All,

I have a problem with how the interface to Postgres works (specifically the=
=20
libpq) and was wondering if there is a known solution to the type of=20
communication I wish to perform.


Basically, I have two components - one which is responsible for submitting=
=20
calls to stored procedures (the Sender), and one which is responsible for=
=20
handling the results (the Receiver).

(I've done it this way for a number of reasons - based on our platform=20
architecture, the need to avoid tying up threads in our platform, and the=
=20
need to store binary data in-memory whilst the stored procedure is being=20
executed - this data will not be held on the database during the life of th=
e=20
database operation).


Thus my communication problems boil down to having two separate processes -=
 I=20
want the Receiver to be able to deal with the results of stored procedures=
=20
that the Sender has executed.


I looked at the Client Interface functions, and they (PQsendQuery and=20
PQgetResult) seem to require both the Sender and Receiver to be available o=
n=20
the same connection (which I don't want to have to do - since I'd be tying =
up=20
threads in our platform waiting for PGgetResult to return NULL before I cou=
ld=20
subtmit another PQsendQuery).

So I'm pretty sure this is not what I'm after.


I also looked at the LISTEN/NOTIFY, which seemed promising. However, there=
=20
does not appear to be any mechanism to identify the event that caused the=
=20
notification. I was hoping to be able to have the Receiver handle the=20
notification by going to the appropriate relation and picking up the data=
=20
which was now ready for it - but there's no way to know which entry it is!


Is there a known Postgres solution to the problem of asynchronous=20
communication with separate sender/receiver?

Is there an alternative to this approach (bearing in mind I want to avoid=
=20
tying up threads in our platform whilst the database is doing its thing -=
=20
especially when there may be delays due to locks from other requests). The=
=20
key goal in the design of the solution is request throughput with a limited=
=20
number of threads - I don't mind the database locking until some requests a=
re=20
continued, but I can't have the thread pool available to my own service bei=
ng=20
consumed with idle listeners!


Many thanks for any advice and pointers,

Damien