Partitioning and deadlocks

From: "Brad King" <brad(dot)king(at)channeladvisor(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Partitioning and deadlocks
Date: 2007-10-13 17:19:20
Message-ID: D45F1ECA30B59A4F96208F86532F901F1CB448F9@rdu-caex-01.channeladvisor.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Patrick De Zlio 2007-10-14 14:41:51 Inconsistent result from request
Previous Message Peter Koczan 2007-10-12 19:00:10 separating data and logs