I had a unique constraint,
def change do
create table(:couplets, primary_key: false) do
add :id, :binary_id, primary_key: true
add :idx, :integer
add :dkey_id, references(:dkeys, on_delete: :delete_all, type: :binary_id)
timestamps()
end
create unique_index(:couplets, [:dkey_id, :idx])
end
But it was failing on this:
Repo.transaction(fn() ->
with {:ok, deleted_couplet} <- Repo.delete(couplet)
do
{:ok, _previous} = update_previous_couplet(previous, from_lead, nil)
{:ok, dkey} = Guide.update_dkey(dkey, %{couplet_count: idx})
# fails here:
(from c in Couplet, where: c.idx > ^couplet.idx)
|> Repo.update_all(inc: [idx: -1])
{deleted_couplet, dkey}
else
{:error, changeset} ->
Repo.rollback(changeset)
end
end)
Which led me to this potential fix:
…which looks like this:
defmodule Treeteacher.Repo.Migrations.ModifyCoupletsDkeyIdIdxUniqueConstraint do
use Ecto.Migration
def up do
execute("""
DROP INDEX couplets_dkey_id_idx_index;
""")
execute("""
ALTER TABLE couplets
ADD CONSTRAINT unique_couplet_dkey_id_idx unique (dkey_id, idx)
DEFERRABLE INITIALLY IMMEDIATE;
""")
end
def down do
execute("""
ALTER TABLE couplets
DROP CONSTRAINT unique_couplet_dkey_id_idx;
""")
execute("""
CREATE UNIQUE INDEX couplets_dkey_id_idx_index
ON couplets
USING btree (dkey_id, idx);
""")
end
end
and
Repo.transaction(fn() ->
# ...
# still fails here:
Repo.query!("SET CONSTRAINTS unique_couplet_dkey_id_idx DEFERRED")
(from c in Couplet, where: c.idx > ^couplet.idx)
|> Repo.update_all(inc: [idx: -1])
# ...
end)
Also, I tried it both with INITIALLY IMMEDIATE outside the transaction; or with INITIALLY DEFERRED, but there is no difference – constraint is still violated.
Could it be because of the transaction?
