Description
Context / Scenario
Did some research on this issue and this recommendation of setting Pooling=false in #823 is not great for scaling. We need to use connection pooling in our projects.
@marcominerva @dluc I hit this exact issue in production with connection pool exhaustion.
We should implement a way to dispose the connections instead of turning off pooling altogether.
@vonzshik also recommended not to turn off pooling for performance reasons here.
From what I have read in best practices in Npgsql and used in my previous projects, you want to have a singleton of NpgsqlDataSource and then dispose the connections on each use. Which isn't happening here.
From their docs - "You typically build a single data source, and then use that instance throughout your application; data sources are thread-safe, and (usually) correspond to a connection pool inside Npgsql."
What happened?
Connection pool exhaustion very quickly as we are not disposing off connections.
Importance
I cannot use Kernel Memory
Platform, Language, Versions
C#, Latest Kernel Memory, Serverless.
Relevant log output
exception:Npgsql.PostgresException (0x80004005): 53300: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role
at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
at Npgsql.Internal.NpgsqlConnector.<Open>g__OpenCore|213_1(NpgsqlConnector conn, SslMode sslMode, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken, Boolean isFirstAttempt)
at Npgsql.Internal.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
at Npgsql.PoolingDataSource.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
at Npgsql.PoolingDataSource.<Get>g__RentAsync|34_0(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlConnection.<Open>g__OpenAsync|42_0(Boolean async, CancellationToken cancellationToken)