postgresql nextval generating existing values

Question

I had to migrate from a mySql based ruby on rails app to using postgresql. No problems but one so far, and I don't know how to solve it.

The migration of data brought ids along with it, and postgresql is now having problems with existing ids: it's not clear to me where it gets the value that it uses to determine the base for nextval: it certainly isn't the highest value in the column, although you might think that would be a good idea. In any case, it's now colliding with existing id values. id column, created from a standard RoR migration is defined as

not null default nextval('geopoints_id_seq'::regclass)

Is there some place that the value it uses as a base can be hacked? This problem could now arise in any of 20 or so tables: I could use

'select max(id) from <table_name>' 

but that seems to make the idea of an autoincrement column pointless.

How is this best handled?

Answer 1

There is a reset_pk_sequences! method on the Postgres adapter. You can call it and it will set it to max(id) + 1, which is probably what you want.

In some projects I get data ETL'ed in often enough to warrant a rake task to do this for all models, or for a specified model. Here's the task - include it in some Rakefile or in it's own under lib/tasks:

desc "Reset all sequences. Run after data imports"
task :reset_sequences, :model_class, :needs => :environment do |t, args|
  if args[:model_class]
    classes = Array(eval args[:model_class])
  else
    puts "using all defined active_record models"
    classes = []
    Dir.glob(RAILS_ROOT + '/app/models/**/*.rb').each { |file| require file }
    Object.subclasses_of(ActiveRecord::Base).select { |c|
      c.base_class == c}.sort_by(&:name).each do |klass|
        classes << klass
      end
  end
  classes.each do |klass|
      next if klass == CGI::Session::ActiveRecordStore::Session && ActionController::Base.session_store.to_s !~ /ActiveRecordStore/

        puts "reseting sequence on #{klass.table_name}"
        ActiveRecord::Base.connection.reset_pk_sequence!(klass.table_name)
    end
end

Now you can run this either for all models (defined under RAIS_ROOT/app/models) using rake reset_sequences, or for a specific model by passing in a class name.