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



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



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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2