Search code examples
sqlpostgresqlnotnull

Can't DROP NOT NULL from postgreSQL command

I try to use this command to DROP the NOT NULL from "name" column, but it's get stunned and not return anything.

ALTER TABLE "school_students" ALTER COLUMN "name" DROP NOT NULL;

Here's my "school_students" table structure: enter image description here

UPDATE 1:

After running query select now() - query_start, pid, query from pg_stat_activity where state != 'idle' order by 1 desc; to check if uncommitted transaction exist, here's the result:

enter image description here

Please help, thanks.

Solution

  • 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, execute

      SELECT 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.