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.

Improve this question
3
  • 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, 2011 at 5: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, 2011 at 5:27
  • Alright thanks for the advice. I think I am going to go to sleep now and test that out tomorrow.
    – thebeagle
    Aug 17, 2011 at 9:22

1 Answer 1

Reset to default
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.

Improve this answer
5
  • What would truncating entail? And how would I do it?
    – thebeagle
    Aug 17, 2011 at 4: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, 2011 at 4:49
  • See the Postgres documentation for information on the TRUNCATE command -- 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, 2011 at 5: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, 2011 at 5: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, 2011 at 7:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .