Max Pool Size is being exceded

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
jamir.araujo
Posts: 11
Joined: Wed 13 Mar 2019 17:25

Max Pool Size is being exceded

Post by jamir.araujo » Wed 25 Aug 2021 12:19

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
Devart Team
Posts: 70
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Thu 26 Aug 2021 14:56

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
Posts: 11
Joined: Wed 13 Mar 2019 17:25

Re: Max Pool Size is being exceded

Post by jamir.araujo » Fri 27 Aug 2021 17:02

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
Devart Team
Posts: 70
Joined: Fri 11 Dec 2020 10:27

Re: Max Pool Size is being exceded

Post by DmitryGm » Mon 30 Aug 2021 14:47

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

Post Reply