Rake Task to fix PostGres sequences after doing row level inserts.

fix_sequences.rake

namespace :db do
  desc "Fix PostGres Sequences, should run after doing row level inserts."
  task :fix_sequences => :environment do

    def exec_sql(sql)
      #puts "Executing SQL...\n#{sql}"
      ActiveRecord::Base.connection.execute(sql)
    end


    def check_seq(seq)
      results = exec_sql <<-EOS
        SELECT COALESCE(MAX(#{seq["primary_key_column"]}),0) AS max_id FROM #{seq["schema"]}.#{seq["table"]};
      EOS
      max_id = results.first["max_id"]
      if max_id.nil?
        puts "Unable to get max id for #{seq.inspect}"
        return
      end


      results = exec_sql <<-EOS
        SELECT nextval('#{seq["schema"]}.#{seq["sequence"]}') AS next_val;
      EOS
      next_val = results.first["next_val"]
      if next_val.nil?
        puts "Unable to get sequence next val for #{seq.inspect}"
        return
      end

      next_val = next_val.to_i
      max_id = max_id.to_i

      if next_val < max_id
        puts "Fixing sequence #{seq.inspect}"

        exec_sql <<-EOS
          SELECT setval('#{seq["schema"]}.#{seq["sequence"]}', (SELECT COALESCE(MAX(#{seq["primary_key_column"]}),0) FROM #{seq["schema"]}.#{seq["table"]}));
        EOS
      end
    end

    results = exec_sql <<-EOS
      SELECT  N.nspname as "schema", S.relname as "sequence", C.attname as "primary_key_column", T.relname as "table"
      FROM
        pg_class AS S,
        pg_depend AS D,
        pg_class AS T,
        pg_attribute AS C,
        pg_namespace AS N
      WHERE S.relkind = 'S'
          AND S.oid = D.objid
          AND D.refobjid = T.oid
          AND D.refobjid = C.attrelid
          AND D.refobjsubid = C.attnum
          AND N.oid = S.relnamespace
      ORDER BY N.nspname, S.relname;
    EOS
    sequences_to_check = results.to_a

    cur_schema = nil
    sequences_to_check.each do |seq|
      if seq["schema"] != cur_schema
        cur_schema = seq["schema"]
        puts "Checking sequences in #{cur_schema}."
      end
      check_seq seq
    end

    puts "Done."



  end
end