Re: dropping partitioned table waits forever

Thanks for the input. My drop statement is in a loop . I have added a
lock table with nowait before the drop table. At some point(over a
couple of hours, I should be able to get the lock). Not sure why I
don't. All queries that access the parent table finish within 30
minutes(and hence should free up the query plan that accesses the
unwanted child table to be dropped).

The other activity that goes on is a 24x7 insert into the parent table
which has rules that redirect data to appropriate child tables. Not sure
if this requires a shared lock on child tables that do not satisfy the
partition criteria

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gregory Stark
Sent: Tuesday, August 15, 2006 10:27 AM
To: PostgreSQL Admin Discussion
Subject: [ADMIN] dropping partitioned table waits forever


Hm, in my experiments what's happening is that dropping a table needs an
AccessExclusiveLock on the table and any open transactions that have
read from
that table will have at least an AccessShareLock on that table which
blocks
the schema change.

But I don't think merely having a trigger will hold a lock. Will it?
Anyone?

I think you need an actual open transaction that has accessed the table.
Is it
possible you have some transactions that are live for an extended
period?
Either large queries that are still running or just some non-autocommit
transaction sitting waiting for user input before committing?

You could investigate by doing

select * from pg_lock where not granted;

Pick out the one looking for an AccessExclusiveLock, its pid should
match the
pid of the backend where you're doing the DROP. Look at the relation
column
and do

select * from pg_lock where relation = NNN

where NNN is the relation of the nongranted lock.

Look at what type of lock is being held by that's blocking the partition
drop
and what the pid of those backends are. You can see what command they're
executing by using a tool like pg_admin to look at the stats command
buffer or
just by looking at ps usually.

8.2 will allow you to move a partition out of an inheritance structure
instead
of dropping it entirely. Sadly it takes the same lock so it won't help
you
here.

I wonder if it really needs such a strong lock. If it dropped the
inheritance
structure without locking it would open up some strange cases though.
Like,
you wouldn't know just because you had committed that other queries
weren't
still running that think your table is still a partition in the
partitioned
table.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org