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.

I have many databases on the same server, all with same templates. When I execute truncate command on exceptions table in each database, it works fine and executes immediately but on database named db_edr_s1 the same truncate command on exceptions table is taking too much, approx. 5 minutes.

The version is 9.1.

Any other info needed?

share
    
Is the table being used by any other process simultaneously? –  Vaibhav Desai Feb 27 '13 at 6:31
    
no this table is accessed by single script only. and same happen when I try this from psql command line without any script running. –  sharafjaffri Feb 27 '13 at 6:57
1  
What do you get from SELECT * FROM pg_locks WHERE relation = 'exceptions'::regclass::oid? –  dezso Feb 27 '13 at 10:22
comments disabled on deleted / locked posts

migration rejected from stackoverflow.com Sep 2 '13 at 21:55

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by dezso, Mark Storey-Smith, Jon Seigel, Max Vernon, StanleyJohns Sep 2 '13 at 21:55

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Mark Storey-Smith, Jon Seigel, Max Vernon, StanleyJohns
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

This is almost certainly a locking issue as Dezso's comment above implies. PostgreSQL is rather fast regarding truncate so the only possible issue I can think of (assuming you don't have major I/O issues) are locks.

Truncate involves replacing the table with a stub file and then removing the old file when the transaction commits. It is very fast, and if it is taking over 5 min this suggests the db is waiting for a lock.

share
add comment