Re: Using real libpq parameters - Mailing list psycopg

On Sun, Feb 27, 2011 at 4:11 PM, A.M. <agentm@themactionfaction.com> wrote:
>
> On Feb 27, 2011, at 5:49 AM, Daniele Varrazzo wrote:

>> What I see is that it offers printf/scanf style parameters passing.
>> This is greatly helpful if you have C variables containing the
>> parameters: it saves building the arrays to be passed to PQexecParams.
>> From their example:
>>
>>  PGresult *res = PQexecf(conn,
>>    "INSERT INTO t VALUES (%int4, %text)", 654321, "some text");
>
> One uses PQparamExec after constructing a PQparam with PQputf- certainly not elegant and easy to mess up, but hey,
it'sC. There's an example here: http://libpqtypes.esilo.com/man3/PQputf.html 
>
> PQexecf is just a convenience wrapper around these functions.

I see, but PQexecf is another variadic function, so my point is the
same. If you have arguments in variables a, b, c, you can call
PQputf("%text, %text, %text", a, b, c) and that's handy. But if you
have an array containing three variables v[] and its length L, how do
you pass them to PQputf?

> Postgresql has a problem with prepared statements in that the execution plan is determined before the values are
bound,so I suspect the old escape-string methods will need to be preserved and pumped through the prepare for '_'. (I
wouldhope that all queries would go through the extended query protocol if libpqtypes were to be used.) Clearly, some
runtimeoption would need to be provided through Python. 

I know about the suboptimal plan Postgres generates in case of
prepared statements. Does it apply for PQexecParams too or just for
PQprepare? I've asked on the -general about this.

> What is most exciting about libpqtypes is that is uses the binary protocol which can bring orders-of-magnitude
performancebenefits for some workloads. 

If there is guarantee it is stable as much as the textual
representation of the data types we'll want to have plenty of it. But
maintainibility has a priority over performance in psycopg and I don't
want to cause problem in future client-server interoperability to gain
some performance.


> So, to summarize, libpqtypes:
> - is a utility wrapper around libpq
> - would allow psycopg to delete a bunch of code surrounding escaping and special type handling while supporting C
implementationsof user-defined types (fast!) 
> - is actively developed and maintained (as a license-compatible project) with developers who would be receptive to
assistingthis project 
> - can offer surprising performance benefits
> - would give psycopg2 a strong competitive advantage over the other 3000 python postgresql client libraries
> - would include some backwards compatibility issues for this project (though nothing insurmountable)

A few things of the library are really interesting, and I have a lot
to learn about the binary protocol. I will study it closer to see if
it can be helpful: I see its utility from the PoV of an end-user
program, but because psycopg is a generic library, and doesn't deal
directly with C variables (but with Python values in C structures rich
of metadata) I have to understand if interfacing to it is really an
improvement respect to interfacing directly to the libpq.

Cheers,

-- Daniele