Message-ID: <49AC39CA.5080604@ca.com>
Date: Mon, 02 Mar 2009 11:55:54 -0800
From: Brian Cox <brian.cox@ca.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20081209)
MIME-Version: 1.0
To: "Tom Lane [tgl@sss.pgh.pa.us]" <tgl@sss.pgh.pa.us>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: "slow" queries
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane [tgl@sss.pgh.pa.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