Ecto.Repo insert_all raises when there are more than one conflicting indexes

Hello,

I am doing the following upsert

Repo.insert_all(Staff, entries,
  on_conflict:
    {:replace,
     [:some, :nice, :fields, :updated_at]},
  conflict_target: [:org_id, :github_user_id]
)

and get the following error


 ** (Postgrex.Error) ERROR 23505 (unique_violation) duplicate key value violates unique constraint "staffs_org_id_github_email_index"

table: staffs
constraint: staffs_org_id_github_email_index

Key (org_id, github_email)=(1fa668c3-4a1f-4465-9a86-61e4874ba33e, bruce_57@wayne.org) already exists

The reason is that I have two constraints (because of two unique indexes) which are conflicting:

  • create unique_index(:staffs, [:org_id, :github_user_idl])
  • create unique_index(:staffs, [:org_id, :github_email])

Unfortunately I can’t just add github_email to the conflicting target because I’d get the following error then:
** (KeyError) key :constraint not found in: %{code: :invalid_column_reference, file: "plancat.c", line: "837", message: "there is no unique or exclusion constraint matching the ON CONFLICT specification", pg_code: "42P10", routine: "infer_arbiter_indexes", severity: "ERROR", unknown: "ERROR"}

Any idea how I can leverage the upsert functionality of insert_all while having those two conflicting constraints? Thank you.

I may be misremembering but I think you can leave the conflict_target option out and it will automatically handle it based on the unique indexes available.

Thank you for your reply!

I’ve tested that actually but I got

** (ArgumentError) the :conflict_target option is required on upserts by PostgreSQL
     code: assert {2, nil} = Staffs.upsert_all(@input_list, org.org_id)
     stacktrace:
       (ecto_sql 3.7.0) lib/ecto/adapters/postgres/connection.ex:1333: Ecto.Adapters.Postgres.Connection.error!/2
       (ecto_sql 3.7.0) lib/ecto/adapters/postgres/connection.ex:181: Ecto.Adapters.Postgres.Connection.on_conflict/2
       (ecto_sql 3.7.0) lib/ecto/adapters/postgres/connection.ex:165: Ecto.Adapters.Postgres.Connection.insert/7
       (ecto_sql 3.7.0) lib/ecto/adapters/sql.ex:657: Ecto.Adapters.SQL.insert_all/9
       (ecto 3.7.1) lib/ecto/repo/schema.ex:58: Ecto.Repo.Schema.do_insert_all/7
       test/my_app/staffs_test.exs:299: (test)

:frowning:

1 Like

Thanks for checking! I am almost sure one database does not require it, so maybe that’s MySQL. It seems it is not possible to achieve what you want, as Postgres will only use one unique index at a time? So maybe you’ll need to use transactions instead of an upsert.

1 Like

Is github_user_ idl a typo? Shouldn’t it be github_user_ id?

Yeah it’s just a typo here in the post. Unfortunately I can’t edit it anymore.