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

The 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).

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment