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.

We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about half the rows in the table -- old data. Now I want to simply remove the multi-column unique constraint, so I executed this SQL:

alter table my_big_table drop constraint my_constraint_name

However, I started the command 10+ hours ago, and it's still running with no sign of abating. Why would dropping a constraint be taking so long? Is there anything I'm missing, or any way to perform actions like this with some kind of progress or status indication?

(I'm using PostgreSQL 9.0 on a Windows 64-bit system, and I generally connect to it using pgAdmin, though I can use the command-line client or connect programmatically too.)

share|improve this question

migrated from stackoverflow.com Mar 9 '12 at 12:41

This question came from our site for professional and enthusiast programmers.

    
At a guess it is reindexing the table completely. If so don't ask me why it thinks it needs to do that. –  EJP Mar 9 '12 at 5:32
    
What version of PG? –  Bob Mar 9 '12 at 5:37
    
See what other activity is going on. –  Bob Mar 9 '12 at 5:39
1  
EJB - if you don't know, don't guess. There's no point in cluttering up stackoverflow with obviously wrong information –  Richard Huxton Mar 9 '12 at 9:04
1  
@benhoyt: did you check if it's waiting for a lock? Look at pg_stat_activity to see if you have transactions that keep locks on the table –  a_horse_with_no_name Mar 9 '12 at 9:11

1 Answer 1

up vote 6 down vote accepted

Step one - go into task manager, have a look and see if anything is actually happening. If you can't see a lot of cpu and/or disk bandwidth then it's not doing anything.

Step two - check what PostgreSQL says is happening. There are two system views useful here: pg_stat_activity and pg_locks. See what's in both. The database/relation numbers in pg_locks are internal OIDs - try SELECT OID,* FROM pg_class to see which tables/indexes are being locked.

My guess is that the system is idle and it's just waiting for an exclusive lock on the table in question and something else is blocking it. The two system views should show you what is happening though.

share|improve this answer
    
Yes, this was it -- thanks very much. It seems that there was an autovacuum happening, causing the table to be locked for the drop constraint. Still not 100% sure of the specifics. It's strange -- even when I now have autovacuum turned off and initiate a manual vacuum, it's not using any CPU at all. –  Ben Hoyt Mar 9 '12 at 15:37

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.