Thread: dropping a master table and all of its partitions?
Hey everyone, I created a master table, and created ~2000 partitions for it. *no* data is in any of these partitions. I am trying to drop the master and all of the partitions with a cascade: DROP TABLE master CASCADE; Except after about 30 seconds my memory usage (4GB) jumps to 99%, and after about 10 minutes it kills over and drops my connection. How do you delete a master and all of its partitions? Thanks! George
Did you use some kind of sensical naming convention for the child tables? If so, couldn't you write a script to loop through and drop them one at a time?
On Feb 26, 2007, at 6:42 PM, George Nychis wrote:
Hey everyone,I created a master table, and created ~2000 partitions for it.*no* data is in any of these partitions.I am trying to drop the master and all of the partitions with a cascade:DROP TABLE master CASCADE;Except after about 30 seconds my memory usage (4GB) jumps to 99%, and after about 10 minutes it kills over and drops my connection.How do you delete a master and all of its partitions?Thanks!George---------------------------(end of broadcast)---------------------------TIP 4: Have you searched our list archives?
erik jones <erik@myemma.com>
sofware developer
615-296-0838
emma(r)
Sure I can do that, but why is this happening? Is this normal behavior? - George Erik Jones wrote: > Did you use some kind of sensical naming convention for the child > tables? If so, couldn't you write a script to loop through and drop > them one at a time? > > On Feb 26, 2007, at 6:42 PM, George Nychis wrote: > >> Hey everyone, >> >> I created a master table, and created ~2000 partitions for it. >> >> *no* data is in any of these partitions. >> >> I am trying to drop the master and all of the partitions with a cascade: >> DROP TABLE master CASCADE; >> >> Except after about 30 seconds my memory usage (4GB) jumps to 99%, and >> after about 10 minutes it kills over and drops my connection. >> >> How do you delete a master and all of its partitions? >> >> Thanks! >> George >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org/ > > erik jones <erik@myemma.com <mailto:erik@myemma.com>> > sofware developer > 615-296-0838 > emma(r) > > >
George Nychis wrote: > Sure I can do that, but why is this happening? Is this normal behavior? Well that is the better question. If it is indeed doing what you say it is doing, I would say it is a bug. However you have not mentioned several important items, like what postgresql version you are running. Joshua D. Drake > > - George > > > Erik Jones wrote: >> Did you use some kind of sensical naming convention for the child >> tables? If so, couldn't you write a script to loop through and drop >> them one at a time? >> >> On Feb 26, 2007, at 6:42 PM, George Nychis wrote: >> >>> Hey everyone, >>> >>> I created a master table, and created ~2000 partitions for it. >>> >>> *no* data is in any of these partitions. >>> >>> I am trying to drop the master and all of the partitions with a cascade: >>> DROP TABLE master CASCADE; >>> >>> Except after about 30 seconds my memory usage (4GB) jumps to 99%, and >>> after about 10 minutes it kills over and drops my connection. >>> >>> How do you delete a master and all of its partitions? >>> >>> Thanks! >>> George >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 4: Have you searched our list archives? >>> >>> http://archives.postgresql.org/ >> >> erik jones <erik@myemma.com <mailto:erik@myemma.com>> >> sofware developer >> 615-296-0838 >> emma(r) >> >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
I tend to forget the important details ;) gnychis@sn001:~$ psql --version psql (PostgreSQL) 8.1.8 contains support for command-line editing gnychis@sn001:~$ uname -a Linux sn001 2.6.17-10-server #2 SMP Tue Dec 5 21:17:26 UTC 2006 x86_64 GNU/Linux - George Joshua D. Drake wrote: > George Nychis wrote: >> Sure I can do that, but why is this happening? Is this normal behavior? > > Well that is the better question. If it is indeed doing what you say it > is doing, I would say it is a bug. However you have not mentioned > several important items, like what postgresql version you are running. > > Joshua D. Drake > > >> - George >> >> >> Erik Jones wrote: >>> Did you use some kind of sensical naming convention for the child >>> tables? If so, couldn't you write a script to loop through and drop >>> them one at a time? >>> >>> On Feb 26, 2007, at 6:42 PM, George Nychis wrote: >>> >>>> Hey everyone, >>>> >>>> I created a master table, and created ~2000 partitions for it. >>>> >>>> *no* data is in any of these partitions. >>>> >>>> I am trying to drop the master and all of the partitions with a cascade: >>>> DROP TABLE master CASCADE; >>>> >>>> Except after about 30 seconds my memory usage (4GB) jumps to 99%, and >>>> after about 10 minutes it kills over and drops my connection. >>>> >>>> How do you delete a master and all of its partitions? >>>> >>>> Thanks! >>>> George >>>> >>>> ---------------------------(end of broadcast)--------------------------- >>>> TIP 4: Have you searched our list archives? >>>> >>>> http://archives.postgresql.org/ >>> erik jones <erik@myemma.com <mailto:erik@myemma.com>> >>> sofware developer >>> 615-296-0838 >>> emma(r) >>> >>> >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> > >
George Nychis wrote: > I tend to forget the important details ;) > > gnychis@sn001:~$ psql --version > psql (PostgreSQL) 8.1.8 > contains support for command-line editing > gnychis@sn001:~$ uname -a > Linux sn001 2.6.17-10-server #2 SMP Tue Dec 5 21:17:26 UTC 2006 x86_64 > GNU/Linux Just tried it here, worked without a hitch. create a table "parent", then 2000 children for i in `seq 1 2000`; do psql -c "create table child_$i (b int) inherits (parent)"; done then DROP TABLE parent CASCADE and it took some seconds. I have 1 GB of physical RAM here. You're going to give us a lot more details ... -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
"Joshua D. Drake" <jd@commandprompt.com> writes:
> George Nychis wrote:
>> Sure I can do that, but why is this happening? Is this normal behavior?
> Well that is the better question. If it is indeed doing what you say it
> is doing, I would say it is a bug. However you have not mentioned
> several important items, like what postgresql version you are running.
Or even more to the point, the table schemas. I would guess that this
could be related to any number of things; perhaps the number of foreign
keys involved, to take one example.
In short: please provide a test case.
regards, tom lane
George Nychis <gnychis@cmu.edu> writes:
> Here is an exact script which generates this every single time...
> After you're done running the ruby script:
> DROP TABLE testflows CASCADE;
I tweaked the ruby script to emit the SQL commands into a script file,
which proved to issue 1765 CREATE TABLE commands (one parent and 1764
children) and 1764 CREATE RULE commands (one per child table). On my
test installation the creation script runs about 6m15s, while
"DROP TABLE testflows CASCADE" runs about 3m4s. While neither number
is exactly awe-inspiring, I'm not seeing why you think the DROP is
particularly broken?
regards, tom lane
Tom Lane wrote: > George Nychis <gnychis@cmu.edu> writes: >> Here is an exact script which generates this every single time... >> After you're done running the ruby script: >> DROP TABLE testflows CASCADE; > > I tweaked the ruby script to emit the SQL commands into a script file, > which proved to issue 1765 CREATE TABLE commands (one parent and 1764 > children) and 1764 CREATE RULE commands (one per child table). On my > test installation the creation script runs about 6m15s, while > "DROP TABLE testflows CASCADE" runs about 3m4s. While neither number > is exactly awe-inspiring, I'm not seeing why you think the DROP is > particularly broken? > > regards, tom lane Then maybe it's a bug in my version of postgresql, what version are you using? Because that DROP TABLE testflows CASCADE; runs for ~5 minutes and then dies on my side. It never finishes. - George >
George Nychis <gnychis@cmu.edu> writes:
> Tom Lane wrote:
>> While neither number
>> is exactly awe-inspiring, I'm not seeing why you think the DROP is
>> particularly broken?
>>
> Then maybe it's a bug in my version of postgresql, what version are you using?
I tried it on HEAD and 8.2 and didn't see a problem. Just now I retried
on 8.1 and indeed it eats memory :-(. It looks like the immediate
difference is the lack of this 8.2 fix:
2006-01-08 15:04 tgl
* src/backend/utils/cache/relcache.c: Avoid leaking memory while
reading toasted entries from pg_rewrite, and nail a couple more
system indexes into cache. This doesn't make any difference in
normal system operation, but when forcing constant cache resets
it's difficult to get through the rules regression test without
these changes.
There are quite a few other changes in 8.2 that are likely to help you
if you want to work with large numbers of partitions, so rather than
worrying about whether this change would be safe to back-patch, I'd
suggest an upgrade. Even with 8.2 though I'm not sure that you will get
decent performance with thousands of partitions. That infrastructure
is intended for maybe 10 to 100 partitions, not thousands ...
regards, tom lane