[Libpqxx-general] Exec query with binary parameters

Fri Mar 27 11:03:55 UTC 2009

Hi Michael,

On Thu, Mar 26, 2009 at 9:44 PM, Michael Omotayo Akinde <
michael.o.akinde at met.no> wrote:

>
> 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 wish you luck trying to define what a "normal" application is. :)

>
> 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.
>

IMHO, pqxx should be a C++ interface to postgresql functionality.
To me it is pretty _lame_ that pqxx does not provide access to all
functionality
provided by the underlying C library.

IMHO me pqxx does not qualify as a general C++ library for postgresql
because there is
functionality provided by the C client libraries that is not provided by
pqxx.
People needing this functionality still need to side-step pqxx.

>
> 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 feel pqxx should simply support binary data because the C libraries
support it. This should be sufficient proof that in the eyes of the
Postgresql designers
support for binary database is worthwhile.

The reasons given for not supporting them seem like a lame cop out to me. It
goes like:

"pqxx does not support much of the  binary stuff because it is too difficult
to implement in a way that would keep the library usable".

I feel such statements are almost never true in general and if the statement
is true in this case then I would claim that
the design of pqxx is deficient.

But until I find time to do something about it I guess I'll have to live
with it.

>
>
> 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. :-)
>

As long as your patch works and does not decrease the performance available
to users not
relying on it I would suggest it be provided.

Kind regards,
Maurice

>
> 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
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/libpqxx-general/attachments/20090327/bb906cb5/attachment.html 