I have a table as follows:
\d entity
Table "public.entity"
Column | Type | Modifiers
--------------+-----------------------------+--------------------
crmid | integer | not null
smcreatorid | integer | not null default 0
smownerid | integer | not null default 0
modifiedby | integer | not null default 0
setype | character varying(30) | not null
description | text |
createdtime | timestamp without time zone | not null
modifiedtime | timestamp without time zone | not null
viewedtime | timestamp without time zone |
status | character varying(50) |
version | integer | not null default 0
presence | integer | default 1
deleted | integer | not null default 0
Indexes:
"entity_pkey" PRIMARY KEY, btree (crmid)
"entity_createdtime_idx" btree (createdtime)
"entity_modifiedby_idx" btree (modifiedby)
"entity_modifiedtime_idx" btree (modifiedtime)
"entity_setype_idx" btree (setype) WHERE deleted = 0
"entity_smcreatorid_idx" btree (smcreatorid)
"entity_smownerid_idx" btree (smownerid)
"ftx_en_entity_description" gin (to_tsvector('vcrm_en'::regconfig,
for_fts(description)))
"entity_deleted_idx" btree (deleted)
Referenced by:
TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid)
REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "servicecontracts" CONSTRAINT "fk_1_servicecontracts" FOREIGN KEY
(servicecontractsid) REFERENCES entity(crmid) ON DELETE CASCADE
TABLE "vantage_cc2entity" CONSTRAINT "fk_vantage_cc2entity_entity"
FOREIGN KEY (crm_id) REFERENCES entity(crmid) ON UPDATE CASCADE ON DELETE
CASCADE
TABLE "vantage_emails_optout_history" CONSTRAINT
"fk_vantage_emails_optout_history_crmid" FOREIGN KEY (crmid) REFERENCES
entity(crmid) ON DELETE CASCADE
TABLE "vantage_emails_optout_history" CONSTRAINT
"fk_vantage_emails_optout_history_emailid" FOREIGN KEY (emailid) REFERENCES
entity(crmid) ON DELETE CASCADE
I execued the query:
ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
The db is stuck. The enity table has 2064740 records;
Watching locks:
select
pg_stat_activity.datname,pg_class.relname,pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,10),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;
datname | relname | mode
| granted | usename | substr | query_start |
age | procpid
-------------------+-------------------------------------+---------------------+---------+----------+------------+-------------------------------+-----------------+---------
db_test | entity_modifiedtime_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | | ExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | entity_modifiedby_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity_createdtime_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity | ShareLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | entity | AccessExclusiveLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | | ExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | entity_pkey | AccessExclusiveLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | | ShareLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | ftx_en_entity_description | AccessExclusiveLock | t | user
| ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 | 13574
db_test | | AccessShareLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 | 13574
db_test | entity_smcreatorid_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity_smownerid_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
db_test | entity_setype_idx | AccessExclusiveLock | t
| user | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
13574
Any idea for the db stuck?
