c# - Parallel.ForEach using NpgsqlConnection -

i using npgsqlconnection inside parallel.foreach, looping through inline queries in list.

when reach number around 1400+ exception saying

'fatal: 53300: remaining connection slots reserved non-replication superuser connections'.

i using

 pooling=true;minpoolsize=1;maxpoolsize=1024;connectionlifetime=1  

in app.config , con.close(), con.clearpool(), con.dispose() in 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();                     }                                     }); 

you hitting max connection limit of postgresql itself:

http://www.postgresql.org/docs/9.4/static/runtime-config-connection.html#guc-max-connections

your parallel queries getting lot of connections , server isn't being able handle it. default, postgresql configured allow 100 concurrent connections. maybe should try increase value in postgresql.conf file.

another option limit pool size of npgsql lower number. concurrent queries wait when max pool size reached.

also, don't call clearpool add overhead pool logic , wouldn't benefit pool @ all. try setting pool=false in connection string instead.

i hope helps.