PostgreSQL query (EF + Devart) fails in application, but works in PgAdmin

0
votes

I have a problem with one of my SQL (PostgreSQL) queries generated by Entity Framework with Devart connector. After exactly 60 seconds (every time), I get an error:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Devart.Data.PostgreSql.PgSqlException: Read failure ---> System.IO.IOException: Read failure ---> System.Net.Sockets.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 at System.Net.Sockets.Socket.Receive (System.Byte[] buffer, Int32 offset, Int32 size, SocketFlags flags) [0x00000] in :0 at System.Net.Sockets.NetworkStream.Read (System.Byte[] buffer, Int32 offset, Int32 size) [0x00000] in :0 --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read (System.Byte[] buffer, Int32 offset, Int32 size) [0x00000] in :0 at Devart.Common.au.a (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 --- End of inner exception stack trace --- at Devart.Data.PostgreSql.aa.d (System.Exception A_0) [0x00000] in :0 at Devart.Common.z.d (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 at Devart.Common.ae.a (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 at Devart.Data.PostgreSql.x.c (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 at Devart.Data.PostgreSql.x.f () [0x00000] in :0 at Devart.Data.PostgreSql.ai.a (Boolean A_0, Boolean A_1, Char A_2, Boolean A_3) [0x00000] in :0 at Devart.Data.PostgreSql.ai.b (Boolean A_0, Boolean A_1) [0x00000] in :0 at Devart.Data.PostgreSql.ai.c (Boolean A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ae.b (Boolean A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ae.g (Devart.Data.PostgreSql.ac A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ae.b (Devart.Data.PostgreSql.ac A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ac.af () [0x00000] in :0 at Devart.Data.PostgreSql.PgSqlDataReader.f (Int32 A_0) [0x00000] in :0 --- End of inner exception stack trace --- at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands (System.Data.Entity.Core.EntityClient.EntityCommand entityCommand, CommandBehavior behavior) [0x00000] in :0 at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[LisExaminationRequest] (System.Data.Entity.Core.Objects.ObjectContext context, System.Data.Entity.Core.Objects.ObjectParameterCollection parameterValues) [0x00000] in :0 [ERROR] FATAL UNHANDLED EXCEPTION: System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Devart.Data.PostgreSql.PgSqlException: Read failure ---> System.IO.IOException: Read failure ---> System.Net.Sockets.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 at System.Net.Sockets.Socket.Receive (System.Byte[] buffer, Int32 offset, Int32 size, SocketFlags flags) [0x00000] in :0 at System.Net.Sockets.NetworkStream.Read (System.Byte[] buffer, Int32 offset, Int32 size) [0x00000] in :0 --- End of inner exception stack trace --- at System.Net.Sockets.NetworkStream.Read (System.Byte[] buffer, Int32 offset, Int32 size) [0x00000] in :0 at Devart.Common.au.a (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 --- End of inner exception stack trace --- at Devart.Data.PostgreSql.aa.d (System.Exception A_0) [0x00000] in :0 at Devart.Common.z.d (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 at Devart.Common.ae.a (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 at Devart.Data.PostgreSql.x.c (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000] in :0 at Devart.Data.PostgreSql.x.f () [0x00000] in :0 at Devart.Data.PostgreSql.ai.a (Boolean A_0, Boolean A_1, Char A_2, Boolean A_3) [0x00000] in :0 at Devart.Data.PostgreSql.ai.b (Boolean A_0, Boolean A_1) [0x00000] in :0 at Devart.Data.PostgreSql.ai.c (Boolean A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ae.b (Boolean A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ae.g (Devart.Data.PostgreSql.ac A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ae.b (Devart.Data.PostgreSql.ac A_0) [0x00000] in :0 at Devart.Data.PostgreSql.ac.af () [0x00000] in :0 at Devart.Data.PostgreSql.PgSqlDataReader.f (Int32 A_0) [0x00000] in :0 --- End of inner exception stack trace --- at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands (System.Data.Entity.Core.EntityClient.EntityCommand entityCommand, CommandBehavior behavior) [0x00000] in :0 at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[LisExaminationRequest] (System.Data.Entity.Core.Objects.ObjectContext context, System.Data.Entity.Core.Objects.ObjectParameterCollection parameterValues) [0x00000] in :0

I tried to experiment with timeouts in connection string, but it didn't help. This is the content of DbContext.DataBase.Connection.ConnectionString

User Id=myuser;Host=myhost;Port=5432;Database=mydatabase;Unicode=True;Connection Timeout=300;Default Command Timeout=300;SSLMode=Disable;

This query (generated by EF) is very large. It has over 6500 lines of code (the query not the result) and consists of numerous UNION statements.

The same query ran manually in PgAdmin executed in about 120s.

1
c#entity-frameworkpostgresqldevart
The connection is not set up properly. Do check if you can connect from that server using those credentials. - Jakub Kania
@JakubKania Connection is set up ok. Other queries work fine. - Andrzej Gis
In that case try manually timeouting the connection like SELECT pg_sleep(120) and see it the error is the same. - Jakub Kania
@JakubKania pg_sleep called using DbContext.Database.Connection.CreateCommand() (the same context I use for EF) worked fine. After I changed timeout to default in connection string - it failed with normal message 'Error: 08006: Server did not respond within the specified timeout interval'. - Andrzej Gis
So if the query timeout error is different I still believe the problematic query is not run with the same connection. - Jakub Kania

1 Answers

0
votes

Default Command Timeout=300;

Could you please send us a small test project with the corresponding DDL/DML script for reproducing the issue with ignoring the Default Command Timeout connection string parameter?