Hey Kenneth,
There are three benefits:
- reduces the CPU overhead in both the client and the DB server
for converting to/from ASCII numbers
You solution based on PL/pgSQL function. I am doubt that execution
of PL/pgSQL parser (loadable module, which aren't built into the
PostgreSQL server) would be cheaper than such conversion.
Furthermore, what if I want to SELECT from table which contains
fields of different types, including arrays? How proposed PL/pgSQL
function (which returns SETOF) can be using in this case?
- decreases the amount of network traffic by more than 1/2
Agree.
- binary transmission of query parameters helps protect against
SQL injection attacks
PQexecParams() was invented to protect users from SQL injections
by separating parameters from SQL command and escaping then
as well. Furthermore, there is no way to execute more than one
command with PQexecParams() (according to the doc, this provides
an extra protection from SQL injections! :-)
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.
On the other hand, programming of such binary transmissions, IMO,
it very tedious and error prone that is not acceptable for highly reliable
systems.
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.
Thanks for sharing!
--
// Dmitriy.