ADF - postgres: Read method cannot be called when another read operation is pending

Question

ADF - postgres: Read method cannot be called when another read operation is pending

Mart Roben 0

I'm using Azure Data Factory copy data activity to move data from Azure PostgreSQL to OneLake. I'm using a self-hosted integration runtime for that (because of how our VNETs are set up).

Here is the error I'm getting:

Operation on target activity_copy_data_to_onelake failed: 'Type=Npgsql.NpgsqlException,Message=Exception while reading from stream,Source=Npgsql,''Type=System.NotSupportedException,Message=The Read method cannot be called when another read operation is pending.,Source=System,'

The same setup works perfectly for all the other tables in the same database, but fails for one. It might be because the problematic table is bigger than others (50 columns x 6M rows).

Here's what I have tried with no success in solving the problem:

Setting connection timeout to 600 and command timeout to 0 (no timeout) in the linked service connection string.
Setting pooling to "no" in the linked service connection string.
Running the same query by pgAdmin. It succeeded, but took 18 minutes. In ADF it fails after ~200 seconds. The query itself is a simple SELECT * FROM <table>

Does anyone have any ideas how to diagnose or solve this problem?

Ganesh Gurram 7,235 Reputation points Moderator

2024-11-13T18:13:28.35+00:00

@Mart Roben - Just checking in to see if the below answer provided by @Amira Bedhiafi helped.

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.
Mart Roben 0 Reputation points

2026-03-10T12:17:49.85+00:00
Microsoft support was finally able to find a hacky workaround.

Let me leave some bread crumbs to whomever might come across the same problem.

The setting that triggered the error was the "additional columns" functionality in the ADF copy data activity. The problematic tables load fine when we remove the additional columns.

Since not using the additional columns was not an option for us (we need to add a load timestamp to all tables), the workaround is to add the following hack to the copy data activity Query input.

