How to do alter table add column without blocking the statement in Postgresql?

Question

How to alter the table to add a column without blocking the statement in Postgresql?

The below statement at the time of execution is getting stuck forever.

ALTER TABLE test_table ADD COLUMN test_column integer;

I tried killing all long-running queries but it didn't help. This table is in active use by the backend. Backend fires mostly <10 ms queries on the table.

The ALTER TABLE will be quite fast (a few milliseconds) but there is no way to avoid the exclusive lock it tries to acquire. — user330315
– user330315, Commented Oct 28, 2021 at 14:19

SunnyShah · Accepted Answer · 2021-10-28 15:11:18Z

5

Run below script:

while true
do
    date
    export PGPASSWORD='YOUR_DB_PASSWORD';
    psql -h DB_HOST_NAME -U USER_NAME DB_NAME -qX -v ON_ERROR_STOP=1 -f alter.sql && break
done

alter.sql looks like this.

begin;
    -- Try to acquire a lock on the table without blocking.
    lock table only test in ACCESS EXCLUSIVE MODE NOWAIT;
    -- Change timeout to higher unlimited so that migration can be complete.
    set statement_timeout = 0;
    ALTER TABLE test ADD COLUMN column_to_add INTEGER;     
commit;

Basically, this code continuously tries to acquire the lock without blocking any queries, run the alter command as soon as the lock is acquired.

Inspiration: https://www.depesz.com/2019/09/26/how-to-run-short-alter-table-without-long-locking-concurrent-queries/

edited Oct 28, 2021 at 15:11

answered Oct 28, 2021 at 15:06

SunnyShah

30.6k30 gold badges97 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eugen Konkov Over a year ago

Ok. This will lock after all current transactions are finished. But how about new transactions? Table will be locked until ALTER TABLE finish.

Collectives™ on Stack Overflow

How to do alter table add column without blocking the statement in Postgresql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related