Обсуждение: Problem dropping a table

I have a java application that is trying to dynamically drop a set of
tables.  Problem is, when it gets to a particular table and I execute
the "drop table foo cascade" command from a prepared statement, the
query never returns.  It just hangs indefinitely.  I presume that it is
waiting on a lock for this table, but there is nothing that I know of
that should be locking this table.

So, I have two issues - the first, how do I prevent myself from hanging
indefinitely?  I tried 'setQueryTimeout' on the prepared statement, but
it doesn't seem to have any effect.  Still hangs indefinitely.

The second, how can I track down what is locking this table?  I presume
that it is some of my code somewhere... maybe a prepared statement that
I didn't get closed - but I can't find it.  I've check my code twice for
any reference to this table, and every use of it is properly closing the
result sets and the prepared statement.

Any ideas?

Thanks,

Dan


--
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/

Dan Armbrust <daniel.armbrust.list@gmail.com> writes:
> I have a java application that is trying to dynamically drop a set of
> tables.  Problem is, when it gets to a particular table and I execute
> the "drop table foo cascade" command from a prepared statement, the
> query never returns.  It just hangs indefinitely.  I presume that it is
> waiting on a lock for this table, but there is nothing that I know of
> that should be locking this table.

> So, I have two issues - the first, how do I prevent myself from hanging
> indefinitely?  I tried 'setQueryTimeout' on the prepared statement, but
> it doesn't seem to have any effect.  Still hangs indefinitely.

You should complain to the JDBC list about that.  The underlying backend
facility works as expected:

session 1:

regression=# create table foo (f int);
CREATE TABLE
regression=# begin;
BEGIN
regression=# lock table foo;
LOCK TABLE

session 2:

regression=# set statement_timeout TO 1000;
SET
regression=# drop table foo cascade;
... after a second ...
ERROR:  canceling statement due to statement timeout
regression=#


> The second, how can I track down what is locking this table?

Look in pg_locks to start with.

            regards, tom lane