public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: [email protected]
Subject: psycopg3, prepared statements
Date: Mon, 21 Dec 2020 14:24:37 +0000
Message-ID: <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com> (raw)

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

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: psycopg3, prepared statements
  In-Reply-To: <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox