PostgreSQL 8.4 hangs on create table, reindex

Question

I have a rather small database(200Mb) which was running on ubuntu 10.10/amazon ec2 fine for a year almost but today I ran into the following problem: When I create a table that references other table like:

 CREATE TABLE "limit" (
 "id" serial NOT NULL PRIMARY KEY,
 "currency_id" integer NOT NULL UNIQUE REFERENCES "currency" ("id") 
DEFERRABLE INITIALLY DEFERRED,
 "min_amount" numeric(10, 2) NOT NULL,
 "max_amount" numeric(10, 2) NOT NULL
 )

The thread hangs and blocks all other threads eventually,(but I can still terminate it with kill -INT) though the other table (currency) is rather small like 24 entries. Sources out there say that I should reindex my table but then again, running

REINDEX table currency 

hangs forever. BTW creating the table without index, works like a charm. What can be the possible explanation and solution to this?

• Erwin Brandstetter: Include the PostgreSQL version number with a question like that.
• Scott Marlowe: look for locks in pg_locks that might be conflicting. There's some nice lock interpretation views in the postgresql wiki.
• Frost.baka: version included
• Jack Douglas: some thoughts: focus on the reindex problem first. "Hangs forever" - using CPU? io?. Have you tried recreating currency? btw postgres does not use threads - do you mean the process? Looks like a corruption issue to me - check filesystem etc for errors too.
• Frank Heikens: Don't use KILL to terminate a database process, use pg_terminate_backend() ! KILL might kill your database and not just a single connection.

Answer 1

if it is hanging forever, without significant CPU or IO activity, it is waiting on a lock. The two things I would try here would be checking pg_stat_activity to see if something else is running that might conflict. Maybe someone else has an open transaction in another window that has conflicting locks? Then I would check pg_locks.

Then I would look at pg_terminate_backend() on the offending process and try again.

However create table is a weird one. I can't think, off-hand, of any case where this should be blocked. Wondering what kinds of locks are required for this and under what circumstances they can conflict.

Edit: It occurs to me that the locking could be lower level than Pg-level locks and involve semaphores against shmem segments. But if that's the case your best bet is to take this up with Amazon since it would be more of an OS issue.