Re: psycopg3, prepared statements

On Mon, 21 Dec 2020 at 16:02, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> So your plan from [1] was like that in plpythonu where the process is
> broken down into two parts.

Is there anything useful to learn from the plpythonu experience? I'm
not very familiar with it.


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

I would do without `cursor.prepare(query)`. As a starter it should be
`cursor.prepare(query, args)` in order to sniff the types, so you
would also need to pass it a sample of data (or the types). So now 1)
as interface it's very similar to `cursor.execute(query, args)`, but
it would prepare and not run the query, and 2) it's a moment in your
program's lifetime where you know the query to run and the arguments:
most likely now you want to run the query too, not only to prepare it.
So the lack of a function "prepare the query, with these types, but
don't run it" doesn't seem a very likely one.

I am also assuming that, if the assumption is that "preparing queries
is good", it would be better to let it happen automatically instead of
asking the people to do it on their own.

Does it make sense?

----

I mistakenly replied Adrian privately. Following, his reply.

On Mon, 21 Dec 2020 at 16:45, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> Is there anything useful to learn from the plpythonu experience? I'm
> not very familiar with it.

The user visible portion can be found here:

https://www.postgresql.org/docs/current/plpython-database.html

The most notable part is that it requires type definition(s) for
parameters in the prepare portion. That and a prepared query can be
passed around to different execute and different function calls.


> I would do without `cursor.prepare(query)`. [...]

If I read the above correctly this boils down to cursor.execute(query,
args) will always be prepared once the
connection.prepare_threshold(default 5)is passed and evicted after
connection.prepared_number(default 100) other queries are run.

> Does it make sense?

I think so. Personally, I prefer the two step approach as I am becoming
less and less enthusiastic about hidden 'magic'. To that end a
global(maybe connection) setting that would disable prepare would be nice.

----

To which, 1) thank you very much, Adrian, for the plpython pointer,
I'll take a look at it.

2) About disabling the automatic prepare: the mechanism I have in mind
is to set prepare_threshold to None on the connection instance;
however we could make sure to have the default attribute defined on
the class: this way who really hates the idea of prepared statements
can be cheeky and set `psycopg3.Connection.prepare_threshold = None`
instead of `myconn.prepare_threshold`... More seriously, if there is a
large base of people who think that something can go wrong with
prepared statement we can either provide a better interface to control
it globally or to have the feature opt-in.


-- Daniele