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 🙂