Hello,
I've run into a problem where floats loose their fractions on the way
from the database to a perl script using DBD::Pg. A minimal reproducer:
Database content:
test=> select * from foo;
foo
-----
1.1
(1 row)
Perl script:
#!/usr/bin/perl
use DBI;
use DBD::Pg;
use POSIX;
POSIX::setlocale(&POSIX::LC_NUMERIC, "");
$dbh = DBI->connect("dbi:Pg:dbname=test", '', '', {AutoCommit => 0});
$sth = $dbh->prepare("SELECT * FROM foo;");
$result = $sth->execute();
print($sth->fetchrow_array(), "\n");
Output:
# LANG=C perl ~/t.pl
FOO: 1.1:1.100000 C
1.1
# LANG=de_DE.UTF-8 perl ~/t.pl
FOO: 1.1:1,000000 de_DE.UTF-8
1
A number of factors seem to have to come together to trigger the
problem:
- the client locale's decimal point character must be something other
than what the database (or the on-wire protocol?) is using, in my
case comma (,) from locale de_DE
- the script needs to reset LC_NUMERIC to empty, the reason for which is
lost to the depths of time. I guess it's because as a
side-effect it seems to enable locale-awareness in perl, particularly
in my case commas being used when printing float values.
- so far only macOS's system perl seems to be affected. A perl 5.29.9
compiled myself on the same system does not exhibit the problem and
neither does a Debian testing box.
I've traced the problem to the following code in dbdimp.c:dbd_st_fetch,
which is also where I've added the above FOO debug output:
AV * dbd_st_fetch (SV * sth, imp_sth_t * imp_sth)
{
[...]
switch (type_info->type_id) {
[...]
case PG_FLOAT4:
case PG_FLOAT8:
TRC(DBILOGFP, "FOO: %s:%2f
%s\n", value, strtod((char *)value, NULL), getenv("LANG"));
sv_setnv(sv, strtod((char
*)value, NULL));
break;
Where the database is delivering strings with dots as decimal point
character, strtod() in my case expects commas. It appears that macOS's
system perl uses a strtod() implementation that respects the locale
settings as per documentation in the man page:
The decimal point character is defined in the program's locale
(category LC_NUMERIC).
Since I've not been able to reproduce this with my self-compiled perl or
Debian perl I can only assume that they're using another implementation
and not the one from libc.
Software versions:
affected system perl: v5.18.4
unaffected vanilla perl: 5.29.9
DBD::Pg: 3.7.4
Postgres client library: vanilla 11.2 compiled myself
Postgres server: Debian testing 11.4-1, locale en_GB.UTF-8
Any advice on how to proceed here would be highly appreciated,
particularly regarding:
- Is it at all valid to tweak LC_NUMERIC using POSIX::setlocale() when
using DBD::Pg?
- Could the macOS system perl at runtime (without recompilation) be
convinced to use another strtod() implementation/behaviour? (... which
doesn't involve mucking about with LD_PRELOAD/DYLD_INSERT_LIBRARIES :)
- Could this reliably be fixed by switching to a non-locale-aware strtod()
implementation in DBD::Pg? Based on the protocol specification
(https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-FORMAT-CODES)
I suspect that the on-wire representation could be similarly affected by
server-side locale settings:
The text representation of values is whatever strings are
produced and accepted by the input/output conversion functions
for the particular data type.
I haven't yet looked at the Postgres code to find out for sure.
Thanks in advance,
--
Michael
