Re: Asynchronous Communication across two independent components

On Monday 24 March 2003 15:35, Damien Dougan wrote:
> 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.
>

You can send whole bunch of SQL commands using PQsendQuery, not only one !
After that you can get more than one PGResults depending on what commands you
send to Postgres. You only need to terminate it with ";".
For example:
PQSendQuery("SELECT * FROM pg_table; BEGIN TRANSACTION; DELETE FROM tbl WHERE
id = 1; COMMIT;");
When PQSendQuery command is executed, queries are just send to server.
Non-blocking means: program will not block and wait for results to come !!!
After that you should use combination of PQconsumeInput, PQisBusy and
PQgetResult. You can even cancel rest of the queries with PQrequestCancel.
It is all explained at:
http://developer.postgresql.org/docs/postgres/libpq-async.html , and it works
perfectly (spiking from my experience) !!!

If You need to wait to see if first command passes, and then send another,
threads and non-blocking commands can't help You! Then You simple must
execute with PQExec and wait for result !

>
> 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!
>
You can RAISE NOTICE inside of the function("stored procedure") with
custom text. But You do not need this. PQsendQuery with multiple sql commands
is what you need !

>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html