Implementing cascading deletes in Ash Postgres

I am deeply confused by what’s going on.

1. I have two entities – Sprint and ActivityStereotype. The former is the “parent,” meaning, sprints can have zero or more activity stereotypes. When deleting a sprint its activity stereotypes should also be deleted.
2. It works fine in iex -S mix interactively. See below.
3. But when I try to compile / run a unit test, I get a bizarre 42P07 “duplicate table” error.
4. But I’m also a little bit confused about Ash architecture and how relationships (and specifically, cascading relationships in Postgres) are supposed to be handled.

Like I said, all is well interactively:

iex(6)> {:ok, s2} = COE.Walk.Sprint.create("meeting", ~D[2011-01-01], ~D[2011-01-15])
{:ok, #COE.Walk.Sprint...}
iex(7)> {:ok, a2} = COE.Walk.ActivityStereotype.add("meetings", s2.id)
{:ok, #COE.Walk.ActivityStereotype...}
iex(8)> s2
iex(9)> |> COE.Walk.destroy!()
:ok

My journey thus far…

I had assumed on_delete: was handled by Ash internally when using relationships with not null constraints. For example:

# activity_stereotype.ex
  relationships do
    # Activity stereotypes belonog to a sprint, so this is a required relationship
    belongs_to :sprint, COE.Walk.Sprint do
      attribute_writable? true
      allow_nil? false
    end
  end

# sprint.ex
  relationships do
    has_many :activity_stereotype, COE.Walk.ActivityStereotype
  end

Apparently not, which is fine. At this stage, deleting a sprint results in an error that “would leave records behind” (which I interpret to mean, “you can’t do that, it would leave an orphan activity stereotype”). Makes sense. Begin quest to figure out how to configure cascading deletes.

In trying to figure out how to set up a cascading delete, I found a relevant page on References in Ash Postgres. It’s lacks some context and complete examples, but… it seems to be saying I should add a new references block to my postgres block, so I added it here (footnote; kind of ugly that I now have two references in my module… seems redundant but meh)…

# activity_stereotype.ex
  postgres do
    table "stereotypes"
    repo BossSite.Repo

    references do
      reference :sprint, on_delete: :delete, on_update: :update, name: "activity_stereotype_sprint_fkey"
    end
  end

And rebuilt the database (it’s young, no need for migrations). Data rebuild is fine. All looks good.

Compile. Run unit tests.

Now I’m getting an error during unit tests that I really don’t understand:

% mix test test/coe/activity_stereotype_test.exs
Compiling 2 files (.ex)
** (Postgrex.Error) ERROR 42P07 (duplicate_table) relation "stereotypes" already exists
    (ecto_sql 3.11.0) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.15.7) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.0) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.11.0) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.15.7) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.0) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 5.0.2) timer.erl:270: :timer.tc/2
    (ecto_sql 3.11.0) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8

I checked the Postgres instance (and the migrations)… there is only one stereotypes table. I’m assuming this means there is some kind of duplicate reference being generated in the dynamic SQL somewhere… but… no idea. Drop into iex and play around to see if I can figure out what’s up:

It works just fine. WTH… (ノಠ益ಠ)ノ彡┻━┻

A few key points:

• I’m getting the error at unit test compile (not when running migrations / creating the database; the database is created – this happens during unit testing only)
• I’ve checked the SQL and it looks good to me, although my SQL is rusty – including it below just in case – anyhow, it works great in iex
• After reading this post I’m more confused about Ash Postgres, relationships, and the idiomatic approach to cascading constraints… and am wondering if I should be adding on_missing: destroy to a manage_relationships call – because mostly, it sounds like @zachdaniel is saying “that should have been managed by Ash core anyhow.” I think. But this is just tangential and doesn’t really answer WTH is going on with my unit tests…!

For thoroughness… the migration:

# database migration
def up do
    create table(:stereotypes, primary_key: false) do
      add :id, :uuid, null: false, default: fragment("uuid_generate_v4()"), primary_key: true
      add :name, :text, null: false
      add :allowed_types, {:array, :text}, null: false
      add :default_type, :text, null: false, default: "value_add"
      add :sprint_id, :uuid, null: false
    end

    create table(:sprints, primary_key: false) do
      add :id, :uuid, null: false, default: fragment("uuid_generate_v4()"), primary_key: true
    end

    alter table(:stereotypes) do
      modify :sprint_id,
             references(:sprints,
               column: :id,
               name: "activity_stereotype_sprint_fkey",
               type: :uuid,
               prefix: "public",
               on_delete: :delete_all,
               on_update: :update_all
             )
    end

    alter table(:sprints) do
      add :name, :text, null: false
      add :start_date, :date, null: false
      add :end_date, :date, null: false
    end

As always thanks for your help!