Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

Postby mmakaay » 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/20141125111529.15508.34881@wrigleys.postgresql.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.
mmakaay
 
Posts: 3
Joined: Tue 17 Feb 2015 00:31

Re: Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

Postby Pinturiccio » Tue 17 Feb 2015 14:22

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.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

Postby mmakaay » Tue 17 Feb 2015 15:40

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
 
Posts: 3
Joined: Tue 17 Feb 2015 00:31

Re: Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

Postby mmakaay » Tue 17 Feb 2015 19:57

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.
mmakaay
 
Posts: 3
Joined: Tue 17 Feb 2015 00:31

Re: Postgres 9.2.10 incompatibility: truncating digits because of wrong dscale value

Postby Pinturiccio » Wed 18 Feb 2015 13:54

We are glad to hear that the issue is solved. If you have any questions, feel free to contact us.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL