[RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite) - Mailing list pgsql-hackers

From Manlio Perillo
Subject [RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite)
Date
Msg-id 511CF353.1040901@gmail.com
Whole thread Raw
In response to Re: libpq test suite  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Responses Re: [RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite)  (Jonathan Rogers <jrogers@socialserve.com>)
Re: [RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite)  (Peter Eisentraut <peter_e@gmx.net>)
Re: [RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite)  (Andrew McNamara <andrewm@object-craft.com.au>)
Re: [RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite)  ("P. Christeas" <xrg@linux.gr>)
List pgsql-hackers
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Il 14/02/2013 14:06, Albe Laurenz ha scritto:
> Manlio Perillo wrote:
>> Sorry for the question, but where can I find the libpq test suite?
>> I can not find it in the PostgreSQL sources; it seems that there are
>> only some examples, in src/test/examples.
> 
> The regression tests are in src/interfaces/libpq/test
> and currently contain only URL parsing tests.
> 

Ok, thanks.

Since I'm not sure if I should add a new test here, I'll use the test
suite of my project, since it contains an (almost) 1:1 wrapper around libpq.

>> I'm planning to add some new features to libpq:
>>
>>     * make PQsendPrepare send a "Describe Portal" protocol message
>>     * add support for setting per column binary result format
> 
> I suggested exactly that here:
> http://www.postgresql.org/message-id/D960CB61B694CF459DCFB4B0128514C208A4EDD4@exadv11.host.magwien.gv.at
> and met resistance:
> - one can use libpqtypes
> - I couldn't find a convincing use case
> - it clutters up the API
> 

For my Python DBAPI2 PostgreSQL driver I plan the following optimizations:

1) always use PQsendQueryParams functions.
  This will avoid having to escape parameters, as it is done in  psycopg2  (IMHO it still use simple query protocol for
compatibilitypurpose)
 

2) when the driver detects a Python string is being sent to the  database, use binary format.
  As a special case, this will avoid having to use PQescapeByteaConn  when sending binary string (e.g. byte strings in
Python3.x)
 

3) enable use of prepared statements, but only if the user requested it,  using setinputsizes function (used to set the
Oidsof the parameters)
 

4) when using a prepared statement, check the Oids of the result tuple.
  In order to make this efficient, I proposed a patch to send a  Describe Portal message in PQsendPrepare function.
  When the driver detects that one of the result column is a string  type, set the result format for that column to
binary.
  As a special case, this will avoid having to use PQunescapeBytea  when receiving a bytea data.
  This is currently impossible, using libpq API.

5) when returning the result set of a query, after a call to  cursor.fetchall(), do not convert all the data to Python
objects.
  This will be done only "on request".
  This should optimize memory usage, as reported in:  http://wiki.postgresql.org/wiki/Python_PostgreSQL_Driver_TODO

6) make available the use of PQsetSingleRowMode, to optimize large  result set (as an option to the connection.cursor
method)

7) as a generalization of PQsetSingleRowMode, expose in libpq API some  of protocol internal portal API.
  One possible idea is to add a PQsetRowSize function, that will set  the size of the result set, to be used in the
Executeprotocol  message (currently libpq always set it to 0, to get the entire  result set, and it does not support
thePortal Suspended message)
 
  This will avoid having to use named cursor, as it is done in psycopg.
  I'll try to make a patch to check if this is feasible, can be  done efficiently, and the new API has a minimal impact
onexisting  API
 

Note that I will have to code these features, in order to check they
will work as I expect.


> [...]
>>
>> [1] A new Python PostgreSQL driver, implemented following
>>     http://wiki.postgresql.org/wiki/Driver_development
>>     and with many optimization (compared to psycopg2) enabled by the
>>     use of the extended query protocol
> 
> I think that you'll need to explain in more detail why
> your proposed additions would be necessary for your project.
> Especially since many good drivers have been written against
> libpq as it is.
> 
> Yours,
> Laurenz Albe
> 

Thanks   Manlio Perillo

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlEc81MACgkQscQJ24LbaURO9ACfctOREoaAtMDm06Sg+qv5jesj
iW0An1CVAOaHzYaSn+P1AIJvXpI7nVT0
=rK4j
-----END PGP SIGNATURE-----



pgsql-hackers by date:

Previous
From: Stephen Frost
Date:
Subject: Re: proposal or just idea for psql - show first N rows from relation backslash statement
Next
From: Merlin Moncure
Date:
Subject: Re: proposal or just idea for psql - show first N rows from relation backslash statement