pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ALTER table taking ages...
4+ messages / 4 participants
[nested] [flat]

* ALTER table taking ages...
@ 2004-02-27 07:47 Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-02-27 16:56 ` Re: ALTER table taking ages... Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-28 12:32 ` Re: ALTER table taking ages... Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Rajesh Kumar Mallah @ 2004-02-27 07:47 UTC (permalink / raw)
  To: 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.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: ALTER table taking ages...
  2004-02-27 07:47 ALTER table taking ages... Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-02-27 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-02-27 17:46   ` Re: ALTER table taking ages... mallah@trade-india.com
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2004-02-27 16:56 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: ALTER table taking ages...
  2004-02-27 07:47 ALTER table taking ages... Rajesh Kumar Mallah <mallah@trade-india.com>
  2004-02-27 16:56 ` Re: ALTER table taking ages... Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-02-27 17:46   ` mallah@trade-india.com
  0 siblings, 0 replies; 4+ messages in thread

From: mallah@trade-india.com @ 2004-02-27 17:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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@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
>
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: ALTER table taking ages...
  2004-02-27 07:47 ALTER table taking ages... Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2004-02-28 12:32 ` Gaetano Mendola <mendola@bigfoot.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Gaetano Mendola @ 2004-02-28 12:32 UTC (permalink / raw)
  To: pgsql-admin

Rajesh Kumar Mallah wrote:
> 
> 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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-02-28 12:32 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-27 07:47 ALTER table taking ages... Rajesh Kumar Mallah <mallah@trade-india.com>
2004-02-27 16:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-27 17:46   ` mallah@trade-india.com
2004-02-28 12:32 ` Gaetano Mendola <mendola@bigfoot.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox