Azure Data Factory: Disable prepared statements

cesar nieto 0 Reputation points
2023-06-03T23:11:29.3+00:00

Hello

I'm using Data Factory to copy data from Cosmos DB to a PostgreSQL server with internal pgbouncer enabled. I have a Copy Activity inside a Loop Activity to perform this operation. The connection is established using PostgreSQL as the underlying linked service. However, I'm encountering an error when trying to complete my workloads:

"Failure happened on the 'Source' side. ErrorCode=UserErrorOdbcOperationFailed, Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=ERROR [HY000] [Microsoft][ODBC PostgreSQL Wire Protocol driver][PostgreSQL] ERROR: VERROR; prepared statement 'ST2C8F013BFD0' does not exist (File prepare.c; Line 493; Routine FetchPreparedStatement), Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors, Type=System.Data.Odbc.OdbcException, Message=ERROR [HY000] [Microsoft][ODBC PostgreSQL Wire Protocol driver][PostgreSQL] ERROR: VERROR; prepared statement 'ST2C8F013BFD0' does not exist (File prepare.c; Line 493; Routine FetchPreparedStatement), Source=mspsql27.dll."

As far as I understand, not only is the linked service unable to use the prepareThreshold parameter to disable this feature because it is using ODBC, but I also cannot change the embedded pgbouncer mode to achieve what I want.

What are my alternatives so far?

Thanks!

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,539 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,177 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Vahid Ghafarpour 20,500 Reputation points
    2023-06-04T00:08:35.4433333+00:00

    You can consider the following alternatives:

    1. Disable pgbouncer temporarily: If possible, you can disable pgbouncer temporarily during the data transfer process. This will allow ADF to connect directly with your PostgreSQL server without going through pgbouncer. However, this approach may impact the performance and resource utilization of your PostgreSQL server, so use it cautiously.
    2. Use a custom solution: Instead of relying on ADF's built-in Copy Activity, you can develop a custom solution using Azure Functions or Azure Logic Apps. You can write code to connect to Cosmos DB and PostgreSQL directly without going through the ODBC driver. This way, you have more control over the connection and can handle prepared statements as per your requirements.
    3. Modify your data transfer process: If the above alternatives are not feasible, you may need to modify your data transfer process to avoid using prepared statements. This may involve changing how you read data from Cosmos DB or changing the destination table structure in PostgreSQL. For example, you can consider using a staging table where you insert data from Cosmos DB, and then use SQL statements to manipulate and insert the data into the final destination table in PostgreSQL.
    1 person found this answer helpful.
    0 comments No comments