ALTER query very slow on tiny table in PostgreSQL

Question

I've got PostgreSQL 9.2 and a tiny database with just a bit of seed data for a website that I'm working on.

The following query seems to run forever:

ALTER TABLE diagnose_bodypart ADD description text NOT NULL;

diagnose_bodypart is a table with less than 10 rows. I've let the query run for over a minute with no results. What could be the problem? Any recommendations for debugging this?

Answer 1

Adding a column does not require rewriting a table (unless you specify a DEFAULT). It is a quick operation absent any locks. pg_locks is the place to check, as Craig pointed out.

In general the most likely cause are long-running transactions. I would be looking at what work-flows are hitting these tables and how long the transactions are staying open for. Locks of this sort are typically transactional and so committing transactions will usually fix the problem.