Thread: libpq and multiple selects in a single query

I was musing a bit today, so don't take this to seriously...

Would it be worth the effort to add to and/or modify the libpq API so 
that multiple selects could be sent to the server in a single request 
and response? Obviously, this is trivial from the command line...

select * from foo; select * from bar;

But - at least as far as I understand the libpq API - only one select 
can be issued using PQexec.

In many of the apps I develop, I find that the majority of the time I 
have to go to the database for a select, I have to select from several 
tables. I haven't done any benchmarking, but given the rule-of-thumb 
that inter-process communication incurs high overhead, I decided that 
being able to issue several statements in one call might be worth 
investigating. Given the holiday season though, I haven't actually done 
much work to see if PQexec actually does support this. I'd be surprised 
if a client-server or n-tier app wouldn't benefit from this 
functionality.

> I was musing a bit today, so don't take this to seriously...
> 
> Would it be worth the effort to add to and/or modify the libpq API so 
> that multiple selects could be sent to the server in a single request 
> and response?
Multiple selects can be sent to the server now.
But as far as I unerstand only last result can
be fetched:
0>pgoblin -s
SELECT date(now());SELECT now()
2002-12-28 12:16:49.321118+03

pgoblin is my direct interface from command line to libpq
with some scripting enhancement

>               Obviously, this is trivial from the command line...
> select * from foo; select * from bar;
You mean "from psql"?
psql is complex tool with its own parser and use
libpq to talk to server

> But - at least as far as I understand the libpq API - only one select 
> can be issued using PQexec.
> 
> In many of the apps I develop, I find that the majority of the time I 
> have to go to the database for a select, I have to select from several 
> tables. I haven't done any benchmarking, but given the rule-of-thumb 
> that inter-process communication incurs high overhead, I decided that 
Usually it is not true - compared to postmaster's
cost for every query.
...or may be... Do you issue PQsetdbLogin for every PQexec?

> being able to issue several statements in one call might be worth 
> investigating. Given the holiday season though, I haven't actually done 
> much work to see if PQexec actually does support this. I'd be surprised 
> if a client-server or n-tier app wouldn't benefit from this 
> functionality.

-- 
@BABOLO      http://links.ru/

Tim Hart <tjhart@mac.com> writes:
> Would it be worth the effort to add to and/or modify the libpq API so 
> that multiple selects could be sent to the server in a single request 
> and response?

You can do it already.  See PQsendQuery and PQgetResult.
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/libpq-async.html
mentions this specifically:
    PQexec can return only one PGresult structure. If the submitted    command string contains multiple SQL
commands,all but the last PGresult are discarded by PQexec.
 

There is a lot of other cruft here to allow nonblocking interaction with
the server, but if all you care about is multiple commands sent in a
single string, you only need
PQsendQuery(...);while ((res = PQgetResult(...))){    process result;    PQclear(res);}
        regards, tom lane