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:
> 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
