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