Thread: extra_float_digits and casting from real to numeric
A customer recently upgraded their jdbc driver from 8.4 to 9.2. This enabled the binary wire protocol (effectively between 9.1 and 9.2). They reported that single precision values inserted into a numeric(10,2) column were suddenly rounded wrongly, i.e. 10000.18 was inserted as 10000.20, while that worked before. Of course we told them that single is the wrong data type for this, but still, this is a regression. The behavior is easily reproducible with SELECT 10000.18::real which returns 10000.2. Now, the jdbc driver sets extra_float_digits = 3, which makes the this ::real cast return 10000.1797 in psql. This is consistent with the documentation which suggests that extra_float_digits = 0 will return the same representation on all platforms, so it must be rounded a bit to account for different implementations. But if extra_float_digits > 0 is set, I'd expect not only the float4 output to be affected by it, but also casts to other datatypes, which is not the case now: set extra_float_digits = 0; select 10000.18::real, 10000.18::real::numeric(10,2), 10000.18::real::text, to_char(10000.18::real, '99999D999');float4 | numeric | text | to_char ---------+----------+---------+----------10000.2 | 10000.20 | 10000.2 | 10000.2 set extra_float_digits = 1; select 10000.18::real, 10000.18::real::numeric(10,2), 10000.18::real::text, to_char(10000.18::real, '99999D999'); float4 | numeric | text | to_char ----------+----------+----------+----------10000.18 | 10000.20 | 10000.18 | 10000.2 set extra_float_digits = 3; select 10000.18::real, 10000.18::real::numeric(10,2), 10000.18::real::text, to_char(10000.18::real, '99999D999'); float4 | numeric | text | to_char ------------+----------+------------+----------10000.1797 | 10000.20 | 10000.1797 | 10000.2 Is that sane? Shouldn't FLT_DIG in float4_numeric() be replaced with "FLT_DIG + extra_float_digits" like float4out() does, so the extra precision is not lost when inserting float4 data into numeric columns? Likewise, float4_to_char() should be adjusted for to_char output, and correspondingly float8_numeric() and float8_to_char()? Christoph -- cb@df7cb.de | http://www.df7cb.de/
Christoph Berg <christoph.berg@credativ.de> writes:
> A customer recently upgraded their jdbc driver from 8.4 to 9.2. This
> enabled the binary wire protocol (effectively between 9.1 and 9.2).
> They reported that single precision values inserted into a
> numeric(10,2) column were suddenly rounded wrongly, i.e. 10000.18 was
> inserted as 10000.20, while that worked before. Of course we told them
> that single is the wrong data type for this, but still, this is a
> regression.
I'm not sure that it's fair to characterize that as a regression.
If anything, it's more sensible than what happened before.
> But if extra_float_digits > 0 is set, I'd expect not only the float4
> output to be affected by it, but also casts to other datatypes,
This proposal scares me. extra_float_digits is strictly a matter of
I/O representation, it does not affect any internal calculations.
Moreover, since one of the fundamental attributes of type numeric
is that it's supposed to give platform-independent results, I don't
like the idea that you're likely to get platform-dependent results
of conversions from float4/float8.
I think your customer got bit by his own bad coding practice, and
that should be the end of it.
regards, tom lane
Re: Tom Lane 2014-01-07 <14979.1389112998@sss.pgh.pa.us> > > But if extra_float_digits > 0 is set, I'd expect not only the float4 > > output to be affected by it, but also casts to other datatypes, > > This proposal scares me. extra_float_digits is strictly a matter of > I/O representation, it does not affect any internal calculations. > Moreover, since one of the fundamental attributes of type numeric > is that it's supposed to give platform-independent results, I don't > like the idea that you're likely to get platform-dependent results > of conversions from float4/float8. I forgot to mention one bit here, and that's actually what made me think "wtf" and post here. The server log is of course also affected by this, so you even get different parameters depending on extra_float_digits, yet the numeric result is the same "bad" one: 2014-01-08 10:13:53 CET LOG: execute <unnamed>: INSERT INTO s VALUES($1) 2014-01-08 10:13:53 CET DETAIL: parameters: $1 = '10000.2' 2014-01-08 10:14:18 CET LOG: execute <unnamed>: INSERT INTO s VALUES($1) 2014-01-08 10:14:18 CET DETAIL: parameters: $1 = '10000.1797' Of course this is all consistent and in practice sums up to "don't use real/single"... > I think your customer got bit by his own bad coding practice, and > that should be the end of it. What about this patch to mention this gotcha more explicitely in the documentation? diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml new file mode 100644 index 0386330..968f4a7 *** a/doc/src/sgml/datatype.sgml --- b/doc/src/sgml/datatype.sgml *************** NUMERIC *** 689,694 **** --- 689,697 ---- <literal>0</literal>, the output is the same on every platform supported by PostgreSQL. Increasingit will produce output that more accurately represents the stored value, but may be unportable. + Casts to other numeric datatypes and the <literal>to_char</literal> + function are not affected by this setting, it affects only the text + representation. </para> </note> Christoph -- cb@df7cb.de | http://www.df7cb.de/
Re: To Tom Lane 2014-01-08 <20140108094017.GA20317@msgid.df7cb.de> > What about this patch to mention this gotcha more explicitely in the > documentation? > > diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml > new file mode 100644 > index 0386330..968f4a7 > *** a/doc/src/sgml/datatype.sgml > --- b/doc/src/sgml/datatype.sgml > *************** NUMERIC > *** 689,694 **** > --- 689,697 ---- > <literal>0</literal>, the output is the same on every platform > supported by PostgreSQL. Increasing it will produce output that > more accurately represents the stored value, but may be unportable. > + Casts to other numeric datatypes and the <literal>to_char</literal> > + function are not affected by this setting, it affects only the text > + representation. > </para> > </note> > Anyone for that patch? Christoph -- cb@df7cb.de | http://www.df7cb.de/
On Tue, Feb 4, 2014 at 11:25 AM, Christoph Berg
<christoph.berg@credativ.de> wrote:
> Re: To Tom Lane 2014-01-08 <20140108094017.GA20317@msgid.df7cb.de>
>> What about this patch to mention this gotcha more explicitely in the
>> documentation?
>>
>> diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
>> new file mode 100644
>> index 0386330..968f4a7
>> *** a/doc/src/sgml/datatype.sgml
>> --- b/doc/src/sgml/datatype.sgml
>> *************** NUMERIC
>> *** 689,694 ****
>> --- 689,697 ----
>> <literal>0</literal>, the output is the same on every platform
>> supported by PostgreSQL. Increasing it will produce output that
>> more accurately represents the stored value, but may be unportable.
>> + Casts to other numeric datatypes and the <literal>to_char</literal>
>> + function are not affected by this setting, it affects only the text
>> + representation.
>> </para>
>> </note>
>>
>
> Anyone for that patch?
Well, the new text kinda recapitulates what the existing text already
says. If we're going to clarify, I'd do it like this:
The <xref linkend="guc-extra-float-digits"> setting controls the number of extra significant digits included
whena floating point value is converted to text for output. It does not affect the results when a floating
pointnumber is converted to some other data type or formatted using <literal>to_char</literal>.
But frankly I'm inclined to just leave it alone. It says that it
affects what happens when the value "is converted to text for output".That's specific and accurate. Granted, someone
couldmisunderstand,
but that's true of almost anything we might write, and being too
long-winded has costs of its own.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: Robert Haas 2014-02-05 <CA+TgmoZY4KKapTkf2PXCoQnT-jb0YSP8w2PhP_qge62NiLv5oA@mail.gmail.com> > >> <literal>0</literal>, the output is the same on every platform > >> supported by PostgreSQL. Increasing it will produce output that > >> more accurately represents the stored value, but may be unportable. > >> + Casts to other numeric datatypes and the <literal>to_char</literal> > >> + function are not affected by this setting, it affects only the text > >> + representation. > >> </para> > >> </note> > >> > > > > Anyone for that patch? > > Well, the new text kinda recapitulates what the existing text already > says. If we're going to clarify, I'd do it like this: > > The <xref linkend="guc-extra-float-digits"> setting controls the > number of extra significant digits included when a floating point > value is converted to text for output. It does not affect the results > when a floating point number is converted to some other data type > or formatted using <literal>to_char</literal>. > > But frankly I'm inclined to just leave it alone. It says that it > affects what happens when the value "is converted to text for output". > That's specific and accurate. Granted, someone could misunderstand, > but that's true of almost anything we might write, and being too > long-winded has costs of its own. Yes, the original text is correct. The point is that the original text doesn't really make the reader (who might think the numbers he's seeing on the screen will also be used for computation/inserts) aware that he's looking at something very different from what will actually be used internally. I'm sure we were not the only one to stumble over that problem, and I even knew what extra_float_digits is for, yet I didn't connect the dots in the beginning. Including some explicit heads-up sentence in there solves that problem, either your version, or mine. Mit freundlichen Grüßen, Christoph Berg -- Tel.: +49 (0)21 61 / 46 43-187 credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209 Hohenzollernstr. 133, 41061 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer