Postgres Pro
Downloads
Home   >   mailing lists

Thread: Recovering from failed transaction

Recovering from failed transaction

From
Brian Crowell
Date:
I feel dumb asking this question, but I can't seem to find the answer online.

I'm running serializable transactions, and so naturally, they will
sometimes fail with the error "could not serialize access due to
concurrent update."

But then I try to issue a ROLLBACK so I can continue using the
connection, and I get error 25P02: "current transaction is aborted,
commands ignored until end of transaction block."

...doesn't "ROLLBACK" end a transaction block? What does Postgres want
here? How can I retry without closing the connection altogether?

--Brian

Re: Recovering from failed transaction

From
Jeff Janes
Date:
On Mon, Mar 10, 2014 at 1:41 PM, Brian Crowell <brian@fluggo.com> wrote:
I feel dumb asking this question, but I can't seem to find the answer online.

I'm running serializable transactions, and so naturally, they will
sometimes fail with the error "could not serialize access due to
concurrent update."

But then I try to issue a ROLLBACK so I can continue using the
connection, and I get error 25P02: "current transaction is aborted,
commands ignored until end of transaction block."

...doesn't "ROLLBACK" end a transaction block? What does Postgres want
here? How can I retry without closing the connection altogether?

What tool are you using to connect to the database?  Perhaps it is confusing the error response to some earlier statement in the stream with the response for the rollback.  Or maybe it is mangling the "ROLLBACK;" into some form the database doesn't recognize.  Look in the postgres log files to see what the events look like from PostgreSQL's perspective.

Cheers,

Jeff

Re: Recovering from failed transaction

From
Brian Crowell
Date:
On Mon, Mar 10, 2014 at 4:16 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> ...  Or maybe it is mangling the "ROLLBACK;" into some form
> the database doesn't recognize.  Look in the postgres log files to see what
> the events look like from PostgreSQL's perspective.

Well that's the clue I needed. I was misinterpreting Postgres's log
file; it was complaining about the "SET statement_timeout" statement
Npgsql was slipping ahead of my ROLLBACK. Apparently I need to do
transactions with Npgsql's transaction class.

--Brian

Re: Recovering from failed transaction

From
"Francisco Figueiredo Jr."
Date:

Hi Brian!

I got a patch to fix this. Unfortunately, I'm having some problems with github at this moment and I couldn't push it to create a pull request.

Would you mind to patch a local copy of Npgsql code and give it a try?

Here is the patch:


diff --git a/Npgsql/Npgsql/NpgsqlConnector.cs b/Npgsql/Npgsql/NpgsqlConnector.cs
index eb7da15..5e090be 100644
--- a/Npgsql/Npgsql/NpgsqlConnector.cs
+++ b/Npgsql/Npgsql/NpgsqlConnector.cs
@@ -427,7 +427,7 @@ internal void ReleaseResources()

         internal void ReleaseWithDiscard()
         {
-            NpgsqlCommand.ExecuteBlind(this, NpgsqlQuery.DiscardAll, 60);
+            NpgsqlCommand.ExecuteBlind(this, NpgsqlQuery.DiscardAll);

             // The initial connection parameters will be restored via IsValid()
         }
@@ -828,7 +828,7 @@ internal void Open()

             initQueries = sbInitQueries.ToString();

-            NpgsqlCommand.ExecuteBlind(this, initQueries, 60);
+            NpgsqlCommand.ExecuteBlind(this, initQueries);

             // Make a shallow copy of the type mapping that the connector will
             // It is possible that the connector may add types to its privateY



Thanks in advance and sorry for this problem.



On Mon, Mar 10, 2014 at 6:49 PM, Brian Crowell <brian@fluggo.com> wrote:
On Mon, Mar 10, 2014 at 4:16 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> ...  Or maybe it is mangling the "ROLLBACK;" into some form
> the database doesn't recognize.  Look in the postgres log files to see what
> the events look like from PostgreSQL's perspective.

Well that's the clue I needed. I was misinterpreting Postgres's log
file; it was complaining about the "SET statement_timeout" statement
Npgsql was slipping ahead of my ROLLBACK. Apparently I need to do
transactions with Npgsql's transaction class.

--Brian


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior