PostgreSQL restrict_violation not mapped to a foreign_key constraint in PostgreSQL 18

[ashutoshrishi]

Elixir version

1.19.4

Database and Version

PostgreSQL 18

Ecto Versions

ecto_sql 3.13.4, ecto 3.13.5

Database Adapter and Versions (postgrex, myxql, etc)

postgrex 0.21.1

Current behavior

1. PostgreSQL 18
2. Create tables child and parent, with child having a foreign key to parent with ON DELETE RESTRICT:

defmodule Foo.Child do
  use Ecto.Schema

  schema "child" do
    belongs_to :parent, Parent
  end
end

defmodule Foo.Parent do
  use Ecto.Schema

  schema "parent" do
    has_many :children, Child
  end
end

defmodule Foo.Repo.Migrations.Init do
  use Ecto.Migration

  def change do
    create table(:parent) do

    end

    create table(:child) do
      add :parent_id, references(:parents, on_delete: :restrict)
    end
  end
end

3. Insert a parent and child, and try to delete the parent but capture the foreign_key_violation in a Changeset:

defmodule Foo.Parent do
  def insert_and_delete do
    # Insert a parent with one child
    %__MODULE__{}
    |> cast(%{children: [%{}]}, [])
    |> cast_assoc(:children)
    |> Foo.Repo.insert!()
    # Attempt to delete the parent using a changeset to capture the foreign key
    # constraint violation
    |> change()
    |> Map.put(:action, :delete)
    |> foreign_key_constraint(:children, name: :child_parent_id_fkey)
    |> Foo.Repo.delete()
  end
end

Expected behavior

Expecting an %Ecto.Changeset{} returned with the foreign key violation mapped to a changeset error for a foreign key with ON DELETE RESTRICT constraint.

However, an exception for (Postgrex.Error) ERROR 23001 (restrict_violation) is raised and not caught:

13:51:06.272 [debug] QUERY OK db=0.6ms idle=1789.7ms
begin []

13:51:06.277 [debug] QUERY OK source="parent" db=0.9ms
INSERT INTO "parent" VALUES (DEFAULT) RETURNING "id" []

13:51:06.279 [debug] QUERY OK source="child" db=1.3ms
INSERT INTO "child" ("parent_id") VALUES ($1) RETURNING "id" [4]

13:51:06.281 [debug] QUERY OK db=1.5ms
commit []

13:51:06.283 [debug] QUERY ERROR source="parent" db=2.1ms queue=0.3ms idle=1799.4ms
DELETE FROM "parent" WHERE "id" = $1 [4]
** (Postgrex.Error) ERROR 23001 (restrict_violation) update or delete on table "parent" violates RESTRICT setting of foreign key constraint "child_parent_id_fkey" on table "child"

    table: child
    constraint: child_parent_id_fkey

Key (id)=(4) is referenced from table "child".
    (ecto_sql 3.13.4) lib/ecto/adapters/sql.ex:1113: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.13.5) lib/ecto/repo/schema.ex:1000: Ecto.Repo.Schema.apply/4
    (ecto 3.13.5) lib/ecto/repo/schema.ex:721: anonymous fn/13 in Ecto.Repo.Schema.do_delete/4
    (ecto 3.13.5) lib/ecto/repo/schema.ex:407: Ecto.Repo.Schema.delete!/4
    iex:15: (file)

The unhandled %Postgrex.Error{} value:

%Postgrex.Error{
  message: nil,
  postgres: %{
    code: :restrict_violation,
    line: "2797",
    message: "update or delete on table \"parent\" violates RESTRICT setting of foreign key constraint \"child_parent_id_fkey\" on table \"child\"",
    table: "child",
    file: "ri_triggers.c",
    unknown: "ERROR",
    constraint: "child_parent_id_fkey",
    severity: "ERROR",
    pg_code: "23001",
    schema: "public",
    routine: "ri_ReportViolation",
    detail: "Key (id)=(27) is referenced from table \"child\"."
  },
  connection_id: 7391,
  query: nil
}

It worked fine in PostgreSQL 17 as the foreign key violation for both ON DELETE NO ACTION and ON DELETE RESTRICT raised the error code :foreign_key_violation and was mapped to the :foreign_key constraint here:

ecto_sql/lib/ecto/adapters/postgres/connection.ex

Line 25 in 57d7c4f

def to_constraints(

In PostgreSQL 18, a commit fixed the behaviour to raise the restrict_violation error code for ON DELETE RESTRICT instead:
postgres/postgres@086c84b

Therefore I am assuming that both foreign_key_violation and restrict_violation should map to the foreign_key constraint in ecto_sql's Ecto.Adapters.Postgres.Connection.to_constraints/2 implementation. But I am not sure if instead Ecto.Changeset should provide a new constraint mapping instead like Ecto.Changeset.restrict_constraint/2.

What is the right place to fix this?

[josevalim]

I beleive we should deal restrict_violation automatically, without a new constraint mapping. A pull request is welcome!