7.3.3 drop table takes very long time

[Eric Freeman]

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

[Tom Lane]

Re: 7.3.3 drop table takes very long time

"Eric Freeman" <ejf7@hotmail.c om> writes:[color=blue]
> I started the drop table command yesterday and it's been running for almost
> 24 hours.[/color]

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

[Mike Mascari]

Re: 7.3.3 drop table takes very long time

Tom Lane wrote:
[color=blue]
>"Eric Freeman" <ejf7@hotmail.c om> writes:
>
>[color=green]
>>I started the drop table command yesterday and it's been running for almost
>>24 hours.
>>
>>[/color]
>
>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?
>
>[/color]
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 majordomo@postg resql.org)

[Tom Lane]

Re: 7.3.3 drop table takes very long time

Mike Mascari <mascarm@mascar i.com> writes:[color=blue]
> Is there any possibility that he's got an open transacation sitting out
> there for days holding a lock on that table?[/color]

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

[Steve Crawford]

Re: 7.3.3 drop table takes very long time

On Thursday 08 January 2004 9:14 am, Mike Mascari wrote:[color=blue]
> Tom Lane wrote:[color=green]
> >"Eric Freeman" <ejf7@hotmail.c om> writes:[color=darkred]
> >>I started the drop table command yesterday and it's been running
> >> for almost 24 hours.[/color]
> >
> >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?[/color]
>
> Is there any possibility that he's got an open transacation sitting
> out there for days holding a lock on that table?
>
> Mike Mascari[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: 7.3.3 drop table takes very long time

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

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