Обсуждение: 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