ADF-Postgres Timeout

Question

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!

Partizan

Can you try modify the connection string Timeout and CommandTimeout to a bigger value? – Lapstrake

I have also tried setting Timeout=600 and CommandTimeout = 0 (which is infinite) and the issue persists. Is it possible that the cancellation is coming from postgres to npgsql instead? Kind of like an override? – Partizan

Answer 1

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.

Zebu

Thank you, this was really helpful. Also it is not mentioned anywhere in the documentation. – Rogatory

Answer 2

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:

Endarch