On Thu, Sep 30, 2010 at 10:30:16PM +0400, Dmitriy Igrishin wrote:
> Hey Kenneth,
>
> Thank you for solution. But sorry, personally, I don't clearly
> understand the benefits of this code compared with using
> simple array literals or even array constructors...
> Conversion "overheads" from text? Doubtfully...
>
> --
> // Dmitriy.
There are three benefits:
- reduces the CPU overhead in both the client and the DB server for converting to/from ASCII numbers
- decreases the amount of network traffic by more than 1/2
- binary transmission of query parameters helps protect against SQL injection attacks
Obviously, this is not as important for low performance systems,
with the possible exception of preventing SQL injection attacks,
but on high performance systems, minimizing the CPU overhead
due to data conversions is very useful indeed.
Whether or not someone chooses to use it would need to be
evaluated on a case-by-case basis. I posted the code because
the details on how to use binary array transmission needed
close examination of the documentation as well as the database
sources. If you are using PostgreSQL 8.3 or higher, and can
use an add on library, libpqtypes provides a very nice and
very useful API for managing binary parameter transmission.
I really hope that it could be included in the core PostgreSQL
to help others avoid the need to troll through the grotty
inner-workings of the database to figure out how to do this
using just libpq.
Cheers,
Ken