Creating trees from unsaved records leading to duplicate key value constraint issues

[chrisvariety]

Hey there. Lovely library you have here.

I have a class like:

class Field
  acts_as_tree order: 'sort_order', dependent: :destroy, name_column: :body
end

I am trying to build a tree and then save all at once, e.g.

a = Field.new(body: "a")
b = Field.new(body: "b")
c = Field.new(body: "c")
a.children << b
b.children << c
a.save

I then get this error:

1.9.3p194 :009 > a.save
   (0.1ms)  BEGIN
  SQL (8.2ms)  INSERT INTO "fields" ("body", "created_at", "extra", "form_id", "parent_id", "required", "sort_order", "type", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["body", "a"], ["created_at", Thu, 26 Jul 2012 14:11:05 EDT -04:00], ["extra", nil], ["form_id", 31], ["parent_id", nil], ["required", false], ["sort_order", nil], ["type", "Field::MultipleChoice"], ["updated_at", Thu, 26 Jul 2012 14:11:05 EDT -04:00]]
  Field Load (0.8ms)  SELECT "fields".* FROM "fields" WHERE "fields"."id" = 255 ORDER BY sort_order ASC LIMIT 1
  FieldHierarchy Load (0.5ms)  SELECT "field_hierarchies".* FROM "field_hierarchies" WHERE "field_hierarchies"."descendant_id" = 255 ORDER BY "field_hierarchies".generations asc
  SQL (0.6ms)  INSERT INTO "fields" ("body", "created_at", "extra", "form_id", "parent_id", "required", "sort_order", "type", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["body", "b"], ["created_at", Thu, 26 Jul 2012 14:11:05 EDT -04:00], ["extra", nil], ["form_id", 31], ["parent_id", 255], ["required", false], ["sort_order", nil], ["type", "Field::MultipleChoice"], ["updated_at", Thu, 26 Jul 2012 14:11:05 EDT -04:00]]
  Field Load (0.4ms)  SELECT "fields".* FROM "fields" WHERE "fields"."id" = 256 ORDER BY sort_order ASC LIMIT 1
  FieldHierarchy Load (0.3ms)  SELECT "field_hierarchies".* FROM "field_hierarchies" WHERE "field_hierarchies"."descendant_id" = 256 ORDER BY "field_hierarchies".generations asc
  SQL (1.2ms)  INSERT INTO "fields" ("body", "created_at", "extra", "form_id", "parent_id", "required", "sort_order", "type", "updated_at") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"  [["body", "c"], ["created_at", Thu, 26 Jul 2012 14:11:05 EDT -04:00], ["extra", nil], ["form_id", 31], ["parent_id", 256], ["required", false], ["sort_order", nil], ["type", "Field::MultipleChoice"], ["updated_at", Thu, 26 Jul 2012 14:11:05 EDT -04:00]]
  SQL (0.4ms)  INSERT INTO "field_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3)  [["ancestor_id", 257], ["descendant_id", 257], ["generations", 0]]
   (0.4ms)   INSERT INTO "field_hierarchies"
 (ancestor_id, descendant_id, generations)
 SELECT x.ancestor_id, 257, x.generations + 1
 FROM "field_hierarchies" x
 WHERE x.descendant_id = 256

  Field Load (0.3ms)  SELECT "fields".* FROM "fields" WHERE "fields"."parent_id" = 257 ORDER BY sort_order ASC, sort_order
  SQL (0.1ms)  INSERT INTO "field_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3)  [["ancestor_id", 256], ["descendant_id", 256], ["generations", 0]]
   (0.2ms)   INSERT INTO "field_hierarchies"
 (ancestor_id, descendant_id, generations)
 SELECT x.ancestor_id, 256, x.generations + 1
 FROM "field_hierarchies" x
 WHERE x.descendant_id = 255

  SQL (0.5ms)  INSERT INTO "field_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3)  [["ancestor_id", 257], ["descendant_id", 257], ["generations", 0]]
   (0.2ms)  ROLLBACK
ActiveRecord::RecordNotUnique: PG::Error: ERROR:  duplicate key value violates unique constraint "index_field_hierarchies_on_ancestor_id_and_descendant_id"
DETAIL:  Key (ancestor_id, descendant_id)=(257, 257) already exists.
: INSERT INTO "field_hierarchies" ("ancestor_id", "descendant_id", "generations") VALUES ($1, $2, $3)
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/postgresql_adapter.rb:1171:in `get_last_result'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/postgresql_adapter.rb:1171:in `exec_cache'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/postgresql_adapter.rb:665:in `block in exec_query'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activesupport-3.2.7.rc1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/postgresql_adapter.rb:663:in `exec_query'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/abstract/database_statements.rb:63:in `exec_insert'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/abstract/database_statements.rb:90:in `insert'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/relation.rb:66:in `insert'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/persistence.rb:367:in `create'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/timestamp.rb:57:in `create'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/callbacks.rb:268:in `block in create'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activesupport-3.2.7.rc1/lib/active_support/callbacks.rb:403:in `_run__1815317462497579710__create__1621482155338212246__callbacks'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activesupport-3.2.7.rc1/lib/active_support/callbacks.rb:405:in `__run_callback'
... 62 levels...
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/validations.rb:50:in `save'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/attribute_methods/dirty.rb:22:in `save'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:241:in `block (2 levels) in save'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:295:in `block in with_transaction_returning_status'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/connection_adapters/abstract/database_statements.rb:192:in `transaction'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:208:in `transaction'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:293:in `with_transaction_returning_status'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:241:in `block in save'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:252:in `rollback_active_record_state!'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/activerecord-3.2.7.rc1/lib/active_record/transactions.rb:240:in `save'
    from (irb):9
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/railties-3.2.7.rc1/lib/rails/commands/console.rb:47:in `start'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/railties-3.2.7.rc1/lib/rails/commands/console.rb:8:in `start'
    from /Users/chrismcc/.rvm/gems/ruby-1.9.3-p194-fast@amanda/gems/railties-3.2.7.rc1/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'1.9.3p194 :010 >

Any ideas?

Note that it DOES work if I do:

a = Field.new(body: "a")
b = Field.new(body: "b")
c = Field.new(body: "c")
a.children << b
b.children << c
c.save
b.save
a.save

Thanks!

[mceachen]

Yeah, I think your first code snippet, if at all possible, shouldn't error out. I'll look into it.

[mceachen]

Created branch: https://github.com/mceachen/closure_tree/tree/lucky_issue_13

[mceachen]

Fixed in version 3.3.1: http://travis-ci.org/#!/mceachen/closure_tree/builds/2063581

[chrisvariety]

Hey, look at that, it works. Super awesome. Thanks so much!