Re: Psycopg and prepared SQL statements

On Fri, May 16, 2014 at 1:22 PM, Nicolas Boullis <postgresql@ilcode.fr> wrote:
> Hi,
>
> Lately, I’ve been designing and writing a WSGI webapp for which
> performance matters. This webapp queries a PostgreSQL database.
>
> As performance matters, I am using a ThreadedConnectionPool from
> psycopg2.pool, but I also want to have my SQL statements prepared.
>
> I read
>   http://initd.org/psycopg/articles/2012/10/01/prepared-statements-psycopg/
> but I could not find a way to mix PreparingCursor with the connection
> pool.
> Each statement should definitely be prepared only once for each
> connection, and I could not find a way to attach the prepared cursors to
> the connections in the pool.
>
> So i designed a class that wraps SQL statements, and a class whose
> instances are connection factories that prepare the needed statements.
>
> Here is the result of my work, wit a simple example.
>
> All comments are welcome. I think it woul be nice if psycopg could offer
> a way to use prepared statements.

Hello Nicolas,

as stated in the article that was a first idea. One of my concerns is
exactly the fact that the statement preparation is a connection's
property and giving the prepared state to the cursor makes it more
"precious" than what you'd like.

I'd be happier to have statements prepared inside the connection too
(although the interface to require the preparation may still be on the
cursor). This begs the question: what happens with too many prepared
statements? This is not a problem for a program that has no
dynamically generated queries, but for one that does it would lead to
unbound use of resources.

I think it could be interesting to have a preparing connection, with
cursors offering some "prepare" interface as in the article you have
linked but where the state of the prepared connection is kept by the
connection. This could be used by a subclass which instead
automatically prepares every statement: easier to use but not suitable
for programs generating dynamic queries.

So I think I'd have the following classes:

PreparingConnection: connection subclass keeping the client-side state
of the statements that were prepared
PreparingCursor: cursor subclass allowing to manually prepare statements
AutoPreparingCursor: PreparingCursor subclass that automatically
prepares everything passed to its execute[many]().

The classes should be mixin-able with other cursor subclasses so that
one could have a beast preparing and returning named tuples and so on.

In the next days/weeks I'll try to make some experiments along this line.

Putting auto-preparing stuff inside a pool would automatically have
statements prepared at the first usage of each connection/query combo.
If this is not sufficient and one doesn't want to pay the price of the
occasional preparation but have all the statements prepared upfront I
think a specific subclass or wrapper as you did could be a good
option.

-- Daniele