Обсуждение: truncate partitioned table locking
Hi
I have master table A, inherited by Table B and Table C
Table B has data for June 18
Table C has data for June 19
The application issues queries against the master table…on June 19, there is no reference to data from June 18 tables.
When I issue a truncate table B, it appears to take a lock on master table A (this is evident as other inserts to the table are in a WAITING state), and the ps listing shows the truncate table in a WAIT state (along with the INSERTS)
Does the truncation of a child table take a lock on the master table ? and if so, why does it go in a WAIT state
"Sriram Dandapani" <sdandapani@counterpane.com> writes: > I have master table A, inherited by Table B and Table C > Does the truncation of a child table take a lock on the master table ? No, but it certainly locks the child table ... and queries on A are going to try to scan all three tables. regards, tom lane
I have a situation where data is constantly inserted and frequently queried into a master table(and routed appropriately to the partition based on a non-overlapping check constraint ). I also need to drop the child partition (say for yesterday's data..the partitioning is done on a daily basis). 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 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, June 19, 2006 11:27 AM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] truncate partitioned table locking "Sriram Dandapani" <sdandapani@counterpane.com> writes: > I have master table A, inherited by Table B and Table C > Does the truncation of a child table take a lock on the master table ? No, but it certainly locks the child table ... and queries on A are going to try to scan all three tables. regards, tom lane
Sriram, I do this on monthly tables which have about 30 million rows. Last time I truncated a partition it took anout 2 milliseconds. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Sriram Dandapani Sent: Monday, June 19, 2006 12:41 PM To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] truncate partitioned table locking I have a situation where data is constantly inserted and frequently queried into a master table(and routed appropriately to the partition based on a non-overlapping check constraint ). I also need to drop the child partition (say for yesterday's data..the partitioning is done on a daily basis).
I have done this before too..except that in this situation, I am trying to truncate a table whose parent is constantly accessed (INSERTS,SELECTS). The truncate table command is shown in WAIT state. Constraint exclusion is set to "on". The only issue I can see with locking is the fact that the check constraint criteria (the column on which partitioning is done) is not in the where clause of the select statements that are issued on the parent table. (and it is not required too). Wonder if this results in a shared lock on the child table that I am trying to truncate -----Original Message----- From: Benjamin Krajmalnik [mailto:kraj@illumen.com] Sent: Monday, June 19, 2006 12:46 PM To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: RE: [ADMIN] truncate partitioned table locking Sriram, I do this on monthly tables which have about 30 million rows. Last time I truncated a partition it took anout 2 milliseconds. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Sriram Dandapani Sent: Monday, June 19, 2006 12:41 PM To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] truncate partitioned table locking I have a situation where data is constantly inserted and frequently queried into a master table(and routed appropriately to the partition based on a non-overlapping check constraint ). I also need to drop the child partition (say for yesterday's data..the partitioning is done on a daily basis).
I am curious, why would a query on a parent table, A, put a lock on a child table, B? If the query doesn't touch B or any of its children, why would PostgreSQL care what happens to the child table during its query?
-Aaron
-Aaron
On 6/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> I have master table A, inherited by Table B and Table C
> Does the truncation of a child table take a lock on the master table ?
No, but it certainly locks the child table ... and queries on A are
going to try to scan all three tables.
regards, tom lane
"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
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
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@illumen.com] Sent: Tuesday, June 20, 2006 8:20 AM To: Tom Lane; Sriram Dandapani Cc: pgsql-admin@postgresql.org 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@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
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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane Sent: Monday, June 19, 2006 6: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
I solved the issue by doing the following LOCK TABLE <CHILD_TABLE> NOWAIT Trap the exception. Sleep for a while Repeat lock table until exception is not thrown Then issue a drop table cascade (or truncate) This appears to be the only way to issue truncate/drop table on a child table ....The application involves heavy writes/reads on the parent table and truncate <child> table seems to cause a deadlock every time a shared lock exists on the parent table -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane Sent: Monday, June 19, 2006 6: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