Content-Type: text/plain;
  charset="iso-8859-2"
From: Darko Prenosil <darko.prenosil@finteh.hr>
To: Damien Dougan <damien.dougan@mobilecohesion.com>,
	pgsql-interfaces@postgresql.org
Subject: Re: Asynchronous Communication across two independent components
Date: Mon, 24 Mar 2003 21:27:01 +0000
User-Agent: KMail/1.4.1
References: <200303241535.25156.damien.dougan@mobilecohesion.com>
In-Reply-To: <200303241535.25156.damien.dougan@mobilecohesion.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200303242127.01355.darko.prenosil@finteh.hr>

On Monday 24 March 2003 15:35, Damien Dougan wrote:
> Hi All,
>
> I have a problem with how the interface to Postgres works (specifically t=
he
> 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 y=
ou=20
send to Postgres. You only need to terminate it with ";".
For example:=20
PQSendQuery("SELECT * FROM pg_table; BEGIN TRANSACTION; DELETE FROM tbl WHE=
RE=20
id =3D 1; COMMIT;");
When PQSendQuery command is executed, queries are just send to server.=20
Non-blocking means: program will not block and wait for results to come !!!
After that you should use combination of PQconsumeInput, PQisBusy and=20
PQgetResult. You can even cancel rest of the queries with PQrequestCancel.
It is all explained at:=20
http://developer.postgresql.org/docs/postgres/libpq-async.html , and it wor=
ks=20
perfectly (spiking from my experience) !!!

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

>
> 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=20
custom text. But You do not need this. PQsendQuery with multiple sql comman=
ds=20
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 limit=
ed
> 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 servi=
ce
> 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