Postgresql 9 table lock resolution

Question

I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute?

From my limited understanding I think it's a table level lock that isn't releasing. The table pg_locks shows a single AccessExclusiveLock that has its granted column set to f. Running ps aux | grep postgres confirms that there is a postgres ALTER TABLE waiting.

If it helps the statement is one that's been created by the Django ORM (South in particular).

Thanks.

Answer 1

There is most probably another transaction open that did a select on that table.

Look for sessions that are " in transaction".

Make sure they end their transaction (so that the sessions shows "" or if you cannot do that, kill them with pg_terminate_backend()

Answer 2

If you have a lot of sessions that are in transaction you can use a query to list the processes blocked and the PID these are blocking on.

Make sure you don't run pg_dump while trying to apply your schema migrations.