ActiveRecord::ConnectionAdapters::SQLServerAdapter does not support skipping duplicates

[Quintasan]

Issue

bundle exec rails db:seed throws ActiveRecord::ConnectionAdapters::SQLServerAdapter does not support skipping duplicates.

Expected behavior

A single SQL INSERT should be generated and executed

Actual behavior

When using #insert_all(Array[Hash]) I'm getting ActiveRecord::ConnectionAdapters::SQLServerAdapter does not support skipping duplicates

How to reproduce

ENTRIES = [{name: "Test"}]
ENTRIES_DATA = ENTRIES.map { |e| e.merge(created_at: Time.zone.now, updated_at: Time.zone.now) }
Model.insert_all(ENTRIES_DATA)

Details

• Rails version: Rails 6.0.3.4
• SQL Server adapter version: 6.0.0
• TinyTDS version: 2.1.3
• FreeTDS details: 1.1.6-1ubuntu0.1

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.1.6
             freetds.conf directory: /etc/freetds
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: no
                             GnuTLS: yes
                               MARS: yes

[jeffchuber]

@Quintasan did you find a solution or workaround for this? I'm seeing the same behavior with PostgreSQLAdapter. I worry it's an environment thing, because it works fine on my colleagues computer.

[Quintasan]

The only workaround I could think of was using find_or_create_by.

[jeffchuber]

I got "something" working... for some reason insert_all! works while insert_all does not. 🤷 🤦

[mgrunberg]

Some info I gathered while working on the issue https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues/859.

insert_all semantic is "skip on duplicates". ActiveRercord validates that the database adapter supports "skip on duplicate" when you call Model.insert_all, before reaching the adapters code and even with a single record. Mysql adapter supports it. Postgresql adapter supports it starting on version 90500 (I guess 90500 means version 9.5). This adapter doesn't.

insert_all! semantic is "raise on duplicate" (raise ActiveRecord::RecordNotUnique on duplicates).

Since the issue involves just one record, as mentioned, using insert_all! and rescuing from ActiveRecord::RecordNotUnique or calling find_or_create_by are valid workarounds.

[jeffchuber]

Thanks @mgrunberg . I ended up finding out that psql --versionwas lying to me and I had an older version than it was letting on. Now insert_all, upsert_all, etc - all work fine.