Re: Binary params in libpq

On Sun, Feb 27, 2011 at 1:13 PM, Daniele Varrazzo
<daniele(dot)varrazzo(at)gmail(dot)com> wrote:
> Hello,
>
> I'm thinking about adding support for PQexecParams and PQprepare in
> Psycopg. I've posted more details yesterday on the Psycopg mailing
> list <http://archives.postgresql.org/psycopg/2011-02/msg00076.php>. I
> have a few preliminary questions:

If you are wrapping libpq, there really is no good reason not to use
the parametrized interfaces exclusively...not doing that is just
asking for SQL injection type problems.

> How stable is the binary representation for the PostgreSQL types? We
> may just pass bytea data in binary format and pass everything else as
> text parameters, or pass different types too as binary, if performace
> would benefit. Did binary format ever changed in the past (at least
> since the introduction of the V3 protocol)? Is such binary
> representation more likely to change in the future respect to the
> textual one, in a way that forward/backward compatibility between
> server and client would be compromised?

The binary representations of types are mostly stable. On average of
about one per release you might see a wire format change...it doesn't
happen that often but you have to be prepared to deal with it.
'money' type is one example -- it was moved from 32 to 64 bit..there
are a couple of others. Backwards compatibility is no problem but
forwards compatibility is going to be problematic because you have no
support from the server. In practice, it usually works ok.

> Query plans for prepared statements may be less efficient than ones
> for queries with bound parameters. Are query plans generated for
> PQexecParams calls as efficient as the ones generated for PQexec? Or
> do they have the same limitation of the ones generated for PQprepare?

Query plans for PQexec is the same for PQexecParams. PQexec can be a
tiny bit more efficient for simple queries but is not worth the risk
unless you are sending 0 parameters.

merlin