Surprising output when server returns string with formatted double precision values.

[janholstjensen]

Issue observed first with Npgsql 2.0.14.3. Reproduced with latest 2.2.0 RC 2.

CREATE OR REPLACE FUNCTION format_error()
  RETURNS void AS
$BODY$
declare
  x double precision;
  y numeric;
begin
  x := 0.2;
  y := 0.2;
  raise exception 'x = %, y = %', x, y;
end;
$BODY$ LANGUAGE plpgsql;

Run "select format_error();" in PgAdmin, and you see the expected result:

ERROR:  x = 0.2, y = 0.2

Do the same with Npgsql using this C# code:

    class Program
    {
        static void Main(string[] args)
        {
            NpgsqlConnection conn = new NpgsqlConnection("server=...;Port=...;Database=postgres;User name=...;Password=...");
            conn.Open();
            NpgsqlCommand command = new NpgsqlCommand("select format_error();", conn);
            IDataReader rdr = command.ExecuteReader();
            rdr.Read();
        }
    }

and you see this error message:

Unhandled Exception: Npgsql.NpgsqlException: x = 0.200000000000000011, y = 0.2

Now, change the raise statement in the server function so it force-casts the double precision value to numeric before formatting:

  raise exception 'x = %, y = %', x::numeric, y;

and now we have:

PgAdmin:    ERROR:  x = 0.2, y = 0.2
C#:            Unhandled Exception: Npgsql.NpgsqlException: x = 0.2, y = 0.2

So now PgAdmin and Npgsql agree. But why does the choice of client influence how the server chooses to format double precision values ?

Can anyone enlighten me as to what is going on ? And is there any way of changing this highly surprising behavior through e.g. a " set session strange_double_formats 'No'; " :-) or similar right after the connection is opened ?

Kind regards
-- Jan Holst Jensen

[janholstjensen]

Add this, and the issue is fixed:

NpgsqlCommand setCommand = new NpgsqlCommand("set session extra_float_digits=0;", conn);
setCommand.ExecuteNonQuery();

Still, I was rather surprised that Npgsql (apparently ?) sets this parameter and thereby causes default server-side string formatting to change.

[roji]

This is pretty strange as a default behavior, am reopening for investigation even if not the highest priority...

[janholstjensen]

Thanks roji, much appreciated.

[franciscojunior]

Run "select format_error();" in PgAdmin, and you see the expected result:

When I run it from psql command line, I get:

npgsql_tests=# CREATE OR REPLACE FUNCTION format_error()
npgsql_tests-#   RETURNS void AS
npgsql_tests-# $BODY$
npgsql_tests$# declare
npgsql_tests$#   x double precision;
npgsql_tests$#   y numeric;
npgsql_tests$# begin
npgsql_tests$#   x := 0.2;
npgsql_tests$#   y := 0.2;
npgsql_tests$#   raise exception 'x = %, y = %', x, y;
npgsql_tests$# end;
npgsql_tests$# $BODY$ LANGUAGE plpgsql;
CREATE FUNCTION
npgsql_tests=# select format_error();
ERROR:  x = 0.20000000000000001, y = 0.2
npgsql_tests=#

I think it is not related to Npgsql specifically. I think it is more related to double precision value representation.

When I set the extra_float_digits to 0 or 1, I get the value as 0.2. When I set it to 2, I start to get the value above.

[janholstjensen]

Interesting. Not what I see:

-bash-4.1$ psql
psql (9.3.3)
Type "help" for help.

postgres=# select format_error();
ERROR:  x = 0.2, y = 0.2
postgres=# show extra_float_digits;
 extra_float_digits 
--------------------
 0
(1 row)
postgres=# 

That is a CentOS 6.5 server with PostgreSQL 9.3 from the yum.postgresql.org repo.

So psql on the server and PgAdmin on the client agree on my machines. But Npgsql behaves differently. So something is going on in Npgsql - probably an explicit setting of extra_float_digits on connect (?).

[franciscojunior]

But Npgsql behaves differently. So something is going on in Npgsql - probably an explicit setting of extra_float_digits on connect (?).

Yep. Npgsql changes the extra_float_digits to 3 when starting a connection in order to handle double precision numbers. In the past we had complaints that those values were being sent truncated in the last digits.

[roji]

It makes sense to me to set extra_float_digits to 3 like Npgsql does - it means you get maximum precision when floats/doubles are converted to text. What mystifies me is how that causes the 0.2 above to be printed out as 0.20000000000000001... If there were any floating point arithmetic involved it may have made sense, but with no operations?

I'll try to dig into this at some point...

[janholstjensen]

There is in fact an operation involved: Conversion from decimal to binary. Only a subset of decimal floating point values can be exactly represented in the binary double precision data. That's why Postgres rounds binary float values when converting to text to provide a buest guess of the original input value.

postgres=# show extra_float_digits;
 extra_float_digits 
--------------------
 0
(1 row)

postgres=# select format('%s', 0.25::double precision);
 format 
--------
 0.25
(1 row)

postgres=# select format('%s', 0.28::double precision);
 format 
