Can't DROP NOT NULL from postgreSQL command
The only thing that can block a short operation like this is a conflicting lock.
An ALTER TABLE
statement like the one you are running requires a (short) ACCESS EXCLUSIVE
lock on the table – you cannot change the table definition while it is in use. Now any concurrent transaction that has ever used the table holds at least an ACCESS SHARE
lock on the table until the transaction ends, and that will block your ALTER TABLE
.
To find out which session blocks you, do this:
-
Before you run
ALTER TABLE
, executeSELECT pg_backend_pid();
to find out your session process number.
-
Run the
ALTER TABLE
that hangs. -
Start another database session and run
SELECT pg_blocking_pids(12345);
where 12345 is the result from the query above. Now you know which sessions are blocking you.
-
Close the blocking transactions and try again. To forcibly end a hanging transaction, you can use
SELECT pg_cancel_backend(23456);
where 23456 is a process number found in the previous statement.
You should fix all bugs in the application that keep transactions open. This is always a bug. If you have no better way, set the database parameter idle_in_transaction_session_timeout
so that transactions that stay open too long get closed by the server.