Re: truncate partitioned table locking

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx
[mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane
Sent: Monday, June 19, 2006 6: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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux