Simple Linq To Postgresql query timeout problem

robofx · Post by **robofx** » Fri 08 May 2009 05:46

Using:
Visual Basic 2008
dotconnect for Postgresql Pro 4.50.29
Linq To Sql
Simple datacontext named testdbdatacontext
A 1Gb postgresql test database

------------------------------------------
Seems to be a postgresql connection or command timeout error?

I have a 1Gb postgresql test database. When I run a simple Linq To Sql query I get this error:

"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"

I tried setting:

testdbdatacontext.CommandTimeout = 120 but it says "The method or operation is not implemented."

I tried setting:
testdbdatacontext.Connection.ConnectionTimeout = 120 but the compiler says "Property 'ConnectionTimeout' is 'ReadOnly'."

How do I change connection timeout in Linq To Sql? Thank you.

AndreyR · Post by **AndreyR** » Fri 08 May 2009 10:50

The DataContext.CommandTimeout property will be implemented in the next build.

robofx · Post by **robofx** » Fri 08 May 2009 15:59

Ok, thank you very much.

When will the new release be made available?

AndreyR · Post by **AndreyR** » Wed 13 May 2009 11:05

We plan to release the next build in a week or so.

voislav · Post by **voislav** » Mon 29 Nov 2010 03:18

I have experienced the similar problem. I am using dotconnect for PostgreSQL version 4.65.79.0 and could not change the command timeout.

I added the following into the method OnCreated of DataContext class:
this.CommandTimeout = MyProject.Properties.Settings.Default.DbCommandTimeout;

But whichever value I used for command timeout, I always got exception after 60 seconds. Note that default value for CommandTimeout is 30 seconds, so I do not know what is the problem?
Because I were not sure that command timeout caused the error I tried to put 1 second for command timeout but result is the same whichever value I use 1, 5, 60, 120 etc.

I tried to read all data from the table with about 500.000 rows.

I found that bug with setting CommandTimeout is fixed in version 4.65.62 16, I am using later version so I am a little bit confused. Do you have an idea what is the problem here?

voislav · Post by **voislav** » Mon 29 Nov 2010 04:28

I also tried to set "Default Command Timeout" in connection string in my config file but it did not help.

StanislavK · Post by **StanislavK** » Mon 29 Nov 2010 17:28

Such error may occur on both opening a connection and executing a command. You can try changing the 'Connection Timeout' connection string parameter to check whether the first option is the case. Otherwise, please specify the stack trace of the exception so that we are able to determine where it originates from.

voislav · Post by **voislav** » Wed 01 Dec 2010 09:44

Thank you for quick answer.

The connection timeout is not a problem, it is set by default to 15 seconds and it never occurs.

The whole problem occurred when I use the command within the System transaction (TransactionScope). I found that even if I set DataContext CommandTimeout the error occurs just after transaction timeout is happen. So if I set command timeout shorter than transaction timeout, the exception will be thrown just after transaction timeout. So this is really confusing! In the same way if I set transaction timeout shorter than command timeout the exception is thrown after transaction timeout. The exception trace is different in these cases, additionally it is different when transaction timeout is greater than command timeout - I found three different exceptions.

So I tried to solve my problem by setting both timeouts to the same value and it is working now.

BTW, setting command timeout through connection string using "Default Command Timeout" does not work, so I did it by setting DataContext.CommandTimeout in the OnCreated method.

Here are stack traces for the cases described above:

