Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value
Posted: Tue 17 Feb 2015 08:23
In the new PostgreSQL 9.2.10 (and also the major branches up I presume, but I did not yet check), a bug fix was introduced, which breaks some operations for Devart dotConnect. In short, the issue causes that I insert for example 21.0084 into the database, but on retrieval I get back 21.00.
The bug report that lead up to the change in PostgreSQL can be found here:
http://www.postgresql.org/message-id/20 ... gresql.org
The information from the release notes:
In numeric_recv(), truncate away any fractional digits that would be hidden according to the value's dscale field (Tom Lane)
A numeric value's display scale (dscale) should never be less than the number of nonzero fractional digits; but apparently there's at least one broken client application that transmits binary numeric values in which that's true. This leads to strange behavior since the extra digits are taken into account by arithmetic operations even though they aren't printed. The least risky fix seems to be to truncate away such "hidden" digits on receipt, so that the value is indeed what it prints as.
The "at least one broken client application" they refer to here, is the Devart connector.
I presume that for my example, my numeric is sent in binary to the database as 21.0084, accompanied by a dscale of 2 instead of 4.
Before this change in PostgreSQL, the underlying value in the database would still be correct and I could read it back correctly from my C# app. At the psql command line though, the value would show as 21.00. So the dscale was off, but it did not affect the C# side of things.
In the new version of PostgreSQL, they made sure that the presented 21.00 matches the underlying value, unfortunately breaking the C# side of things.
I hope this describes the issue well enough.
If you need more information, then please let me know.
The bug report that lead up to the change in PostgreSQL can be found here:
http://www.postgresql.org/message-id/20 ... gresql.org
The information from the release notes:
In numeric_recv(), truncate away any fractional digits that would be hidden according to the value's dscale field (Tom Lane)
A numeric value's display scale (dscale) should never be less than the number of nonzero fractional digits; but apparently there's at least one broken client application that transmits binary numeric values in which that's true. This leads to strange behavior since the extra digits are taken into account by arithmetic operations even though they aren't printed. The least risky fix seems to be to truncate away such "hidden" digits on receipt, so that the value is indeed what it prints as.
The "at least one broken client application" they refer to here, is the Devart connector.
I presume that for my example, my numeric is sent in binary to the database as 21.0084, accompanied by a dscale of 2 instead of 4.
Before this change in PostgreSQL, the underlying value in the database would still be correct and I could read it back correctly from my C# app. At the psql command line though, the value would show as 21.00. So the dscale was off, but it did not affect the C# side of things.
In the new version of PostgreSQL, they made sure that the presented 21.00 matches the underlying value, unfortunately breaking the C# side of things.
I hope this describes the issue well enough.
If you need more information, then please let me know.