unique_constraint doesn't work when using cast_assoc from parent

[michaelst]

Elixir version

Erlang/OTP 27 [erts-15.1.3] [source] [64-bit] [smp:16:16] [ds:16:16:10] [async-threads:1] [jit]
Elixir 1.18.2 (compiled with Erlang/OTP 27)

Database and Version

docker postgres:17

Ecto Versions

ecto 3.13.5

Database Adapter and Versions (postgrex, myxql, etc)

postgrex 0.22.0

Current behavior

Parent schema (workflow)

defmodule Devhub.Workflows.Schemas.Workflow do
  use Ecto.Schema

  import Ecto.Changeset

  alias Devhub.Workflows.Schemas.Step

  schema "workflows" do
    has_many :steps, Step, preload_order: [:order], on_replace: :delete, on_delete: :delete_all

    timestamps()
  end

  def changeset(schema \\ %__MODULE__{}, attrs) do
    schema
    |> cast_assoc(:steps,
      with: &Step.changeset/3,
      sort_param: :step_sort,
      drop_param: :step_drop
    )
  end
end

Child schema (step)

defmodule Devhub.Workflows.Schemas.Step do
  use Ecto.Schema

  import Ecto.Changeset

  schema "workflow_steps" do
    field :order, :integer
    field :name, :string

    timestamps()
  end

  def changeset(schema, attrs, order) do
    schema
    |> cast(attrs, [:workflow_id, :name])
    |> put_change(:order, order)
    |> unique_constraint([:workflow_id, :name],
      error_key: :name,
      name: "workflow_steps_workflow_id_name_unique",
      message: "step names must be unique"
    )
  end
end

Then if you update the workflow if crashes on Repo.update

workflow
|> Workflow.changeset(%{
  "steps" => %{
    "0" => %{"name" => "test"},
    "1" => %{"name" => "test"}
  }
})
|> Repo.update()

Error log

[error] Postgrex.Protocol (#PID<0.11502.0> ("db_conn_5")) disconnected: ** (Postgrex.Error) ERROR 23505 (unique_violation) duplicate key value violates unique constraint "workflow_steps_workflow_id_name_unique"

    table: workflow_steps
    constraint: workflow_steps_workflow_id_name_unique

Key (workflow_id, name)=(wf_01KJVBR7EYV4H22BN96F1QEYYY, test) already exists.
[error] GenServer #PID<0.16119.0> terminating
** (Postgrex.Error) ERROR 23505 (unique_violation) duplicate key value violates unique constraint "workflow_steps_workflow_id_name_unique"

    table: workflow_steps
    constraint: workflow_steps_workflow_id_name_unique

Key (workflow_id, name)=(wf_01KJVBR7EYV4H22BN96F1QEYYY, test) already exists.
    (db_connection 2.9.0) lib/db_connection.ex:1782: DBConnection.run_transaction/4
    (devhub 0.1.0) lib/devhub/workflows/actions/update_workflow.ex:15: Devhub.Workflows.Actions.UpdateWorkflow.update_workflow/2
    (devhub 0.1.0) lib/devhub_web/live/workflows/edit_workflow.ex:288: DevhubWeb.Live.Workflows.EditWorkflow.handle_event/3
    (phoenix_live_view 1.1.17) lib/phoenix_live_view/channel.ex:530: anonymous fn/3 in Phoenix.LiveView.Channel.view_handle_event/3
    (telemetry 1.4.1) /Users/michael/Code/Devhub/devhub-private/deps/telemetry/src/telemetry.erl:359: :telemetry.span/3
    (phoenix_live_view 1.1.17) lib/phoenix_live_view/channel.ex:260: Phoenix.LiveView.Channel.handle_info/2
    (stdlib 6.1.2) gen_server.erl:2345: :gen_server.try_handle_info/3
    (stdlib 6.1.2) gen_server.erl:2433: :gen_server.handle_msg/6
    (stdlib 6.1.2) proc_lib.erl:329: :proc_lib.init_p_do_apply/3

Expected behavior

Repo.update to return {:error, %Ecto.Changeset{}} with the appropriate error

[josevalim]

Can you please provide a small app that reproduces it? That looks weird here is that the Postgres database connection is crashing, it is not only the query that is failing, so it seems there is something more at work.

[josevalim]

There is an examples app in the examples/ folder, you could fork it and add a way to reproduce it there.

[michaelst]

I found what is causing the problem, it is if the unique index is deferred michaelst/ecto

[josevalim]

I am afraid we can't make this work. IIRC the deferred index works by failing the transaction when you commit it. There is no way we can associate it with the original changeset.