Re: Multiple queries in transit

On Mon, Oct 31, 2011 at 12:49 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Oct 31, 2011 at 12:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>>> On 31.10.2011 17:44, Mark Hills wrote:
>>>> Could libpq be reasonably modified to allow this?
>>
>>> I believe it's doable in theory, no-one has just gotten around to it.
>>> Patches are welcome.
>>
>> Can't you do that today with a multi-command string submitted to
>> PQsendQuery, followed by multiple calls to PQgetResult?
>
> Multi command string queries don't support parameterization.  The way
> I do it is to keep an application managed stack of data (as an array
> of record types) to send that is accumulated when the last stack is in
> transit.  Then when the last response comes in you repeat.

(offlist) in more detail, what I do here is to place action data into
a composite type and parameterize it into an array. That array is
passed directly to a receiving query or a function if what's happening
in the server is complex. We wrote a library for that purpose: see
here:

http://libpqtypes.esilo.com/
and especially here:
http://libpqtypes.esilo.com/man3/pqt-composites.html

so that while the connection is busy, and data is coming in from the
app, you continually PQputf() more records into the array that is
going to be shipped off to the server when the connection becomes
available.

On the query that gets to the server, it can be as simple as:
"insert into foo select unnest(%foo[])"

"select work_on_data(%foo[])"

libpqtypes sends all the data in native binary formats so is very fast.

merlin