X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BF8DB9FA621 for ; Tue, 20 Jun 2006 12:20:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 50026-09 for ; Tue, 20 Jun 2006 12:20:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 729C09FA219 for ; Tue, 20 Jun 2006 12:20:25 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: Re: truncate partitioned table locking Date: Tue, 20 Jun 2006 09:20:22 -0600 X-MimeOLE: Produced By Microsoft Exchange V6.5 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] truncate partitioned table locking thread-index: AcaUCG4zwuFL9Ns8TUSKxRr7+mmg0gAdFdvQ From: "Benjamin Krajmalnik" To: "Tom Lane" , "Sriram Dandapani" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/158 X-Sequence-Number: 22120 Siriam, As I mentioned to you yesterday, I have a partitioned table which gets over a million inserts per day (routed to the correct partition via triggers). Each partition holds one month' worth of data, so approximately 30 million rows. Last time I truncated the oldest partition took 2 ms. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane Sent: Monday, June 19, 2006 7:24 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] truncate partitioned table locking=20 "Sriram Dandapani" 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