Parallel.ForEach using NpgsqlConnection

Question

I am using Npgsqlconnection inside a Parallel.ForEach, looping through inline queries in a List.

When I reach the number around 1400+ I get an Exception saying

'FATAL: 53300: remaining connection slots are reserved for non-replication superuser connections'.

I am using

 Pooling=true;MinPoolSize=1;MaxPoolSize=1024;ConnectionLifeTime=1 

in my app.config and con.Close(), con.ClearPool(), con.Dispose() in my code.

Parallel.ForEach(queries, query => { using (NpgsqlConnection con = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["PSQL"].ConnectionString)) { con.ClearPool(); con.Open();

                        //int count = 0;
                        int queryCount = queries.Count;

                        using (NpgsqlCommand cmd = con.CreateCommand())
                        {
                            cmd.CommandType = CommandType.Text;
                            //cmd.CommandTimeout = 0;

                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();

                            count += 1;
                            this.label1.Invoke(new MethodInvoker(delegate { this.label1.Text = String.Format("Processing...\n{0} of {1}.\n{2}% completed.", count, queryCount, Math.Round(Decimal.Divide(count, queryCount) * 100, 2)); }));                                
                        }

                        con.Close();                            
                        //con.Dispose();
                        //con.ClearPool();
                    }                    
                });

Answer 1

You are hitting the max connection limit of postgresql itself:

http://www.postgresql.org/docs/9.4/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS

Your parallel queries are getting a lot of connections and the server isn't being able to handle it. By default, Postgresql is configured to allow 100 concurrent connections. Maybe you should try to increase this value in your postgresql.conf file.

Another option is to limit the pool size of Npgsql to a lower number. Your concurrent queries would wait when the max pool size is reached.

Also, don't call ClearPool as you would add overhead to the pool logic and wouldn't benefit from the pool at all. You could try setting Pool=false in your connection string instead.

I hope it helps.