[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: How to Resolve Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to v12
From: "Hilbert, Karin" <ioh1 () psu ! edu>
Date: 2021-10-28 21:26:39
Message-ID: MN2PR02MB6829CF1357BD02F373775A2C89869 () MN2PR02MB6829 ! namprd02 ! prod ! outlook ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
Thanks for the explanation, Tom. 🙂
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, October 28, 2021 11:04 AM
To: Hilbert, Karin <ioh1@psu.edu>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: 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
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} \
</style> </head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; \
color: rgb(0, 0, 0);"> Thanks for the explanation, Tom. <span \
id="🙂">🙂</span></div> <div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" \
style="font-size:11pt" color="#000000"><b>From:</b> Tom Lane \
<tgl@sss.pgh.pa.us><br> <b>Sent:</b> Thursday, October 28, 2021 11:04 AM<br>
<b>To:</b> Hilbert, Karin <ioh1@psu.edu><br>
<b>Cc:</b> pgsql-general@lists.postgresql.org \
<pgsql-general@lists.postgresql.org><br> <b>Subject:</b> Re: How to Resolve \
Data Being Truncated or Rounded Up During PostgreSQL Migration from v9.623 to \
v12.8?</font> <div> </div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">"Hilbert, Karin" <ioh1@psu.edu> writes:<br>
> [ PG12 displays float values a tad differently from 9.6 ]<br>
<br>
This is not a bug; we just changed the behavior of the<br>
"extra_float_digits" display option, so that it's less likely<br>
to print garbage digits. A float4 value only has about six<br>
decimal digits of precision to begin with, and those extra<br>
digits you are seeing in the 9.6 dump are basically fictional.<br>
<br>
pg_dump does "set extra_float_digits to 3", which used to be<br>
necessary to get reproducible results when dumping from old<br>
servers. That has this effect on 9.6:<br>
<br>
regression=# select '53809.6'::float4;<br>
float4 <br>
---------<br>
53809.6<br>
(1 row)<br>
<br>
regression=# set extra_float_digits to 3;<br>
SET<br>
regression=# select '53809.6'::float4;<br>
float4 <br>
------------<br>
53809.6016<br>
(1 row)<br>
<br>
But it has no effect on new servers, because the six digits are<br>
already enough to recreate the float4 value exactly. The "016"<br>
added by the old server is basically roundoff noise.<br>
<br>
The reason for extra_float_digits is that with the old output<br>
algorithm, there were corner cases where we had to print more than<br>
six digits to ensure the value reloads exactly. The new algorithm<br>
automatically prints the minimum number of digits needed to ensure<br>
exact reload.<br>
<br>
All the same comments apply to float8, of course, with a<br>
different number of digits.<br>
<br>
\
regards, tom lane<br> </div>
</span></font></div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic