Re: Psycopg and prepared SQL statements

Hello Daniele,

Thanks for your answer.

On Fri, May 16, 2014 at 09:59:45PM +0100, Daniele Varrazzo wrote:
>
> 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.

Sorry if my patch made you think I thought all SQL statements should be
prepared, but I think that, by default, SQL statements should be
executed directly without being prepared first, and that the developper
who uses psycopg should to it explicitely if (s)he wants the statement
to be prepared first.

As for your question, I have no idea how PostgreSQL behaves with many
prepared statements and, as a rule of thumb, I’d say that dynamically
generated quereies should not be prepared.

Moreover, as I understand it, it is sometimes more efficient to let
PostgreSQL optimize a request for a given set of parameter values than
to optimize it first and then use it as is for the values.


> 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

Sorry for asking, but I don’t understand the point of that
PreparingCursor subclass. How would it be different from “normal”
cursors?


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

As far as I am concerned, I would not care to pay the extra price of the
occasional preparation the first time a user uses to my webapp.
But I guess that, each time I use execute[many](), it will have to check
whether the statement has already been prepared. Do you have an idea how
expensive this test might be? My point when I designed my classes was to
avoid such systematic overhead.

By the way, I was willing to use a NamedTupleCursor cursor, but the
overhead was to high for me. I have not investigated yet, but I guess it
would be more efficient if the Record namedtuple was defined only once
for a given statement, and then re-used the next times it is used.


Cheers,

--
Nicolas Boullis