Thread: BUG #3686: Incorrect numeric type conversion to long in plpythonu

The following bug has been logged online:

Bug reference:      3686
Logged by:          Sergi
Email address:      sergi.vladykin@googlemail.com
PostgreSQL version: 8.2.5
Operating system:   Solaris 10 11/06  x86
Description:        Incorrect numeric type conversion to long in plpythonu
Details:

Trouble in usage of numeric types in plpythonu.
PostgreSQL 8.2.5 on i386-pc-solaris2.10, compiled by /opt/SUNWspro/bin/cc
-xpentium -xO4 -Xa with Python 2.5.1 also compiled from source.
Problem is that numeric type converts to pythons long
incorrectly. Here is the simple script that shows this.

-----
-----
create or replace function test2(x numeric) returns numeric
as $$
return long(x);
$$ language plpythonu;

select test2(1231312312313956756464);/*not the same as input*/

select 1231312312313956756464=test2(1231312312313956756464);/* expected to
be equal*/

create table test_table(
id numeric(22) not null
);

insert into test_table(id) values (1234567890098765432199);

create or replace function test(n int) returns numeric
as $$
return long(plpy.execute('select id from test_table limit 1 offset
'+str(n))[0]['id']);
$$ language plpythonu;

select test(0);/* expected 1234567890098765432199*/
select count(*) from test_table where id = test(0);/* expected 1 returns
0*/

-----
-----

"Sergi" <sergi.vladykin@googlemail.com> writes:
> Problem is that numeric type converts to pythons long
> incorrectly. Here is the simple script that shows this.

The reason you're having a problem is that plpython translates PG's
numeric datatype to Python Float type, and thus the precision is
lost before the long() function ever sees it.

We can't fix this by translating to Long instead, since that would lose
fractional digits.

We could consider translating to the Decimal type proposed in PEP 327
http://www.python.org/dev/peps/pep-0327/
however it appears that that's only standard in Python 2.4 and later
which makes it rather a large portability problem.  I have no idea
what sort of backwards-compatibility issues such a change would
create, either ...

            regards, tom lane