Thread: Cannot DROP while process running

Steve Horn <steve@stevehorn.cc> writes:
> When I issue the drop command for a table, the server doesn't respond - not
> even with increased disk activity or CPU usage. I am wondering if a lock is
> blocking it?

Look into pg_locks to find out.

> When I select from pg_stat_activity the table I am intending
> on dropping is not listed as having anything actively using it.

Perhaps it has a foreign key link to/from something that's in use?

            regards, tom lane

On Mon, 2012-04-09 at 16:08 -0400, Tom Lane wrote:
> > When I issue the drop command for a table, the server doesn't
> respond - not
> > even with increased disk activity or CPU usage. I am wondering if a
> lock is
> > blocking it?
>
> Look into pg_locks to find out.

We probably need a better view which joins pg_stat_activity and
pg_locks, along with the new pg_stat_activity column in 9.2, which will
give users better idea about what is locking what...
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

Steve Horn <steve@stevehorn.cc> writes:
> I ran this command:
> select pg_class.relname,pg_locks.* from pg_class,pg_locks where
> pg_class.relfilenode=pg_locks.relation;

> and it appears that almost every table in my database has
> ""AccessShareLock". I have read that those locks are placed on tables for
> SELECT access.

Well, an AccessShareLock would definitely prevent you from dropping the
table.  The question is which session(s) are holding such locks and why.
I suspect that you have some long-running transactions, perhaps
accidentally so.  As Devrim mentioned, joining pg_locks against
pg_stat_activity would go a long way towards explaining what's blocking
your DROP attempt.

            regards, tom lane

Steve Horn <steve@stevehorn.cc> writes:
> Here is the result of this select:
> select datname, procpid, current_query from pg_stat_activity;

> As you can see, there appears to be no connection to the other tables with
> the AccessShareLocks.

Well, it's hard to tell what other locks those transactions have already
got.  You really ought to be looking in pg_locks not just
pg_stat_activity.

BTW, another thing that occasionally trips people up is unintended use
of a prepared transaction (that they then forget to commit or roll back).
Does the pg_prepared_xacts view show anything?

            regards, tom lane

Steve Horn <steve@stevehorn.cc> writes:
> Restarted the PG service before reading your email, so I can't verify - but
> your idea about uncomitted prepared transactions may be the culprit.

A prepared transaction would persist through a restart, so if your
problem is now gone then I think we can eliminate that theory.  What
seems most likely to me now is that you had some client that was sitting
on a lot of AccessShare locks in an open transaction.

            regards, tom lane