It appears I am running into this issue posted here http://archives.postgresql.org/pgsql-general/2005-07/msg00693.php The truncate gets a lock and waits for other transactions to finish. Since the other transactions issue selects on the parent table(and thus requiring a share lock on the child, due to the nature of partitioning), the transactions get blocked. This is bizarre. Why would truncate post a lock and thus block other transactions when it should wait for the other transactions to finish before acquiring a lock -----Original Message----- From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Monday, June 19, 2006 6:24 PM To: Sriram Dandapani Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: [ADMIN] truncate partitioned table locking "Sriram Dandapani" <sdandapani@xxxxxxxxxxxxxxx> writes: > How can I issue a truncate /drop table on the child without running into > locking issues. Doesn't constraint exclusion prevent access of a child > table based on the check constraint criteria No, because the planner has to access the child table in order to examine its constraints. (Since TRUNCATE is a metadata update, the fact that the constraints are metadata not content doesn't help.) TRUNCATE in itself is fast enough that you shouldn't really have any problems here. If you are having locking issues then I suspect you need to look for transactions that are sitting on ordinary reader or writer locks of the table, instead of doing their jobs and committing. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
