X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9AF84D1B91B for ; Fri, 27 Feb 2004 07:49:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 63203-02 for ; Fri, 27 Feb 2004 03:49:29 -0400 (AST) Received: from trade-india.com (unknown [203.145.171.48]) by svr1.postgresql.org (Postfix) with SMTP id 550F9D1BA22 for ; Fri, 27 Feb 2004 03:49:24 -0400 (AST) Received: (qmail 23748 invoked from network); 27 Feb 2004 07:47:20 -0000 Received: from system66.trade-india-local.com (HELO trade-india.com) (192.168.0.66) by system66.trade-india-local.com with SMTP; 27 Feb 2004 07:47:20 -0000 Message-ID: <403EF608.7040005@trade-india.com> Date: Fri, 27 Feb 2004 13:17:20 +0530 From: Rajesh Kumar Mallah Organization: Infocom Network Limited User-Agent: Mozilla Thunderbird 0.5 (X11/20040208) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: ALTER table taking ages... Content-Type: multipart/alternative; boundary="------------000604050001070504040501" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/375 X-Sequence-Number: 12548 This is a multi-part message in MIME format. --------------000604050001070504040501 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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. --------------000604050001070504040501 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
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.



--------------000604050001070504040501--