Deleting a resource with a restrict on_delete gives an unknown error

[jeroen11dijk]

Describe the bug
had a chat about the error here: https://discord.com/channels/711271361523351632/1069610909296766998/1308825724760752270

So we have a matching table with these relations

  relationships do
    has_many :timeslots, Cvs.MatchingTable.Timeslot do
      sort [
        Ash.Sort.expr_sort(offset, :integer)
      ]
    end

    has_many :entries, Cvs.MatchingTable.Entry do
      no_attributes? true
      filter expr(timeslot.matching_table_id == parent(id))
    end
  end

And then timeslot has the following:

    references do
      reference :matching_table, on_delete: :delete
    end

  relationships do
    belongs_to :matching_table, Cvs.MatchingTable.MatchingTable, allow_nil?: false, public?: true
    has_one :entry, Cvs.MatchingTable.Entry
  end

And lastly entry:

    references do
      reference :timeslot, on_delete: :restrict
    end
    
      relationships do
    belongs_to :dossier, Cvs.Client.Dossier, allow_nil?: false, public?: true
    belongs_to :user, Cvs.Account.User, public?: true
    has_one :conclusion, Cvs.MatchingTable.EntryConclusion
    belongs_to :timeslot, Cvs.MatchingTable.Timeslot, allow_nil?: false, public?: true
  end

But now when I try to delete a matching table which has a timeslot which has an entry I get the following error:

   ** (Ash.Error.Unknown) 
    Bread Crumbs:
      > Exception raised in: Cvs.MatchingTable.MatchingTable.destroy

    Unknown Error

    * ** (Postgrex.Error) ERROR 23503 (foreign_key_violation) update or delete on table "matching_table_timeslot" violates foreign key constraint "matching_table_entry_timeslot_id_fkey" on table "matching_table_entry"

        table: matching_table_entry
        constraint: matching_table_entry_timeslot_id_fkey

    Key (id)=(a22155be-26ee-42e4-a187-0afec8fee808) is still referenced from table "matching_table_entry".
      (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1096: Ecto.Adapters.SQL.raise_sql_call_error/1
      (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:994: Ecto.Adapters.SQL.execute/6
      (ash_postgres 2.4.14) lib/data_layer.ex:2903: AshPostgres.DataLayer.destroy/2
      (ash 3.4.43) lib/ash/actions/destroy/destroy.ex:219: anonymous fn/3 in Ash.Actions.Destroy.commit/3
      (ash 3.4.43) lib/ash/changeset/changeset.ex:3962: Ash.Changeset.run_around_actions/2
      (ash 3.4.43) lib/ash/changeset/changeset.ex:3495: anonymous fn/3 in Ash.Changeset.with_hooks/3
      (ecto_sql 3.12.1) lib/ecto/adapters/sql.ex:1400: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
      (db_connection 2.7.0) lib/db_connection.ex:1756: DBConnection.run_transaction/4
      (ash 3.4.43) lib/ash/changeset/changeset.ex:3493: anonymous fn/4 in Ash.Changeset.with_hooks/3
      (ash 3.4.43) lib/ash/changeset/changeset.ex:3640: anonymous fn/2 in Ash.Changeset.transaction_hooks/2
      (ash 3.4.43) lib/ash/changeset/changeset.ex:3468: Ash.Changeset.with_hooks/3
      (ash 3.4.43) lib/ash/actions/destroy/destroy.ex:178: Ash.Actions.Destroy.commit/3
      (ash 3.4.43) lib/ash/actions/destroy/destroy.ex:120: Ash.Actions.Destroy.do_run/4
      (ash 3.4.43) lib/ash/actions/destroy/destroy.ex:57: Ash.Actions.Destroy.run/4
      (ash 3.4.43) lib/ash.ex:2756: Ash.destroy/2
      test/cvs/matching_table/matching_table_test.exs:158: Cvs.MatchingTableTest."test MatchingTable CRUD operations Delete: cant delete a MatchingTable with an entry"/1
      (ex_unit 1.16.2) lib/ex_unit/runner.ex:472: ExUnit.Runner.exec_test/2
      (stdlib 4.3.1.3) timer.erl:235: :timer.tc/1
      (ex_unit 1.16.2) lib/ex_unit/runner.ex:394: anonymous fn/6 in ExUnit.Runner.spawn_test_monitor/4

Yet I expect that like I one had before I get an error that says "would leave records behind" and is also an actual error. Both restrict and nothing give me the same result.

Feel free to ask more questions on discord.

To Reproduce
See above

Expected behavior
An actual error with the message "would leave records behind" which I have seen before.

elixir: "~> 1.16",
**Runtime**
      {:ash_postgres, "~> 2.4.14"},
      {:ash_phoenix, "~> 2.0"},
      {:ash_admin, "~> 0.11.9"},
      {:ash_authentication, "~> 4.3.2"},
      {:ash_authentication_phoenix, "~> 2.3.0"},

[zachdaniel]

Hmm...this definitely is a bug, somewhere in ash_postgres when constructing a changeset for destroy, where we are not properly configuring the foreign key names I imagine. Did you perhaps edit the name of the foreign key constraint manually? matching_table_entry_timeslot_id_fkey looks like the default we'd assume, just want to double check.

[jeroen11dijk]

No I did not change it manually, this is what it says in the latest migration:

    {
      "allow_nil?": false,
      "default": "nil",
      "generated?": false,
      "primary_key?": false,
      "references": {
        "deferrable": false,
        "destination_attribute": "id",
        "destination_attribute_default": null,
        "destination_attribute_generated": null,
        "index?": false,
        "match_type": null,
        "match_with": null,
        "multitenancy": {
          "attribute": null,
          "global": null,
          "strategy": null
        },
        "name": "matching_table_entry_timeslot_id_fkey",
        "on_delete": "restrict",
        "on_update": null,
        "primary_key?": true,
        "schema": "public",
        "table": "matching_table_timeslot"
      },
      "size": null,
      "source": "timeslot_id",
      "type": "uuid"
    }

[zachdaniel]

Gotcha. So I unfortunately won't have time to look at this in the near future, but the place to look will be in the for anyone who wants to help with this issue would be here:

 (ash_postgres 2.4.14) lib/data_layer.ex:2903

I'm not sure if we have code or not to detect this scenario to provide a good error, but if not we should add it. If we do, it seems to be missing a case. In the meantime @jeroen11dijk a reproduction repo would be really useful for whoever decides to tackle this (or myself when I get time).