From: Benjamin Krajmalnik <kraj@illumen.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Sriram Dandapani <sdandapani@counterpane.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: truncate partitioned table locking
Date: Tue, 20 Jun 2006 09:20:22 -0600
Message-ID: <BF337097BDD9D849A2F4B818DDB27987029371@stash.stackdump.local> (raw)
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
"Sriram Dandapani" <sdandapani@counterpane.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: kraj@illumen.com, tgl@sss.pgh.pa.us, sdandapani@counterpane.com
Subject: Re: truncate partitioned table locking
In-Reply-To: <BF337097BDD9D849A2F4B818DDB27987029371@stash.stackdump.local>
References: <BF337097BDD9D849A2F4B818DDB27987029371@stash.stackdump.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox