The truncation is very fast. I am having locking issues which I am trying to resolve. The truncation seems to be conflicting with a select on the parent table. This is the scenario 1st SQL insert into TABLE D select nextval(TABLE_D_SEQ'), COL2 from (select distinct COL2 from PARENT_TABLE where COL2 is not null and not exists (select 'x' from TABLE D a where PARENT_TABLE.COL2 = a.COL2 ) and id between 105927644 and 106777644) aa Followed by TRUNCATE TABLE ONE_OF_THE_NOT_NEEDED_CHILD_OF_PARENT_TABLE This puts truncate in a WAIT state As well as the INSERT into a WAIT state Why does the insert go into a wait state only when I issue a truncate command -----Original Message----- From: Benjamin Krajmalnik [mailto:kraj@xxxxxxxxxxx] Sent: Tuesday, June 20, 2006 8:20 AM To: Tom Lane; Sriram Dandapani Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: RE: [ADMIN] truncate partitioned table locking 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@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Monday, June 19, 2006 7: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
