ALTER table taking ages...

[Rajesh Kumar Mallah]

Greetings!

It takes ages to drop a constraint from one of my tables
[ table details at the end ] I cannot insert into it also.

I know pg_dump is not running and no other query is accessing the table.
Can anyone help me debugging this problem? Can anyone explain the
following entires in pg_locks where relation and database
are both null.

tradein_clients=# SELECT * from pg_locks where mode='ExclusiveLock' and
granted is true;
+----------+----------+-------------+-------+---------------+---------+
| relation | database | transaction | pid | mode | granted |
+----------+----------+-------------+-------+---------------+---------+
| NULL | NULL | 116230313 | 19898 | ExclusiveLock | t |
| NULL | NULL | 116230309 | 24779 | ExclusiveLock | t |
| NULL | NULL | 116230267 | 24780 | ExclusiveLock | t |
| NULL | NULL | 116230303 | 24764 | ExclusiveLock | t |
| NULL | NULL | 116230302 | 24751 | ExclusiveLock | t |
| NULL | NULL | 116230308 | 24767 | ExclusiveLock | t |
| NULL | NULL | 116230274 | 24761 | ExclusiveLock | t |
| NULL | NULL | 116230306 | 24752 | ExclusiveLock | t |
| NULL | NULL | 116230312 | 23222 | ExclusiveLock | t |
| NULL | NULL | 116230290 | 24768 | ExclusiveLock | t |
| NULL | NULL | 116230292 | 24776 | ExclusiveLock | t |
| NULL | NULL | 116230297 | 24753 | ExclusiveLock | t |
| NULL | NULL | 116230295 | 24765 | ExclusiveLock | t |
| NULL | NULL | 116230152 | 24096 | ExclusiveLock | t |
| NULL | NULL | 116230311 | 24769 | ExclusiveLock | t |
| NULL | NULL | 116194826 | 23048 | ExclusiveLock | t |
| NULL | NULL | 116230307 | 24758 | ExclusiveLock | t |
+----------+----------+-------------+-------+---------------+---------+
(17 rows)

Time: 449.422 ms
tradein_clients=# \d general.user_accounts
Table "general.user_accounts"
+-----------------+------------------------+--------------------------------------------------------------------+
| Column | Type |
Modifiers |
+-----------------+------------------------+--------------------------------------------------------------------+
| userid | integer | not null default
nextval('general.user_accounts_userid_seq'::text) |
| username | character varying(50) | not
null |
| password | character varying(50) | not
null |
| title | character varying(15)
| |
| fname | character varying(200)
| |
| mname | character varying(30)
| |
| lname | character varying(30)
| |
| desg | character varying(100)
| |
| creation_date | integer | not
null |
| creation_time | integer | not
null |
| last_visit | integer
| |
| activation_code | double precision
| |
| auto_registered | boolean | not null default
false |
| buy_inquiry | boolean | not null default
true |
| sell_inquiry | boolean | not null default
true |
| webmail_status | character varying(20) | not null default 'NOT
REQUESTED'::character varying |
| account_status | character varying(20) | not null default
'INACTIVE'::character varying |
| section_id | integer | not
null |
| heard_from | integer
| |
+-----------------+------------------------+--------------------------------------------------------------------+
Indexes:
"user_accounts_userid_pkey" primary key, btree (userid)
"user_accounts_index_creation_date" btree (creation_date)
"user_accounts_index_creation_time" btree (creation_time)
"user_accounts_userid" btree (userid)
Check constraints:
"user_accounts_check_account_status" CHECK (account_status::text =
'INACTIVE'::text OR account_status::text = 'ACTIVE'::text OR
account_status::text = 'DISABLED'::text OR account_status::text =
'DELETED'::text)
"user_accounts_check_webmail_status" CHECK (webmail_status::text =
'NOT REQUESTED'::text OR webmail_status::text = 'REQUESTED'::text OR
webmail_status::text = 'ACTIVATED'::text)
Foreign-key constraints:
"user_accounts_fkey_section_id" FOREIGN KEY (section_id) REFERENCES
registration_source(section_id)
"user_accounts_fkey_creation_time" FOREIGN KEY (creation_time)
REFERENCES time_dimension(time_id)
"user_accounts_fkey_creation_date" FOREIGN KEY (creation_date)
REFERENCES date_dimension(date_id)


Regds
Mallah.

[Tom Lane]

Post by Rajesh Kumar Mallah
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.

Post by Rajesh Kumar Mallah
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 ***@postgresql.org so that your
message can get through to the mailing list cleanly

[m***@trade-india.com]

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.

Post by Tom Lane

Post by Rajesh Kumar Mallah
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.

Post by Rajesh Kumar Mallah
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
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Gaetano Mendola]

Post by Rajesh Kumar Mallah
Greetings!
It takes ages to drop a constraint from one of my tables
[ table details at the end ] I cannot insert into it also.
I know pg_dump is not running and no other query is accessing the table.
Can anyone help me debugging this problem? Can anyone explain the
following entires in pg_locks where relation and database
are both null.
tradein_clients=# SELECT * from pg_locks where mode='ExclusiveLock' and
granted is true;
+----------+----------+-------------+-------+---------------+---------+
| relation | database | transaction | pid | mode | granted |
+----------+----------+-------------+-------+---------------+---------+
| NULL | NULL | 116230313 | 19898 | ExclusiveLock | t |
| NULL | NULL | 116230309 | 24779 | ExclusiveLock | t |
| NULL | NULL | 116230267 | 24780 | ExclusiveLock | t |
| NULL | NULL | 116230303 | 24764 | ExclusiveLock | t |
| NULL | NULL | 116230302 | 24751 | ExclusiveLock | t |
| NULL | NULL | 116230308 | 24767 | ExclusiveLock | t |
| NULL | NULL | 116230274 | 24761 | ExclusiveLock | t |
| NULL | NULL | 116230306 | 24752 | ExclusiveLock | t |
| NULL | NULL | 116230312 | 23222 | ExclusiveLock | t |
| NULL | NULL | 116230290 | 24768 | ExclusiveLock | t |
| NULL | NULL | 116230292 | 24776 | ExclusiveLock | t |
| NULL | NULL | 116230297 | 24753 | ExclusiveLock | t |
| NULL | NULL | 116230295 | 24765 | ExclusiveLock | t |
| NULL | NULL | 116230152 | 24096 | ExclusiveLock | t |
| NULL | NULL | 116230311 | 24769 | ExclusiveLock | t |
| NULL | NULL | 116194826 | 23048 | ExclusiveLock | t |
| NULL | NULL | 116230307 | 24758 | ExclusiveLock | t |
+----------+----------+-------------+-------+---------------+---------+
(17 rows)

May I know how you obtain this kind of output ( biq square around
results ) ?



Regards
Gaetano Mendola