Hi All,
I have a problem with how the interface to Postgres works (specifically the
libpq) and was wondering if there is a known solution to the type of
communication I wish to perform.
Basically, I have two components - one which is responsible for submitting
calls to stored procedures (the Sender), and one which is responsible for
handling the results (the Receiver).
(I've done it this way for a number of reasons - based on our platform
architecture, the need to avoid tying up threads in our platform, and the
need to store binary data in-memory whilst the stored procedure is being
executed - this data will not be held on the database during the life of the
database operation).
Thus my communication problems boil down to having two separate processes - I
want the Receiver to be able to deal with the results of stored procedures
that the Sender has executed.
I looked at the Client Interface functions, and they (PQsendQuery and
PQgetResult) seem to require both the Sender and Receiver to be available on
the same connection (which I don't want to have to do - since I'd be tying up
threads in our platform waiting for PGgetResult to return NULL before I could
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
does not appear to be any mechanism to identify the event that caused the
notification. I was hoping to be able to have the Receiver handle the
notification by going to the appropriate relation and picking up the data
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
communication with separate sender/receiver?
Is there an alternative to this approach (bearing in mind I want to avoid
tying up threads in our platform whilst the database is doing its thing -
especially when there may be delays due to locks from other requests). The
key goal in the design of the solution is request throughput with a limited
number of threads - I don't mind the database locking until some requests are
continued, but I can't have the thread pool available to my own service being
consumed with idle listeners!
Many thanks for any advice and pointers,
Damien