npgsql: "database ... is being accessed by other users" error trying to drop database even after disposing of the connection

This is expected and by design, so closing the issue. To stop other users from accessing the database without cleaning the pool use pg_terminate_backend from the script.

I think the Close docs are already pretty clear on this point:

Releases the connection. If the connection is pooled, it will be returned to the pool and made available for re-use. If it is non-pooled, the physical connection will be closed.

It follows pretty clearly that if pooling is on, the physical connection isn’t closed etc.

But I agree that ClearPool and ClearAllPools could benefit from better docs - I’ve added some.

It closes the physical connection only if there’s no pooling. Otherwise, it puts it into the pool. The NpgsqlConnection changes the state too in any case.

Thanks! Will take a look.

Here you go:

using Npgsql;

namespace NpgsqlChangeDb
{
    class Program
    {
        const string ConnString = "Host=localhost;Database=postgres;Username=...;Password=...";

        static void Main(string[] args)
        {
            DropDb(); // Drop any DB left from a previous failed test
            CreateAndUseDb();
            DropDb(); // Npgsql.PostgresException: '55006: database "create_drop_test" is being accessed by other users'
        }

        private static void CreateAndUseDb()
        {
            using var connection = new NpgsqlConnection(ConnString);
            connection.Open();

            using var createDbCommand = new NpgsqlCommand("CREATE DATABASE create_drop_test", connection);
            createDbCommand.ExecuteNonQuery();

            connection.ChangeDatabase("create_drop_test"); // Use the new database

            //NpgsqlConnection.ClearPool(connection); // This allows the DB to be dropped
            connection.ChangeDatabase("postgres"); // This doesn't!
        }

        private static void DropDb()
        {
            using var connection = new NpgsqlConnection(ConnString);
            connection.Open();

            using var dropDbCommand = new NpgsqlCommand("DROP DATABASE IF EXISTS create_drop_test", connection);
            dropDbCommand.ExecuteNonQuery();
        }
    }
}

It exists, take a look at NpgsqlConnection.ClearPool which closes all idle connections in the pool.

Consider the ChangeDatabase method. It just closes the connection, detaches it from the pool and opens it again with the replaced value of the Database connection string parameter. Other connections in the pool won’t be affected.