@replace( pipeline().parameters.load_object_query, 'SELECT ', concat( 'SELECT ''', formatDateTime(pipeline().TriggerTime,'yyyy-MM-ddTHH:mm:sszzz'), '''::text AS "', pipeline().parameters.load_timestamp_field, '", ' ) )

Here load_object_query is the SQL query parameter (SELECT * FROM ...) and load_timestamp_field is the name of the field that is to be added. This works for postgres sources.

The underlying cause of the problem was never discovered. The error message is totally unrelated. It might be because of some hard-coded timeout limits in the Npgsql library in ADF backend that are not exposed to users. The problem seems to occur with either very large tables or views that are based on a lot of geometric transformations.

It took 1 year and 4 months to find this workaround. I spent hundreds of hours hauling logs and screenshots to Microsoft support over and over again to keep the case alive. They kept suggesting the same tried and failed solutions, refusing to escalate. The level of indifference and incompetence I experienced was mind-numbing (and we have the paid support tier).

I hope no-one else will have have to go through same experience.

2 answers

Your answer

Ganesh Gurram 7,235 Reputation points Moderator

2024-11-13T18:13:28.35+00:00

@Mart Roben - Just checking in to see if the below answer provided by @Amira Bedhiafi helped.

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.
Mart Roben 0 Reputation points

2026-03-10T12:17:49.85+00:00

Microsoft support was finally able to find a hacky workaround.

Let me leave some bread crumbs to whomever might come across the same problem.

The setting that triggered the error was the "additional columns" functionality in the ADF copy data activity. The problematic tables load fine when we remove the additional columns.

Since not using the additional columns was not an option for us (we need to add a load timestamp to all tables), the workaround is to add the following hack to the copy data activity Query input.

@replace( pipeline().parameters.load_object_query, 'SELECT ', concat( 'SELECT ''', formatDateTime(pipeline().TriggerTime,'yyyy-MM-ddTHH:mm:sszzz'), '''::text AS "', pipeline().parameters.load_timestamp_field, '", ' ) )

Here load_object_query is the SQL query parameter (SELECT * FROM ...) and load_timestamp_field is the name of the field that is to be added. This works for postgres sources.

The underlying cause of the problem was never discovered. The error message is totally unrelated. It might be because of some hard-coded timeout limits in the Npgsql library in ADF backend that are not exposed to users. The problem seems to occur with either very large tables or views that are based on a lot of geometric transformations.

It took 1 year and 4 months to find this workaround. I spent hundreds of hours hauling logs and screenshots to Microsoft support over and over again to keep the case alive. They kept suggesting the same tried and failed solutions, refusing to escalate. The level of indifference and incompetence I experienced was mind-numbing (and we have the paid support tier).

I hope no-one else will have have to go through same experience.

Answer 1

@Mart Roben - Thanks for the question and using MS Q&A forum.

The error you are encountering, The Read method cannot be called when another read operation is pending, typically indicates a concurrency issue where multiple read operations are being attempted simultaneously on the same connection.

Here are some steps you can take to diagnose and potentially solve this problem:

Review Connection Pooling Settings: Ensure that connection pooling settings are correctly configured. Setting pooling to "no" might not be ideal for handling large datasets. Instead, try adjusting the pool size settings.
Increase Command Timeout: You've already set the command timeout to 0 (no timeout), which is a good step. However, ensure that the connection timeout is also sufficiently large to accommodate the data volume.
Optimize Query: Consider optimizing your query if possible. Instead of SELECT *, specify only the necessary columns. Use pagination or batch processing to handle large datasets. This can be done by modifying your query to fetch data in smaller chunks.
Check Self-Hosted Integration Runtime Configuration: Ensure that your self-hosted integration runtime has sufficient resources (CPU, memory) to handle large data transfers. Check for any network latency issues between your self-hosted integration runtime and the Azure PostgreSQL database.
Monitor Database Performance: Use PostgreSQL performance monitoring tools to check for any bottlenecks or issues on the database side. Ensure that the database is not under heavy load and there are no locks or long-running transactions that could affect performance.
Retry Logic: Implement retry logic in your ADF pipeline to handle transient issues. This can be done by setting the retry policy in the Copy Data activity.
Detailed Logs and Diagnostics: Enable detailed logging in ADF to capture more information about the failure. Check PostgreSQL logs for any related errors or warnings during the data transfer.

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Amira Bedhiafi 41,131 Volunteer Moderator

For large tables, it’s helpful to partition or batch data. In the ADF Copy Data activity, try enabling "Data partitioning" and specify partition columns if you have any integer or date columns that can divide the data into manageable chunks.

Try limiting the maximum concurrent connections in the self-hosted integration runtime configuration, which could reduce competition for resources during the copy operation.

Consider upgrading the VM or adding more nodes to your self-hosted integration runtime cluster. With more resources, the runtime may handle the large dataset more effectively.

If partitioning isn't feasible, consider using a paginated query with offsets and limits in the "Source Query" of the Copy Data activity to retrieve data in chunks manually.

Check if the CPU and memory usage are spiking during the transfer. If so, consider increasing the compute size or using additional nodes.

Ganesh Gurram 7,235 Reputation points Moderator

2024-11-14T15:46:45.01+00:00

@Mart Roben - Following up to see if the above answer provided by @Amira Bedhiafi helped.

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.
Mart Roben 0 Reputation points

2024-11-15T13:20:43.7933333+00:00

@Amira Bedhiafi , @Ganesh Gurram thank you for the suggestions!

Unfortunately these suggestions didn't help me to fix the problem (see details below). What else could I do to diagnose this bug?

try enabling "Data partitioning" and specify partition columns

The data partitioning functionality doesn't seem to be available for my PostgresSQL data source.

Try limiting the maximum concurrent connections

I set the maximum concurrent connections from 6 to 3. The same error still persisted.

Consider upgrading the VM or adding more nodes to your self-hosted integration runtime

I added a second note to the integration runtime. The same error still persisted.

consider using a paginated query

I would really like to understand the reason why I'm getting the current error and try to fix it, before adding workarounds and complexity to the system. Out of 300+ tables that we're regularly loading with our current setup, only one keeps failing.

Check if the CPU and memory usage are spiking during the transfer

The error occurs even if I run only a single query. Integration Runtime should be able to handle a single table.

Share via

ADF - postgres: Read method cannot be called when another read operation is pending

2 answers

Your answer