--------
 0.28
(1 row)

postgres=# select format('%s', 0.3::double precision);
 format 
--------
 0.3
(1 row)

postgres=#

What you get when you set extra_float_digits higher is to get all the "information" that Postgres can get from a binary float - including the representation errors.

postgres=# set extra_float_digits = 3;
SET
postgres=# select format('%s', 0.25::double precision);
 format 
--------
 0.25
(1 row)

postgres=# select format('%s', 0.28::double precision);
        format        
----------------------
 0.280000000000000027
(1 row)

postgres=# select format('%s', 0.3::double precision);
        format        
----------------------
 0.299999999999999989
(1 row)

postgres=#

I can see why you would want to bump extra_float_digits up to avoid rounding if you transfer binary floats as text, but wire transfer of binary floats ought to be done in binary. Not to mention the localization issues you have to work around when transferring floats as text...

[roji]

Thanks for the in-depth explanation @janholstjensen, what you say seems to make total sense to me.

@glenebob (and @franciscojunior), I guess we're transfering floats in text rather than binary, right? What do you think switching to binary for that? It would allows us to stop setting the extra_float_digits to 3, leave it totally up to the user?

[franciscojunior]

I guess we're transfering floats in text rather than binary, right? What do you think switching to binary for that? It would allows us to stop setting the extra_float_digits to 3, leave it totally up to the user?

Binary encoding is only used when the user prepares the command. Otherwise it is transfered as text.

Not to mention the localization issues you have to work around when transferring floats as text...

The localization issue is handled by using invariant culture formatting when writing the float point value: https://github.com/npgsql/Npgsql/blob/master/Npgsql/NpgsqlTypes/NpgsqlTypeConvNativeToBackend.cs#L471
and
https://github.com/npgsql/Npgsql/blob/master/Npgsql/NpgsqlTypes/NpgsqlTypeConvNativeToBackend.cs#L482

[roji]

Ah, right, I forgot about that.

So as long Postgresql simple queries, results are always given in text format, which means that floats text encoding is sensitive to extra_float_digits, right?

If this is so, then unless we move to a more complex query model (either extended or simple with cursors) there's no way around this problem - except for maybe switching extra_float_digits back to its default of 0?

What do you guys think?

[Emill]

In the past we had complaints that those values were being sent truncated in the last digits.

Isn't that more of a PostgreSQL issue than an Npgsql issue? I think it's better to leave the default value as it is, to be consistent with other client libraries. If the users really want different float representations, they can manually set extra_float_digits.

[roji]

In the past we had complaints that those values were being sent truncated in the last digits.

Isn't that more of a PostgreSQL issue than an Npgsql issue? I think it's better to leave the default value as it is, to be consistent with other client libraries. If the users really want different float representations, they can manually set extra_float_digits.

I think I agree with @Emill here - forcing non-default Postgresql behavior on Npgsql users (i.e. increased precision) seems problematic, and the strange default behavior described in this issue is a good example (where instead of 0.3 users get 0.299999999999999989 by default).

However, I admit I don't know what kind of trouble users had in the first place when the extra_float_digits wasn't set. @franciscojunior, do you remember what kind of trouble caused this in the first place? How would you feel about removing the extra_float_digits setting in Npgsql for 3.0?

[franciscojunior]

Hi all!

The root of the extra_float_digits handling is this bug report about double precision numbers handling:
http://pgfoundry.org/tracker/index.php?func=detail&aid=1010992&group_id=1000140&atid=590

Then, I imported Udo Liess test case to our test suite and created the Bug1010992DoubleValueSupport test: https://github.com/npgsql/Npgsql/blob/c734ed7d9de01ff32b218139175481e5366d7e03/tests/CommandTests.cs#L1328

I agree with @Emill as well and I'd add a connection string parameter which would let the user specify how many digits she wants to use?
I'm saying that because although the user can set the value with a query, I think it wouldn't be very practical to set it every time a connection was created. I think a connection string parameter would simplify a lot this process.

[roji]

Hmm... The Bug1010992DoubleValueSupport test seems to make an assert that is too strict, if I understand floating point properly. In general, when comparing floats one always sets a "tolerance" (or "epsilon") value, within which differences are tolerated - see the section on float comparison in the NUnit docs. Among other things, this is because floating point arithmetic does not always yield the expected result (e.g. (x * 0.5) / 0.5 isn't necessarily exactly equal to x).

In other words, I think it's perfectly acceptable for Bug1010992DoubleValueSupport to fail as it's currently written.

I guess we're in agreement to stop setting extra_float_digits in Npgsql 3.0, and leave it up to the user, right? @franciscojunior, it's possible to add this as a connection string parameter but I'm not sure about the usefulness - why provide it for this parameter and not for any/all other parameters Postgresql supports? Also, connection string parameters are important for connection pooling - connections with different extra_float_digits won't be reused by the pool, although they are in essence the same...

I'm not really that against an extra_float_digits connstring param, I'm just not sure it make that much sense... Let me know what you think.

[roji]

Another motivation for removing this parameter: apparent incompatibility with the pgbouncer connection pool