Thread: Problem with pg_dump and decimal mark

On 11/28/2014 05:35 AM, Eric Svenson wrote:
> Hello,
>
> I have done a backup of a postgres database on a virtual machine
> (Windows 8.1) using pg_dump.
>
> On another (non-virtual) machine the restore (with psql) worked without
> problems.
>
> On the third virtual machine, however, the restore fails.
>
> (ERROR: invalid input syntax for type double precision: 0.100000000001)
>
> When I change the value in the sql file manually to 0,100000000001 the
> and try again, the restore resumes until the next double value.
>
> How is this possible? Does psql really expect comma-seperated decimal
> values in the sql file? How can I change this behaviour?

Seems you have a locale mismatch issue. The dump is coming from a locale
where a '.' is the decimal mark and is being restored to a locale where
',' is the mark. Look at what the locales are the machines that work and
the one that does not.

>
> Regards,
> Eric Svenson
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 11/28/2014 05:35 AM, Eric Svenson wrote:
>> I have done a backup of a postgres database on a virtual machine
>> (Windows 8.1) using pg_dump.
>>
>> On another (non-virtual) machine the restore (with psql) worked without
>> problems.
>>
>> On the third virtual machine, however, the restore fails.
>>
>> (ERROR: invalid input syntax for type double precision: 0.100000000001)
>>
>> When I change the value in the sql file manually to 0,100000000001 the
>> and try again, the restore resumes until the next double value.
>>
>> How is this possible? Does psql really expect comma-seperated decimal
>> values in the sql file? How can I change this behaviour?

> Seems you have a locale mismatch issue. The dump is coming from a locale
> where a '.' is the decimal mark and is being restored to a locale where
> ',' is the mark. Look at what the locales are the machines that work and
> the one that does not.

That's what it sounds like all right, but how could that be?  The behavior
of float8in/float8out is not supposed to be locale-dependent.

float8in does depend on strtod(), whose behavior is locale-dependent
according to POSIX, but we keep LC_NUMERIC set to "C" to force it to
only believe that "." is decimal point.

Of course, this wouldn't be the first time we've found out that Microsoft
can't read the POSIX spec :-(.

            regards, tom lane

On 11/28/2014 02:08 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 11/28/2014 05:35 AM, Eric Svenson wrote:
>>> I have done a backup of a postgres database on a virtual machine
>>> (Windows 8.1) using pg_dump.
>>>
>>> On another (non-virtual) machine the restore (with psql) worked without
>>> problems.
>>>
>>> On the third virtual machine, however, the restore fails.
>>>
>>> (ERROR: invalid input syntax for type double precision: 0.100000000001)
>>>
>>> When I change the value in the sql file manually to 0,100000000001 the
>>> and try again, the restore resumes until the next double value.
>>>
>>> How is this possible? Does psql really expect comma-seperated decimal
>>> values in the sql file? How can I change this behaviour?
>
>> Seems you have a locale mismatch issue. The dump is coming from a locale
>> where a '.' is the decimal mark and is being restored to a locale where
>> ',' is the mark. Look at what the locales are the machines that work and
>> the one that does not.
>
> That's what it sounds like all right, but how could that be?  The behavior
> of float8in/float8out is not supposed to be locale-dependent.
>
> float8in does depend on strtod(), whose behavior is locale-dependent
> according to POSIX, but we keep LC_NUMERIC set to "C" to force it to
> only believe that "." is decimal point.

Not sure if this makes a difference but if I am reading the original
post correctly the OP was trying a plain text restore via psql.

>
> Of course, this wouldn't be the first time we've found out that Microsoft
> can't read the POSIX spec :-(.
>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/29/2014 12:25 AM, Eric Svenson wrote:
>
>
>
>             Seems you have a locale mismatch issue. The dump is coming
>             from a locale
>             where a '.' is the decimal mark and is being restored to a
>             locale where
>             ',' is the mark. Look at what the locales are the machines
>             that work and
>             the one that does not.
>
>
>
> I have already done that and found something strange:
>
> On the PC where the backup was done with pg_dump, all locale settings of
> Postgres were English/United States. (LC_COLLATE, LC_CTYPE, LC_MONETARY,
> LC_NUMERIC in postgresql.conf)

OS and OS version?
Postgres version?
What was the pg_dump command used?

>
> On the first PC on which I tried to load the backup file with psql, all
> locale settings if Postgres were  German_Germany. Everything is ok, the
> SQL file with '.' as decimal point was accepted without a problem

OS and OS version?
Postgres version?
How was it loaded via psql?
Was the psql on the same machine and from the same version of Postgres?

>
> On the second PC (Virtual Machine) I had the SAME settings in
> postgresql.conf (German_Germany)

OS and OS version?
Postgres version?
How was it loaded via psql?
Did you use the psql on the VM or did you use the psql on the host?
Was the Postgres/psql on the host the same as the VM?
What are you using for virtualization?
What is host OS?

>
> -> no success
>
> I tried to change all the settings to English/United States, restart
> postgres
>
> -> still no success
>
> Changed all Windows settings to English / United States
>
> -> still no success.
>
> So what I am searching for (at the moment without success) is the
> 'switch' which decides what decimal seperator to expect by psql.

