[DB-SIG] Prepared statements in python drivers

[Tony Locke]

Hi Daniele, the latest release of pg8000 (1.9.7) has (experimental)
support for prepared statements, but takes an implicit approach rather
than an explicit one. In the connect() function there's a boolean
use_cache parameter which tells pg8000 to cache prepared statements,
keyed against the SQL query string. So when you do:

cursor.execute(sql_query, params)

it does a lookup on sql_query in a local cache, and executes the
prepared statement if one is found, or creates and caches a new one if
not. This implicit approach means that no extension to the DB-API is
needed. To address some of your points:

> A cursor may prepare
automatically a statement and then execute it, but this adds a network
roundtrip, uses more server resources and can lead to suboptimal plans
(because the planner doesn't know the parameter so can't decide about
a filter selectivity etc.).

Internally, pg8000 uses prepared statements for everything, because it
always uses the extended protocol rather than the simple protocol.
This means the roundtrip is always done, so caching is always better
from a roundtrip point of view in pg8000.

Also, regarding your point about suboptimal server plans, I believe
that this was true until PostgreSQL 9.3. In 9.3 the query plan may be
changed on each execution of a prepared statement.

> A cursor may support a single prepared
> statement or many, in which case a cache invalidation policy could be
> needed etc.

With caching turned on, pg8000 stores the cache at the connection
level. The cache is never invalidated, and prepared statements are
never explicitly closed. When the connection is closed, postgres will
close any prepared statements associated with that session.

As I say, the caching of prepared statements is still at an
experimental stage in pg8000, and any feedback is very welcome.

Cheers,

Tony.



On 24 March 2014 16:53, Daniele Varrazzo <daniele.varrazzo at gmail.com> wrote:
> Hello,
>
> lately there has been some interest in adding prepared statements
> support in Psycopg. It's a feature of which I see the usefulness but
> which I haven't used extensively enough to make my mind about be the
> best interface to present it to the driver clients.
>
> A toy prototype that hasn't lead to a great deal of discussion is
> described in this article:
> <http://initd.org/psycopg/articles/2012/10/01/prepared-statements-psycopg/>.
> This implementation is explicit: the cursor has a prepare() method and
> execute can be run without the statement, only with the parameters,
> which would call the prepared statements.
>
> Other implementations are possible of course. A cursor may prepare
> automatically a statement and then execute it, but this adds a network
> roundtrip, uses more server resources and can lead to suboptimal plans
> (because the planner doesn't know the parameter so can't decide about
> a filter selectivity etc.). A cursor may support a single prepared
> statement or many, in which case a cache invalidation policy could be
> needed etc.
>
> I was wondering a few things:
>
> - is there enough consensus - not only in the Python world - about how
> to implement a prepared statements interface on a db driver?
> - do other Python drivers implement stored procedures? Do they do it
> in a uniform way?
> - is the topic generic enough for the DB-SIG to suggest a DB-API
> interface or is it too database specific and the interface would be
> better left to the single driver?
>
> Thank you very much for any help provided.
>
> -- Daniele
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> https://mail.python.org/mailman/listinfo/db-sig