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.

Answer 2

Use setval() to set the starting value for the sequence.

Answer 3

with that definition, the column will get the next value from the geopoints_id_seq sequence. That sequence is not directly attached to the table. If you're migrating data, you have to create or update that sequence so its starting point is larger than the current max id in your table.

You should be able to set its new value with e.g.

   ALTER SEQUENCE geopoints_id_seq RESTART with 1692;

Or whatever select max(id) from table_name; yields

Answer 4

The rails 3 version looks like this:

namespace :db do
  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 }
      ActiveRecord::Base.subclasses.select { |c|c.base_class == c}.sort_by(&:name).each do |klass|
        classes << klass
      end
    end
    classes.each do |klass|
      puts "reseting sequence on #{klass.table_name}"
      ActiveRecord::Base.connection.reset_pk_sequence!(klass.table_name)
    end
  end
end

https://gist.github.com/909032

Answer 5

PG uses sequences :

Make it's current value 1 higher than the highest value in your table like this.

SELECT setval('geopoints_id_seq', 999999999, true);

Also see these

http://www.postgresql.org/docs/8.4/interactive/datatype-numeric.html#DATATYPE-SERIAL

http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html