Обсуждение: add column with default value is very slow
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?
AI Rumman wrote: > 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: [all locks are granted] > Any idea for the db stuck? To add the column, PostgreSQL has to modify all rows in the table. But then 2064740 records is not very much, so it shouldn't take forever. Do you see processor or I/O activity? Yours, Laurenz Albe
On Tue, Sep 11, 2012 at 07:20:28PM +0600, AI Rumman wrote: > I have a table as follows: > 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; such alter table has to rewrite whole table. So it will take a while > Watching locks: output of this was perfectly unreadable, because your email client wrapped lines at some random places. In future - please put such dumps on some paste site, or just attach it to mail, and not copy/paste them to body of message. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
I added the excel file for locks data.
I was surprised to see that while I was updating a single column value for all records in a tables, all indexes are locked by the server.
On Tue, Sep 11, 2012 at 7:44 PM, hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Tue, Sep 11, 2012 at 07:20:28PM +0600, AI Rumman wrote:
> I have a table as follows:> I execued the query:such alter table has to rewrite whole table. So it will take a while
> 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:
output of this was perfectly unreadable, because your email client
wrapped lines at some random places.
In future - please put such dumps on some paste site, or just attach it
to mail, and not copy/paste them to body of message.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
Вложения
On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote: > I added the excel file for locks data. well, it worked, but why didn't you just make it text file, in notepad or something like this? > I was surprised to see that while I was updating a single column value for > all records in a tables, all indexes are locked by the server. alter table is not locked (At least looking at the pg_locks data you showed). this means - it just takes long time. Please do: select pg_total_relation_size('entity'); to see how much data it has to rewrite. for future - just don't do alter table, with default, and not null. doing it via add column; set default; batch-backfill data, set not null will take longer but will be done with much shorter locks. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
Table size is 1186 MB.
I split the command in three steps as you said, but the result same during the update operation.
One more thing, I have just restored the db from dump and analyzed it and
I am using Postgresql 9.1 with 3 GB Ram with dual core machine.
On Tue, Sep 11, 2012 at 7:59 PM, hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote:well, it worked, but why didn't you just make it text file, in notepad or
> I added the excel file for locks data.
something like this?alter table is not locked (At least looking at the pg_locks data you
> I was surprised to see that while I was updating a single column value for
> all records in a tables, all indexes are locked by the server.
showed).
this means - it just takes long time.
Please do:
select pg_total_relation_size('entity');
to see how much data it has to rewrite.
for future - just don't do alter table, with default, and not null.
doing it via add column; set default; batch-backfill data, set not null
will take longer but will be done with much shorter locks.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Tue, Sep 11, 2012 at 08:04:06PM +0600, AI Rumman wrote: > Table size is 1186 MB. if it takes long, it just means that your IO is slow. > I split the command in three steps as you said, but the result same during > the update operation. three? I was showing four steps, and one of them is usually consisting hundreds, if not thousands, of queries. > One more thing, I have just restored the db from dump and analyzed it and > I am using Postgresql 9.1 with 3 GB Ram with dual core machine. so it looks like your IO channel is slow. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
On 09/11/2012 09:55 AM, AI Rumman wrote: > I added the excel file for locks data. > I was surprised to see that while I was updating a single column value > for all records in a tables, all indexes are locked by the server. Any ALTER TABLE command locks the whole table in ACCESS EXCLUSIVE mode, indexes included. See the description of ACCESS EXCLUSIVE lock at <http://www.postgresql.org/docs/current/static/explicit-locking.html> cheers andrew