Re: Session state per transaction

On Wed, Sep 26, 2012 at 8:53 PM, Tobias Oberstein
<tobias(dot)oberstein(at)gmail(dot)com> wrote:

>> This is one of these things that would stop work moving to
>> PQexecParams: that's why I want to make sure to leave open the
>> possibility to keep on using PQexec even if we move to the *Params
>> functions.
>
> Guess you refer to (which I here reproduce for others who might wonder):
>
> Unlike PQexec, PQexecParams allows at most one SQL command in the given
> string.
>
> http://www.postgresql.org/docs/9.2/static/libpq-exec.html

Correct.

> Btw: does Psycopg currently use PQprepare/PQexecPrepared ?

Not yet: note that these function are very similar in signature to
PQexecParams: supporting the latter would bring us closer to use
prepare statement.

Note 2: you can still prepare a statement in sql using the PREPARE
statement, and this can currently be used by psycopg.

But for both the methods, one question remains: how to use statement
preparation? prepare() at each execute() seems overkilling if you have
to execute a statement only once. An obvious place that would benefit
of preparation is executemany(): we could prepare the query once on
the query and execPrepared once for each item.

Still there is an important use case: in a connection, using one or
several cursors, the same query could be repeated over and over. So
what?

- should we have a prepare() method to be called to manually prepare a
query? How to refer to the prepared query? Should prepare return a
name/opaque object? Should it just intern the string and detect that
the same query is used by execute()?
- should we have a connection subclass preparing all the queries you
throw at it (until a certain limit after which start discarding the
older ones)?

So, I'd say once we know how we would use a prepare/execute feature we
can implement it. In the meanwhile the feature can be somewhat
prototyped by subclassing connection and cursor and mangling the
queries with a PREPARE statement, with which we can use the current
psycopg parameters adaptation.

-- Daniele