Foreign key constraint not caught on row deletion with PostgreSQL 18

[Gladear]

Elixir version

Erlang/OTP 27 [erts-15.2.4] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [jit] Elixir 1.19.4 (compiled with Erlang/OTP 27)

Database and Version

PostgreSQL 18.1

Postgrex Version

0.22.0

Current behavior

There is a different behaviour between PostgreSQL 17 and 18, when attempting to delete a entity that is referenced by another one using a foreign key.

Here's an SQL schema to reproduce the issue:

CREATE DATABASE pg_18_demo;
\c pg_18_demo;
CREATE TABLE a (id bigserial PRIMARY KEY);
INSERT INTO a (id) VALUES (1);
CREATE TABLE b (id bigserial, a_id bigint REFERENCES a(id) ON DELETE RESTRICT);
INSERT INTO b (id, a_id) VALUES (1, 1);

-- The foreign key is named `b_a_id_fkey`, as can be seen on the deletion error message:
-- DELETE FROM a;
-- update or delete on table "a" violates RESTRICT setting of foreign key constraint "b_a_id_fkey" on table "b"
-- DETAIL:  Key (id)=(1) is referenced from table "b".

The Elixir side of the repro:

Mix.install([:ecto, :ecto_sql, :postgrex])

defmodule App.Repo do
  use Ecto.Repo,
    otp_app: :app,
    adapter: Ecto.Adapters.Postgres
end

App.Repo.start_link(
  username: "postgres",
  password: "postgres",
  database: "pg_18_demo",
  hostname: "localhost",
)

defmodule App.A do
  use Ecto.Schema
  
  import Ecto.Changeset, only: [change: 1, foreign_key_constraint: 3]

  schema "a" do
  end
  
  def delete_changeset(struct) do
    struct
    |> change()
    |> foreign_key_constraint(:b, name: "b_a_id_fkey")
  end
end

a = App.Repo.get(App.A, 1)
a |> App.A.delete_changeset() |> App.Repo.delete()

When using a Postgres 18 dbms, this raises with a Postgrex.Error:

15:54:06.661 [debug] QUERY ERROR source="a" db=7.1ms queue=2.7ms idle=1746.3ms
DELETE FROM "a" WHERE "id" = $1 [1]
** (Postgrex.Error) ERROR 23001 (restrict_violation) update or delete on table "a" violates RESTRICT setting of foreign key constraint "b_a_id_fkey" on table "b"

    table: b
    constraint: b_a_id_fkey

Key (id)=(1) is referenced from table "b".
    (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
    iex:13: (file)

Expected behavior

I would expect the error to be caught by the foreign_key_constraint. With PostgreSQL 17, I get the following output:

15:55:29.181 [debug] QUERY ERROR source="a" db=1.9ms queue=0.4ms idle=8.4ms
DELETE FROM "a" WHERE "id" = $1 [1]
{:error,
 #Ecto.Changeset<
   action: :delete,
   changes: %{},
   errors: [
     b: {"does not exist",
      [constraint: :foreign, constraint_name: "b_a_id_fkey"]}
   ],
   data: #App.A<>,
   valid?: false,
   ...
 >}

[josevalim]

Fixed here I beleive: elixir-ecto/ecto_sql#713

[Gladear]

Indeed, thank you for pointing it out 🙏