MIME-Version: 1.0
References: 
 <CA+mi_8aAuORkdXZ9bG_GpU34iPCGktMO83ktn+ODjL+Y=Y=e3Q@mail.gmail.com>
 <25a137a0-9ad3-bb9f-b008-263dcc81a645@aklaver.com>
 <CA+mi_8YoZ6_0NM4TbfqE8SQY8-b=RgaBH-jUVxzbWQdp+4oZGQ@mail.gmail.com>
 <331f273f-fc13-8d80-b209-c94c0960268b@aklaver.com>
In-Reply-To: <331f273f-fc13-8d80-b209-c94c0960268b@aklaver.com>
From: Vladimir Ryabtsev <greatvovan@gmail.com>
Date: Mon, 21 Dec 2020 21:39:19 -0800
Message-ID: 
 <CAMqTPq=G1E=b32JxNo6dGk1-9qGHSCP7Puo5FCqf68nakiBo_Q@mail.gmail.com>
Subject: Re: psycopg3, prepared statements
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Daniele Varrazzo <daniele.varrazzo@gmail.com>, psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a01a5305b706ffcb"
Precedence: bulk

--000000000000a01a5305b706ffcb
Content-Type: text/plain; charset="UTF-8"

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

I am just thinking about type issues in some edge cases...
What if the data is such that in the first row it has some small integers,
but on subsequent rows it has larger numbers that do not fit into "integer"?
There are probably other cases similar to this, including "None" values
on the moment you want to capture types... Maybe it is more reliable to
oblige the user, who knows their data better, to explicitly supply the types
for preparation... Will any type inference work in case of types mismatch?
If yes, to what extent?

Generally I think that automatic preparation is a great idea, but if it is
achieved
by sacrificing reliability to any extent, I would prefer to have such a
feature
switched off by default.

> - if more than `connection.prepared_number` queries are prepared, the
> one used least recently is deallocated and evicted from the cache
> (proposed default: 100).

Why do you need such logic? Why not just keep some limited number of
prepared statements? Is it a problem if a PS is in cache but rarely used?

--
Vlad


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

> On 12/21/20 12:26 PM, Daniele Varrazzo wrote:
> > On Mon, 21 Dec 2020 at 16:02, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
> >
>
> >
> > I mistakenly replied Adrian privately. Following, his reply.
> >
>
> > 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;
>
> So the above is something the user would have to do on each connection?
>
> > 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`
>
> I could get behind that. This all may be premature optimization on my
> part. As long as there is some way to turn it off at some level I could
> live with it.
>
> > 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
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>

--000000000000a01a5305b706ffcb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">&gt; it&#39;s a moment in your<br>&gt; program&#39;s lifet=
ime where you know the query to run and the arguments:<br>&gt; most likely =
now you want to run the query too, not only to prepare it.<div><br></div><d=
iv>I am just thinking about type issues in some edge cases...</div><div>Wha=
t if the data is such that in the first row it has some small integers,</di=
v><div>but on subsequent rows it has larger numbers that do not fit into &q=
uot;integer&quot;?</div><div>There are probably other cases similar to this=
, including &quot;None&quot; values</div><div>on the moment you want to cap=
ture types... Maybe it is more reliable to</div><div>oblige the user, who k=
nows their data better, to explicitly supply the types</div><div>for prepar=
ation... Will any type inference work in case of types mismatch?</div><div>=
If yes, to what extent?</div><div><br></div><div>Generally I think that aut=
omatic preparation is a great idea, but if it is achieved</div><div>by sacr=
ificing=C2=A0reliability to any extent, I would prefer to have such a featu=
re</div><div>switched off by default.</div><div><br></div><div>&gt; - if mo=
re than `connection.prepared_number` queries are prepared, the</div>&gt; on=
e used least recently is deallocated and evicted from the cache<br>&gt; (pr=
oposed default: 100).<div><br></div><div>Why do you need such logic? Why no=
t just keep some limited number of</div><div>prepared statements? Is it a p=
roblem if a PS is in cache but rarely used?</div><div><br><div>--</div><div=
>Vlad</div><div><br></div></div></div><br><div class=3D"gmail_quote"><div d=
ir=3D"ltr" class=3D"gmail_attr">On Mon, 21 Dec 2020 at 13:55, Adrian Klaver=
 &lt;<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.=
klaver@aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">On 12/21/20 12:26 PM, Daniele Varrazzo wrote:<br>
&gt; On Mon, 21 Dec 2020 at 16:02, Adrian Klaver &lt;<a href=3D"mailto:adri=
an.klaver@aklaver.com" target=3D"_blank">adrian.klaver@aklaver.com</a>&gt; =
wrote:<br>
&gt; <br>
<br>
&gt; <br>
&gt; I mistakenly replied Adrian privately. Following, his reply.<br>
&gt; <br>
<br>
&gt; I think so. Personally, I prefer the two step approach as I am becomin=
g<br>
&gt; less and less enthusiastic about hidden &#39;magic&#39;. To that end a=
<br>
&gt; global(maybe connection) setting that would disable prepare would be n=
ice.<br>
&gt; <br>
&gt; ----<br>
&gt; <br>
&gt; To which, 1) thank you very much, Adrian, for the plpython pointer,<br=
>
&gt; I&#39;ll take a look at it.<br>
&gt; <br>
&gt; 2) About disabling the automatic prepare: the mechanism I have in mind=
<br>
&gt; is to set prepare_threshold to None on the connection instance;<br>
<br>
So the above is something the user would have to do on each connection?<br>
<br>
&gt; however we could make sure to have the default attribute defined on<br=
>
&gt; the class: this way who really hates the idea of prepared statements<b=
r>
&gt; can be cheeky and set `psycopg3.Connection.prepare_threshold =3D None`=
<br>
<br>
I could get behind that. This all may be premature optimization on my <br>
part. As long as there is some way to turn it off at some level I could <br=
>
live with it.<br>
<br>
&gt; instead of `myconn.prepare_threshold`... More seriously, if there is a=
<br>
&gt; large base of people who think that something can go wrong with<br>
&gt; prepared statement we can either provide a better interface to control=
<br>
&gt; it globally or to have the feature opt-in.<br>
&gt; <br>
&gt; <br>
&gt; -- Daniele<br>
&gt; <br>
<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
<br>
</blockquote></div>

--000000000000a01a5305b706ffcb--