[Libpqxx-general] Exec query with binary parameters

[Michael Akinde]

Thanks for the response Maurice, and the reference to the wiki page
(hadn't found that myself previously; that answered some questions).

I suppose our usage patterns are a little special (our database is part of
the backend for the weather site http://www.yr.no). We need to transfer
massive amounts of binary data to the database (a typical loading session
will do 20-30 GB) immediately it gets produced; and it does make a
difference to us whether this takes 20 minutes or five. But our
performance benefits are obviously not representative of what a "normal"
application would see.

I should also point out (as mentioned) that the tests I did on
performance are preliminary (using my laptop - dual core processors, an
awful disk drive, and smaller amount of data). I have not yet managed to
run these tests on any kind of substantial scaled up system, and it may
well be that the performance profile is different with more CPUs and
better drives.

PQExecParams is not without its problems, too. It uses OID's to identify
types (defined in some obscure postgres header), and binary transfer of
the result is inadequate. It seems the the binary output function
doesn't know how to handle NULLs in the return set.

I do recognize the validity of many of the issues stated on the wikipage.
However, a simple T.execParam function (implementing a subset of the
PQExecParam functionality - without binary returns) seems like it would be
a fairly simple addition to the library amd would probably fulfill our
requirements.

I'll be spending some time to properly test the functionality (to verify
that the performance improvement really is there) in the coming days. If
there is a significant performance gain, however, we will need to
exploit it (our users are nothing if not data hungry, and 20 GB today
will easily become 200GB in a year or two) and purely from principle, I
would much prefer to help improve an existing C++ library that we like,
rather than end up building our own. :-)

Regards,

Michael A.

> Hi Michael,
>
> 2009/3/25 Michael Akinde <michael.akinde at met.no>
>
>> Hi,
>>
>> I have now patched in a simple PQExecParams function into
>> connection_base and transaction_base, and preliminary tests are
>> showing 4-5 times improved write performance when using this over
>> T.exec in our application.
>>
>> We would like to see this functionality included in future versions of
>> pqxx. How would we go about contributing this (I assume some more
>> comprehensive tests would need to be implemented, etc.)?
>>
>
> On this page the maintainer gives an opinions on support for binary
> data.
>
> http://www.pqxx.org/development/libpqxx/wiki/BinaryTransfers
>
> But I think your suggestion might be a good compromise to avoid the
> downsides mentioned on the
> reference page. Especially so given the performance improvements you
> measured.
>
> Care to weigh in on this Jeroen?
>
> Kind regards,
> Maurice
>
>
>>
>>
>> Regards,
>>
>> Michael A.
>> WDB / wdb.met.no
>>
>> Michael Akinde wrote:
>>
>>> No interest in this question? It would be nice to know if this is
>>> worth doing, before we spend a bunch of time trying to implement
>>> this.
>>>
>>> Regards,
>>>
>>> Michael A.
>>> WDB / wdb.met.no
>>>
>>> Michael Akinde wrote:
>>>
>>>> Hi,
>>>>
>>>> In our project, we use pqxx to insert large binary objects into a
>>>> postgres database. Recently, we've moved from large objects to bytea
>>>> objects in the database, but have hit upon a complication. Inserting
>>>> very large binary objects apparently forces us to convert the binary
>>>> data to text in order to use T.exec().
>>>>
>>>> libpq, of course, has the PQExecParam function which allows to pass
>>>> variables as binary objects.
>>>>
>>>> Pqxx does not include this functionality, as far as I can see. Or
>>>> have I missed something?
>>>>
>>>> Looking at the implementation of T.exec, it seems that it would be
>>>> easy to include a similar function ( T.execParam ? ) that maps to
>>>> PQExecParam instead of PQExec. Is there anything that would
>>>> complicate that?
>>>>
>>>> Regards,
>>>>
>>>> Michael A.
>>>> WDB / wdb.met.no _______________________________________________
>>>> Libpqxx-general mailing list
>>>> Libpqxx-general at pgfoundry.org
>>>> http://pgfoundry.org/mailman/listinfo/libpqxx-general
>>>>
>>>>
>>>
>>> _______________________________________________
>>> Libpqxx-general mailing list
>>> Libpqxx-general at pgfoundry.org
>>> http://pgfoundry.org/mailman/listinfo/libpqxx-general
>>>
>>>
>>
>>
>> _______________________________________________
>> Libpqxx-general mailing list
>> Libpqxx-general at pgfoundry.org
>> http://pgfoundry.org/mailman/listinfo/libpqxx-general