Re: psycopg3 and adaptation choices - Mailing list psycopg

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.