Ecto, on_replace and deferred checks

Today I learned a valuable lesson about how a seemingly simple task can have very rough edge cases, which take hours to solve. It involved Ecto, its associations and on_replace option, and uniqueness checks in the database. Here’s the story.

The problem

Let’s say you are modelling some kind of processes. These processes have steps and the steps have to be executed in a precise order. This is how a database structure for it would look like:

def change do
  create table(:processes) do
    add :name, :string, null: false
  end

  create table(:steps) do
    add :process_id, references(:processes)
    add :name, :string, null: false
    add :order, :integer, null: false
  end
end

It is fairly straightforward: the order of steps inside of a process is controlled by an order integer column. Since it is important to always have the order of steps precise, we would like to additionally ensure it by a uniqie index:

create unique_index(:steps, [:process_id, :order])

We also know that the steps are only edited via a parent process. There is a form in the application, where you can edit the process fields and add, change, delete steps for it. The payload sent to a server always includes all the steps. Armed with that knowledge we create Ecto schemas like this:

defmodule Core.Process do
  use Ecto.Schema
  import Ecto.Changeset

  schema "processes" do
    field :name, :string
    has_many :steps, Core.Step, on_replace: :delete, preload_order: [asc: :order]
  end

  def changeset(process, params) do
    process
    |> cast(params, [:name])
    |> cast_assoc(:steps)
  end
end

defmodule Core.Step do
  use Ecto.Schema
  import Ecto.Changeset

  schema "steps" do
    field :name, :string
    field :order, :integer
    belongs_to :process, Core.Process
  end

  def changeset(step, params) do
    step
    |> cast(params, [:name, :order])
  end
end

I did that, quite happily, tested a bit with adding, changing and removing some steps. Finally I merged the pull request.

It was only a few hours later when a colleague slacked me that something goes wrong. What he did was reordering the steps, or rather trying to do it. From this structure:

step 1, order: 1
step 2, order: 2

he wanted to go to

step 1, order: 2
step 2, order: 1

I quickly drafted code that replicated the issue:

proc =
  %Process{}
  |> Process.changeset(%{
    name: "Test",
    steps: [%{order: 1, name: "First"}, %{order: 2, name: "Second"}]
  })
  |> Repo.insert!()

proc = Repo.preload(proc, [:steps])
[s1, s2] = proc.steps

s1 = %{id: s1.id, name: s1.name, order: 2}
s2 = %{id: s2.id, name: s2.name, order: 1}

proc
|> Process.changeset(%{steps: [s1, s2]})
|> Repo.update!()

Attempt to run it resulted in an error:

** (Ecto.ConstraintError) constraint error when attempting to update struct:

    * "steps_process_id_order_index" (unique_constraint)

It took me a while to realize what’s going on: Ecto tried to update the existing steps, one by one. So the first operation was:

Update “order” of step 1 to 2

This left us with the following situation:

step 1, order: 2
step 2, order: 2

This, quite obviously in retrospect, triggered the unique index. You cannot have 2 steps for one process with the same order! It did not matter that right after you were going to update step 2’s order to 1. The index operates here and now, you cannot do that.

The solution

After discovering I was pretty close to writing my own hacky solution to update the steps, including inefficient approach with first deleting all and then creating all anew. Fortunately, in time, I vaguely remembered that there is something called deferrable constraints in PostgreSQL.

Deferrable constraint waits until the end of the transaction with checking if its condition is met. This was exactly what I was looking for! But an index cannot be deferrable in PostgreSQL. Luckily, another very similar construct - a uniqueness check - can.

In the migration above, I had to replace the index creation with this:

execute """
ALTER TABLE steps ADD CONSTRAINT unique_step UNIQUE (process_id, "order") DEFERRABLE INITIALLY DEFERRED
"""

With this statement we create a constraint on the steps table which checks uniqueness of two columns and not only is deferrable - it is deferred to be checked on transaction commit by default.

This is really all I had to do. The Ecto’s association replacing mechanism started working perfectly.

This proved once again a truth I knew: the database is not a dumb storage you can just gloss over. It always pays off to learn it, its capabilities and intricacies. Because then you end up with a simple change instead of rewriting part of Ecto, but poorly.