So this is the query that hangs forever:
ALTER TABLE tasks ADD COLUMN in_progress BOOLEAN NOT NULL DEFAULT FALSE;
tasks has less than 20,000 rows and is queried once every 5 minutes or so.
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.
Queries executed via DBeaver (I invalidated/reconnected several times just in case).