From: Tom Lane <tgl@sss.pgh.pa.us>
To: Brian Cox <brian.cox@ca.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: "slow" queries
Date: Mon, 02 Mar 2009 15:11:07 -0500
Message-ID: <23442.1236024667@sss.pgh.pa.us> (raw)
In-Reply-To: <49AC39CA.5080604@ca.com>
References: <49AC39CA.5080604@ca.com>
Brian Cox <brian.cox@ca.com> writes:
> 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;
Well, that's certainly a sufficient reason, if perhaps not the only
reason. Dropping ts_defects_20090227 will require removal of FK triggers
on ts_transets, and we can't do that concurrently with transactions that
might be trying to fire those triggers.
Now admittedly, it would probably be sufficient to take ExclusiveLock
rather than AccessExclusiveLock when removing triggers, since we do not
have triggers ON SELECT. Right now though, we just take
AccessExclusiveLock for most any DDL on a table. There was a patch
submitted last fall to reduce DDL locking in some cases, but it hasn't
been reworked to fix the problems that were pointed out (and I
disremember if it addressed DROP TRIGGER in particular anyway).
regards, tom lane
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: tgl@sss.pgh.pa.us, brian.cox@ca.com
Subject: Re: "slow" queries
In-Reply-To: <23442.1236024667@sss.pgh.pa.us>
* 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