Re: psycopg3, prepared statements

On 12/21/20 6:24 AM, Daniele Varrazzo wrote:
> The one thing, the most requested thing in psycopg, is support for
> prepared statements.
>
> In psycopg3 for the moment there is:
>
> - very low level support for prepared statement, i.e. wrapping of
> libpq functions such as PQsendPrepare/PQsendQueryPrepared
> (https://www.postgresql.org/docs/current/libpq-async.html#LIBPQ-PQSENDPREPARE)
> - automatic use of prepared statements in `cursor.executemany()`,
> which might eventually stop sucking.
>
> Gathering some ideas:
>
> Prepared statements in the server are per session, so any form of
> cache is better connected to the connection than the cursor, although
> the cursors are the obvious interface to give commands.
>
> In the past [1] I thought about exposing explicit prepare/deallocate
> on the cursor, and it was a single prepared query per cursor. A
> `cursor.prepare(query)` with no args doesn't have types information
> though: if any it should take an optional array of parameters to get
> types from.
>
> What I'm thinking about is to prepare queries automatically with a schema such:
>
> - decisions are made after the query is transformed to postgres format
> (i.e. it is reduced to bytes, all the client-side manipulations have
> been done, placeholders have been transformed to $ format). There is
> an object in psycopg3 that takes care of this transformation [2]
> - the number of times a query is seen is stored in a LRU cache on the connection
> - if a query is seen more than `connection.prepare_threshold` times
> (proposed default: 5) then it is prepared with the name
> f'pg3_{hash(query)}' and the following executions are prepared.
> - if more than `connection.prepared_number` queries are prepared, the
> one used least recently is deallocated and evicted from the cache
> (proposed default: 100).
> - Parameters may be fudged on the connection: prepared_threshold=0
> would prepare all queries, prepared_threshold=None would disable
> preparing.
> - For the control freak, cursor.execute(query, params, prepare=True)
> would prepare the query immediately, if it isn't already,
> prepare=False would avoid preparation. The default None would enable
> the automatic choice.

So your plan from [1] was like that in plpythonu where the process is
broken down into two parts. What I'm not following is whether that is
still the plan or whether prepare/execute is going to happen without the
cursor.prepare() and just be automatic on cursor.execute()? With the
provision to override per cursor.

>
> [1] https://gist.github.com/dvarrazzo/3797445
> [2] https://github.com/psycopg/psycopg3/blob/c790a832/psycopg3/psycopg3/_queries.py#L27
>
> What do you think?
>
> Cheers
>
> -- Daniele
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com