on_duplicate_key_update behavior change when using unique column validation and Rails 7.2

[kaliara]

Summary

I'm try to use the activerecord-import gem to import CSV files of "Comans" (food manufacturers). I want to insert new records and update existing records. The column used to determine whether a record is existing is the domain_name column.

Set up

I already have a Coman created in my database with year founded: 2015:

Coman.create(domain_name: 'dips.com', year_founded: 2015, name: 'Dips')

Import to upsert

Next, I want to import this CSV. Notice that the "dips.com" record already exists from the line above.

domain_name,name,year_founded
sauces.com,SaucesRus,2001
dips.com,DipsRus,2001
chips.com,ChipsRus,2001

So I make a call to import in my service:

Coman.import(
  [:domain_name, :name, :year_founded],
  CSV.open(@csv_file, headers: :first_row).map(&:to_h),
  validate: true,
  track_validation_failures: true,
  batch_size: 1000,
  on_duplicate_key_update: { conflict_target: [ :domain_name ], columns: [ :name, :year_founded ] }
)

Before the upgrading to Rails 7.2, the on_duplicate_key_update option was working correctly. It would result in the 3 following rows in the database:

#<Coman:0x00000001250117d0
id: 1,
name: "Dips",
domain_name: "dips.com",
year_founded: "2001"  # << updated

#<Coman:0x0000000125011190
id: 2,
name: "SaucesRus",
domain_name: "sauces.com",
year_founded: "2001"

#<Coman:0x00000001250108d0
id: 3,
name: "ChipsRus",
domain_name: "chips.com",
year_founded: "2001"

⚠️ However, after upgrading to Rails 7.2, the on_duplicate_key_update option doesn't seem to be working correctly. Instead, it produces the following records:

#<Coman:0x00000001250117d0
id: 1,
name: "Dips",
domain_name: "dips.com",
year_founded: "2015"  # << NOT updated

#<Coman:0x0000000125011190
id: 2,
name: "SaucesRus",
domain_name: "sauces.com",
year_founded: "2001"

#<Coman:0x00000001250108d0
id: 3,
name: "ChipsRus",
domain_name: "chips.com",
year_founded: "2001"

Notes / Thoughts

It seems to skip records where the domain name is in conflict.
Before upgrade: {:success=>true, :data=>{:import_errors=>[]}}
After upgrade: {:success=>true, :data=>{:import_errors=>[{:row=>1, :domain_name=>"dips.com", :errors=>["has already been taken"]}]}}

I do have the following validation on my model (which was present before the upgrade as well):

# /app/models/coman.rb
...
validates :domain_name, uniqueness: true

When I remove this validation, it works correctly. 🤷 I'm not sure what difference between Rails 7.1 and 7.2 would account for that.

Also, here is a diff of the SQL statement before and after the upgrade from Rails 7.1 to Rails 7.2

Question

Am I missing something? Is the importer expected to work with validations in this way? Should I use validate: false instead?

Coman.import(
  [:domain_name, :name, :year_founded],
  CSV.open(@csv_file, headers: :first_row).map(&:to_h),
  validate: false,  # << skip validations
  track_validation_failures: true,
  batch_size: 1000,
  on_duplicate_key_update: { conflict_target: [ :domain_name ], columns: [ :name, :year_founded ] }
)

[kaliara]

Looks like it could be related/duplicate of #841

[jkowens]

Yeah, the issue for both is that the uniqueness validator was preventing the record from being upserted. Should be resolved by #845.

[kaliara]

That fixed it @jkowens, thank you very much!