public inbox for [email protected]
help / color / mirror / Atom feedFrom: Daniele Varrazzo <[email protected]>
To: Vladimir Ryabtsev <[email protected]>
Cc: Federico Di Gregorio <[email protected]>
Cc: [email protected]
Subject: Re: psycopg3 and adaptation choices
Date: Tue, 10 Nov 2020 03:20:51 +0000
Message-ID: <CA+mi_8aRQBG+Lz12oW0Be62RSR7UEEoev5Yn34ZSygcOgxbOCw@mail.gmail.com> (raw)
In-Reply-To: <CAMqTPqkac5xa=+kwKLj8DEr9OdrkJpEzJj4Re8VvKP=JeTdZog@mail.gmail.com>
References: <CA+mi_8Zdk_beGcbOjkpdo+fchTR5ot1XM2zZc=ujSGqP+WVh-g@mail.gmail.com>
<[email protected]>
<CA+mi_8ZfV+rEDKaqM-pcSf43FuN4FUJGoGTxvEEXap-x_GWP0Q@mail.gmail.com>
<[email protected]>
<CA+mi_8YUEPfTPqD-1Jxm6NUwgJpWxPgejRZ+2-mPQr=0DQWiHA@mail.gmail.com>
<[email protected]>
<CA+mi_8a+Q0C2LwiedwNkRPVY=-G3q0zW6w19zYvvADtdEd4b_w@mail.gmail.com>
<[email protected]>
<CAMqTPqkac5xa=+kwKLj8DEr9OdrkJpEzJj4Re8VvKP=JeTdZog@mail.gmail.com>
On Tue, 10 Nov 2020 at 01:06, Vladimir Ryabtsev <[email protected]> wrote:
> May I ask you again about using 'unknown' for numbers? Could you recap
> all the downsides of this approach?
After this useful conversation, I've updated the article
(https://www.varrazzo.com/blog/2020/11/07/psycopg3-adaptation/) and
I've added comparison tables with the choices available. The integer
one is the following:
Choices to cast Python ``int`` type:
To ``unknown``:
* +1: can express the full range of the unbounded Python ``int``
* +2: can be passed without cast to most data types and functions
* -2: it round-trips back to string, error on PostgreSQL < 10
To ``numeric``:
* +1: can express the full range of the unbounded Python ``int``
* +1: can be passed without cast to most data types
* -1: requires a cast for some functions
* DELETED [-1: it round-trips to 'Decimal'`]
* +0.5: **it can round-trip back to int**, with additional care - more to
follow
To ``int8``:
* -0.5: can't express values not fitting in 64 bits (relatively limited
use case: if the target column is ``numeric`` then it would be wise for
the user to pass a ``Decimal``)
* +1: can be passed without cast to most data types
* -1: requires a cast for some functions
* +1: it round-trips back to ``int``
To ``int4``:
* -1: limited range: couldn't be used to pass a value not fitting into 32
bytes from Python to a ``bigint``.
* +2: can be passed without cast to most data types and functions
* +1: it round-trips back to ``int``
What about the DELETED entry on ``numeric``? If we dump ``int`` -> ``numeric``
to the db, and load back ``numeric`` -> ``Decimal`` from it, we end up with
integers round-tripping to ``Decimal``, which could easily create errors in
Python contexts which are not ready to deal with fixed-point arithmetic.
However, upon receiving a ``numeric`` from the database, we can check what
number it is: if it has no decimal digit it can be returned to Python as
``int``, if it has any decimal digit it must be returned as ``Decimal``.
This mechanism can be as trivial as `looking if there is a '.'`__ in the data
returned by the database; however in many cases the is job made simpler (or at
least more efficient) by the presence of the ``numeric`` modifiers:
``numeric`` comes in three flavours:
- ``numeric``: arbitrary precision (number of digits) and scale (number of
digits after the decimal point),
- ``numeric(n)``: limited precision, no digit after the decimal
point (equivalent to ``numeric(n, 0)``),
- ``numeric(n, m)``: limited precision, fixed number of
digits after the decimal point.
.. __: https://github.com/psycopg/psycopg3/commit/5ced659f4838cf72c1981518ae2804942ebbd07b
The modifier information is returned in many contexts (among which the most
important: selecting data from tables): if we know the scale `we can decide
upfront`__ to load the entire column as ``int`` if the scale is 0, ``Decimal``
otherwise. If the modifier is unknown we can look at the presence of the dot.
.. __: https://github.com/psycopg/psycopg3/commit/a9444144f7d0581284ccab198ad0355436e6822a
Returning an ``int`` in a context where ``Decimal`` are expected doesn't seem
a big problem: throwing an integer to a fixed-point calculation doesn't wreak
havoc has it does throwing a ``Decimal`` in a context of ``flaot``
calculations. Running the entire Django test suite after this change caused no
test to fail, which gives me some comfort.
view thread (23+ 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], [email protected], [email protected], [email protected]
Subject: Re: psycopg3 and adaptation choices
In-Reply-To: <CA+mi_8aRQBG+Lz12oW0Be62RSR7UEEoev5Yn34ZSygcOgxbOCw@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