Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (1.0)
Subject: Re: Rounding numbers from copy expert
From: John Abraham <jea@hbaspecto.com>
In-Reply-To: <fb01e8ae-597d-5ccf-f71f-75dd4e00e338@aklaver.com>
Date: Wed, 24 Jul 2019 17:35:05 -0700
Cc: psycopg@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <046E2360-462C-4B55-8034-160E4612E493@hbaspecto.com>
References: <8DC745DF-DDF9-4697-BAAF-23ABE78A1EB9@hbaspecto.com>
 <fb01e8ae-597d-5ccf-f71f-75dd4e00e338@aklaver.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Precedence: bulk


> On Jul 24, 2019, at 4:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wro=
te:
>=20
>> On 7/24/19 4:28 PM, John Abraham wrote:
>> Hmm, I have two machines connecting to the same database running the same=
 query inside copy_expert.
>> Both seem to have psycopg2 2.7.5 (based on pip3 freeze).
>> One seems to be rounding the double precision number 696538951.985381 to 6=
.96538944E8 whereas the other machine isn't rounding it.
>=20
> First:
>=20
> https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-FLOAT
>=20
> 8.1.3. Floating-Point Types
> "The data types real and double precision are inexact, variable-precision n=
umeric types. ...
>=20
> Inexact means that some values cannot be converted exactly to the internal=
 format and are stored as approximations, so that storing and retrieving a v=
alue might show slight discrepancies. Managing these errors and how they pro=
pagate through calculations is the subject of an entire branch of mathematic=
s and computer science and will not be discussed here, except for the follow=
ing points: ..."


One of my proudest moments in my very first job in the late 80s was solving i=
n just a few minutes a lack-of-precision problem my boss was fighting with f=
or more than a day because he was subtracting two similar floating point num=
bers. I=E2=80=99m enjoying the irony of fighting a similar problem now, myse=
lf, 30 years later.

Anyway, in this case it=E2=80=99s not the precision, it=E2=80=99s the repeat=
ability.  If you run the same program twice on two computers with the same s=
oftware, you *should* get *exactly* the same answer. =20

>=20
> Second:
>=20
> Where are you actually 'looking' at the values?

I=E2=80=99m seeing the difference in a .CSV file produced by copy_expert, wh=
ich as far as I can tell just streams the stdout from a database =E2=80=98co=
py to stdout=E2=80=99 command to a text file. I=E2=80=99m trying to figure o=
ut why the =E2=80=98copy to stdout=E2=80=99 SQL command would give different=
 interpretations of a number.

>=20
>=20
>> This is causing auditability/repeatability problems, as the difference is=
 actually significant in this context.
>> Is there some thing about the way psycopg2 connects to the database, or t=
he way psycopg2 is configured, that could be causing this rounding? Is there=
 some way to express a preference for scientific notation?
>> I'm at a complete loss as to why copy_expert (which basically does a 'cop=
y to stdin' query) would be different when called from different machines. M=
y next step in the madness is to see if the locale has an effect, one is set=
 to en_CA.UTF-8 and the other is set to en_US.UTF-8.   But, perhaps someone e=
lse has a better idea.
>> Thanks for any help.
>> --
>> John Abraham
>> jea@hbaspecto.com <mailto:jea@hbaspecto.com>
>> 403-232-1060
>=20
>=20
> --=20
> Adrian Klaver
> adrian.klaver@aklaver.com


=E2=80=94
John Abraham