Rails core - Feb 2011 - Postgres adapter optimization for add_column method

Looking at postgres adapter I found that add_column works not optimal.
First it executes ALTER TABLE ADD COLUMN query and only then sets
defaults and NOT NULL if they are needed. But in the real production
environment it takes hours to run such migrations.

For example we run:

add_column :users, :failed_attempts, :integer, :null=>false, :default=>0
this migration executes:

UPDATE "users" SET "failed_attempts"=0 WHERE
"failed_attempts" IS NULL

which took more then one hour to complete for the table with 2M+
records.

But:

ALTER TABLE users ADD COLUMN failed_attempts INTEGER NOT NULL DEFAULT
0;

took just 2 minutes to complete for the same table.

So such migration will lock the users table for an hour (or even more)
and so stop the whole application for hours instead of just 2 minutes.

I''ve created a ticket and assigned a patch:
https://rails.lighthouseapp.com/projects/8994/tickets/6422-patch-postgres-adapter-optimization-for-add_column-method

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

This is interesting.
I think there aren''t so many of us using Postgres
so quite a few bugs seem to have crept in over time.

Perhaps you can also implement some optimisations like Pratik has done for 
multi column changes on MySQL
http://m.onkey.org/bulk-alter-table-with-rails-3-and-mysql

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.

As a postgres user, +1 to the optimizations.

On Tue, Feb 15, 2011 at 7:49 PM, matthewrudyjacobs@gmail.com
<matthewrudyjacobs@gmail.com> wrote:
> This is interesting.
> I think there aren''t so many of us using Postgres
> so quite a few bugs seem to have crept in over time.
>
> Perhaps you can also implement some optimisations like Pratik has done for
> multi column changes on MySQL
> http://m.onkey.org/bulk-alter-table-with-rails-3-and-mysql
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Core" group.
> To post to this group, send email to rubyonrails-core@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-core+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-core?hl=en.
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Core" group.
To post to this group, send email to rubyonrails-core@googlegroups.com.
To unsubscribe from this group, send email to
rubyonrails-core+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-core?hl=en.