PgSqlException: canceling statement due to user request

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
dghundt
Posts: 38
Joined: Thu 24 Aug 2006 01:16

PgSqlException: canceling statement due to user request

Post by 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?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by 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.

dghundt
Posts: 38
Joined: Thu 24 Aug 2006 01:16

Post by 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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by 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.

dghundt
Posts: 38
Joined: Thu 24 Aug 2006 01:16

Post by dghundt » Mon 09 Feb 2009 21:35

Thank you.

Post Reply