pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gregory Stark <gsstark@mit.edu>
To: PostgreSQL Admin Discussion <pgsql-admin@postgresql.org>
Subject: dropping partitioned table waits forever
Date: 15 Aug 2006 13:27:26 -0400
Message-ID: <87u04dzz7l.fsf@stark.xeocode.com> (raw)


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




view thread (9+ messages)  latest in thread

Message-ID: <87u04dzz7l.fsf@stark.xeocode.com>
Permalink:  ../87u04dzz7l.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87u04dzz7l.fsf@stark.xeocode.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: gsstark@mit.edu
  Subject: Re: dropping partitioned table waits forever
  In-Reply-To: <87u04dzz7l.fsf@stark.xeocode.com>
  References: <87u04dzz7l.fsf@stark.xeocode.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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