Re: Partitioning and deadlocks

["Brad King" <brad(dot)king(at)channeladvisor(dot)com>]

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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Brad King
Sent: Saturday, October 13, 2007 1:19 PM
To: pgsql-admin(at)postgresql(dot)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