Thread: Partitioning and deadlocks
I'm experiencing deadlocking trying to clean up old partition data, similar to this user: http://svr5.postgresql.org/pgsql-admin/2006-06/msg00160.php Specifically, an UPDATE on the base table for a single row, by PK, often deadlocks the TRUNCATE on a child partition. I've added "where" criteria to the UPDATE hoping constraint exclusion would help here, since the row I'm updating is not even in the partition I'm truncating. No luck. So, should I be using constraint exclusion and constraint critieria on the UPDATE or not ? I need to be able to automate garbage collection of old partitions. Since I can't use functions in constraints, that means I have to do both a truncate and an alter table to drop and re-create constraints. I simply want a circular partition of months 01 through 12, but it sure seems like I'm doing a lot of ddl to get that accomplished, and I have deadlocks. Thanks, Brad
Let me re-phrase this to see if I can get any response. Has anyone used partitioning with inheritance successfully ? Can you point to any specific concurrency strategies for garbage collecting old partitions on a live system ? Thank you. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brad King Sent: Saturday, October 13, 2007 1:19 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Partitioning and deadlocks I'm experiencing deadlocking trying to clean up old partition data, similar to this user: http://svr5.postgresql.org/pgsql-admin/2006-06/msg00160.php Specifically, an UPDATE on the base table for a single row, by PK, often deadlocks the TRUNCATE on a child partition. I've added "where" criteria to the UPDATE hoping constraint exclusion would help here, since the row I'm updating is not even in the partition I'm truncating. No luck. So, should I be using constraint exclusion and constraint critieria on the UPDATE or not ? I need to be able to automate garbage collection of old partitions. Since I can't use functions in constraints, that means I have to do both a truncate and an alter table to drop and re-create constraints. I simply want a circular partition of months 01 through 12, but it sure seems like I'm doing a lot of ddl to get that accomplished, and I have deadlocks. Thanks, Brad ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
On 10/23/07, Brad King <brad.king@channeladvisor.com> wrote: > Let me re-phrase this to see if I can get any response. Has anyone used > partitioning with inheritance successfully ? Can you point to any > specific concurrency strategies for garbage collecting old partitions on > a live system ? Thank you. I've never truncated on a live one. It is pretty easy to just update the update/insert rules/triggers to ignore the old table, then drop it. I'm guessing you could do that, then recreate the table and get good performance.
Thanks for the reply. I really want to have something generic to garbage collect old data. This is pretty easy to do with re-writing check constraints but much more verbose if you have drop and recreate rules, since the column lists are different for each table. Also I have several related tables to deal with, which adds to the fun. I think I will go back to a non partitioned system at this point. I think the complexity involved in the solution is not worth the gain over plain old delete. -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Tuesday, October 23, 2007 2:51 PM To: Brad King Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Partitioning and deadlocks On 10/23/07, Brad King <brad.king@channeladvisor.com> wrote: > Let me re-phrase this to see if I can get any response. Has anyone used > partitioning with inheritance successfully ? Can you point to any > specific concurrency strategies for garbage collecting old partitions on > a live system ? Thank you. I've never truncated on a live one. It is pretty easy to just update the update/insert rules/triggers to ignore the old table, then drop it. I'm guessing you could do that, then recreate the table and get good performance.