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

1 answer

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?


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.