pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER 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>
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 16:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rajesh Kumar Mallah <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 17:46 mallah@trade-india.com
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-28 12:32 Gaetano Mendola <mendola@bigfoot.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.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