Well according to below, flipping a switch is not necessary. There is a
mismatch occurring, which is why I posed all the questions above. To
narrow the possibilities it would help to a have fuller picture of what
the environment is in each situation.

>
>         That's what it sounds like all right, but how could that be?
>         The behavior
>         of float8in/float8out is not supposed to be locale-dependent.
>
>         float8in does depend on strtod(), whose behavior is locale-dependent
>         according to POSIX, but we keep LC_NUMERIC set to "C" to force it to
>         only believe that "." is decimal point.
>
>
>     Not sure if this makes a difference but if I am reading the original
>     post correctly the OP was trying a plain text restore via psql.
>
>
> This is correct.
>
>
> regards and thanks for your support,
> Eric Svenson
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/03/2014 12:23 AM, Eric Svenson wrote:
>
> Hi Adrian,
>
> thank you for your support. Here are the informations you requested
>
>
>     I have already done that and found something strange:
>
>     On the PC where the backup was done with pg_dump, all locale settings of
>     Postgres were English/United States. (LC_COLLATE, LC_CTYPE, LC_MONETARY,
>     LC_NUMERIC in postgresql.conf)
>
>
>  > OS and OS version?
>
> Windows Server 2012R2
>
>  > Postgres version?
>
> Version 9.2.1 (same version used on all machines)
>
>  > What was the pg_dump command used?
>
> pg_dump -p 6789 -EUTF8 -f myFile.sql my_Database
>

>  > How was it loaded via psql?
>
> psql -p 6789 -U postgres my_Database < myFile.sql
>
>  > Was the psql on the same machine and from the same version of Postgres?
>
> Same version, but on different machines.
>
>
>     On the second PC (Virtual Machine) I had the SAME settings in
>     postgresql.conf (German_Germany)
>
>
>  > OS and OS version?
>
> Windows 7 Ultimate SP 1
>
>  > Postgres version?
>
> Version 9.2.1 (same version used on all machines)
>
>  > How was it loaded via psql?
>
> psql -p 6789 -U postgres my_Database < myFile.sql
>
>  > Did you use the psql on the VM or did you use the psql on the host?
>
> psql on the VM
>

So what if you load to the VM Postgres using the psql from the host?

>
> Again, thank you for the support.

As Tom said LC_NUMERIC is set to C in main.c:

/*
          * We keep these set to "C" always, except transiently in
pg_locale.c; see
          * that file for explanations.
          */

The transiently part intrigued me so I went to pg_locale.c and there was
a big:

* !!! NOW HEAR THIS !!!

with regards to resetting locales. Honestly the explanation is beyond my
expertise. I offer it only as a starting point for those that can
understand it.


>
> Regards,
> Eric Svenson


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/04/2014 02:01 AM, Eric Svenson wrote:
>> So what if you load to the VM Postgres using the psql from the host?
>
> I tried that: On the host I started psql with
> psql -h 192.168.2.55 -U postgres -p 5432 my_Database < myFile.sql
>
> I get the same error
>
> ERROR: invalid input syntax for type double precision "0.2"
> KONTEXT: COPY dev_my_settings, line xxx, column xxx: "0.2"

Is the above how the message was actually presented or has a partial
translation taken place? Just asking because it would seem to indicate
further confusion about the locale.

>
> ERROR: invalid input syntax for type double precision "15.776653623"

Hmm, I'm at a loss. It is not a strict Windows issue as you can restore
on other Windows machines. The remaining suspect would be VMware. VMware
and locales tickles a memory, one that I cannot drag up at the moment.
One of those things where the more you think of it the further it goes
away. We will see if working on other stuff causes it to sneak up on me:)

>
> Regards,
> Eric Svenson
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/05/2014 01:13 AM, Eric Svenson wrote:
>
>
> Hi Adrian,
>
>>Is the above how the message was actually presented or has a partialtranslation taken >place? Just asking because it
wouldseem to indicate 
> further confusion about the locale.
>
> This is an exact copy of the screen contents, no translation by me has
> taken place. Indeed strange, "ERROR" (english) "KONTEXT" (german, one
> line below) ??
>
> To add further confusion: I have a report that the error also appeared
> on a non-VM native PC with Windows 7 Enterprise. Unfortunately, I dont
> have the exact error message, but I try to get it ASAP.

Well nothing came back to me on VMware and locales, but that does not
seem to be the issue if the above is correct.

So:

How where the Postgres instances installed?
   From a package?
   Compiled and if so with what compiler and what settings?

What happens if you?:

Use --inserts with pg_dump to get INSERT statements instead of a COPY
and then feed to psql.
This will slow the process down, so I would try with a small sample set.

Do pg_dump -Fc and then use pg_restore.

The above are more a way of seeing if the issue is on a particular path
or is generic, than a solution.

