Skip site navigation (1) Skip section navigation (2)

Re: dropping partitioned table waits forever

From: "Sriram Dandapani" <sdandapani(at)counterpane(dot)com>
To: "Jim Nasby" <jnasby(at)pervasive(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: dropping partitioned table waits forever
Date: 2006-08-19 06:46:17
Message-ID: 6992E470F12A444BB787B5C937B9D4DF0574FB36@ca-mail1.cis.local (view raw or flat)
Thread:
Lists: pgsql-admin
1. Create a parent table with a few child tables(atleast 2 child
partitions) partitioned by date(partitioning implemented by rules)

2. Continuously insert into the table at a high rate.

3. Create a script that queries the parent table every 5 minutes.

4. Run a drop child table(any child) command. The drop table goes into a
wait state AND causes the inserts to go into a wait state

5. Modify the drop table command to do a lock table nowait(so that if a
lock is not available, it immediately fails...you will notice that most
of the time, the lock cannot be obtained as there are other share locks
on the table.

The only consistent way to drop a child partitioned table on a live
system with a high access rate on the target parent table is to stop and
restart postgres and immediately run the drop table commands.

-----Original Message-----
From: Jim Nasby [mailto:jnasby(at)pervasive(dot)com] 
Sent: Friday, August 18, 2006 6:04 AM
To: Sriram Dandapani
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] dropping partitioned table waits forever

I think you'll probably need to submit a self-contained, reproducible  
test case.

On Aug 15, 2006, at 6:00 PM, Sriram Dandapani wrote:
> Postmaster was automatically restarted. (didn't crash).
>
> Rules are used to implement the partitioning (and the triggers as  
> well)
>
> Using postgres 8.1.2
>
> -----Original Message-----
> From: Jim C. Nasby [mailto:jnasby(at)pervasive(dot)com]
> Sent: Tuesday, August 15, 2006 3:29 PM
> To: Sriram Dandapani
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] dropping partitioned table waits forever
>
> On Tue, Aug 15, 2006 at 02:34:57PM -0700, Sriram Dandapani wrote:
>> When I modify the trigger, I noticed that postgres restarted. Not  
>> sure
>> why, but it happens when it is being executed. This behaviour is
>> consistent.
>
> Restarted as in the database crashed??
>
> Is this actually being done with a trigger or with rules?
> -- 
> Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



pgsql-admin by date

Next:From: arsiDate: 2006-08-19 09:59:08
Subject: Re: Restoring database question..
Previous:From: Michael FuhrDate: 2006-08-18 20:28:54
Subject: Re: Inserting a timed delay in a pl/pgsql procedure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group