Обсуждение: truncate partitioned table locking

Поиск
Список
Период
Сортировка

truncate partitioned table locking

От
"Sriram Dandapani"
Дата:

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

Re: truncate partitioned table locking

От
Tom Lane
Дата:
"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

Re: truncate partitioned table locking

От
"Sriram Dandapani"
Дата:
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

Re: truncate partitioned table locking

От
"Benjamin Krajmalnik"
Дата:
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).

Re: truncate partitioned table locking

От
"Sriram Dandapani"
Дата:
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).

Re: truncate partitioned table locking

От
"Aaron Bono"
Дата:
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

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

Re: truncate partitioned table locking

От
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

Re: truncate partitioned table locking

От
"Benjamin Krajmalnik"
Дата:
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

Re: truncate partitioned table locking

От
"Sriram Dandapani"
Дата:
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

Re: truncate partitioned table locking

От
"Sriram Dandapani"
Дата:
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

Re: truncate partitioned table locking

От
"Sriram Dandapani"
Дата:
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