Why does upsert_all disallow unique_by primary key?

[kapso]

I am looking to batch UPDATE... on my database, and found upsert_all. But it seems you cannot upsert using the primary key, so something like this...

Product.upsert_all([{id: 1, order_count: 23}, {id: 93, order_count: 67}], unique_by: :id)

...results in the following error - ArgumentError (No unique index found for id)

Curious why does upsert_all disallows updates by primary key. BTW, I did try unique_by: :products_pkey as well but that did not work either.

System configuration

Rails version: 6.0.3.2

Ruby version: 2.7.1

[simi]

Can you try to skip unique_by at all since you're using primary key and that's default option?

[kapso]

Yea skipping the unique_by option is the first thing I had tried, but that doesn't work either.

[simi]

Is your primary key present here?

ActiveRecord::Base.connection.schema_cache.primary_keys(Product.table_name)

[kapso]

@simi yes this is the response for the above "id"

[simi]

I can't reproduce your problem. Can you share script to reproduce this? You can use prepared template for that - https://github.com/rails/rails/blob/master/guides/bug_report_templates/active_record_master.rb.

[KapilSachdev]

Duplicate of #36528

# frozen_string_literal: true

begin
  require 'bundler/inline'
rescue LoadError => e
  warn 'Bundler version 1.10 or later is required. Please update your Bundler'
  raise e
end

gemfile(true) do
  source 'https://rubygems.org'

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  gem 'rails', '~>6.0.3'
  gem 'sqlite3'
  gem 'byebug'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :products, force: true do |t|
    t.bigint :order_count
  end
end

class Product < ActiveRecord::Base
end

class BugTest < Minitest::Test
  def test_association_stuff
    Product.upsert_all([{ id: 1, order_count: 23 }, { id: 93, order_count: 67 }], unique_by: :id)
  end
end

Failing on 6-0-stable.
Passing on master due to

rails/activerecord/lib/active_record/insert_all.rb

Lines 77 to 78 in 9078abc

	elsif match == primary_keys
	unique_by.nil? ? nil : ActiveRecord::ConnectionAdapters::IndexDefinition.new(model.table_name, "#{model.table_name}_primary_key", true, match)

This is in master for a year now, so unsure whether its being targeted for 6.1 or wasn't able to get into 6.0.x somehow, but i think it should be backported.

cc @kaspth

[KapilSachdev]

@jonathanhefner can you provide some information if this should be backported or not?
We can close this if we are not interested in backporting this.

[SampsonCrowley]

this really feels like it should be backported. was extremely surprised that PK index doesn't count as a unique index to use when specifying PK value to skip needing to check all indexes for known unique values

[kapso]

Curious, if there's a fix coming out for this anytime soon? since 6.0.3.4 did not have it.

Thanks.

[kapso]

Just noticed that 6.1.0.rc1 is out, has the above issue been fixed in 6.1?

[KapilSachdev]

It will be shipped with 6.1.0 and is available in 6.1.0.rc1(8389f99).

rails/activerecord/lib/active_record/insert_all.rb

Lines 77 to 78 in 8389f99

	elsif match == primary_keys
	unique_by.nil? ? nil : ActiveRecord::ConnectionAdapters::IndexDefinition.new(model.table_name, "#{model.table_name}_primary_key", true, match)

[kapso]

Updated to 6.1, still finding issues...

1. When combining new record insertion + update existing records in upsert_all, I see the following

ArgumentError (All objects being inserted must have the same keys)

2. When only doing update existing records, I see the following error

ActiveRecord::NotNullViolation (PG::NotNullViolation: ERROR:  null value in column "product_name" violates not-null constraint)

for this Product.upsert_all([{id: 85, count: 899929}, {id: 86, count: 9223232}])