postgres alter table hangs with connection pooling

Question

Executing simple alter table add column hangs, this could be due to my application uses connection pooling which opens connections and probably locking tables. is there any way we can still execute alter table command in postgres while application is still running ?

Answer 1

Check the view pg_locks to see which concurrent transaction holds a lock the blocks the ALTER TABLE.

If your connection pool keeps connections hanging in the state “idle in transaction” (check pg_stat_activity), there is a bug in the connection pool or the application. You should fix that, because it causes other problems too, like table bloat because VACUUM cannot do its job.

A collection of statements to monitor locks is available in the Postgres Wiki:

• https://wiki.postgresql.org/wiki/Lock_Monitoring
• https://wiki.postgresql.org/wiki/Lock_dependency_information