Recovering from failed transaction

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

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

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