Self-hosted IR 5.66.9620.1 multiplies PostgreSQL numeric values by 10,000 (PostgreSQL 17.8 source)

Jun Jie Lin 0 Reputation points
2026-11-18T13:29:52.4566667+00:00

We copy data from a PostgreSQL database to a Synapse dedicated SQL pool using the PostgreSQL V2 connector (linked service type PostgreSqlV2) through a self-hosted integration runtime.

Against our new source server (PostgreSQL 17.8 on x86_64-windows, compiled by msvc-19.44.35222), all numeric values with decimals are returned multiplied by 10,000. Integer values are returned correctly. The columns are numeric without precision/scale.

The issue already occurs in "Preview data" with a query, so it is not caused by the copy activity mapping:

SELECT "bedrag", "bedrag"::text AS bedrag_tekst, scale("bedrag") AS schaal
FROM public."fJournaalpost" LIMIT 10;
bedrag (as returned) bedrag::text scale
-7721600.0 -772.16 2
14029800.0 1402.98 2
-4380900.0 -438.09 2

The text cast shows that the data in the database is correct. The value appears to be decoded incorrectly by the integration runtime: the fractional base-10000 digit group is read as an integer (772.16 → 772·10⁴ + 1600).

Comparison:

  • Workspace syn-reym-d1, SHIR 5.66.9620.1, PostgreSQL 17.8 → values ×10,000 (wrong)
  • Workspace syn-reym-p1, SHIR 5.66.9620.1, PostgreSQL 14.8 → values correct (e.g. -33.2500)

This looks like the issue reported earlier for SHIR 5.55.9315.1 and 5.64.9558.1:

Impact: our ERP vendor moves our production source to the PostgreSQL 17.8 environment on 25 November 2026. From then on, all financial amounts in our data warehouse would be wrong by a factor of 10,000.

Questions:

  1. Is this a known issue in SHIR 5.66.9620.1 with PostgreSQL 17, and is a fix planned?
  2. Which SHIR version is confirmed to work correctly, and where can we download it?

SHIR diagnostic logs uploaded with Report ID: 1db9a2bb-dfa5-4b2b-87cf-2f7e3d97906a

Azure Synapse Analytics
Azure Synapse Analytics

An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.

0 comments No comments

2 answers

Sort by: Oldest
  1. Allan Solomon Mejia 11,050 Reputation points
    2026-11-18T20:18:17.2133333+00:00

    Hi @Jun Jie Lin

    Your test strongly indicates that the corruption occurs in the PostgreSQL V2 connector’s numeric-decoding path, before Copy Activity mapping or the Synapse sink:

    • numeric returns the incorrect scaled value.
    • The same value cast to text returns correctly.
    • scale() confirms the source value’s actual scale.
    • The problem is already visible in Preview data.
    • PostgreSQL 14.8 works through the same SHIR build, while PostgreSQL 17.8 does not.

    The PostgreSQL V2 connector officially supports PostgreSQL 12 and later and maps PostgreSQL numeric to the service’s interim Decimal type. PostgreSQL 17.8 is therefore within the connector’s documented version range.

    However, I cannot find a published Microsoft advisory or SHIR release note that confirms this exact PostgreSQL 17 numeric-decoding defect in version 5.66.9620.1, identifies its engineering status, or gives a planned fix date. The earlier Q&A reports show similar symptoms, but they aren’t equivalent to an official product advisory.

    I also cannot find verified official documentation confirming that a specific SHIR build correctly handles unbounded PostgreSQL numeric values from PostgreSQL 17.8. Microsoft’s Download Center currently offers several builds, including 5.69.9720.1, 5.68.9739.1, 5.66.9620.1, 5.65.9600.3, and 5.62.9520.4, but availability doesn’t prove that any particular build contains this fix. Microsoft generally recommends running the latest SHIR release.

    Given the November 25 production deadline, I recommend these actions:

    1. Escalate the existing diagnostic report Open a Microsoft support request and reference Report ID: 1db9a2bb-dfa5-4b2b-87cf-2f7e3d97906a Include the SHIR version, complete PostgreSQL build string, linked-service JSON with secrets removed, test query, expected and returned values, and equivalent results from PostgreSQL 14.8. Microsoft documents retaining the Report ID so Support can retrieve the uploaded SHIR logs.
    2. Test the latest Microsoft-hosted SHIR build in an isolated IR Register a separate test SHIR rather than upgrading the production node first. Run the exact query against PostgreSQL 17.8 and verify positive, negative, zero, high-precision, and different-scale values. Don’t call the version safe until those tests pass.
    3. Use a text-based containment path if the latest build still fails The PostgreSQL V2 source supports a custom SQL query. Temporarily cast affected values to text:
    SELECT
           "bedrag"::text AS bedrag_text,
           scale("bedrag") AS bedrag_scale
       FROM public."fJournaalpost";
    

    Load bedrag_text into a staging varchar column, validate it, and then convert it explicitly into the required Synapse decimal type:

       TRY_CONVERT(decimal(38, 4), bedrag_text)
    

    Select precision and scale based on the financial system’s actual requirements rather than assuming (38,4). TRY_CONVERT is supported by Azure Synapse Analytics and returns NULL when a permitted conversion fails, allowing invalid rows to be quarantined instead of silently loading incorrect amounts.

    1. Add reconciliation before the production migration Compare row counts, sums, minimums, maximums, and sampled values between PostgreSQL and Synapse. Block publication of the warehouse load if any converted value is NULL or aggregate totals differ.

    I don't recommend dividing the returned values by 10,000. That assumes every affected numeric representation fails identically and could corrupt integers or values with different scales. Casting to text bypasses the observed failing numeric-decoding path while preserving the value PostgreSQL itself returns.

    References:


    Help make this community better for everyone: If this answer helped or resolved your issue, please accept it or upvote it. If not, share more details in a comment so we can continue the discussion and find the right solution. Thank you.

    Was this answer helpful?


  2. Vinodh247-1375 44,281 Reputation points Volunteer Moderator
    2026-11-21T00:42:12.8733333+00:00

    The evidence points to the PostgreSQL V2 connector or SHIR numeric decoding path rather than the Copy activity mapping. The incorrect value is already present in Preview data, while bedrag::text returns the correct value from PostgreSQL.

    PostgreSQL 17 is within the connector’s documented support range of PostgreSQL 12 and later. However, the documentation does not identify this decimal-scaling behaviour as a known issue, publish a fix schedule, or list a SHIR version confirmed to work with PostgreSQL 17.8. Therefore, I would not recommend a specific downgrade based only on the earlier SHIR 5.55 and 5.64 reports. Those reports demonstrate similar behaviour, but they do not validate a particular version against PostgreSQL 17.8. PostgreSQL V2 connector documentation [learn.microsoft.com], [learn.microsoft.com], [learn.microsoft.com]

    For the production migration, a safer temporary mitigation is to bypass numeric decoding for the affected columns:

    SQL

    SELECT "bedrag"::text AS "bedrag_text", scale("bedrag") AS "bedrag_scale" FROM public."fJournaalpost";

    Land bedrag_text in a staging varchar column and convert it in Synapse using an explicit target definition, for example:

    SQL

    TRY_CONVERT(decimal(38, 4), bedrag_text)

    Choose the destination scale based on the business definition rather than assuming scale 4 for every column. Since the PostgreSQL columns are declared as unconstrained numeric, retaining the text value in staging also prevents unintended loss of precision or scale.

    Before the cutover, validate this workaround with boundary cases such as:

    • Positive and negative decimals
    • Zero and NULL
    • Values with different scales
    • Large financial amounts
    • Values such as 0.0001, 123.45 and -772.16

    A source-to-staging reconciliation using the text representation and row-level counts would provide an additional control against financial corruption.

    For the 2 ques:

    1. I briefly wandered through thte web and I can say that cannot find a public Microsoft confirmation for SHIR 5.66.9620.1 with PostgreSQL 17.8 or a published fix schedule.
    2. No publicly documented SHIR version is confirmed for this exact PostgreSQL 17.8 scenario. Test any candidate version on a separate SHIR node with automatic updates disabled before using it for production. Microsoft documents that SHIR versions are released regularly, can be managed manually, and expire after one year. SHIR update and expiration documentation [learn.microsoft.com]

    Help make this community better for everyone: if this answer resolved your issue, please accept it or leave an upvote. If not, share more details in a comment so we can continue the discussion and find the right solution

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.