CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
pr1001
pr1001

Reputation: 21962

ActiveRecord find_each and Postgres

I get the following error:

PGError: ERROR:  operator does not exist: character varying >= integer
LINE 1: ...CT  "games".* FROM "games"  WHERE ("games"."uuid" >= 0) ORDE...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT  "games".* FROM "games"  WHERE ("games"."uuid" >= 0) ORDER BY "games"."uuid" ASC LIMIT 1000

when I try to do this:

Game.find_each do |game|
  # ...
end

I have a string (UUID) primary key for my model:

class Game < ActiveRecord::Base
  self.primary_key = 'uuid'

  before_create do |game|
    game.uuid = UUIDTools::UUID.timestamp_create().to_s if game.uuid.blank?
  end
end

I don't know why ActiveRecord is putting in that WHERE clause but it's completely unnecessary and the cause for the type error (since it's a string column, not an integer one).

So, how can I avoid this? It there something I should put in my model definition? Or should I avoid find_each and use a different method? This is a for a rake task that just goes through all the entries and looks up some additional information...

Upvotes: 4

Views: 1772

Answers (3)

Dan Kohn
Dan Kohn

Reputation: 34327

This blog post has the fix for your bug:

in lib/clean_find_in_batches.rb

module CleanFindInBatches

  def self.included(base)
    base.class_eval do
      alias :old_find_in_batches :find_in_batches
      alias :find_in_batches :replacement_find_in_batches
    end
  end

  # Override due to implementation of regular find_in_batches
  # conflicting using UUIDs
  def replacement_find_in_batches(options = {}, &block)
    relation = self
    return old_find_in_batches(options, &block) if relation.primary_key.is_a?(Arel::Attributes::Integer)
    # Throw errors like the real thing
    if (finder_options = options.except(:batch_size)).present?
      raise "You can't specify an order, it's forced to be #{batch_order}" if options[:order].present?
      raise "You can't specify a limit, it's forced to be the batch_size" if options[:limit].present?
      raise 'You can\'t specify start, it\'s forced to be 0 because the ID is a string' if options.delete(:start)
      relation = apply_finder_options(finder_options)
    end
    # Compute the batch size
    batch_size = options.delete(:batch_size) || 1000
    offset = 0
    # Get the relation and keep going over it until there's nothing left
    relation = relation.except(:order).order(batch_order).limit(batch_size)
    while (results = relation.offset(offset).limit(batch_size).all).any?
      block.call results
      offset += batch_size
    end
    nil
  end

end

and in config/initializers/clean_find_in_batches.rb

ActiveRecord::Batches.send(:include, CleanFindInBatches)

Upvotes: 0

simaob
simaob

Reputation: 423

I think that the find_each without any parameters will result in a find_by_id where id >= 0. Even though ActiveRecord uses the correct column, in your case, it doesn't seem to know that the column is of type varchar, instead of integer.

You can try to use another find method, or maybe try adding some conditions to the find_each.

This might be relevant for the issue of using a string as a primary key: http://railsforum.com/viewtopic.php?id=11110

Cheers

Upvotes: 0

tokland
tokland

Reputation: 67850

It seems find_each has a bug with non-numeric primary keys:

https://groups.google.com/group/compositekeys/browse_frm/month/2011-06

Upvotes: 4

Related Questions