Re: ALTER table taking ages...

Thanks very much,

Unfortunately i restarted the postmaster
as we had to move forward. Since then i have not faced this problem.
I shall post the results next time i face this kind of problem.

Regds
mallah.

> Rajesh Kumar Mallah <mallah(at)trade-india(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>