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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Tags: , ,

Leave a Reply