Re: "slow" queries

Brian Cox <brian(dot)cox(at)ca(dot)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