Re: psycopg3, prepared statements

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