Search code examples
postgresqlazure-data-factoryazure-postgresql

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!

Solution

  • 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.