MIME-Version: 1.0
References: 
 <CA+mi_8Zdk_beGcbOjkpdo+fchTR5ot1XM2zZc=ujSGqP+WVh-g@mail.gmail.com>
 <2b9859f0-0964-2baa-b6bc-13f975ae0f67@aklaver.com>
 <CA+mi_8ZfV+rEDKaqM-pcSf43FuN4FUJGoGTxvEEXap-x_GWP0Q@mail.gmail.com>
 <88fb7a7e-a182-a816-c1a7-8a1f54b65215@aklaver.com>
 <CA+mi_8YUEPfTPqD-1Jxm6NUwgJpWxPgejRZ+2-mPQr=0DQWiHA@mail.gmail.com>
 <4830fb8d-fa57-e0f8-0e4f-a96ed040dede@dndg.it>
 <CA+mi_8a+Q0C2LwiedwNkRPVY=-G3q0zW6w19zYvvADtdEd4b_w@mail.gmail.com>
 <1546497c-ff41-9bd0-b4d7-931bd305caac@dndg.it>
 <CAMqTPqkac5xa=+kwKLj8DEr9OdrkJpEzJj4Re8VvKP=JeTdZog@mail.gmail.com>
In-Reply-To: 
 <CAMqTPqkac5xa=+kwKLj8DEr9OdrkJpEzJj4Re8VvKP=JeTdZog@mail.gmail.com>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Tue, 10 Nov 2020 03:20:51 +0000
Message-ID: 
 <CA+mi_8aRQBG+Lz12oW0Be62RSR7UEEoev5Yn34ZSygcOgxbOCw@mail.gmail.com>
Subject: Re: psycopg3 and adaptation choices
To: Vladimir Ryabtsev <greatvovan@gmail.com>
Cc: Federico Di Gregorio <fog@dndg.it>, psycopg@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Precedence: bulk

On Tue, 10 Nov 2020 at 01:06, Vladimir Ryabtsev <greatvovan@gmail.com> 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.