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