Hi,
On Mon, Jul 01, 2019 at 03:56:28PM +0200, Michael Weiser wrote:
> 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:
Am I wrong on this list with my question or was there just too much or
too little or too specific information at once? :)
I'd appreciate any advice since I'm totally lost how to proceed here.
Thanks,
Michael
> 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,
--
tschuess, Micha
Dagegen!
