From: | "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com> |
---|---|
To: | psycopg(at)postgresql(dot)org |
Subject: | Re: Using real libpq parameters |
Date: | 2011-02-27 16:11:25 |
Message-ID: | 36393852-402D-483D-B394-6CBDD07387C2@themactionfaction.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniele Varrazzo | 2011-02-27 17:49:23 | Re: Using real libpq parameters |
Previous Message | Federico Di Gregorio | 2011-02-27 12:17:00 | RELEASE: psycopg 2.4 |