MIME-Version: 1.0
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Mon, 21 Dec 2020 14:24:37 +0000
Message-ID: 
 <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>
Subject: psycopg3, prepared statements
To: psycopg@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

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.

[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