Resolving SSIS PostgreSQL ODBC Hang on Long‑Running Queries

by

SSIS PostgreSQL ODBC Connection Hang: A Technical Postmortem

Summary

A production SSIS package executing long-running queries against PostgreSQL experiences a hang condition after the query completes successfully. The package remains in execution mode until the timeout expires, at which point it throws a connection-related error. This issue consistently manifests with queries exceeding 10 minutes while shorter queries complete normally.

Key Takeaway: The problem stems from a mismatch between the PostgreSQL ODBC driver configuration and SSIS’s connection handling for long-running, idle connections.

Root Cause

The hang occurs due to a combination of two critical factors:

  • Server-side prepared statements are enabled in the ODBC connection string (useserversideprepare=1)
  • The ADO.NET ODBC provider does not properly release the connection state after query completion when using server-side prepared statements with long-running queries

When PostgreSQL executes a query with server-side preparation, it maintains a prepared statement plan in memory. The ODBC driver expects the client to explicitly close or release this statement handle. However, the SSIS Execute SQL Task, after retrieving results, does not properly signal completion to the driver, leaving the connection in a pending state.

The server has already finished processing (confirmed by monitoring), but the client-side driver is waiting for a response that will never come because the statement handle is not being properly cleaned up.

Why This Happens in Real Systems

  • Protocol mismatch: PostgreSQL ODBC driver version compatibility with the .NET ODBC provider
  • Idle connection handling: Long-running queries leave the connection idle while waiting for result fetching to complete
  • Driver quirks: The useserversideprepare=1 setting is aggressive and not always handled correctly by higher-level providers
  • Timeout defaults: Default command timeout in SSIS may not align with long-running query expectations

Real-World Impact

  • Failed ETL jobs: Scheduled data loads fail due to timeout errors
  • Resource exhaustion: Connections remain open, potentially exhausting the PostgreSQL connection pool
  • Operational overhead: Manual intervention required to restart failed packages
  • Data freshness issues: End-of-day batch processing delays affect reporting downstream

Example or Code (if necessary and relevant)

The problematic connection string contains the offending parameter:

useserversideprepare=1

Fixed connection string – disable server-side prepare:

Driver={PostgreSQL Unicode(x64)};server=;uid=;database=;port=;sslmode=disable;readonly=0;protocol=7.4;fakeoidindex=0;showoidcolumn=0;rowversioning=0;showsystemtables=0;fetch=100;unknownsizes=0;maxvarcharsize=255;maxlongvarcharsize=8190;debug=0;commlog=0;usedeclarefetch=0;textaslongvarchar=1;unknownsaslongvarchar=0;boolsaschar=1;parse=0;lfconversion=1;updatablecursors=1;trueisminus1=0;bi=0;byteaaslongvarbinary=1;useserversideprepare=0;lowercaseidentifier=0;d6=-101;optionalerrors=0;fetchrefcursors=0;xaopt=1;ab=40

Alternative: Use ADO.NET PostgreSQL Provider directly instead of ODBC:

Server=;Port=;Database=;User Id=;Password=;Timeout=30;CommandTimeout=900;

How Senior Engineers Fix It

  • Disable server-side prepare: Set useserversideprepare=0 in the ODBC connection string
  • Switch providers: Use the native Npgsql ADO.NET provider instead of ODBC for PostgreSQL connections in SSIS
  • Adjust timeouts: Increase command timeout to accommodate long-running queries
  • Configure connection pooling: Enable connection pooling with appropriate settings to handle connection reuse
  • Use ODBC alternative driver: Consider the psqlODBC driver with updated configuration or the Devart PostgreSQL ODBC Driver which handles this scenario better

Why Juniors Miss It

  • Symptom confusion: The query completes on the server, leading engineers to investigate database-side issues rather than client driver behavior
  • Short query success: Since short queries work, the connection string appears valid
  • Timeout focus: Engineers often focus on increasing timeout values rather than addressing the underlying protocol handling issue
  • Provider assumption: Assuming the ODBC driver handles all connection states correctly without understanding driver-specific quirks
  • Documentation gap: This specific issue is not well-documented in Microsoft or PostgreSQL documentation, requiring tribal knowledge or deep driver understanding

Leave a Comment