Обсуждение: BUG #8355: PL/Python 3 can't convert infinity to PostgreSQL's value

The following bug has been logged on the website:

Bug reference:      8355
Logged by:          Basil Peace
Email address:      grv87@yandex.ru
PostgreSQL version: 9.2.4
Operating system:   Windows 7 x64 (PostgreSQL is x86), Python 3.2.5
Description:

PL/Python can't convert Python's float with infinity value to PostgreSQL's
float.
The reason is that Python's standard representation of infinity is 'inf'
('Infinity' is accepted as well), but PostgreSQL's representation is
'Infinity' only.
I'm speaking of Python 3 version since I have no Python 2 to test.


Consider the following code:


-- CREATE LANGUAGE plpython3u;


CREATE TABLE IF NOT EXISTS test10 (
    a double precision
);


DO LANGUAGE plpython3u $$
    plan = plpy.prepare('INSERT INTO test10 (a) VALUES ($1)', ['double
precision'])
    a = float('inf')
    plpy.execute(plan, [a])
$$;




ERROR:  spiexceptions.InvalidTextRepresentation: invalid input syntax for
type double precision: "inf"
CONTEXT:  Traceback (most recent call last):
  PL/Python anonymous code block, line 4, in <module>
    plpy.execute(plan, [a])
PL/Python anonymous code block




I suppose this should work without any workarounds.

grv87@yandex.ru writes:
> PL/Python can't convert Python's float with infinity value to PostgreSQL's
> float.
> The reason is that Python's standard representation of infinity is 'inf'
> ('Infinity' is accepted as well), but PostgreSQL's representation is
> 'Infinity' only.

Hmm, I was about to contradict you, because it works fine on my Linux
and OS X machines:

regression=# select 'inf'::float8;
  float8
----------
 Infinity
(1 row)

but further experimentation says that this doesn't work on my ancient
HPUX box; and you're complaining about Windows.  So what we've got here
is a platform dependency in the behavior of strtod().  I don't think
we can promise to hide all such dependencies, but maybe it'd be a good
idea to take care of this particular one.

            regards, tom lane

I wrote:
> ... further experimentation says that this doesn't work on my ancient
> HPUX box; and you're complaining about Windows.  So what we've got here
> is a platform dependency in the behavior of strtod().  I don't think
> we can promise to hide all such dependencies, but maybe it'd be a good
> idea to take care of this particular one.

I took a look in the C99 standard, and what it has to say about it is
actually this:

       [#3] The  expected  form  of  the  subject  sequence  is  an
       optional plus or minus sign, then one of the following:

         -- a   nonempty  sequence  of  decimal  digits  optionally
            containing a decimal-point character, then an  optional
            exponent part as defined in 6.4.4.2;

         -- a  0x  or  0X,  then a nonempty sequence of hexadecimal
            digits optionally containing a decimal-point character,
            then  an  optional  binary-exponent  part as defined in
            6.4.4.2, where either the  decimal-point  character  or
            the binary-exponent part is present;

         -- one of INF or INFINITY, ignoring case

         -- one  of  NAN or NAN(n-char-sequence-opt), ignoring case
            in the NAN part, where:
                    n-char-sequence:
                            digit
                            nondigit
                            n-char-sequence digit
                            n-char-sequence nondigit

Current versions of the POSIX standard say the same, though SUS v2
didn't mention any of the non-numeric variants.

So what we've got is that Windows and some other (obsolete?) platforms
don't accept everything the standard says they should, and that results in
a visible cross-platform behavioral difference for us.

I'm not at all excited about supporting 0x... constants, nor about the
expanded form of NaN.  But it seems like maybe we had better cover the
following cases that we do not cover today:

    inf
    +inf
    -inf
    +Infinity

We already backstop strtod() for these cases:

    NaN
    Infinity
    -Infinity

but the wording of the spec clearly requires +Infinity as well as the
forms with just "inf".  (It also appears to require +/- NaN to be
accepted, but I have no idea what that would mean and suspect it to
be a thinko.)

Barring objections I'll go make this change.

            regards, tom lane

I wrote:
> ... But it seems like maybe we had better cover the
> following cases that we do not cover today:

>     inf
>     +inf
>     -inf
>     +Infinity

I've committed a patch that makes sure float4in and float8in accept
these spellings even when the underlying strtod(3) function does not.
However, it turns out that this doesn't fix your test case on my HPUX
box, and I'm betting that it won't help on Windows either, because
it's actually Python that's failing.  You can reproduce the failure
without any Postgres code involved at all:

$ python
Python 2.5.1 (r251:54863, Jul 12 2007, 23:25:33)
[GCC 2.95.3 20010315 (release)] on hp-ux10
Type "help", "copyright", "credits" or "license" for more information.
>>> a = float('inf')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: invalid literal for float(): inf

Now, I seriously doubt that the Python guys will give a darn about
a 15-year-old version of HPUX, but if you can reproduce the above
on your Windows machine, I'd suggest filing a bug about it with them.

            regards, tom lane

I based on the documentation of PostgreSQL
š(http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-FLOAT)which says about 'Infinity' and
'-Infinity'and doesn't mention other possible spellings, including 'inf'. 
And on my installation of 9.2.4 'inf' doesn't work too (as I supposed according to documentation):

ššššššššSELECT 'inf'::float8

ššššššššERROR: šinvalid input syntax for type double precision: "inf"
ššššššššLINE 2: SELECT 'inf'::float8

According to Python's documentation (http://docs.python.org/2/library/stdtypes.html#typesnumeric), handle of infinities
andNaNs was added in 2.6. At least this works in 2.7.1: 

ššššššššPython 2.7.1 (r271:86832, Apr š1 2013, 01:27:27) [MSC v.1600 64 bit (AMD64)] onšwin32
ššššššššType "help", "copyright", "credits" or "license" for more information.
>>> šfloat('inf')
ššššššššinf
>>> šfloat('infinity')
ššššššššinf
>>> šfloat('+inf')
ššššššššinf
>>> šfloat('-inf')
šššššššš-inf

So, Python is honest in this way. And, C99 says that style of representation ('inf' or 'infinity') is
implementation-defined,so it is all OK with modern Python. 

> šWe already backstop strtod() for these cases:
>
> šššššššššNaN
> šššššššššInfinity
> ššššššššš-Infinity
>
> šbut the wording of the spec clearly requires +Infinity as well as the
> šforms with just "inf". š(It also appears to require +/- NaN to be
> šaccepted, but I have no idea what that would mean and suspect it to
> šbe a thinko.)

As I can judge, signed NaNs are from the same world as signed zeros and signed infinities. Strictly speaking: (-0)/(+0)
is-NaN, (-inf)/(+inf) is -NaN, and so on. 
I think that PostgreSQL's ability to handle signed zeros (and all other rare stuff) depends on compiler used. Google
saysme that '-NaN' exists in modern glibc. I don't know about MSVC. My Python accepts '-nan' as input, but doesn't give
me'-NaN' as output. 
So, I think it would be good if '-NaN' and other forms were workable.

--
Best regards,
Basil Peace