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);