Re: [HACKERS] extra_float_digits and casting from real to numeric
Re: Tom Lane 2014-01-07 14979.1389112...@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 = '1.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 = '1.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 literal0/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 literalto_char/literal + function are not affected by this setting, it affects only the text + representation. /para /note Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
[HACKERS] 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. 1.18 was inserted as 1.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 1.18::real which returns 1.2. Now, the jdbc driver sets extra_float_digits = 3, which makes the this ::real cast return 1.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 1.18::real, 1.18::real::numeric(10,2), 1.18::real::text, to_char(1.18::real, '9D999'); float4 | numeric | text | to_char -+--+-+-- 1.2 | 1.20 | 1.2 | 1.2 set extra_float_digits = 1; select 1.18::real, 1.18::real::numeric(10,2), 1.18::real::text, to_char(1.18::real, '9D999'); float4 | numeric | text | to_char --+--+--+-- 1.18 | 1.20 | 1.18 | 1.2 set extra_float_digits = 3; select 1.18::real, 1.18::real::numeric(10,2), 1.18::real::text, to_char(1.18::real, '9D999'); float4 | numeric |text| to_char +--++-- 1.1797 | 1.20 | 1.1797 | 1.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 -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] extra_float_digits and casting from real to numeric
Christoph Berg christoph.b...@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. 1.18 was inserted as 1.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
