Re: Session state per transaction

Hi Daniele,

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

Agreed. executemany is definitely a use case.

> the query and execPrepared once for each item.

My use case is this: all DB access is via Stored Procedures (SP) on a
pool of long lived DB connections. The set of eligible SPs is determined
in advance. Hence, something that does not lead to reparsing/replanning
of the SP call SELECT statements on every execution is desirable.

I notice there is

http://www.postgresql.org/docs/9.2/static/libpq-fastpath.html

Does Psycopg support this?

Is cursor.callproc using that under the hood?

(This won't allow me to do the "multiple statements" trick for setting
the per-transaction state .. but I may find another way to achieve the
latter).

The pqlib docs mention:

"This interface is somewhat obsolete, as one can achieve similar
performance and greater functionality by setting up a prepared statement
to define the function call. Then, executing the statement with binary
transmission of parameters and results substitutes for a fast-path
function call."

So it can be emulated, but one needs to do multiple things (not only
prepared statement)

>
> 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()?

+1 for returning an opaque object

I can build query string (plain SQL with placeholders before adaption)
to prepared statement object mapping in 3 lines of I need it on top.

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

Mmh. As a convenience class, why not? Seems neat.

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

Cheers,
Tobias