When transaction timeout is greater than command timeout, I got different exceptions, each time different which is really confusing:

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException: Error on reading data from IDataReader. ---> Devart.Data.PostgreSql.PgSqlException: Unexpected server response.
   at Devart.Data.PostgreSql.i.a(Char A_0, Boolean A_1, Boolean A_2)
   at Devart.Data.PostgreSql.r.a(Char A_0, Boolean A_1, Boolean A_2)
   at Devart.Data.PostgreSql.i.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
   at Devart.Data.PostgreSql.r.i()
   at Devart.Data.PostgreSql.r.a(ad A_0, Boolean A_1, Int32 A_2)
   at Devart.Data.PostgreSql.ad.af()
   at Devart.Data.PostgreSql.ad.m()
   at Devart.Data.PostgreSql.PgSqlDataReader.Read()
   at Devart.Data.Linq.Provider.ObjectReader`1.MoveNext()
   --- End of inner exception stack trace ---
   at Devart.Data.Linq.LinqCommandExecutionException.a(String A_0, Exception A_1)
   at Devart.Data.Linq.Provider.ObjectReader`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at CC.SyncServiceClient.SyncService.SynchronizeProductGroups(Action`1 writeMessage) in D:\CC.SyncServiceClient\SyncService.cs line 55
Error message: Error on reading data from IDataReader.

System.NullReferenceException: Object reference not set to an instance of an object.
   at Devart.Data.PostgreSql.ad.a(Byte[] A_0, Int32 A_1, ArrayList A_2)
   at Devart.Data.PostgreSql.r.a(Boolean A_0)
   at Devart.Data.PostgreSql.i.a(Char A_0, Boolean A_1, Boolean A_2)
   at Devart.Data.PostgreSql.r.a(Char A_0, Boolean A_1, Boolean A_2)
   at Devart.Data.PostgreSql.i.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
   at Devart.Data.PostgreSql.r.i()
   at Devart.Data.PostgreSql.r.a(ad A_0, Boolean A_1, Int32 A_2)
   at Devart.Data.PostgreSql.ad.af()
   at Devart.Data.PostgreSql.ad.m()
   at Devart.Data.PostgreSql.PgSqlDataReader.Read()
   at Devart.Data.Linq.Provider.ObjectReader`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at CC.SyncServiceClient.SyncService.SynchronizeProductGroups(Action`1 writeMessage) in D:\CC.SyncServiceClient\SyncService.cs line 55
Error message: Object reference not set to an instance of an object.

System.ArgumentException: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.
   at System.Buffer.BlockCopy(Array src, Int32 srcOffset, Array dst, Int32 dstOffset, Int32 count)
   at Devart.Data.PostgreSql.g.a(Byte[] A_0, Int32 A_1, ArrayList A_2)
   at Devart.Data.PostgreSql.g.d()
   at Devart.Data.PostgreSql.g.a(Boolean A_0, Boolean A_1)
   at Devart.Data.PostgreSql.g.af()
   at Devart.Data.PostgreSql.g.a(Boolean A_0, Int32 A_1)
   at Devart.Data.PostgreSql.PgSqlDataReader.a()
   at Devart.Data.PostgreSql.PgSqlDataReader.Read()
   at Devart.Data.Linq.Provider.ObjectReader`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at CC.SyncServiceClient.SyncService.SynchronizeProductGroups(Action`1 writeMessage) in D:\CC.SyncServiceClient\SyncService.cs line 55
Error message: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.

When transaction timeout is smaller than command timeout:

Code: Select all

System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.TimeoutException: Transaction Timeout
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateAborted.BeginCommit(InternalTransaction tx, Boolean asyncCommit, AsyncCallback asyncCallback, Object asyncState)
   at System.Transactions.CommittableTransaction.Commit()
   at System.Transactions.TransactionScope.InternalDispose()
   at System.Transactions.TransactionScope.Dispose()
   at CC.SyncServiceClient.SyncService.SynchronizeProductGroups(Action`1 writeMessage) in D:\CC.SyncServiceClient\SyncService.cs line 113
Error message: The transaction has aborted.

StanislavK · Post by **StanislavK** » Wed 01 Dec 2010 15:03

Could you please try the latest 5.0.58 version of dotConnect for PostgreSQL and tell us if the problem can be reproduced with it? For the latter case, I've sent you a test project in a letter (please check that it was not blocked by your mail filter); please specify what should be changed in the project to reproduce the error.

Provided that you have an active subscription, you can download dotConnect for PostgreSQL 5 from Registered Users' Area:
http://secure.devart.com/
The trial version is available at
http://www.devart.com/dotconnect/postgr ... nload.html
JIC: the trial period is renewed in the new version, meaning that you should be able to evaluate dotConnect for PostgreSQL 5 even if your trial for prior versions has already expired.