Re: Using real libpq parameters - Mailing list psycopg

On Feb 27, 2011, at 6:35 PM, Daniele Varrazzo wrote:

> 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:
>
> 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?

One calls it multiple times. Hopefully this example will clear up the confusion:

#include <libpq-fe.h>
#include <libpqtypes.h>
#include <assert.h>

int main(int argc,char *argv[])
{
  PGconn *conn = PQconnectdb("dbname=test");
  assert(conn!=NULL);

  PQtypesRegister(conn);

  PGparam *param = PQparamCreate(conn);

  PQputf(param,"%int4",5);    //calling PQputf multiple times
  PQputf(param,"%text","spam");

  PGresult *res=PQparamExec(conn,param,"SELECT $1,$2;",0);
  PGint4 val1;
  PGtext val2;

  assert(PQgetf(res,0,"%int4",0,&val1)>0);
  assert(PQgetf(res,0,"%text",1,&val2)>0);

  printf("%d %s\n",val1,val2);

  PQclear(res);
}


> 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.

It applies to all prepared statements regardless of the API. However, as a special case, the special "" (empty string)
preparedstatement follows this rule: 
"The unnamed prepared statement is likewise planned during Parse processing if the Parse message defines no parameters.
Butif there are parameters, query planning occurs every time Bind parameters are supplied. This allows the planner to
makeuse of the actual values of the parameters provided by each Bind message, rather than use generic estimates." 
http://www.postgresql.org/docs/9.0/interactive/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

This allows one to better mimic the behavior of the simple query protocol.

>
>> 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.

libpqtypes supports ALL possible PostgreSQL types (user-defined as well). Right now, creating pycopg2 types involves
parsingthe textual representations from the result, however, using libpqtypes, psycopg2 could offer a much more natural
APIlike this: 

class Point(object):
    def __init__(self,x,y):
        self.x = x
        self.y = y

psycopg2.register_type(Point,["x","y"])
or
psycopg2.register_type(Point,{"x":int,"y":int})
or even
psycopg2.register_type(Point) #psycopg2 extracts all properties as a tuple to pass to libpqtypes

or something like that- I'm just brainstorming here. Obviously, this wouldn't work if a Point instance needed to be
interpolatedinto a query string. 

>
>
>> 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.


The best thing about libpqtypes is that you don't need to learn about the binary protocol because it is all cleanly
wrappedup. For example, the library has to account for endianness of the server and other hurdles. It is all handled
transparently.

Cheers,
M