Thread: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12.8?

"Hilbert, Karin" <ioh1@psu.edu> writes:
> [ PG12 displays float values a tad differently from 9.6 ]

This is not a bug; we just changed the behavior of the
"extra_float_digits" display option, so that it's less likely
to print garbage digits.  A float4 value only has about six
decimal digits of precision to begin with, and those extra
digits you are seeing in the 9.6 dump are basically fictional.

pg_dump does "set extra_float_digits to 3", which used to be
necessary to get reproducible results when dumping from old
servers.  That has this effect on 9.6:

regression=# select '53809.6'::float4;
 float4  
---------
 53809.6
(1 row)

regression=# set extra_float_digits to 3;
SET
regression=# select '53809.6'::float4;
   float4   
------------
 53809.6016
(1 row)

But it has no effect on new servers, because the six digits are
already enough to recreate the float4 value exactly.  The "016"
added by the old server is basically roundoff noise.

The reason for extra_float_digits is that with the old output
algorithm, there were corner cases where we had to print more than
six digits to ensure the value reloads exactly.  The new algorithm
automatically prints the minimum number of digits needed to ensure
exact reload.

All the same comments apply to float8, of course, with a
different number of digits.

            regards, tom lane

On 10/28/21 07:44, Hilbert, Karin wrote:
> I manage aPostgreSQL databases - we currently have clusters on 
> *PostgreSQL v9.6.23* & **PostgreSQL v12.8**.
> Our database clusters are on Linux VMs, with OS:
>    Flavor:*redhat_7*
>    Release: *3.10.0-1160.45.1.el7.x86_64*
> 
> We have repmgr clusters of 1 Primary & 2 Standby servers & use another 
> server with PgBouncer to direct the connections to the current Primary.
> 
> How can I ensure that the data was migrated correctly - that the data 
> hasn't been truncated or rounded up in the v12.8 tables?
> Any help would be greatly appreciated.

In postgresql.conf what are the settings for?:

https://www.postgresql.org/docs/12/runtime-config-client.html

extra_float_digits

"
Note

The meaning of this parameter, and its default value, changed in 
PostgreSQL 12; see Section 8.1.3 for further discussion.
"

Section 8.1.3

https://www.postgresql.org/docs/12/datatype-numeric.html#DATATYPE-FLOAT


> 
> Thanks,
> 
> Karin Hilbert
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com