Обсуждение: ALTER table taking ages...
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.
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
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 > >
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