Re: dropping partitioned table waits forever

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