Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support ON_ERROR_ROLLBACK #2533

Open
buptlee opened this issue Jul 16, 2019 · 7 comments

Comments

@buptlee
Copy link

commented Jul 16, 2019

The issue

I know npgsql can support savepoint, save and rollback, is there a way to support ON_ERROR_ROLLBACK like we did in psql?

so we can enable it with a single line.

@austindrenski

This comment has been minimized.

Copy link
Member

commented Jul 17, 2019

@buptlee Can you give us an example of what you're trying to accomplish with it versus savepoints in the raw SQL?

@buptlee

This comment has been minimized.

Copy link
Author

commented Jul 18, 2019

@austindrenski

@buptlee Can you give us an example of what you're trying to accomplish with it versus savepoints in the raw SQL?

I'm creating savepoint in following way,

public void AddSavePoint(IDbConnection connection)
{
    var transaction = ((NpgsqlConnection)connection).BeginTransaction();
    transaction.Save("savepoint");
}

public void RollBackSavePoint(IDbConnection connection)
{
    var transaction = ((NpgsqlConnection)connection).BeginTransaction();
    transaction.Rollback("savepoint");
}

public void ReleaseSavePoint(IDbConnection connection)
{
    var transaction = ((NpgsqlConnection)connection).BeginTransaction();
    transaction.Release("savepoint");
}

When I use it, I have following pattern:

using(var conn = OpenConnection())
{
    AddSavePoint(conn);
    try
    {
        //Tosomething
     }
    catch(Exception)
    {
        RollBackSavePoint(conn);
    }
    finally
    {
        ReleaseSavePoint(conn);
    }
}

When the application runs a while, it will throws me an exception by saying The connection is already in state 'Executing':

Would you please let me know what I did wrong?

@buptlee

This comment has been minimized.

Copy link
Author

commented Jul 18, 2019

@austindrenski in my case, I have a really big transaction scope in c#, which contains lot of business logic and lots of database reading/writing. I feel like when I create lots of Savepoint, it will stop working at some point, the connection will be in executing status.

Is there a limitation? I didn't find anything based on my reading

@austindrenski

This comment has been minimized.

Copy link
Member

commented Jul 18, 2019

Per the docs:

PostgreSQL doesn't support nested or concurrent transactions - only one transaction may be in progress at any given moment. Calling BeginTransaction() while a transaction is already in progress will throw an exception.

I'm a bit rusty with the savepoints API, but in your example, you're callingBeginTransaction() a second time in the catch block. Have you tried not doing that?

@buptlee

This comment has been minimized.

Copy link
Author

commented Jul 18, 2019

@austindrenski

Per the docs:

PostgreSQL doesn't support nested or concurrent transactions - only one transaction may be in progress at any given moment. Calling BeginTransaction() while a transaction is already in progress will throw an exception.

I agree with that, I only see this error after many executions, if that's the case, if should throw exception in the first execution, right? so what's the best way to solve this issue?

@buptlee

This comment has been minimized.

Copy link
Author

commented Jul 18, 2019

@austindrenski

even I rewrote my code like following way.

  public void AddSavePoint(IDbConnection conn)
        {
            if (GetDBProvider() == DatabaseType.PostgreSQL)
            {
                using (IDbCommand savePointCMD = GetDbCommand(conn, "SAVEPOINT savepoint;"))
                {
                    savePointCMD.ExecuteNonQuery();
                }
            }
        }
`
        `
  public void ReleaseSavePoint(IDbConnection conn)
        {
            if (GetDBProvider() == DatabaseType.PostgreSQL)
            {
                using (IDbCommand savePointCMD = GetDbCommand(conn, "RELEASE SAVEPOINT savepoint;"))
                {
                    savePointCMD.ExecuteNonQuery();
                }
            }
        }
`

        public void RollBackSavePoint(IDbConnection conn)
        {
            if (GetDBProvider() == DatabaseType.PostgreSQL)
            {
                using (IDbCommand savePointCMD = GetDbCommand(conn, "ROLLBACK TO SAVEPOINT savepoint;"))
                {
                    savePointCMD.ExecuteNonQuery();
                }
            }

        }

and I'm using it like

using(var conn = OpenConnection()){
AddSavePoint(conn);
try{
//TosomeDBwork
}
catch{
RollBackSavePoint(conn);
}
finally{
ReleaseSavePoint(conn);
}
}

I met the same exception after a while, do you have any idea or suggestions?

@roji

This comment has been minimized.

Copy link
Member

commented Jul 20, 2019

@buptlee in general your code looks like it should work, but it's hard to know exactly what's happening. Can you please try to create a code sample that reproduces the problem? You can try looping and performing the same work many times if you think it happens only after a while (which would be strange).

Some other comments:

PostgreSQL doesn't support nested or concurrent transactions - only one transaction may be in progress at any given moment. Calling BeginTransaction() while a transaction is already in progress will throw an exception.

I agree with that, I only see this error after many executions, if that's the case, if should throw exception in the first execution, right?

If you called BeginTransaction() twice on the same connection (as you seemed to be doing above), you should get an exception right away. If not, can you please double-check your code to make sure that's what you're doing?

PS in your code samples, it's better to call the Save(string), Rollback(string) and Release(string) on NpgsqlTransaction rather than sending SAVEPOINT savepoint yourself.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.