Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> It takes ages to drop a constraint from one of my tables
> [ table details at the end ] I cannot insert into it also.
DROP CONSTRAINT in itself isn't going to take any significant amount of
time. The only plausible explanation is that some other session has a
lock on the table, forcing the DROP to wait. DROP is going to want
access-exclusive lock on the table, so pretty much anything will block it.
> tradein_clients=# SELECT * from pg_locks where mode='ExclusiveLock' and
> granted is true;
This is unhelpful. In the first place there are multiple kinds of lock
and you've shown only one. In the second place, the entries you want to
start from are the ones with granted not true, because that indicates
someone waiting for a lock. Try something like
select h.pid as blocker, w.pid as blockee from pg_locks h, pg_locks w
where h.granted and not w.granted and
((h.relation = w.relation and h.database = w.database) or
h.transaction = w.transaction);
and then look in pg_stat_activity to find out what each process is
doing.
regards, tom lane
