On 13rd of November 2019, Amit Kapila committed patch:

Introduce the 'force' option for the Drop Database command.
 
 
This new option terminates the other sessions connected to the target
database and then drop it.  To terminate other sessions, the current user
must have desired permissions (same as pg_terminate_backend()).  We don't
allow to terminate the sessions if prepared transactions, active logical
replication slots or subscriptions are present in the target database.
 
Author: Pavel Stehule with changes by me
 
Ryan Lambert and Amit Kapila
Discussion: https://postgr.es/m/CAP_rwwmLJJbn70vLOZFpxGw3XD7nLB_7+NKz46H5EOO2k5H7OQ@mail.gmail.com

This is great.

Whenever I work on some new app/database, I pretty often recreate test db.

And this is not always easy. Quite often I run:

=$ DROP DATABASE app;

Just to wait, and wait, and only a minute or so later realize that DROP DATABASE is waiting for some connection to app database to close. Which generally is either the application or some of my forgotten psql sessions.

Now, this problem is no more, as I can:

$ DROP DATABASE depesz WITH (force);
DROP DATABASE

And the other session will, on next query, simply see:

=$ SELECT 1;
FATAL:  terminating connection due TO administrator command
server closed the connection unexpectedly
        This probably means the server TERMINATED abnormally
        BEFORE OR while processing the request.
The connection TO the server was lost. Attempting reset: Failed.

Which, in my test/dev environment, is perfectly acceptable.

Obviously I could have done it myself earlier, by doing appropriate pg_terminate_backend() calls, but this way is much nicer. Thanks Pavel, Ryan, and Amil 🙂