5

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!

Robert Riley
  • 389
  • 1
  • 7
  • 31
  • 3
    Can you try modify the connection string Timeout and CommandTimeout to a bigger value? – Leon Yue Jan 15 '21 at 00:13
  • 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? – Robert Riley Jan 15 '21 at 14:20

2 Answers2

6

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.

robin loche
  • 276
  • 2
  • 7
4

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:

ArKan
  • 171
  • 2
  • 11
DeliciousMalware
  • 41
  • 1