Re: Fwd: Fwd: Problem with pg_dump and decimal mark

[Eric Svenson <esvenson74@xxxxxxxxxxxxxx>]

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.

Regards,
Eric

2014-12-08 15:22 GMT+01:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:

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@xxxxxxxxxxx