ALTER TABLE … ADD COLUMN on a small table takes forever, pg_stat_activity doesn't show any queries on this table

Question

So this is the query that hangs forever:

ALTER TABLE tasks
ADD COLUMN in_progress BOOLEAN NOT NULL DEFAULT FALSE;

The table tasks has less than 20,000 rows and is queried once every 5 minutes or so.

I checked pg_stat_activity like 10 times and it never shows any queries locking the table:

SELECT *
FROM pg_stat_activity
WHERE query LIKE '%tasks%';

--- No results

I tried a vacuum but it didn't help:

VACUUM (VERBOSE, ANALYZE) tasks;

I also tried to add the column without the constraint and default, which I would expect to be pretty much instant on such table, but the query was running for 1 minute when I stopped it:

ALTER TABLE tasks
ADD COLUMN in_progress BOOLEAN;

I ran the query on another table (~1000 rows) in the same period of time and it was instant.

Any idea?

PostgreSQL 11.13

Queries executed via DBeaver (I invalidated/reconnected several times just in case).

Answer 1

Somebody must hold a lock on the table, which means that you have an open transaction. That is a bug; no transaction should ever stay open.

To find out which sessions block your statement:

1. Before you run the ALTER TABLE, run

   SELECT pg_backend_pid();
   

2. Run the hanging ALTER TABLE.

3. Start a new database session and run

   SELECT pg_blocking_pids(12345);
   

   where 12345 is the result from the previous query.

4. Kill the sessions you found with the previous query with

   SELECT pg_terminate_backend(54321);
   

Answer 2

Just like what @Laurenz has mentioned, locking is happened. Hence no transaction can be processed during the lock period. If you must release the lock period, here something you can do.

Find out the active processes

SELECT datname, pid, usename, query FROM pg_stat_activity WHERE STATE ='active';

Kill the process based on PID

SELECT pg_cancel_backend(YourPID);

SELECT pg_cancel_backend(20280);