python2.4-pgsql locale bug - Hoary

Current Status

• Martin Pitt creates a patch for Ubuntu, see the Ubuntu Bugzilla and his Floatparsing Patch
• The Debian Bug Database #311328
• pysql SourceForge BugEntry

Error Description

The following error occours on a Ubuntu Hoary System with universe repository. If i'm using the locale de_AT.utf8 with python2.4-pgsql and a simple table with a double datatype column, then the value 10.123 will be 10,000! With the locale en_US.utf8 the outout is correct: 10.123. This is massive error in a accounting application. I realize this error situation, while porting from Warty to Hoary.

I isolate the error with the following test sequenz. Details follow.

• create a PostgreSQL database with one testtable and one column. This looks like:

     \l
              List of databases
         Name     |  Owner   | Encoding
     -------------+----------+----------
      stbtest     | postgres | UNICODE
  
     stbtest=> \dt
           List of relations
      Schema |   Name    | Type  | Owner
     --------+-----------+-------+-------
      public | testtable | table | stb
     (1 row)
  
     stbtest=> select * from testtable;
        a
     --------
      10.123
     (1 row)
  
     stbtest=>
  

• Create this python script:

      #!/usr/bin/python
  
      from pyPgSQL import PgSQL
      import locale, types
  
      if __name__ == '__main__':
  
              locale.setlocale(locale.LC_ALL, "")
              db = PgSQL.connect ("localhost::stbtest::")
              st = db.cursor()
              st.execute ("select a from testtable")
              res = st.fetchone()
              print res
              print "x=%s" % locale.format ("%.3f", res[0])
  
    then setup locale en_US.utf8 and run the script::
  
      export LANG=en_US.utf8
  
      ./test1.py
      [10.122999999999999]
      x=10.123
  

• create a equivalent perl script:

      #!/usr/bin/perl
  
      use DBI;
      use POSIX qw(locale_h);
  
      setlocale (LC_ALL, "");
  
      my $dbh = DBI->connect("DBI:Pg:dbname=stbtest");
      my $sth = $dbh->prepare ("select a from testtable");
  
      $sth->execute ();
      @data = $sth->fetchrow_array ();
      print join(" ", @data), "\n";
      printf ("Float: %.3f\n", $data[0]);
  
    setup the locale en_US.utf8 and run the script::
  
      ./test1.pl
      10.123
      Float: 10.123
  

• Now we do the same with locale de_AT.utf8, first with the python and then with the perl script (and see the output!!):

      export LANG=de_AT.utf8
      ./test1.py
      [10.0]
      x=10,000
  
      ./test1.pl
      10.123
      Float: 10,123
  

• I found a workaround to elimenate the error. I think the error occours with the strtod function call in pgresult.c (python2.4-pgsql). I modified the source with the following patch and left the commentars for further testings. I don't know, if this is the correct place, but it seems to be working and fix the bug situation:

      --- pgresult.c.ORIG 2005-04-26 16:32:54.951324128 +0200
      +++ pgresult.c      2005-04-26 16:33:35.469164480 +0200
      @@ -89,6 +89,7 @@
       | 06AUG2001 bga Initial release by Billy G. Allie.                 |
       \*(H-)******************************************************************/
  
      +#include <locale.h>
       #include <string.h>
       #include <stdlib.h>
       #include <stddef.h>
      @@ -623,7 +624,15 @@
                      /*FALLTHRU*/
  
                  case PG_FLOAT8:
      -           valueObj = Py_BuildValue("d", strtod(value, NULL));
      +           /* Straub */
      +           {}
      +           char *old = strdup(setlocale (LC_ALL, NULL));
      +           //printf ("oldlocale: %s\n", old);
      +           setlocale (LC_ALL, "C");
      +           double x = strtod (value, NULL);
      +           setlocale (LC_ALL, old);
      +           //printf ("Restored locale: %s\n", setlocale(LC_ALL, NULL));
      +           valueObj = Py_BuildValue("d", x);
                      break;
  
                  case PG_BYTEA:
  

• Build the package and install it:

      patch -p0 < ~/python-pgsql-2.4-locale.patch
      dpkg-buildpacke -rfakeroot -uc -us
      sudo dpkg -i ../python2.4-pgsql_2.4.0-5ubuntu2_amd64.deb \
         ../python-pgsql_2.4.0-5ubuntu2_all.deb
  

• Testscripts with locale de_AT.utf8:

      export LANG=de_AT.utf8
      ./test1.py
      [10.122999999999999]
      x=10,123
  
      ./test1.pl
      10.123
      Float: 10,123
  

• Seems to be OK, the same with locale en_US.utf8:

      export LANG=en_US.utf8
      ./test1.py
      [10.122999999999999]
      x=10.123
  
      ./test1.pl
      10.123
      Float: 10.123
  

• If i understand this page http://www.opengroup.org/onlinepubs/009695399/functions/strtod.html correctly, then the strtod function is influenced by the locale setting.