PSA: Postgres has extra_float_digits

[Markus Demleitner]

Hi Pat,

On Thu, Jan 14, 2021 at 10:32:27AM -0800, Patrick Dowler wrote:
> Interesting... I do expect bit-exact-ness and in CAOM the metadata
> checksums that are used would detect if there was a bit of lossiness.
> However, CAOM only uses double precision for floating point numbers... Is
> this specific to single precision aka real aka float?

https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-FLOAT
is fairly explicit that the "extra_float_digits setting controls the
number of extra significant digits included when a floating point
value is converted to text for output", so I'm rather sure all kinds
of floating point types are affected.

In itself, there isn't much of a way around this *when* you convert
to text, and what really surprised me in December is that this
conversion obviously takes place in the communication between the
database server and the client when querying through psycopg2.

There is a binary wire protocol (as used in the binary copy, for
instance), and I had always assumed that's what's in use in normal
client-server operations.  The fact that with less common types
(e.g., pgsphere objects) binary copy fails ("no binary output
function available for type spoint"), whereas that doesn't happen
with ordinary queries might have tipped me off, though.

Indeed, it seems libpq doesn't have an option to, perhaps, "enter
binary mode":
https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS .

Hence, if your DB stack somehow preserves these bits without
extra_float_digits=3, I'd be curious how it does that.

> At some point in the past I had a piece of test code to round trip all
> manner of IEEE float and double values through databases to check for
> support of edge cases and special values. Some RDBMS did worse than
> others...

Do you still have a writeup of that exercise?  While I don't see
DaCHS migrating away from postgres any time soon, having some idea
how the different vendors deal with the binary vs. decimal issue
would be interesting -- me, I'd be most interested in MonetDB's
choices.

         -- Markus