Subject: Re: psycopg3, prepared statements
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>, psycopg@postgresql.org
References: 
 <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <25a137a0-9ad3-bb9f-b008-263dcc81a645@aklaver.com>
Date: Mon, 21 Dec 2020 08:02:29 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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.klaver@aklaver.com