From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Gilleain Torrance <Gilleain.Torrance@alfasystems.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Incorrect rounding of double values at max precision
Date: Tue, 20 Oct 2020 21:48:52 -0400
Message-ID: <695837.1603244932@sss.pgh.pa.us> (raw)
In-Reply-To: <20201021011725.kmnkgj7ghedjre6j@alap3.anarazel.de>
References: <DB3D1D143DDA534BBB39DE713E92343701CBA89718@GBEDBP01.chp.co.uk>
<5045.1571686957@sss.pgh.pa.us>
<875zkhhjs0.fsf@news-spur.riddles.org.uk>
<26443.1571712071@sss.pgh.pa.us>
<20201021011725.kmnkgj7ghedjre6j@alap3.anarazel.de>
[ btw, thanks for finding this thread; I searched for relevant
discussion earlier today and couldn't find it ]
Andres Freund <andres@anarazel.de> writes:
> It seems crazy that we throw away integer precision in the range it's
> guaranteed to be accurate (that's what, −16777216 to 16777216 for
> float4, more than a magnitude larger than this value). It does feel
> different to me that we're throwing away precision that we could know is
> not just the result of floating point imprecision.
Meh. Yeah, we could improve the observed results for float4 values
that are integers between 1M and 16M, and some similarly-sized band
for float8; but to what end? The most likely practical result is
just to postpone the user's discovery that they're Doing It Wrong.
If you expect exact answers out of float calculations then you are
going to learn an expensive lesson sooner or later. Better sooner,
before you've stored even more inexact data that you cannot fix.
> I didn't find much discussion about how we could deal with pg_upgrade
> issues if we were to change the behaviour. Detect indexes involving such
> casts, and mark them as invalid?
For what that's worth, I do not think I buy the argument that
float4_numeric's behavior can't ever be changed because it's marked
immutable. Compare other recent discussions about what "immutable"
really means; or compare the fact that various text search functions
are marked immutable despite being quite dependent on config files
that we don't even track. If we think this is actually an improvement
I'd be fine with changing it in a major release, and documenting
that users should reindex any indexes whose semantics are affected
(of which there'd be epsilon in the real world, anyway). But I'm
not convinced that changing this is doing anything except putting
more lipstick on the pig. float is imprecise, and we do no one
a service by trying to hide that.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, andrew@tao11.riddles.org.uk, Gilleain.Torrance@alfasystems.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Incorrect rounding of double values at max precision
In-Reply-To: <695837.1603244932@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox