Re: psycopg3 and adaptation choices

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