Issue
You want to use Heroku External Objects with Salesforce External Data Sources and occasionally get an error related to the number of digits returned for a double floating point number. It is considered an invalid value by Salesforce and the number of digits do not match the IEEE 754 double floating point specification. You may see an error similar to External Object Error Could not deserialize feed... on Salesforce.
Resolution
Background
As of Postgres 12, extra_float_digits settings default to 1 instead of 0, which adjusts the number of digits used for the textual output of floating-point values, such as float and double. In OData spec, doubles are IEEE 754 binary64 floating-point numbers represented with 15-17 decimal digits.
- When set to 1: Output uses the shortest-precise format. This setting can sometimes result in a precision of 18 digits, which is not compliant with the OData spec.
- When set to 0: Rounds the output to the standard number of digits for the given type. For double, it is a precision of 15-17 digits and compliant with the OData spec.
Here is an example what the results look like with this setting:
postgres=# SHOW extra_float_digits;
extra_float_digits
--------------------
1
(1 row)
postgres=# select 0.008999999999999947::double precision;
float8
----------------------
0.008999999999999947
(1 row)
postgres=# SET extra_float_digits=0;
SET
postgres=# select 0.008999999999999947::double precision;
float8
---------------------
0.00899999999999995
(1 row)
More info: https://www.postgresql.org/docs/12/runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS
Resolution
For Salesforce to parse a value for a number like in the example, the number of digits must match the IEEE 754 spec. You must set the extra_float_digits flag to 0 when querying Postgres. We do not enable this flag by default for all customers because some customers may have existing integrations built that expect the extra number of digits.
If you are experiencing this issue and want your connections updated to match the IEEE 754 spec, raise a ticket for the Connect team to enable the feature. In the ticket, include the connection name where you want it changed.