How to raise custom Postgresql error and handle it in Ecto

Question

I created a custom function in Postgresql that checks data before insert or update and raises error if something goes wrong.

CREATE FUNCTION custom_check() RETURNS TRIGGER AS $$
  BEGIN
    IF <SOME CONDITION> THEN
        RAISE EXCEPTION 'CUSTOM ERROR';
    END IF;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql
""")

When I'm using constraints in Postgresql, I can handle errors raised with Ecto.Changeset.check_constraint.

But I didn't find a way to handle this error I'm raising, to reflect it in changeset instead of getting an exception and probably catching it inside my code.

Should I raise error differently for Ecto.Changeset.check_constraint to handle it, or do differently something else?

Adriaadriaens

What is the error you are getting? I don't think there is a built-in method of handling trigger errors, so you might end up manually rescuing it in your function call and adding the error to your changeset. – Denudation

I changed exception to RAISE EXCEPTION 'CUSTOM ERROR', USING ERRCODE = 'check_violation';, but Ecto still doesn't like catching it. I'm catching it in tests using assert_raise(Postgrex.Error, ~R/CUSTOM ERROR/. And yes, I will probably end up catching it in my Elixir code. – Adriaadriaens

Why do you run logic (condition checks) on DB level instead of Elixir function? – Fad

Because it's more robust. Gives me 100% guarantee that nothing will go wrong. – Adriaadriaens

Answer 1

As far as I know, there is no built-in mechanism for handling custom PostgreSQL's errors. However, you can do it at the repository level.

To do that, you have to raise errors in the PostgreSQL using ERRCODE like:

RAISE '[message for logs]' USING ERRCODE = 'integrity_constraint_violation';

and then handle them in the application:

defmodule Core.Repo do
  use Ecto.Repo, otp_app: :core

  defoverridable insert: 2

  def insert(changeset, opts) do
    super(changeset, opts)
  rescue
    exception in Postgrex.Error ->
      handle_postgrex_exception(exception, __STACKTRACE__, changeset)
  end

  # ... other functions

  defp handle_postgrex_exception(exception, stacktrace, changeset \\ nil)

  defp handle_postgrex_exception(%{postgres: %{code: :integrity_constraint_violation}}, _, nil) do
    {:error, :integrity_constraint_violation}
  end

  defp handle_postgrex_exception(
         %{postgres: %{code: :integrity_constraint_violation}},
         _,
         changeset
       ) do
    {:error, %{changeset | valid?: false}}
  end

  defp handle_postgrex_exception(exception, stacktrace, _) do
    reraise(exception, stacktrace)
  end
end

Note the {:error, %{changeset | valid?: false}} response. It means that at that point, there won't be any useful message to display.

PS you could probably write some macros to override Ecto's functions and hide the implementation there (instead of the proposed solution) but I believe it would be much harder to maintain.

Nose

Answer 2

There is another way to intercept trigger raised errors within a changeset. The method is to disguise your error as one of the constraints that is already implemented by the Postgres Ecto adapter:

1. :unique_violation
2. :foreign_key_violation
3. :exclusion_violation
4. :check_violation

In order to do that, you need to raise error in your PLSQL function in the following way:

 RAISE EXCEPTION 'CUSTOM ERROR' USING ERRCODE = 'check_violation', CONSTRAINT = 'name_of your_contraint';

where ERRCODE is one from the above list, and CONSTRAINT is custom.

in your code:

CREATE FUNCTION custom_check() RETURNS TRIGGER AS $$
  BEGIN
    IF <SOME CONDITION> THEN
        RAISE EXCEPTION 'CUSTOM ERROR' 
        USING ERRCODE = 'check_violation', 
        CONSTRAINT = 'name_of_your_contraint';
    END IF;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql

and then in your changeset:

def changeset(schema, attrs) do
   schema
   |> check_constraint(:some_field, name: name_of_your_contraint: , message: "custom error message")
end

Domestic

This the way, thank you so much! – Upholstery