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.varra...@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@python.org > https://mail.python.org/mailman/listinfo/db-sig _______________________________________________ DB-SIG maillist - DB-SIG@python.org https://mail.python.org/mailman/listinfo/db-sig