>
> regards,
> Eric Svenson
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/08/2014 12:21 AM, Eric Svenson wrote:
> Hi Adrian,
>
> here are the results of today:
>
>  > How where the Postgres instances installed?
>  >   From a package?
>
> Yes. It is Version 9.2 for Windows, comment of the package is
> "The PostgreSQL RDBMS, version 9.2, packaged by EnterpriseDB"
>
>
>  >  Compiled and if so with what compiler and what settings?
> No.
>
>
>  > Use --inserts with pg_dump to get INSERT statements instead of a COPY
> and then feed to psql.
>  > This will slow the process down, so I would try with a small sample set.
>
> Much slower, but still the same error:
>
> (part of the output)
>
> INSERT 50415934 1
> INSERT 50415935 1
> INSERT 50415936 1
> INSERT 50415937 1
> INSERT 50415938 1
> INSERT 50415939 1
> ERROR:  invalid input syntax for type double precision:
> "0.10000000000000001"
>
>  > Do pg_dump -Fc and then use pg_restore.
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2558; 0 5161040 TABLE
> DATA dev_my_settings my_Database
> pg_restore: [archiver (db)] COPY failed for table "dev_my_settings":
> ERROR:  invalid input syntax
> for type double precision: "0.10000000000000001"
> CONTEXT:  COPY dev_meas_settings, line 718, column dms_dble_value:
> "0.10000000000000001"
> pg_restore: [archiver (db)] Error from TOC entry 2552; 0 5160884 TABLE
> DATA file_item my_Database
> pg_restore: [archiver (db)] COPY failed for table "file_item": ERROR:
> invalid input syntax for type
>   double precision: "48.200082999999999"
> CONTEXT:  COPY file_item, line 54, column fi_latitude: "48.200082999999999"
> WARNING: errors ignored on restore: 2

Well at least it is consistent:) Postgres is deployed to a lot of
Windows machines, so if this was a generic Windows problem I would
expect more reports on this. There is something about this setup that is
causing the problem and we are missing.

In a previous post you made mention of a possible instance where this
cropped up on a non-VM machine. Did you get a chance to track that down?

Also the output from pg_restore shows only two errors on restore which I
presume are the two COPY errors with the input syntax. So are there
other tables in the database, with float values, that do restore correctly?

Also in the errors above, in the first case COPY does not error until
line 718 and in the second case line 54. So are there float values in
the data for those columns that are valid?

Also what are the table definitions for dev_my_settings and file_item?

>
> Regards,
> Eric


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/08/2014 06:53 AM, Eric Svenson wrote:
> Hi Adrian,
>
> I try to get access to the non-VM machine, at the moment access is not
> possible for me unfortunately.
>
> You are right, there are more tables in the database which are restored
> correctly but these tables do NOT contain float values. These two tables
> are the only tables in the database which contain floats.
>
> The errors occur with the first float in the table, the restore process
> seems to terminate with that table and seems to continue with the next
> table. The result are completely empty tables for dev_my_settings and
> file_item.
>
> There are float values in the table which can be viewed with pg_admin.
>
> The table definitions for dev_my_settings and file_item contain lots of
> BIGINTS, smallints and integers, and several double precision values.
> All other tables do not contain any double precision values.

At the moment I am out of ideas.

>
> Regards,
> Eric
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/08/2014 06:53 AM, Eric Svenson wrote:
> Hi Adrian,
>
> I try to get access to the non-VM machine, at the moment access is not
> possible for me unfortunately.
>
> You are right, there are more tables in the database which are restored
> correctly but these tables do NOT contain float values. These two tables
> are the only tables in the database which contain floats.
>
> The errors occur with the first float in the table, the restore process
> seems to terminate with that table and seems to continue with the next
> table. The result are completely empty tables for dev_my_settings and
> file_item.
>
> There are float values in the table which can be viewed with pg_admin.
>
> The table definitions for dev_my_settings and file_item contain lots of
> BIGINTS, smallints and integers, and several double precision values.
> All other tables do not contain any double precision values.

Alright a chance to think some more.

So:

The restore left you with two empty tables. What happens if you log into
Postgres via psql and then INSERT one set of values containing floats
into say, dev_my_settings?

While you are in psql, what does SHOW ALL display for the lc_* settings?

On the Windows server where the Postgres server is running what does SET
show from the command line?

>
> Regards,
> Eric
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/10/2014 01:32 AM, Eric Svenson wrote:
> So, one more success...
>
> I have taken a part of the backup SQL file which fills the table
>
> COPY dev_my_settings (.....) from stdin;
> 12345  text   text   0   123.345345
>
> This file ALONE works! (without changing ANYTHING!)

Hmm, almost like the encoding/locale is changing in the complete file.

>
> So if I run the first (huge) SQL file and then the second, which fills
> the dev_my_settings table, everything is ok.

FYI if you do a pg_dump using the custom format(-Fc) you have more
control over the restore. You can run pg_restore -l against the dump
file to get a Table of Contents(TOC), which you can edit by commenting
out items you do not want to restore and then feed back to pg_restore
via -L. You also have the option to 'restore' all or part of the custom
file to a text file using the -f option. The details can be found here:

http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html
>
> 2014-12-10 10:23 GMT+01:00 Eric Svenson <esvenson74@googlemail.com


--
Adrian Klaver
adrian.klaver@aklaver.com