Re: Using real libpq parameters

On Feb 27, 2011, at 5:49 AM, Daniele Varrazzo wrote:

> On Sat, Feb 26, 2011 at 11:47 PM, A.M. <agentm(at)themactionfaction(dot)com> wrote:
>
>> Have you looked at libpqtypes? It makes dealing with complex datatypes trivial.
>>
>> http://libpqtypes.esilo.com/
>
> Hi A.M.,
>
> David Blewett had suggested the same just a few hours before you :)
>
> Maybe I'm missing something about libpqtypes: in this case some
> explanation would be appreciated.
>
> 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's C. There's an example here: http://libpqtypes.esilo.com/man3/PQputf.html

PQexecf is just a convenience wrapper around these functions.

>
> About the types, using the bare libpq, psycopg should iterate over the
> python types in input and map every python type into a numeric OID
> (about which psycopg already has knowledge), pack them into a C array
> and send it as paramTypes. Using libpqtypes instead psycopg should
> map the python types into a string - the Postgres name of the types -
> and then mangle the type names into the query string. I don't see
> particular saving in doing the latter instead of the former: there is
> still types mapping to do, and the result should be sprintf'd into a
> new query instead of put into a C array - it seems more clumsy.

If psycopg2 doesn't know the type, then it can pass it as text and set the OID to 0 so the server will try to figure it out. And yes, psycopg2 would be able to delete the OID juggling code, but I see that as a positive step. For backwards compatibility, the register_type function and friends could hook into the libpqtypes OID handler table: http://libpqtypes.esilo.com/browse_source.html?file=handler.c

For numeric types, psycopg would add ints and doubles to the PQParam, saving the server that parsing step.

>
> So, I think libpqtypes is a huge saving for a program that would need
> otherwise a lot of bureaucracy to operate with the libpq (knowledge of
> the Postgres types etc). But psycopg has already sorted out this kind
> of layer.

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 would hope that all queries would go through the extended query protocol if libpqtypes were to be used.) Clearly, some runtime option would need to be provided through Python.

However, I believe (as a matter of opinion) that the multi-statement execution string is the worst misfeature of the v1 protocol. As you mentioned, there is no such option in v2, but I don't believe that to be a loss. However, it would definitely be a backwards-compatibility issue.

I suspect that using libpqtypes would allow psycopg2 to delete a lot of its bureacracy.

>
> Then, you may be referring to some different libpqtypes functions and
> I may have misunderstood both David's and your advice. In this case I
> would be grateful if you could illuminate me about what part of
> libpqtypes would be great for psycopg to use: I may be too focused on
> PQexecf and completely missing the forest for the trees.

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

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 implementations of user-defined types (fast!)
- is actively developed and maintained (as a license-compatible project) with developers who would be receptive to assisting this 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)

Cheers,
M