Environment

LLBLGen Pro 4.0 Framework: LLBLGen Pro Runtime Framework Template group: Adapter Database: Postgresql 9.2

Background

An application which accesses a PostgreSQL database via LLBLGen Pro Adapter also provides a facility to restore the database. Restoring the database requires exclusive access to the database.

Problem

If the restore is attempted after a DataAccessAdapter has accessed the database, the restore fails with error message

'database "MyDatabase" is being accessed by other users', 

even though the DataAccessAdapter is disposed of immediately after each database access.

Investigation

The disposed DataAccessAdapter leaves a process running on the PostgreSQL server. (To view PostgreSQL server processes: pgAdmin -> Tools -> Server Status.) The process's database is the database that was accessed by the DataAccessAdapter, e.g. "MyDatabase". The process's last query is "unlisten *".

If the process is killed before the restore is run, the restore works.

Doing a garbage collection after disposing of the DataAccessAdapter does not make the process disappear. But closing the application does.

Circumvention

To get round the problem, the application has been amended to kill the orphan process, if found, immediately before the restore, like this:

using (var connection = new NpgsqlConnection(
        "Server=localhost;Port=5433;Database=postgres;User Id=postgres;Password=youmustbejoking")) {
    connection.Open();
    var fetchProcessIdCommand = new NpgsqlCommand(
        "select pid from pg_stat_activity where datname = 'MyDatabase' and query = 'unlisten *'", 
        connection);
    int? processed =
        (int?)fetchProcessIdCommand.ExecuteScalar();
    if (processId != null) {
        // Process found
        var killProcessCommand = new NpgsqlCommand(
            "select pg_terminate_backend(" + processId + ")",
            connection);
        bool hasProcessBeenKilled =
            (bool)killProcessCommand.ExecuteScalar();
        if (!hasProcessBeenKilled) {
            throw new DataException(
                "Failed to kill orphan PostgreSQL server process " + processId + ".");
        }
    }
}

Objective

I would like to find a less messy and brutal way, probably using the LLBLGen Pro Runtime Framework, of removing the orphan server-side process. I'm surprised that disposing of the DataAccessAdapter does not do it. On the other hand, the fact that the server-side process disappears when the application closes suggests that there should be a better way of removing the server-side process from within the application.

Isn't this due to connection pooling? THe ADO.NET provider keeps native connections open (from client to server, so the server sees this as a real 'live' connection) and when a new connection is created at the .NET level, that native connection is mapped into that ado.net connection and used. When the ado.net connection is closed and disposed (and it is disposed, we checked ) the native connection is cleared and returned to the pool (but kept alive!) so a new ado.net connection can re-use it. This leaves a real connection alive on the server though (as from the server's PoV it simply has a native connection from a client). Other databases show the same thing: connections still open while the ado.net connection has been closed.

If you don't want this, switch off connection pooling through the connection string. (I think npgsql allows you to do that).

Switching off connection pooling through the connection string fixed the problem: I just had to add ";Pooling=False" to the connection string. The server process is no longer left running after each use and disposal of a DataAccessAdapter. For this particular application, doing without connection pooling should have no noticeable impact on performance. So it is a viable fix in this case. Thanks, Franz!

Cheers, Simon