pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
truncate partitioned table locking
11+ messages / 4 participants
[nested] [flat]

* truncate partitioned table locking
@ 2006-06-19 17:11  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-06-19 17:11 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-19 18:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2006-06-19 18:26 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-19 18:41  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-06-19 18:41 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-19 19:45  Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Benjamin Krajmalnik @ 2006-06-19 19:45 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

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).



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-19 19:50  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-06-19 19:50 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin

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).



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-19 20:02  Aaron Bono <postgresql@aranya.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Aaron Bono @ 2006-06-19 20:02 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-20 01:24  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Tom Lane @ 2006-06-20 01:24 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-20 15:20  Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Benjamin Krajmalnik @ 2006-06-20 15:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-20 15:30  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-06-20 15:30 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; Tom Lane <tgl@sss.pgh.pa.us>; tgl@sss.pgh.pa.us; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-20 16:13  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-06-20 16:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: truncate partitioned table locking
@ 2006-06-20 22:18  Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-06-20 22:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2006-06-20 22:18 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-19 17:11 truncate partitioned table locking Sriram Dandapani <sdandapani@counterpane.com>
2006-06-19 18:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-19 20:02   ` Aaron Bono <postgresql@aranya.com>
2006-06-19 18:41 Re: truncate partitioned table locking Sriram Dandapani <sdandapani@counterpane.com>
2006-06-20 01:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-19 19:45 Re: truncate partitioned table locking Benjamin Krajmalnik <kraj@illumen.com>
2006-06-19 19:50 Re: truncate partitioned table locking Sriram Dandapani <sdandapani@counterpane.com>
2006-06-20 15:20 Re: truncate partitioned table locking Benjamin Krajmalnik <kraj@illumen.com>
2006-06-20 15:30 Re: truncate partitioned table locking Sriram Dandapani <sdandapani@counterpane.com>
2006-06-20 16:13 Re: truncate partitioned table locking Sriram Dandapani <sdandapani@counterpane.com>
2006-06-20 22:18 Re: truncate partitioned table locking Sriram Dandapani <sdandapani@counterpane.com>

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