From: Brian Cox <brian.cox@ca.com>
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
Date: Mon, 02 Mar 2009 11:55:54 -0800
Message-ID: <49AC39CA.5080604@ca.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: brian.cox@ca.com, tgl@sss.pgh.pa.us
Subject: Re: "slow" queries
In-Reply-To: <49AC39CA.5080604@ca.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox