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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Brad King Sent: Saturday, October 13, 2007 1:19 PM To: pgsql-admin@xxxxxxxxxxxxxx Subject: 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
