Thread: 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
"Eric Freeman" <ejf7@hotmail.com> 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
Tom Lane wrote: >"Eric Freeman" <ejf7@hotmail.com> 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
Mike Mascari <mascarm@mascari.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
On Thursday 08 January 2004 9:14 am, Mike Mascari wrote: > Tom Lane wrote: > >"Eric Freeman" <ejf7@hotmail.com> 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
Steve Crawford <scrawford@pinpointresearch.com> 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
I restarted Postgres and it dropped in a second or 2. I can't believe I didn't think of trying that before. Thanks for all the help. >From: Tom Lane <tgl@sss.pgh.pa.us> >To: Steve Crawford <scrawford@pinpointresearch.com> >CC: Mike Mascari <mascarm@mascari.com>, Eric Freeman <ejf7@hotmail.com>, >pgsql-general@postgresql.org >Subject: Re: [GENERAL] 7.3.3 drop table takes very long time Date: Thu, 08 >Jan 2004 16:26:32 -0500 >Received: from mc12-f8.hotmail.com ([65.54.167.144]) by mc12-s4.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 8 Jan 2004 13:31:21 -0800 >Received: from hosting.commandprompt.com ([207.173.200.216]) by >mc12-f8.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Thu, 8 Jan 2004 >13:30:36 -0800 >Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])by >hosting.commandprompt.com (8.11.6/8.11.6) with ESMTP id i08LRZk29714;Thu, 8 >Jan 2004 13:28:14 -0800 >Received: from localhost (neptune.hub.org [200.46.204.2])by >svr1.postgresql.org (Postfix) with ESMTP id 20237D1B4AFfor ><pgsql-general-postgresql.org@localhost.postgresql.org>; Thu, 8 Jan 2004 >21:27:25 +0000 (GMT) >Received: from svr1.postgresql.org ([200.46.204.71]) by localhost >(neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id >70015-01 for <pgsql-general-postgresql.org@localhost.postgresql.org>; Thu, >8 Jan 2004 17:26:36 -0400 (AST) >Received: from sss.pgh.pa.us (unknown [192.204.191.242])by >svr1.postgresql.org (Postfix) with ESMTP id A0509D1B48Afor ><pgsql-general@postgresql.org>; Thu, 8 Jan 2004 17:26:34 -0400 (AST) >Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])by >sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i08LQW19016036;Thu, 8 Jan >2004 16:26:33 -0500 (EST) >X-Message-Info: 820stLNiepTzJGdgouOWLmzTpS/lU6jjUO41cNhiU8g= >X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org >In-reply-to: <200401081306.23269.scrawford@pinpointresearch.com> >References: <BAY99-F32kpDasYutu4000304b7@hotmail.com> ><11871.1073581548@sss.pgh.pa.us> <3FFD8FED.3000900@mascari.com> ><200401081306.23269.scrawford@pinpointresearch.com> >Comments: In-reply-to Steve Crawford ><scrawford@pinpointresearch.com>message dated "Thu, 08 Jan 2004 13:06:23 >-0800" >Message-ID: <16035.1073597192@sss.pgh.pa.us> >X-Virus-Scanned: by amavisd-new at postgresql.org >X-Mailing-List: pgsql-general >Precedence: bulk >Return-Path: pgsql-general-owner+M55362@postgresql.org >X-OriginalArrivalTime: 08 Jan 2004 21:30:36.0245 (UTC) >FILETIME=[A76F8850:01C3D62E] > >Steve Crawford <scrawford@pinpointresearch.com> 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 _________________________________________________________________ Check your PC for viruses with the FREE McAfee online computer scan. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963