Thursday, April 29, 2010

Npgsql Connection Pool Explained

Hi all!

From time to time, we receive some questions regarding connection pool in Npgsql and I think I should post some info about its current design.

Npgsql connection pool implements the common pattern of having some connections open beforehand so when one is needed, it will be readily available for using.

How it works

When a application opens a connection, Npgsql tries to find a pool of connections based on the connection string. If a pool doesn't exist, it is created with a number of connections specified in the MinPoolSize connectionstring parameter. After that, a connection is retrieved from this pool.

The min and max number of connections created in each pool is controlled by connection string parameters called MinPoolSize and MaxPoolSize respectively. This way, users can fine tune the pool behavior to match their scalability needs.

Npgsql controls the lifetime of unused connections in the pool, trying to get connections number near the minimum value set by user. This is done by closing unused connections which are open far long than NpgsqlConnection.ConnectionLifeTime. This control is helpful in a scenario where application uses a lot of connections in a peak situation and later goes back to normal connection usage. Those "extra" connections will stay open but won't be used anytime soon, so instead of laying there consuming server resources, Npgsql simply closes them when their lifetime is reached.

Applications also can clear a pool or all pools by using NpgsqlConnection.ClearPool() and NpgsqlConnection.ClearAllPools() static methods.

EOF Error Message

There is one error message which appears in server log with applications which use Npgsql with pooled connections. This is the error message:

LOG: unexpected EOF on client connection

This is generally caused when the application is terminated and there are connections in the pool. The Tcp connection is closed by the .Net framework without Npgsql sending the Terminate message. Sending the Terminate message to all open connections would be the best thing to do, but Npgsql, by itself, isn't be able to know when the application is being terminated and so the log is generated. According to docs, this disconnection will make the backend clean up and terminate the connection ok. So, the only drawback of this situation is this message log.

In order to get more information about Npgsql connection pooling, you may check the NpgsqlConnectorPool.cs file.

I hope this information helps developers to understand better how connection pool works with Npgsql.

If you have any other questions, please drop by Npgsql forums.

Sunday, April 04, 2010

Function call performance optimizations

On my last post about that subject, I wrote about some optimizations I did to get better performance when calling functions with Npgsql.

While that optimizations were very nice, they had a drawback: you had to reuse your NpgsqlCommand object. You had to reuse it because the optimizations were based on cached data and if you created a new NpgsqlCommand object the data would need to be cached again.

In the general case, where you would create many NpgsqlCommand objects and call functions with them, you would not benefit from those optimizations.

In order to fix that, Noah Misch created a patch which remove 2 of the 3 internal calls which were giving performance problems.

The only case left is for functions which have return type of 'record'. We are working to get this case also covered.

I'm going to show here how much performance improvement you get with this patch with a simple call to a function which returns an integer. This function is on Npgsql unit test suite, but I reproduce it here just for completeness:

create function funcA() returns int as '
select 0;

' language 'sql';

I'm going to compare the latest stable release version Npgsql 2.0.8 with our latest cvs version with Noah's patch.

In a loop of 100 iteractions, this is what we get with Npgsql 2.0.8 and Npgsql cvs:

Npgsql 2.0.8:

time mono teste.exe

real 0m0.537s
user 0m0.457s
sys 0m0.028s

Npgsql cvs:

time mono teste.exe

real 0m0.467s
user 0m0.420s
sys 0m0.026s

It is 13% faster!

If we raise the number of interactions to 1000 we get:

Npgsql 2.0.8:

time mono teste.exe

real 0m1.237s
user 0m0.698s
sys 0m0.089s

Npgsql cvs:

time mono teste.exe

real 0m0.655s
user 0m0.492s
sys 0m0.054s

Which gives 47% improvement!

So, when next Npgsql release is out, we can see a modest to good performance improvement in function calling scenarios using Npgsql.

If you want to try it out today, please grab the latest cvs code and let us know what do you get.

Please, leave your comments and feedback. Also, participate on our Forums so you can share your experience.

Friday, April 02, 2010

Using SSL Client Certificates with Npgsql

Hi all!

Recently, Jarrod Kinsley asked on our Forums how to establish an SSL connection. As Laurenz Albe pointed out, normally you just need to change your connection string to put "SSL=True;Sslmode=Require;" in your connection string and "ssl=on" in postgresql.conf and you are ready to go.

The problem was that this works in the general case where you don't have to deal with client certificates and other stuff. Npgsql has a lot of callbacks to help you to validate and talk to the server.

The last callback added to the chain by Frank Bollack was to provide a way to pass client certificates to server.

Later on the thread, Jennifer Marienfeld was also trying to connect and was stuck in the client certificate part. Jennifer eventually got success to establish connection to the server and I decided to create this post to show the code so others can benefit from this.

Here is Jennifer's code so you all can use as a template:

using System;
using System.IO;
using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;

using Npgsql;
using Mono.Security.Protocol.Tls;
using Mono.Security.Authenticode;

namespace my
class Program
public static void Main(string[] args)
string conStr =
";" +
"User Id=xxx;" +
"Password=xxx;" +
"Protocol=3;" +
"Database=xxx;" +
"SSL=True;" +

NpgsqlConnection conn = new NpgsqlConnection(conStr);

conn.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback(

conn.CertificateSelectionCallback +=
new CertificateSelectionCallback(

conn.CertificateValidationCallback +=
new CertificateValidationCallback(

conn.PrivateKeySelectionCallback +=
new PrivateKeySelectionCallback(

System.Console.WriteLine("Verbindung aufgebaut");
catch (Exception e)

static void MyProvideClientCertificates(X509CertificateCollection clienteCertis)
X509Certificate cert = new X509Certificate("mycert.crt");

static X509Certificate MyCertificateSelectionCallback(X509CertificateCollection clienteCertis, X509Certificate serverCerti, string hostDestino, X509CertificateCollection serverRequestedCertificates)
return clienteCertis[0];

static AsymmetricAlgorithm MyPrivateKeySelectionCallback(X509Certificate certificate, string targetHost)
PrivateKey key =null;
//it is very important that the key has the .pvk format in windows!!!
key = PrivateKey.CreateFromFile("myKey.pvk", "xxx");
catch (CryptographicException ex)

if (key == null)
return null;

return key.RSA;

static bool MyCertificateValidationCallback(X509Certificate certificate, int[] certificateErrors)
* CertVALID = 0,
* CertEXPIRED = -2146762495,//0x800B0101
* CertVALIDITYPERIODNESTING = -2146762494,//0x800B0102
* CertROLE = -2146762493,//0x800B0103
* CertPATHLENCONST = -2146762492,//0x800B0104
* CertCRITICAL = -2146762491,//0x800B0105
* CertPURPOSE = -2146762490,//0x800B0106
* CertISSUERCHAINING = -2146762489,//0x800B0107
* CertMALFORMED = -2146762488,//0x800B0108
* CertUNTRUSTEDROOT = -2146762487,//0x800B0109
* CertCHAINING = -2146762486,//0x800B010A
* CertREVOKED = -2146762485,//0x800B010C
* CertUNTRUSTEDTESTROOT = -2146762484,//0x800B010D
* CertREVOCATION_FAILURE = -2146762483,//0x800B010E
* CertCN_NO_MATCH = -2146762482,//0x800B010F
* CertWRONG_USAGE = -2146762481,//0x800B0110
* CertUNTRUSTEDCA = -2146762480,//0x800B0112

//error: -2146762487, -2146762481
return true;