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>
 <CAMqTPq=G1E=b32JxNo6dGk1-9qGHSCP7Puo5FCqf68nakiBo_Q@mail.gmail.com>
 <CA+mi_8YhQVo8xouWnc6hCR6RvE1THRB0Wz0OzHhMHsuvdLNYnw@mail.gmail.com>
In-Reply-To: 
 <CA+mi_8YhQVo8xouWnc6hCR6RvE1THRB0Wz0OzHhMHsuvdLNYnw@mail.gmail.com>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Wed, 23 Dec 2020 22:53:58 +0000
Message-ID: 
 <CA+mi_8akdCJC=q9_EH5n5Prj32ttLKCY8-=yncxRr7K8V2mZhw@mail.gmail.com>
Subject: Re: psycopg3, prepared statements
To: Vladimir Ryabtsev <greatvovan@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, psycopg@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

On Tue, 22 Dec 2020 at 22:36, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
>
> On Tue, 22 Dec 2020 at 05:39, Vladimir Ryabtsev <greatvovan@gmail.com> wrote:

> > 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?
>
> The only case I have thought where this can happen is with None vs.
> not None, in which case the oid info would be missing. Oid selection
> is based only on the Python type, not on the specific values (for
> instance Python datetime is always passed as timestamptz, never
> timestamp, even if the tezinfo is missing, and it relies on Postgres
> cast): I have avoided so far to make a choice based on the values
> exactly to avoid to find ourselves in this type of situations.
>
> I don't have in mind a case where a missing value would make a
> difference but that doesn't mean that they don't exist. What I think
> is that if in a certain context a NULL is acceptable and Python can
> pass either a None or e.g. a date, preparing witn unknown or date oid
> shouldn't make a difference. Of course it is possible to trigger
> ambiguities: trivially the query `SELECT %s` could be prepared with a
> date and then you can throw it a string that will make it fail. But if
> a placeholder is part of an expression or target for an insert I
> believe (but will test too) that preparing with unknown types will be
> equivalent.

Heads up about this: it's better than I thought!

I wrote a first implementation of the prepared statements cache using
the query as a key, but it's actually enough to use the (query, types)
tuple in order to tell apart statements that are executed with
different types. This way even the "SELECT %s" case won't be a
problem. Of course a statement executed with a mix of types will be
prepared later than `prepare_threshold`, but I think it's perfectly
acceptable: the case doesn't happen often and having the query
prepared after 10 times instead of 5 doesn't change much if it will be
executed hundreds of times or more.

What seems a feature-complete branch is available in [1]. The tests
[2] illustrate the main behaviour of the prepared statements system.

[1]: https://github.com/psycopg/psycopg3/tree/prepared-statements>.
[2]: https://github.com/psycopg/psycopg3/blob/prepared-statements/tests/test_prepared.py

Off to do some benchmarks now...

-- Daniele