Обсуждение: dropping partitioned table waits forever
Hm, in my experiments what's happening is that dropping a table needs an AccessExclusiveLock on the table and any open transactions that have read from that table will have at least an AccessShareLock on that table which blocks the schema change. But I don't think merely having a trigger will hold a lock. Will it? Anyone? I think you need an actual open transaction that has accessed the table. Is it possible you have some transactions that are live for an extended period? Either large queries that are still running or just some non-autocommit transaction sitting waiting for user input before committing? You could investigate by doing select * from pg_lock where not granted; Pick out the one looking for an AccessExclusiveLock, its pid should match the pid of the backend where you're doing the DROP. Look at the relation column and do select * from pg_lock where relation = NNN where NNN is the relation of the nongranted lock. Look at what type of lock is being held by that's blocking the partition drop and what the pid of those backends are. You can see what command they're executing by using a tool like pg_admin to look at the stats command buffer or just by looking at ps usually. 8.2 will allow you to move a partition out of an inheritance structure instead of dropping it entirely. Sadly it takes the same lock so it won't help you here. I wonder if it really needs such a strong lock. If it dropped the inheritance structure without locking it would open up some strange cases though. Like, you wouldn't know just because you had committed that other queries weren't still running that think your table is still a partition in the partitioned table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Thanks for the input. My drop statement is in a loop . I have added a lock table with nowait before the drop table. At some point(over a couple of hours, I should be able to get the lock). Not sure why I don't. All queries that access the parent table finish within 30 minutes(and hence should free up the query plan that accesses the unwanted child table to be dropped). The other activity that goes on is a 24x7 insert into the parent table which has rules that redirect data to appropriate child tables. Not sure if this requires a shared lock on child tables that do not satisfy the partition criteria -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gregory Stark Sent: Tuesday, August 15, 2006 10:27 AM To: PostgreSQL Admin Discussion Subject: [ADMIN] dropping partitioned table waits forever Hm, in my experiments what's happening is that dropping a table needs an AccessExclusiveLock on the table and any open transactions that have read from that table will have at least an AccessShareLock on that table which blocks the schema change. But I don't think merely having a trigger will hold a lock. Will it? Anyone? I think you need an actual open transaction that has accessed the table. Is it possible you have some transactions that are live for an extended period? Either large queries that are still running or just some non-autocommit transaction sitting waiting for user input before committing? You could investigate by doing select * from pg_lock where not granted; Pick out the one looking for an AccessExclusiveLock, its pid should match the pid of the backend where you're doing the DROP. Look at the relation column and do select * from pg_lock where relation = NNN where NNN is the relation of the nongranted lock. Look at what type of lock is being held by that's blocking the partition drop and what the pid of those backends are. You can see what command they're executing by using a tool like pg_admin to look at the stats command buffer or just by looking at ps usually. 8.2 will allow you to move a partition out of an inheritance structure instead of dropping it entirely. Sadly it takes the same lock so it won't help you here. I wonder if it really needs such a strong lock. If it dropped the inheritance structure without locking it would open up some strange cases though. Like, you wouldn't know just because you had committed that other queries weren't still running that think your table is still a partition in the partitioned table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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. Dropping the trigger is not viable as inserts happen 24x7 at a high rate. -----Original Message----- From: Jim C. Nasby [mailto:jnasby@pervasive.com] Sent: Tuesday, August 15, 2006 2:32 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] dropping partitioned table waits forever On Mon, Aug 14, 2006 at 03:32:10PM -0700, Sriram Dandapani wrote: > Hi > > > > I have a table partitioned on a daily basis. Data is constantly inserted > in this table. When I attempt to drop a partition that is not used(i.e > previous day's), the drop table waits for a loooong time.This is > probably due to the fact that the old partitioned table is being > referenced in a query plan(the insert into the parent table uses > triggers). > > How can I prevent the locking out of the drop table? I tried truncate > table but it is worse. It locks out the inserts in the parent table and > then it waits.(hence causing a deadlock) What about dropping/changing the trigger? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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@pervasive.com] Sent: Tuesday, August 15, 2006 3:29 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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@pervasive.com] > Sent: Tuesday, August 15, 2006 3:29 PM > To: Sriram Dandapani > Cc: pgsql-admin@postgresql.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@pervasive.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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@pervasive.com] Sent: Friday, August 18, 2006 6:04 AM To: Sriram Dandapani Cc: pgsql-admin@postgresql.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@pervasive.com] > Sent: Tuesday, August 15, 2006 3:29 PM > To: Sriram Dandapani > Cc: pgsql-admin@postgresql.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@pervasive.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@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461