Ecto: Strange behavior with `conflict_target`

[edisonywh]

Environment

• Elixir version (elixir -v): Elixir 1.9.1 (compiled with Erlang/OTP 20)
• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): PostgresSQL 11.5
• Ecto version (mix deps): ecto: 3.4.0
• Database adapter and version (mix deps): ecto_sql: 3.4.1
• Operating system: MacOS Catalina

Current behavior

There is a few different issues happening, I tried it out with the #ecto Slack channel so I'm separating them by cases.

It is worth noting that, for most of the cases below, I create my index in migration like this:

create unique_index(:readers, "lower(slug)", name: :readers_email_index) (I will explain why I manually name it later)

Case 1

My upsert code looks like this:

%Reader{}
    |> Reader.changeset(attrs)
    |> Repo.insert(
      on_conflict: [set: [updated_at: now]],
      conflict_target: [:email],
      returning: [:id]
    )

I am getting this error when I try an upsert:

ERROR 42P10 (invalid_column_reference) there is no unique or exclusion constraint matching the ON CONFLICT specification

When I check my index via psql, this is what I get: :readers_email_index

Case 2

Trying out with different syntax for conflict_target.

%Reader{}
    |> Reader.changeset(attrs)
    |> Repo.insert(
      on_conflict: [set: [updated_at: now]],
-      conflict_target: [:email],
+      conflict_target: {:constraint, :readers_email_index},
      returning: [:id]
    )

This is now returning:

ERROR 42704 (undefined_object) constraint "readers_email_index" for table "readers" does not exist

(As checked in psql again, the index definitely does exists.)

Case 3

Tried to remove the on_conflict altogether:

%Reader{}
    |> Reader.changeset(attrs)
    |> Repo.insert()

Ecto is now failing correctly:

no match of right hand side value: {:error, #Ecto.Changeset<action: :insert, changes: %{email: "[email protected]"}, errors: [email: {"has already been taken", [constraint: :unique, constraint_name: "readers_email_index"]}], data: #Caesar.Readers.Reader<>, valid?: false>}

Case 4 (conflict_target: :email)

I also tried to run with conflict_target: :email.

This returns:

ERROR 42P10 (invalid_column_reference) there is no unique or exclusion constraint matching the ON CONFLICT specification

Case 5 (conflict_target: :readers_email_index)

unknown field :readers_email_index in conflict_target

Note

It is worth noting that, I initially had my index created like this:

create unique_index(:readers, :email)

And this works fine, conflict_target is fine, unique_constraint is fine. However I wanted to add lower() to get some DB guarantee, so I did and updated it to look like this:

- create unique_index(:readers, :email)
+ create unique_index(:readers, "lower(slug)")

This now generates me a index name that looks something like :readers_lower_email_index or something and IIRC, conflict_target started failing here. I figured that since the previous index name worked perfectly, I will use that one instead, so I rollbacked my database, created with the previous syntax, got the previous index name (readers_email_index), and then updated the whole migration to look like this:

create unique_index(:readers, "lower(slug)", name: :readers_email_index)

For now since this is not working I've just resorted not to use lower at all, and decided to manually lowercase data in my application level.

Expected behavior

I expect the conflict_target option to actually work.

[josevalim]

Hi @edisonywh! Case 2 should work, can you please provide a sample app that reproduces the error so we can take a further look at it? Thanks!

[edisonywh]

Sure, here you go, I did a quick & dirty version but I think this should show exactly what I'm talking about.

https://github.com/edisonywh/ecto_conflict_target_issue

iex(1)> ConflictTargetIssue.Readers.replicate_upsert_behavior(%{email: "abc"})
[debug] QUERY ERROR db=1.4ms queue=6.8ms idle=9308.6ms
INSERT INTO "readers" AS r0 ("email","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT ON CONSTRAINT "readers_email_index" DO UPDATE SET "updated_at" = $4 RETURNING "id" ["abc", ~N[2020-03-30 22:05:18], ~N[2020-03-30 22:05:18], ~N[2020-03-30 22:05:18]]
** (Postgrex.Error) ERROR 42704 (undefined_object) constraint "readers_email_index" for table "readers" does not exist

    query: INSERT INTO "readers" AS r0 ("email","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT ON CONSTRAINT "readers_email_index" DO UPDATE SET "updated_at" = $4 RETURNING "id"
    (ecto_sql) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto) lib/ecto/repo/schema.ex:655: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

Now checking from psql

Indexes:
    "readers_pkey" PRIMARY KEY, btree (id)
    "readers_email_index" UNIQUE, btree (lower(email::text))

[edisonywh]

I also tried a variant without the custom renaming:

create unique_index(:readers, ["lower(email)"])

%Reader{}
    |> Reader.changeset(attrs)
    |> Repo.insert(
      on_conflict: [set: [updated_at: now]],
      conflict_target: {:constraint, :readers_lower_email_index},
      returning: [:id]
    )

Indexes:
    "readers_pkey" PRIMARY KEY, btree (id)
    "readers_lower_email_index" UNIQUE, btree (lower(email::text))

Same thing happens:

** (Postgrex.Error) ERROR 42704 (undefined_object) constraint "readers_lower_email_index" for table "readers" does not exist

    query: INSERT INTO "readers" AS r0 ("email","inserted_at","updated_at") VALUES ($1,$2,$3) ON CONFLICT ON CONSTRAINT "readers_lower_email_index" DO UPDATE SET "updated_at" = $4 RETURNING "id"
    (ecto_sql) lib/ecto/adapters/sql.ex:612: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto) lib/ecto/repo/schema.ex:655: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:263: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

Perhaps this could indicate some problem handling index made with DB's lower() function?

[josevalim]

Thanks! I took a look at the app and then I got a deja vu feeling. It happens that when you do expression indexes, PG defines an index but not a constraint. So you can't use ON CONSTRAINT. The best option in this case is to use unsafe_fragment. See #2081 for a discussion with people reporting similar issues. The solution is to use unsafe_fragment. I will likely deprecate {:constraint, ...}.

[edisonywh]

Ah okay thanks for the quick reply!