PgSqlException: canceling statement due to user request

PgSqlException: canceling statement due to user request

Postby dghundt » Fri 06 Feb 2009 13:58

I received this error while sequentially executing several update commands in a transaction. On the sixth update, I received this error.

Code: Select all
Devart.Data.PostgreSql.PgSqlException: canceling statement due to user request
   at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
   at Devart.Data.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Devart.Common.DbCommandBase.ExecuteNonQuery()


The transaction was unable to rollback. I have tested the transaction with 'bogus' type queries, and the rollback feature does work properly.
I ran my code again minutes later against the same data and all update queries worked ok.

I have the postgres server and client application running on the same machine. Windows xp pro sp3, C#, .net 3.5. The connection string to the server uses my computers name.

Any thoughts on what this error means and what I can do to avoid it?
Also, a rollback does not work in this situation - does the transaction still get committed? - what is the best way to handle this?
dghundt
 
Posts: 38
Joined: Thu 24 Aug 2006 01:16

Postby Shalex » Fri 06 Feb 2009 15:16

We don't know why this exception appeared. We recommend you to use the code like this:
Code: Select all
      pgSqlConnection1.Open();
      pgSqlConnection1.BeginTransaction();
      try {
        PgSqlCommand command = new PgSqlCommand();
        command.Connection = pgSqlConnection1;
        for (int i = 0; i < 100; i++) {
          command.CommandText = "Update dept set dname = " + i + "where deptno=10";
          command.ExecuteNonQuery();
        }
        pgSqlConnection1.Commit();
      }
      catch {
        pgSqlConnection1.Rollback();
      }
     pgSqlConnection1.Close();

If the problem persists, please send us a small test project with a script. We will try to reproduce the error.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby dghundt » Fri 06 Feb 2009 15:43

Appreciate your reply. Two questions using your code as an example.

1. I put the pgSqlConnection1.BeginTransaction() inside the try block. You recommend it outside? If so, why?


2. If there is an exception in the try block because a connection to the host is lost, the rollback command obviously will not work. Will all the previous command.ExecuteNonQuerys be committed to the database or will the postgres server ignore all the queries in the transaction and restore the database to the original state?

Thanks.
David
dghundt
 
Posts: 38
Joined: Thu 24 Aug 2006 01:16

Postby Shalex » Mon 09 Feb 2009 09:10

1. It doesn't matter for the code we have posted above where the pgSqlConnection1.BeginTransaction() is. Maybe, it will be more correct to place the pgSqlConnection1.BeginTransaction() inside the try block.

2. If the "Connection to the host is lost" exception appears, the rollback command will not be executed, but all the changes to the database will not be committed. We have placed the rollback command to the catch block to rollback all the changes if the connection to the database will not be lost but another exception will arise.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44

Postby dghundt » Mon 09 Feb 2009 21:35

Thank you.
dghundt
 
Posts: 38
Joined: Thu 24 Aug 2006 01:16


Return to dotConnect for PostgreSQL