Skip site navigation (1) Skip section navigation (2)

Re: Cannot DROP while process running

From: Steve Horn <steve(at)stevehorn(dot)cc>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Devrim GÜNDÜZ <devrim(at)gunduz(dot)org>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Cannot DROP while process running
Date: 2012-04-10 12:58:10
Message-ID: CAFLkBaWjGD8vtm+xwP2si0=g+Tp=Bda2YB89+9-y0+Kv1hq0_g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)sss(dot)pgh(dot)pa(dot)us>

> Steve Horn <steve(at)stevehorn(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2012-04-10 13:57:26
Subject: Re: Cannot DROP while process running
Previous:From: Tom LaneDate: 2012-04-09 23:18:00
Subject: Re: Cannot DROP while process running

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group