Autoincrement on Join Table not working

Hi all, I'm trying to create a directed may to many relationship against a single table as seen here: http://wiki.rubyonrails.com/rails/pages/HowToUseManyToManyAgainstASingleTable

I'm running 1.2RC2 right now and am doing this on a Postgresql 8.2 database.

I've defined my model like this:

class Item < ActiveRecord::Base   has_and_belongs_to_many :tags,     :class_name => 'Item',     :join_table => 'items_items',     :foreign_key => "parent_id",     :association_foreign_key => "child_id"

  has_and_belongs_to_many :tasks,     :class_name => 'Item',     :join_table => 'items_items',     :foreign_key => "child_id",     :association_foreign_key => "parent_id"

  validates_presence_of :name end

Then, I try it out in the console:

phone_context = Item.new(:name => "phone") phone_context.save => true email_context = Item.new(:name => "email") email_context.save => true alice_task = Item.new(:name => "speak with alice about account") alice_task.save => true email_context.tasks << alice_task

=> [#<Item:0x32e4e30 @attributes={"end_date"=>nil, "start_date"=>nil, "name"=>"speak with alice about account", "done"=>false, "updated_on"=>Thu Jan 18 11:31:37 -0500 2007, "id"=>26, "reset"=>nil, "due"=>false, "end_time"=>nil, "start_time"=>nil}, errors#<ActiveRecord::Errors:0x32dc514 @errors={}, @base=#<Item:0x32e4e30 ...>, new_recordfalse, new_record_before_savetrue]

email_context.tasks[0].name => "speak with alice about account" alice_task.tags[0].name => "email"

So far so good. Now, since I have Alice's phone number too, I decide to add the alice_task to the phone_context tasks.

phone_context.tasks << alice_task ActiveRecord::StatementInvalid: RuntimeError: ERROR C23505 Mduplicate key violates unique constraint "items_items_pkey" Fnbtinsert.c L277 R_bt_check_unique: INSERT INTO items_items ("id", "child_id", "parent_id") VALUES (26, 24, 26)         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:128:in `log'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:152:in `execute'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/has_and_belongs_to_many_association.rb:132:in `insert_record'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:26:in `<<'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:23:in `each'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:23:in `<<'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:59:in `transaction'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/transactions.rb:95:in `transaction'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/transactions.rb:121:in `transaction'         from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:22:in `<<'         from (irb):9

When I run "select * from items_items_id_seq" on my database, I get this back:

   sequence_name | last_value | increment_by | max_value

I guess what I was doing was wrong, Here's the updated model for anyone that's curious.

class Item < ActiveRecord::Base   has_many :notes   has_and_belongs_to_many :tags,     :class_name => 'Item',     :join_table => 'items_items',     :foreign_key => "parent_id",     :association_foreign_key => "child_id",     :select => "items.*",     :insert_sql => 'INSERT INTO items_items ("child_id", "parent_id") VALUES (#{record.id}, #{id})'

  has_and_belongs_to_many :tasks,      :class_name => 'Item',      :join_table => 'items_items',      :foreign_key => "child_id",      :association_foreign_key => "parent_id",      :select => "items.*",      :insert_sql => 'INSERT INTO items_items ("child_id", "parent_id") VALUES (#{id}, #{record.id})'

  validates_presence_of :name end

What I was not aware of was that Rails would attempt to populate the records found through the join with the attributes of the join table (not something I particularly see the value of) and in so doing overwrites the accessor for the real ids of the found records. Further, and this one I can't explain, the default insert statement wants to make the association foreign key the primary key of the record of the join table.

Is this stuff really intended to work this way?