Max Pool Size is being exceded

[jamir.araujo]

Hello!

A team on my company is facing an issue where the connection parameter Max Pool Size is not being respected when there a too many locks.

The context where this behavior happens is too big, so i made a simple console application to reproduce it.

Code: Select all

class Program
    {
        static async Task Main(string[] args)
        {
            var connectionString = "Data Source=127.0.0.1;Port=5432;Database=MaxPoolSize;User ID=postgres;password=admin;Max Pool Size=5";

            PostgreSqlLicense.Validate(connectionString);

            var recordId = Guid.NewGuid();

            var connection = new PgSqlConnection(connectionString);
            await connection.OpenAsync();

            var command = connection.CreateCommand();
            command.CommandText = $"INSERT INTO public.\"Records\" (\"Id\", \"Text\") VALUES ('{recordId}'::uuid, 'Teste')";
            await command.ExecuteNonQueryAsync();

            await connection.CloseAsync();

            var tasks = new List<Task>();
            foreach (var operation in Enumerable.Range(1, 30))
            {
                tasks.Add(Task.Run(async () =>
                {
                    try
                    {
                        var connection = new PgSqlConnection(connectionString);
                        await connection.OpenAsync();

                        var transaction = connection.BeginTransaction();

                        var sqlText = $"UPDATE public.\"Records\" SET \"Text\" = 'text-{operation}' WHERE \"Id\" = '{recordId}'";

                        var command = new PgSqlCommand(sqlText, connection, transaction);

                        await command.ExecuteNonQueryAsync();

                        await command.DisposeAsync();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("Operation {0} failed", operation);
                        throw;
                    }
                }));
            }

            await Task.WhenAll(tasks);

            Console.WriteLine("Done!");
        }
    }

In this application I set Max Pool Size to 5 and really forced the lock to occur by opening connections and transactions in parallel, without committing the transactions or closing the connections.

The table I'n using is very simple. It's table named "Records" with Primary Key "Id" type uuid and a column "Text" type text.

The application starts with 6 connections (one of them being the connection that was opened to validate the license), which already exceeds the configured limit. After one minute it goes to 10, after another minute 14, then 18, then 19, then all connections time out and the line Task.WhenAll (tasks) throws.

The connections timeout is expected, but exceeding Max Pool Size was not.

It is possible to see when the Max Pool Size has been reached, the code stops on the OpenAsync method, but than after a minute and some connections timeouts, it allows more connections.

[DmitryGm]

Max Pool Size limit doesn't restrict creation of connection objects. But when the limit is reached, the Open() method waits until an existing connection is closed or until timeout.

This behavior is demonstrated by your code example - this is the designed behavior.

[jamir.araujo]

Yes, the code is stopping at the Open() method, but the connections are opened and exceeding the max pool size.

You can see this by running the code and executing the following query on pgadmin:

Code: Select all

SELECT * FROM pg_stat_activity where datname = 'MaxPoolSize'

The code will stop at the Open() Method and eventually will throw a timeout exception. But code connection are being open and are not released when the timeout occur.

The connection are not being released when I manually call the Close or Dispose. They are not released even when I call the ClearAllPools method. The only thing that releases the connections is closing the application.

[DmitryGm]

Thank you for your report. We are investigating this case with PostgreSQL connections and shall inform you as soon as we have any results.

[DmitryGm]

This is due to a "forgot" transaction.

Code: Select all

var transaction = connection.BeginTransaction();

In that case, the Application cannot keep track of the release connections and allocation of new connections.

Note that the transaction class implements IDisposable. You should wrap the transaction into a "using" like that:

Code: Select all

                        
using (var transaction = connection.BeginTransaction())
{
    var sqlText = $"UPDATE public.\"Records\" SET \"Text\" = 'text-{operation}' WHERE \"Id\" = '{recordId}'";
    var command = new PgSqlCommand(sqlText, connection, transaction);
    await command.ExecuteNonQueryAsync();
    await command.DisposeAsync();
}

In this case, everything works correctly.

[jamir.araujo]

Yes, my code lets the transaction and connection open intentionaly to generate locks.

I did this becousa we were seeing a similar issue on some application of my company. More connections open than we should have, and many of them locked by transactions (possibly due to some dead lock)

I executed the same code using the Npgsql driver and it did not exceded the max pool size.

[DmitryGm]

Yes, dotConnect for PostgreSQL requires more correct code here. This results in better performance. Therefore, this behavior of dotConnect for PostgreSQL will not be changed.

[jamir.araujo]

Fair enough. Could you at least inform of this behavior in the Max Pool Size documentation?

[DmitryGm]

Thank you for your report. Yes indeed, in the case of forgot unclosed transactions, compliance with Max Pool Size is not guaranteed. It may be reflected in the documentation soon.