Skip site navigation (1) Skip section navigation (2)

Re: ALTER table taking ages...

From: mallah(at)trade-india(dot)com
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: ALTER table taking ages...
Date: 2004-02-27 17:46:39
Message-ID: 3673.192.168.0.100.1077903999.squirrel@system67.trade-india-local.com (view raw or flat)
Thread:
Lists: pgsql-admin
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
>
>


In response to

pgsql-admin by date

Next:From: Jean-Michel ChabanneDate: 2004-02-27 19:14:53
Subject: Re: copying data between tables
Previous:From: Peter EisentrautDate: 2004-02-27 17:25:56
Subject: Re: Solution! (was: Re: Linux (Debian Woody)...)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group