2

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?

6
  • 1
    Include the PostgreSQL version number with a question like that. Nov 25, 2011 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. Nov 26, 2011 at 3:54
  • version included
    – Frost.baka
    Nov 26, 2011 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. Nov 26, 2011 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. Nov 26, 2011 at 15:38

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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