Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

[mmakaay]

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.

[Pinturiccio]

We could not reproduce the issue with PostgreSQL server 9.2.10. Please provide us the following for reproducing the issue:
1. Please create and send us a small test project;
2. DDL script of your table;
3. Describe how you insert data to your table. If possible, please make this sample in such a way that it will insert the data necessary for reproducing the issue, and then reproduce the issue with reading this data.
4. Describe the steps for reproducing the issue.

[mmakaay]

Maybe it is something that was fixed in the PostgreSQL connector already then? We are running 7.2.100. I will check if I can reproduce the problem using a recent version of the connector.

[mmakaay]

Indeed, it seems the issue has been fixed in the meanwhile, so my apologies for reporting a bug for an old version of the connector. Looking at the activity in the PostgreSQL bug ticket, I presumed the bug was still there, and I clearly had been searching for the wrong terms when going over the release notes.

Going back to the revision history with the knowledge I have now, I end up with this possible fix for the issue: 7.3.293 20-Nov-14: The bug with precision loss when working with PgSqlType.Numeric via the protocol 3 is fixed.

I'll see if I can add a pointer to the PostgreSQL bug thread, pointing back here for people that run into the same kind surprises as I did.

Thanks.

[Pinturiccio]

We are glad to hear that the issue is solved. If you have any questions, feel free to contact us.