5

I am kind of new to Postgresql and server administration so I am not quite sure if this is normal behavior or not. Right now I am trying to drop a table with the cascade option.

The command I used was 

DROP TABLE library_genre CASCADE;

It's been about half an hour and it is still saying 

NOTICE:  drop cascades to constraint radio_logs_entry_genre_id_fkey on table logs_entry

I am not really sure how to check how big the database is, but I can't imagine is being too big. Do you think there could be some sort of corruption that is causing it to take so long.

Any suggestions to figure this out would be greatly appreciated. What sort of commands should I be using to figure out whats going on?

Edit: New Info

I ran this on a pg_dump I did before I tried dropping the table

du -k pg_dump_file

and it gave me 27712. I am not sure what data size it is returning though. If it is kilobytes that is only 27 megabytes and that doesn't seem like it would take so long modify.

Edit #2. I ran top and it looks like postgres isn't really using much CPU or memory.

thebeagle
  • 151
  • 1
  • 1
  • 5
  • Remember that the size of a dump does not tell you much about the complexity of a database (indexes, foreign key references, etc.) - It's possible to contrive a <1K dump file where you cannot `DELETE` from a table due to circular constraints. – voretaq7 Aug 17 '11 at 05:16
  • If you're not using much CPU time you may be blocked waiting on a lock -- You can check out the `pg_locks` and `pg_stat_activity` system views to see if that's the case, and note that `TRUNCATE` will also block if that's the case. – voretaq7 Aug 17 '11 at 05:27
  • Alright thanks for the advice. I think I am going to go to sleep now and test that out tomorrow. – thebeagle Aug 17 '11 at 09:22

1 Answers1

4

Dropping your table is cascading through a constraint - Postgres is most likely bound up examining rows in that referencing table to determine what it needs to do about them.

To speed things up you can drop your constraints first, and/or TRUNCATE the table you want to drop.

voretaq7
  • 79,879
  • 17
  • 130
  • 214
  • What would truncating entail? And how would I do it? – thebeagle Aug 17 '11 at 04:48
  • Also do you think I should stop the process now and figure out what the problem is or keep it running? I tried to figure out the size of the database but it wouldn't let me because of the DROP TABLE running. – thebeagle Aug 17 '11 at 04:49
  • See the Postgres documentation for information on the `TRUNCATE` command -- http://www.postgresql.org/docs/8.4/static/sql-truncate.html -- It is basically a "super delete", but you should note the caveats and warnings in the documentation before using it. You should be able to cancel the current `DROP TABLE` process without any ill effects. – voretaq7 Aug 17 '11 at 05:06
  • `TRUNCATE` will happily offer to `TRUNCATE` your dependents -- if that isn't what you want you will need to drop the constraint first. – voretaq7 Aug 17 '11 at 05:22
  • I tried the truncate with cascade and it totally screwed up the database and broke the web application. Do you think there is an other way to speed up the process? How would I go about dropping the constraint first? Sorry for all the questions. And thanks for your help so far. – thebeagle Aug 17 '11 at 07:48