Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Application freezes on ExecuteNonQueryAsync 100 000 inserts #2503

Open
2m0nd opened this issue Jun 21, 2019 · 2 comments

Comments

@2m0nd
Copy link

commented Jun 21, 2019

Npgsql version: 4.0.7
.Net: 4.7.2
PostgreSQL version: PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
Operating system: Windows 10

SQL:

CREATE TABLE public.test_db
(
    id integer,
    status text COLLATE pg_catalog."default",
    datetime timestamp without time zone
)
WITH(
   OIDS = FALSE
)
TABLESPACE pg_default;

Example code:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Npgsql;
using Npgsql.Logging;

namespace Exapmple
{
    class Program
    {
        static void Main(string[] args)
        {
            NpgsqlLogManager.Provider = new ConsoleLoggingProvider(NpgsqlLogLevel.Debug);

            var size = 100000;
            var st = Stopwatch.StartNew();
            var range = Enumerable.Range(0, size);

            var counter = 0;
            var tasks = new List<Task>();
            foreach (var r in range)
            {
                tasks.Add(Task.Run(async () =>
                {
                    using (var cn = new NpgsqlConnection(
                        "Server=localhost;Port=5432;Database=test;User Id=user;Password=pwd;"))
                    {
                        await cn.OpenAsync();
                        var cmd = cn.CreateCommand();
                        cmd.CommandText = "INSERT INTO public.test_db(id, status, datetime) VALUES(@id, @status, @currentTime);";
                        cmd.Parameters.Add(new NpgsqlParameter("id", r));
                        cmd.Parameters.Add(new NpgsqlParameter("status", r));
                        cmd.Parameters.Add(new NpgsqlParameter("currentTime", DateTime.Now));
                        await cmd.ExecuteNonQueryAsync();
                    }
                    Interlocked.Increment(ref counter);
                }));
            }

            Task.WaitAll(tasks.ToArray());
            var elapsed = st.Elapsed;
            Console.WriteLine($"Total time: {elapsed} / {size}");
        }
    }
}

On run this code in .net core 2.1 throw exception:

Npgsql.NpgsqlException: The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 120 seconds)
   at Npgsql.ConnectorPool.AllocateLong(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:line 301
--- End of stack trace from previous location where exception was thrown ---
   at TestAsyncAndThreadPool.Program.<>c__DisplayClass0_1.<<Main>b__0>d.MoveNext() in C:\Users\dima\source\repos\TestAsyncAndThreadPool\TestAsyncAndThreadPool\Program.cs:line 36
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.WaitAllCore(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.WaitAll(Task[] tasks)
   at TestAsyncAndThreadPool.Program.Main(String[] args)

If enable ngpsql logs, last lines before freezes or exception:

Connection closed
Connection closed
Connection closed
Connection closed
Connection closed
Connection closed
Connection closed
Connection closed
Connection closed
Connection closed

Application inserts only first 100 rows, this size connection pool, it looks like the pool is not free.

If downgrade npgsql to last 3.x version (3.2.7) then there is no problem.

@roji

This comment has been minimized.

Copy link
Member

commented Jul 2, 2019

Am investigating some odd behavior, can you please try adding Max Pool Size=99 to your connection string and report on the results?

I'd also be interested in hearing how both scenarios works with 3.0.0-preview6.

@2m0nd

This comment has been minimized.

Copy link
Author

commented Jul 2, 2019

Hi @roji, if set up MaxPoolSize=99 or MaxPoolSize=20 then the problem is the same.
In table inserted firrst 99 rows or 20 rows and and on freezes application (Npgsql version: 4.0.7).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.