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?

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.