Ecto: Cannot create `conflict_target` using partial index

[JonRowe]

This is half a bug, half a feature request, currently is not possible to create an "exotic" conflict_target for an upsert, our use case here is that we have a partial index across our table to allow us to upsert on null values (we have two fields we care about and two we don't in this instance). We can upsert on all 4 just fine, but when the 2 are null we need to use the partial index.

Currently there is no way we could find to tell ecto about this partial index in the conflict_target of our insert. fragment was unavailable (this is in an seed script, even with an import Ecto.Query statement), the where: option isn't supported here, and using a simple string got quoted as a field.

Ideally we'd be able to tell ecto to produce this:

ON CONFLICT (field_a, field_b) WHERE field_c IS NULL AND field_id IS NULL

Which is the valid statement for this scenario.

Environment

• Elixir version (elixir -v): 1.4.4
• Database and version: PostgreSQL 9.6.3
• Ecto version (mix deps): 2.1.4
• Database adapter and version (mix deps): postgrex 0.13.2
• Operating system: Mac OS X

[josevalim]

Can you please post the full SQL code you want Ecto to generate and confirm that
it is supported by Postgres?

[JonRowe]

Still slightly pseudo coded but only with field name replacement:

INSERT INTO model (field_a, field_b) VALUES (value_a, value_b)
ON CONFLICT (field_a, field_b) WHERE field_c IS NULL AND field_d IS NULL
DO UPDATE SET
field_a = EXCLUDED.field_a, field_b = EXCLUDED.field_b
RETURNING "id"

Its definitely supported by Postgres, we can run it with a hand crafted snippet like this:

Ecto.Adapters.SQL.query!(Repo, upsert_query, ["value_a", "value_b"])

[lauraannwilliams]

Just adding a link to the Postgres
https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
and MariaDB docs
https://mariadb.com/kb/en/mariadb/insert-on-duplicate-key-update/

It looks like this is not supported by MariaDB/MySQL - I don't think they have partial index support. So like conflict_target, this would be a Postgres only option.

[JonRowe]

I'd be happy with the ability to use the where keyword, or fragment, as I know this is non standard, I'd just like to not have to write the entire statement!

[seanomlor]

Would be happy with fragment support, since that should also allow specifying constraint by name via ON CONFLICT ON CONSTRAINT <constraint_name> syntax.

example:

INSERT INTO countries (country) 
VALUES ('France'),('Japan') 
ON CONFLICT ON CONSTRAINT my_exotic_constraint_name 
DO NOTHING;

[josevalim]

If the goal is to support precisely the on constraint syntax, that's much easier. For example: conflict_target: {:constraint, ...}. For supporting where, it may be best to pass a query in the :on_conflict option which we would then extract out.

[josevalim]

on constraint support is on master which show allow those cases to be handled. Let me know if there any issues. :heart:

[bglusman]

Curious if anyone is actively using this feature at present on a compound partial index? I created one and upgraded to Ecto 2.2.6 specifically to use this, and the synxtax was recognized, but it errors:
** (Postgrex.Error) ERROR 42704 (undefined_object): constraint "my_exotic_constraint_name" for table "my_table" does not exist, but I checked on both dev and test databases and the unique partial index exists and is named exactly that... haven't debugged into generated postgres yet, but what was funny is, if I changed the constraint to the tables primary key constraint like in the tests, it got past this spot fine, so wondering if there's something unusual here I'm missing about using this with a partial compound/unique index.

[wfgilman]

@bglusman I'm experiencing this behavior. I have two partial indexes on a table

    "uidx_metrics_user_date_type" UNIQUE, btree (user_id, date, savings_type) WHERE sponsor_id IS NULL
    "uidx_metrics_user_sponsor_date_type" UNIQUE, btree (user_id, sponsor_id, date, savings_type) WHERE sponsor_id IS NOT NULL

When I specify them using {:constraint, ...}, I get the Postgrex.Error 42704 claiming the constraint does not exist, yet it does.

  defp insert_metrics!(%{sponsor_id: sponsor_id} = metrics) do
    opts =
      case sponsor_id do
        nil ->
          [on_conflict: :replace_all, conflict_target: {:constraint, :uidx_metrics_user_date_type}]
        _ ->
          [on_conflict: :replace_all, conflict_target: {:constraint, :uidx_metrics_user_sponsor_date_type}]
      end
    Repo.insert!(metrics, opts)
  end

And the Postgres error

** (Postgrex.Error) ERROR 42704 (undefined_object): constraint "uidx_metrics_user_sponsor_date_type" for table "user_metrics" does not exist
    (ecto) lib/ecto/adapters/sql.ex:554: Ecto.Adapters.SQL.struct/8
    (ecto) lib/ecto/repo/schema.ex:546: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:212: anonymous fn/14 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/repo/schema.ex:773: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto) lib/ecto/adapters/sql.ex:576: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
    (db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
    (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
    (db_connection) lib/db_connection.ex:790: DBConnection.transaction/3

[net]

@josevalim I believe ON CONSTRAINT support does not solve the original issue: that :conflict_target does not support partial indexes.

Partial unique indexes are used in Postgres as there are no partial unique constraints. See https://www.postgresql.org/docs/9.1/static/ddl-constraints.html:

A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

When used in ON CONFLICT the predicate is given after the column names for index inference as shown above by @JonRowe.

Two possible options to solve this are

1) fragment support for :conflict_target such as {:fragment, ~s<("title", "score") WHERE foo = true>}. The changes required for this should be pretty small:

[lib/ecto/repo/schema.ex:476](https://github.com/elixir-ecto/ecto/blob/48607c72c07f7cea08f0b943431c41a1bc8bdeda/lib/ecto/repo/schema.ex#L476):
```elixir
defp conflict_target({:fragment, fragment}, _dumper) when is_binary(fragment) do
  {:fragment, fragment}
end
```

[lib/ecto/adapters/postgres/connection.ex:183](https://github.com/elixir-ecto/ecto/blob/48607c72c07f7cea08f0b943431c41a1bc8bdeda/lib/ecto/adapters/postgres/connection.ex#L183):
```elixir
defp conflict_target({:fragment, fragment}),
  do: [fragment, ?\s]
```

1. some form of where support as mentioned above

If I am not wrong this issue should be reopened.

[stavro]

I am also unable to use Upsert on unique indexes with expressions (eg. https://www.postgresql.org/docs/9.1/static/indexes-expressional.html)

Fragments could support this... But the current unique index does not have a constraint name (so I cannot use :constraint), and I cannot add the expressions as strings, because Ecto complains about an unknown field name.