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

ALTER table taking ages...

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: ALTER table taking ages...
Date: 2004-02-27 07:47:20
Message-ID: 403EF608.7040005@trade-india.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.



Responses

pgsql-admin by date

Next:From: Bruno Wolff IIIDate: 2004-02-27 13:11:56
Subject: Re: "SELECT ANY TABLE" !!
Previous:From: Jyry KuukkanenDate: 2004-02-27 06:25:06
Subject: Re: Any Gentoo users interested in a slotted PostgreSQL

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