Re: "slow" queries

Tom Lane [tgl(at)sss(dot)pgh(dot)pa(dot)us] wrote:
> If you hadn't left out the "granted" column we could be more sure,
> but what it looks like to me is the DROP (pid 13842) is stuck behind
> the <IDLE> transaction (pid 13833). In particular these two rows of
> pg_locks look like a possible conflict:
>
> > relation | 26472437 | 26472508 | | 15/69749
> > | 13842 | AccessExclusiveLock
>
> > relation | 26472437 | 26472508 | | 11/131
> > | 13833 | AccessShareLock

select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c join
pg_locks l on c.oid=l.relation order by l.pid;

26472508 | ts_transets | 13833 | AccessShareLock | t
26472508 | ts_transets | 13842 | AccessExclusiveLock | f

pid 13833 is the idle transaction and 13842 is doing the drop table.

So, the idle transaction is the problem. Thanks to you, Scott Carey and
Robert Haas for pointing this out. However, why does the drop of
ts_defects_20090227 need exclusive access to ts_transets? I assume it
must be due to this FK?

alter table ts_defects_20090227 add constraint FK34AA2B629DADA24
foreign key (ts_transet_id) references ts_transets;

Thanks again,
Brian