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@xxxxxxxxxxxxx] Sent: Friday, August 18, 2006 6:04 AM To: Sriram Dandapani Cc: pgsql-admin@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxx] > Sent: Tuesday, August 15, 2006 3:29 PM > To: Sriram Dandapani > Cc: pgsql-admin@xxxxxxxxxxxxxx > 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@xxxxxxxxxxxxx > 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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
