postgresql - Does dropping a database have to be done not in any transaction?

Question

From https://wiki.postgresql.org/wiki/Psycopg2_Tutorial

PostgreSQL can not drop databases within a transaction, it is an all or nothing command. If you want to drop the database you would need to change the isolation level of the database this is done using the following.

conn.set_isolation_level(0)

You would place the above immediately preceding the DROP DATABASE cursor execution.

Why "If you want to drop the database you would need to change the isolation level of the database"? In particular, why do we need to change the isolation level to 0? (If I am correct, 0 means psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)

From https://stackoverflow.com/a/51859484/156458

The operation of destroying a database is implemented in a way which prevents undoing it - therefore you can not run it from inside a transaction because transactions are always undoable. Also keep in mind that unlike most other databases PostgreSQL allows almost all DDL statements (obviously not the DROP DATABASE one) to be executed inside a transaction.

Actually you can not drop a database if anyone (including you) is currently connected to this database - so it does not matter what is your isolation level, you still have to connect to another database (e.g. postgres)

"you can not run it from inside a transaction because transactions are always undoable". Then how can I drop a database not from inside a transaction?

---

I found my answer at https://stackoverflow.com/a/51880577/156458

Answer 1

I'm unfamiliar with psycopg2 so I can only provide steps to be performed.

Steps to be taken to perform DROP DATABASE from Python:

1. Connect to a different database, which you don't want to drop
2. Store current isolation level in a variable
3. Set isolation level to 0
4. Execute DROP DATABASE query
5. Set isolation level back to original (from #2)

---

Steps to be taken to perform DROP DATABASE from PSQL:

1. Connect to a different database, which you don't want to drop
2. Execute DROP DATABASE query

Code in psql

c second_db
DROP DATABASE first_db;

---

Remember, that there can be no live connections to the database you are trying to drop.