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 < dep...@depesz.com> wrote: > 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/ >