Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Include the PostgreSQL version number with a question like that. –  Erwin Brandstetter Nov 25 '11 at 21:12
3  
look for locks in pg_locks that might be conflicting. There's some nice lock interpretation views in the postgresql wiki. –  Scott Marlowe Nov 26 '11 at 3:54
    
version included –  Frost.baka Nov 26 '11 at 11:55
1  
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. –  Jack Douglas Nov 26 '11 at 12:40
1  
Don't use KILL to terminate a database process, use pg_terminate_backend() ! KILL might kill your database and not just a single connection. –  Frank Heikens Nov 26 '11 at 15:38
show 1 more comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.