Transient Error: "too many connections for role" in ASP.NET Core project with PostgreSQL and Entity Framework Core

Question

In my ASP.NET Core project, which uses PostgreSQL as the database and Entity Framework Core for data access, I'm running into a recurring problem. I'm getting the following error message on and off:

{"Message":"An error occurred while processing your request.","ExceptionMessage":"53300: too many connections for role \"yxyjmsin\"","ExceptionType":"Npgsql.PostgresException"}
{"Message":"An error occurred while processing your request.","ExceptionMessage":"An exception has been raised that is likely due to a transient failure.","ExceptionType":"System.InvalidOperationException"}

This error seems to be related to an excessive number of open database connections. I have tried various configurations and code changes, but I'm still struggling to pinpoint the root cause and resolve the issue.

This is how I added dbcontext in my Program.cs

builder.Services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
{
    var configuration = serviceProvider.GetRequiredService<IConfiguration>();
    var builder = new NpgsqlDataSourceBuilder(configuration.GetConnectionString("ElephantSQL"));
    options.UseNpgsql(builder.ConnectionString, npgsqlOptions => 
    {
        npgsqlOptions.EnableRetryOnFailure();
        npgsqlOptions.MaxBatchSize(5);
    });
    builder.MapEnum<UserRole>();
    builder.MapEnum<OrderStatus>();
    options.AddInterceptors(new TimeStampInterceptor());
    options.UseNpgsql(builder.Build()).UseSnakeCaseNamingConvention();
});

"ConnectionStrings": {
    "ElephantSQL": "Server=myServer;Port=5432;Username=yxyjmsin;Password=myPasswd;Database=myDb Pooling=true;"
 },

usage of applicationdbcontext in my base repo

public async Task<TEntity> AddAsync(TEntity entity)
        {
            try
            {
                if (entity is User userEntity)
                {
                    if (userEntity.Role != UserRole.Admin)
                    {
                        userEntity.Role = UserRole.Customer;
                    }
                }
                var entry = await _dbSet.AddAsync(entity);
                await _applicationDbContext.SaveChangesAsync();
                return entry.Entity;
            }
            catch (DbUpdateException ex)
            {                
                Console.WriteLine("Base Repository exception.");
                if (ex.InnerException != null)
                {
                    Console.WriteLine("Inner exception: " + ex.InnerException.Message);
                }
                throw;
            }
        }

I had the configuration of my db in the ApplicationDbContext file but moved to Program.cs with hopes that it was going to resolve the transient issue but it didnt.

I checked the rolconnlimit which was 5 and so did set maxbatchsize to 5.

I've enabled retry on failure in the database connection configuration and noticed this error in the console: Exception data:

Severity: FATAL
SqlState: 53300
MessageText: too many connections for role "yxyjmsin"
File: miscinit.c
  
Line: 734
Routine: InitializeSessionUserId

Could you please help me fix this issue in my code?

Thank you in advance for your assistance!

Answer 1

There are couple of things that could be wrong with your code:

1. Your connection string doesn't seem to be okay. you missed ; before Pooling=true

2. change the connection string to have more options regarding the pooling like: Server=myServer;Port=5432;Username=yxyjmsin;Password=myPasswd;Database=myDb;Pooling=true;Min Pool Size=0;Max Pool Size=100;

if those didn't work, try the connection string without pooling: Server=myServer;Port=5432;Username=yxyjmsin;Password=myPasswd;Database=myDb; and also without batch config:

builder.Services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
{
    var configuration = serviceProvider.GetRequiredService<IConfiguration>();
    var builder = new NpgsqlDataSourceBuilder(configuration.GetConnectionString("ElephantSQL"));
    options.UseNpgsql(builder.ConnectionString, npgsqlOptions => npgsqlOptions.EnableRetryOnFailure(3));
    builder.MapEnum<UserRole>();
    builder.MapEnum<OrderStatus>();
    options.AddInterceptors(new TimeStampInterceptor());
    options.UseNpgsql(builder.Build()).UseSnakeCaseNamingConvention();
});