ALTER query is very slow in a tiny table in PostgreSQL

Question

I have PostgreSQL 9.2 and a tiny database with a bit of raw data for the website I'm working on.

The following query seems to work forever:

ALTER TABLE diagnose_bodypart ADD description text NOT NULL; 

diagnose_bodypart is a table with less than 10 rows. I allowed the request to work for more than a minute without any results. What could be the problem? Any debugging recommendations?

Answer 1

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

In general, the most likely cause is long-term transactions. I would see what workflows get into these tables and how long the transactions remain. Locks of this type are usually transactional, and therefore transactional transactions usually fix the problem.