Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ADF-Postgres Timeout

I'm working with ADF and Azure Managed Postgres. I've had a reoccurring issue with look-ups and query-sourced copy activities timing out after about 35 seconds.

Failure happened on 'Source' side. 'Type=Npgsql.NpgsqlException,Message=Exception while reading from stream,Source=Npgsql,''Type=System.IO.IOException,Message=Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.,Source=System,''Type=System.Net.Sockets.SocketException,Message=A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond,Source=System,' 

So the error says it's a Npgsql Exception, so I took a look at their documentation and modified the connection string to take Timeout = 60 and CommandTimeout = 60 as well (Internal Timeout will default to CommandTimeout).

And the queries still timeout at ~35 seconds. Could this be a socket issue with the Azure Managed Instance causing the timeout and it's just propagating down to npgsql?

Any help would be appreciated!

like image 442
Robert Riley Avatar asked Jul 18 '26 19:07

Robert Riley


2 Answers

I just want to add some precision because I had the same problem (and thanks @DeliciousMalware and @Leon_Yue):

  • There is a default timeout of 30s for requests with a postgres connection
  • There is no way to change this timeout from the lookup activity directly.
  • The only option that does something is to add Timeout=600;CommandTimeout=0; to your connection string in your linked service (if you use a key vault for exemple) or add the options in the linked service additionnal parameters like in @DeliciousMalware screenshot.
  • Timeout is to establish the connection, and CommandTimeout is the timeout for the command itself (in second, 0 means infinity)
  • The library behind the connection is npgsql, and the others parameters and details that are usable are there: https://www.npgsql.org/doc/connection-string-parameters.html

I had a hard time to find what the parameters of the connection string are and what they mean, and which one exists, so I was really happy to find this doc. I didn't found a lot of doc on postgres in azure, so I though this list of param would be of some use for others.

like image 161
robin loche Avatar answered Jul 21 '26 22:07

robin loche


I added the 2 parameters suggested by Leon and that resolved the issue I had.

Here is a screenshot of the parameters being added to the linked service:

Here is a screenshot of the error and completed run:

Here is a screenshot of the error and completed run:

like image 40
DeliciousMalware Avatar answered Jul 21 '26 23:07

DeliciousMalware



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!