473,836 Members | 1,471 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

7.3.3 drop table takes very long time

I'm trying to drop a table and it's taking a very long time. There has been
a lot of modification to the table and it has a lot of old data still being
used as a result of not using the vacuum function enough. I ran an insert
into and it was taking a long time (ran for about 48 hours) so I aborted it.
I tried vacuuming it and that ran for about the same amount of time before I
aborted. I figured the fastest ting would be to drop the table and re-create
it.
I tried running a pg_dump and it ran for about 4 days without putting any of
the data to the output file.
I started the drop table command yesterday and it's been running for almost
24 hours.
I know it's processing because it's using up the CPU and I can run
transactions on all of the other tables except this one. It has about
132,000 records in the table.
Any ideas on how I can speed this up?
Can I go into the /data directory and find the file that contains that table
and delete that? If so, how would I go about doing this?
Eric

_______________ _______________ _______________ _______________ _____
Expand your wine savvy — and get some great new recipes — at MSN Wine.
http://wine.msn.com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
5 8727
"Eric Freeman" <ej**@hotmail.c om> writes:
I started the drop table command yesterday and it's been running for almost
24 hours.


You're stuck in some kind of infinite loop --- there's no way that DROP
should take any noticeable amount of time. I'm guessing that the system
catalog entries for this particular table are corrupted somehow, but no
idea just how. It would be worth trying to track it down in case there
is a PG bug lurking.

Can you attach to the looping backend with a debugger and get a stack
trace for us?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2
Tom Lane wrote:
"Eric Freeman" <ej**@hotmail.c om> writes:

I started the drop table command yesterday and it's been running for almost
24 hours.


You're stuck in some kind of infinite loop --- there's no way that DROP
should take any noticeable amount of time. I'm guessing that the system
catalog entries for this particular table are corrupted somehow, but no
idea just how. It would be worth trying to track it down in case there
is a PG bug lurking.

Can you attach to the looping backend with a debugger and get a stack
trace for us?

Is there any possibility that he's got an open transacation sitting out
there for days holding a lock on that table?

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #3
Mike Mascari <ma*****@mascar i.com> writes:
Is there any possibility that he's got an open transacation sitting out
there for days holding a lock on that table?


Good thought ... but if that was the issue then the DROP would just be
sleeping waiting for the lock, and Eric did say it was consuming CPU ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #4
On Thursday 08 January 2004 9:14 am, Mike Mascari wrote:
Tom Lane wrote:
"Eric Freeman" <ej**@hotmail.c om> writes:
I started the drop table command yesterday and it's been running
for almost 24 hours.


You're stuck in some kind of infinite loop --- there's no way that
DROP should take any noticeable amount of time. I'm guessing
that the system catalog entries for this particular table are
corrupted somehow, but no idea just how. It would be worth
trying to track it down in case there is a PG bug lurking.

Can you attach to the looping backend with a debugger and get a
stack trace for us?


Is there any possibility that he's got an open transacation sitting
out there for days holding a lock on that table?

Mike Mascari


Yesterday I had someone drop a table while a pg_dumpall was running.
The drop didn't complete till the dump was done.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #5
Steve Crawford <sc*******@pinp ointresearch.co m> writes:
On Thursday 08 January 2004 9:14 am, Mike Mascari wrote:
Is there any possibility that he's got an open transacation sitting
out there for days holding a lock on that table?
Yesterday I had someone drop a table while a pg_dumpall was running.
The drop didn't complete till the dump was done.


Yup, because pg_dump takes an AccessShareLock (reader's lock) on every
table it intends to dump. But the process wanting to drop the table
would have been blocked on the lock, and would not have been chewing any
CPU time while it waited. Eric seems to be seeing something different.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1773
by: Bennett Haselton | last post by:
I have a MySQL query running inside a CGI script on my site that, at random intervals, seems to take 10-20 seconds to complete instead of less than 1 second. I spent so much time trying to track this down that I wrote a script which runs once a minute on the site, which (a) captures the output of "ps auwx" (listing all processes) so I can see if that has anything to do with the slowdown; (b) times how long it takes to run the query, and;...
3
10988
by: David Link | last post by:
Hi All, Here's a Conditional drop_table func for those interested. There was a thread on this a long time back. We do this all the time : DELETE TABLE sales; CREATE TABLE sales (...);
2
2632
by: tom horner | last post by:
Any ideas on why drop and create alias statements would take a long time? We recently went through an upgrade of our production database, which included alter statements to table structures, create tables, drop tables, and data inserts, deletes, and updates. Also, we dropped and recreated all the alias's we had on the tables and views to those tables. We are on UDB 7.2, FP7, on AIX. Everything took about the same time as in our...
5
3239
by: IdeaMan | last post by:
Windows 2000 Access 97 I am working on an issue tracking DB, where I need to link (not attach due to size)screen prints of various system errors. I have created a public folder on a network drive to store these images, under the assumption that no one will originally save them to that location. To resolve this, I need to copy the file to this location without possibility of user error in selecting the correct folder (the DB is to be...
2
3078
by: Dragon | last post by:
When I run a make-table query, the results take over 1 minute to return. The query is making a table that has 12,000 records in it. If I run the make-table query ONLY to see the records that will be put into the table, this only takes 5 seconds. It's the making of the table that takes so long. Does anyone know what I should do to improve the return time? Also, the new table has a field in it that is an auto number. However, the auto...
2
2956
by: VM | last post by:
When I display data to a Windows datagrid I usually fill the underlying table (in another class) and then, once it contains all the data, I attach it to the grid. But there are some processes that involve large amounts of data so the user won't be able to see anything in the grid until after the table's already filled up (usually 40-60 secs with these large processes). Is it possible (without changing the process of filling the table...
2
2479
by: tpaulson | last post by:
I have a couple of DIV's that I hide/display based on radio buttons. On the DIV's, I have multiple drop down boxes. The source shows that they are populated, but I can't make them drop down. Only click on the Unplatted Radio button, the rest isn't functional, yet. Any ideas how to make these drop down in FF. This works in IE. Here is the code: <HTML> <HEAD> <TITLE>Door County Register of Deeds - Tract Inquiry</TITLE>
4
9302
by: TycoonUK | last post by:
Hi, As I do not have IE7 on my computer, I was wondering if there is a fault in my CSS Menu when using IE7. Please can someone look at my site - http://www.worldofmonopoly.co.uk and tell me if it works, and if it does not, tell me why it does not work. Thanks.
14
4345
by: Philth | last post by:
Hi there, I've essentially got a form with several drop down, each populated by columns in various tables. The populating bit works fine - the column rows appear as they should in the menu. Ideally the user needs to make their selections, and enter the form into a new database table. The problem is, when the selection is entered into the new table, only the first word of the string is entered. For example, the drop down may have...
0
9816
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10840
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10546
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10588
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.