8

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.)

6
  • 1
    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, 2012 at 5:32
  • What version of PG?
    – Kuberchaun
    Mar 9, 2012 at 5:37
  • See what other activity is going on.
    – Kuberchaun
    Mar 9, 2012 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, 2012 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
    – user1822
    Mar 9, 2012 at 9:11

1 Answer 1

9

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.

2
  • 1
    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, 2012 at 15:37
  • Thanks that was super helpful! Aug 30, 2021 at 14:54

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.