Обсуждение: Cannot DROP while process running
I am on Postgres 9.1 and running into a problem when trying to drop a table in my public schema.
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? When I select from pg_stat_activity the table I am intending on dropping is not listed as having anything actively using it.
The only table with a lock or any activity is 'public.raw_tomtom'.
mapping=# select datname, procpid, current_query from pg_stat_activity;
datname | procpid | current_query
----------+---------+----------------------------------------------------------------------------------
postgres | 3944 | <IDLE>
mapping | 3945 | <IDLE>
mapping | 16193 | SELECT * FROM public.assign_vertex_id('raw_tomtom', 0.00001, 'the_geom', 'gid');
mapping | 25456 | <IDLE>
mapping | 20897 | autovacuum: VACUUM ANALYZE public.raw_tomtom
mapping | 20405 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODE
mapping | 19461 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODE
mapping | 17570 | <IDLE>
mapping | 20466 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODE
postgres | 20874 | <IDLE>
mapping | 20976 | select datname, procpid, current_query from pg_stat_activity;
mapping | 20609 | <IDLE>
mapping | 20876 | <IDLE>
*procpid 16193 "SELECT * FROM ..." is a long running process that would not be touching the table I am trying to drop.
Thanks for any help!
Steve Horn
That should have said " The only table with a lock or any activity is 'public. vertices_tmp'.
--
Steve Horn
On Mon, Apr 9, 2012 at 3:38 PM, Steve Horn <steve@stevehorn.cc> wrote:
I am on Postgres 9.1 and running into a problem when trying to drop a table in my public schema.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? When I select from pg_stat_activity the table I am intending on dropping is not listed as having anything actively using it.The only table with a lock or any activity is 'public.raw_tomtom'.mapping=# select datname, procpid, current_query from pg_stat_activity;datname | procpid | current_query----------+---------+----------------------------------------------------------------------------------postgres | 3944 | <IDLE>mapping | 3945 | <IDLE>mapping | 16193 | SELECT * FROM public.assign_vertex_id('raw_tomtom', 0.00001, 'the_geom', 'gid');mapping | 25456 | <IDLE>mapping | 20897 | autovacuum: VACUUM ANALYZE public.raw_tomtommapping | 20405 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODEmapping | 19461 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODEmapping | 17570 | <IDLE>mapping | 20466 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODEpostgres | 20874 | <IDLE>mapping | 20976 | select datname, procpid, current_query from pg_stat_activity;mapping | 20609 | <IDLE>mapping | 20876 | <IDLE>*procpid 16193 "SELECT * FROM ..." is a long running process that would not be touching the table I am trying to drop.Thanks for any help!--
Steve Horn
Steve Horn
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
Вложения
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.
There should be no queries actively accessing the other tables in that database (this database is in development only). A second question would be, can I clear those locks?
--
Steve Horn
2012/4/9 Devrim GÜNDÜZ <devrim@gunduz.org>
On Mon, 2012-04-09 at 16:08 -0400, Tom Lane wrote:We probably need a better view which joins pg_stat_activity and
> > 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.
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 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
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.
mapping=# select datname, procpid, current_query from pg_stat_activity;
datname | procpid | current_query
----------+---------+----------------------------------------------------------------------------------
postgres | 23424 | <IDLE>
mapping | 22787 | select datname, procpid, current_query from pg_stat_activity;
mapping | 16193 | SELECT * FROM public.assign_vertex_id('raw_tomtom', 0.00001, 'the_geom', 'gid');
mapping | 23425 | <IDLE>
mapping | 20897 | autovacuum: VACUUM ANALYZE public.raw_tomtom
mapping | 20405 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODE
mapping | 19461 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODE
mapping | 23426 | <IDLE>
mapping | 20466 | LOCK TABLE public.vertices_tmp IN ACCESS SHARE MODE
mapping | 8059 | <IDLE>
mapping | 20976 | <IDLE>
Thanks for your help on this.
2012/4/9 Tom Lane <tgl@sss.pgh.pa.us>
Steve Horn <steve@stevehorn.cc> writes:Well, an AccessShareLock would definitely prevent you from dropping the
> 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.
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 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
Restarted the PG service before reading your email, so I can't verify - but your idea about uncomitted prepared transactions may be the culprit.
I am using the ruby PG gem's exec method and then closing the connection. When I get some time I will fire off a few tests and see if I get the same unexpected locking.
Thank you folks for taking the time to reply.
--
Steve Horn
2012/4/10 Tom Lane <tgl@sss.pgh.pa.us>
Steve Horn <steve@stevehorn.cc> writes:Well, it's hard to tell what other locks those transactions have already
> 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.
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 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