Hello everyone
I'm using the free release of DotConnect drivers for PostgreSQL, version 7.22
The PostgreSql server version 9.6.10
Executing a long query, after almost 40 minutes the execution returns an exception.
Hereunder the full stack trace, there are 3 nested Exceptions: the first has got the error message "Server did not respond within the specified timeout interval".
The PgSqlCommand has been set with a timeout of 1500 seconds but it seems that this setting is ignored.
When the exception is raised, the return value of ExecuteNonQuery methods matches the total amount of data to be processed.
After this event, the database connection is closed.
This behaviour occurs sistematically: no issues with the previous version of dotConnect driver (7.5.658).
Any suggestion is really appreciated,
regards.
Giuseppe
Devart.Data.PostgreSql.PgSqlException
HResult=0x80004005
Message=Server did not respond within the specified timeout interval.
Source=Devart.Data.PostgreSql
StackTrace:
at Devart.Data.PostgreSql.q.d(Exception A_0)
at Devart.Common.a7.d(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Common.am.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Data.PostgreSql.aa.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
at Devart.Data.PostgreSql.t.g(r A_0)
at Devart.Data.PostgreSql.t.b(r A_0)
at Devart.Data.PostgreSql.r.ai()
at Devart.Data.PostgreSql.PgSqlDataReader.f(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at ClaRoot.Data.DataProvider.ExecuteNonQuery(DbConnection connection, DbProviderFactory dbProviderFactory, DbTransaction transaction, String strQuery) in C:\develop\git\wbgit\dblibrary\dbcommonlibrary\Data\DataProvider.vb:line 2020
Inner Exception 1:
TimeoutException: Server did not respond within the specified timeout interval.
Inner Exception 2:
IOException: 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.
Stack Trace:
at Devart.Common.a8.a(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Common.o.c(Byte[] A_0, Int32 A_1, Int32 A_2)
at Devart.Common.a7.d(Byte[] A_0, Int32 A_1, Int32 A_2)
Inner Exception 3:
SocketException: 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
Stack Trace:
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
Server did not respond within the specified timeout interval
Re: Server did not respond within the specified timeout interval
After further tests I have discovered that the CommandTimeout is not ignored, but the time set is different from the amount of time elapsed by a non responsive query.
e.g Timeout set to 1500 seconds (25 minutes) but the exception is raised after 40 minutes.
The worst thing is that after this exception the connection is closed.
From the documentation I've read that the PgSqlConnection "is not guaranteed to be thread-safe".
The query is executed in a BackgroundWorker while the connection has been already created and opened in the Main Thread.
Might be for this reason that the connection is closed when this exception is raised?
e.g Timeout set to 1500 seconds (25 minutes) but the exception is raised after 40 minutes.
The worst thing is that after this exception the connection is closed.
From the documentation I've read that the PgSqlConnection "is not guaranteed to be thread-safe".
The query is executed in a BackgroundWorker while the connection has been already created and opened in the Main Thread.
Might be for this reason that the connection is closed when this exception is raised?
Re: Server did not respond within the specified timeout interval
Please show us the example of your connection string (without password). It would be better to show the whole test project, especially the code to create the command.
If your query runs for 40 minutes, why are you setting CommandTimeout = 1500 (25 min)?
Note that our PostgreSQL components are not thread-safe, so you shouldn't use a connection in the different parts of code at the same time. You should create a different connection for each thread.
Note also that PostgreSQL Server has its own timeouts parameters such as: statement_timeout, idle_in_transaction_session_timeout
https://www.postgresql.org/docs/9.6/run ... lient.html
It is possible that the connection is interrupted by the server itself, in which case we will not be able to fix it on the connector side.
If your query runs for 40 minutes, why are you setting CommandTimeout = 1500 (25 min)?
Note that our PostgreSQL components are not thread-safe, so you shouldn't use a connection in the different parts of code at the same time. You should create a different connection for each thread.
Note also that PostgreSQL Server has its own timeouts parameters such as: statement_timeout, idle_in_transaction_session_timeout
https://www.postgresql.org/docs/9.6/run ... lient.html
It is possible that the connection is interrupted by the server itself, in which case we will not be able to fix it on the connector side.