Thread: 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)] onwin32 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