Question 190463 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres connection pooling not working with .NET core 6 Transaction scope

I am using Npgsql and Dapper for connecting to postgres db from .NET 6 application. I have enabling connection pooling in postgres connection.

please refer https://www.npgsql.org/doc/connection-string-parameters.html

Commands are executed inside a transaction scope. I am executing multiple commands and each command opens new connection using NpgsqlDataSource.OpenConnection method.

Getting an error when transcationscope.Complete is called. Following is the error.

Message: 
System.Transactions.TransactionAbortedException : The transaction has aborted.
—- Npgsql.PostgresException : 55000: prepared transactions are disabled

We understand that this means connection is not reused as its trying to start prepared transaction. This is against what is documented in the last para of https://www.npgsql.org/doc/basic-usage.html#systemtransactions-and-distributed-transactions
and https://www.npgsql.org/doc/basic-usage.html#pooling

What are we doing wrong

Please help.

#edit 1 Attaching code sample

using (var transactionScope = new TransactionScope()) 
{
  
   using connection1 = NpgsqlConnection();
       
    await connection1.ExecuteAsync("procedure1"); 

    using connection2 = NpgsqlConnection();
    await connection2.ExecuteAsync("procedure2");
    
    transactionScope.Complete()
}



private static NpgsqlConnection NpgsqlConnection()
{
    string connectionString = "User ID=postgres;Password=*****;Host=hostName;Port=5432;Database=DBTestPostgres;Pooling=true;";
    NpgsqlDataSourceBuilder dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString); ,
    using NpgsqlDataSource dataSource = dataSourceBuilder.Build();
    NpgsqlConnection conn = dataSource.OpenConnection();
    return conn;
}

I was expecting the same connection should be reused as pooling is turned on in connection string.

Tags:

2

Answers


  1. 0

    you can make your connection using this code.
    Be sure to replace "DataBase" with the correct name of your connection string in the config file.

    builder.Services.AddScoped<IDbConnection>(provider =>
{
    var connectionString = provider.GetRequiredService<IConfiguration>().GetConnectionString("DataBase");
    return new NpgsqlConnection(connectionString);
});
    Login or Signup to reply.
  2. 0

    It seems you want to avoid Distributed Transactions (which is a good idea, as they have serious overhead, and also require server configuration).

    The documentation states:

    Note that if you open and close connections to the same database inside an ambient transaction, without ever having two connections open at the same time, Npgsql internally reuses the same connection, avoiding the need for a distributed transaction

    But you actually have two concurrent connections. You need to change your code to close the connection, by using the old style using block, rather than a using statement.

    using (var transactionScope = new TransactionScope()) 
{
    using (var connection1 = NpgsqlConnection())
    {
        await connection1.ExecuteAsync("procedure1");
    }

    using (var connection2 = NpgsqlConnection())
    {
        await connection2.ExecuteAsync("procedure2");
    }
    
    transactionScope.Complete()
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search