Re: Multiple queries in transit

On Mon, Oct 31, 2011 at 1:08 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.

heh, sorry for the noise here :-).

merlin