Check constraint results in uncaught Postgrex.Error

Questions / Help
, ,
1

I have a time table that stores a time zone. I want to ensure that the value entered is a valid time zone. To do that I have a check constraint that attempts to cast the current time to the submitted tz. When using real tzs like “America/New_York” everything works expected. When I use the fake, “New_York”, tz insert fails as expected but Ecto.Changeset.check_constraint/3 fails to catch the resulting Postgrex.Error.

# Migration 
def change do
  create table(:times) do
    add :time_zone, :string, null: false
  end

  create constraint(:times, :valid_time_zone, check: "now() at time zone time_zone is not null")
end

# Schema
defmodule MyApp.Time do
  use Ecto.Schema
  import Ecto.Changeset

  schema "times" do
    field :time_zone, :string
  end

  def changeset(time, attrs) do
    time
    |> cast(attrs, [:time_zone])
    |> validate_required([:time_zone])
    |> check_constraint(:time_zone, name: :valid_time_zone)
  end
end

# Console Error
** (Postgrex.Error) ERROR 22023 (invalid_parameter_value) time zone "New_York" not recognized
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.11.2) lib/ecto/repo/schema.ex:775: Ecto.Repo.Schema.apply/4
    (ecto 3.11.2) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto 3.11.2) lib/ecto/repo/schema.ex:1033: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1358: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection 2.6.0) lib/db_connection.ex:1710: DBConnection.run_transaction/4
    iex:3: (file)

Why does the check constraint fail here?
How should I handle raw raw Postgrex errors (using try?)
Is there a better way to test for timezone in a check constraint?

2

It looks like the check is failing because Postgres is trying to parse the date/time input and throwing an error before it gets to the is not null check.
Have you confirmed that the psql statement SELECT now() AT TIME ZONE 'New_York'; does return null when providing an invalid time zone?

Edit: Looks like it is a parse error in this case, see step 2a in the datetime input rules .

Probably nothing straightforward directly in a check constraint, but you could have your application code validate the time_zone input against the pg_timezone_names view before creating or editing the Time entry.

3

The parse error is intentional as its the best way to know if my string is a time zone. If it parses, it’s a tz, if it doesn’t, it’s not one. I do see time zone handled in a similar way here and here.

Postgres responding with (invalid_parameter_value) time zone "New_York" not recognized is enough information for me to know that the data provided is invalid. What I need is a way to catch that specific postgrex error. Im going to try using a try/rescue but I was hoping for a solution that would allow me to treat this error like a constraint so that I can handle it directly in a changeset.

This stack overflow suggests manually catching and rethrowing errors as a constraint error, then catching them by overriding query functions in the Repo module, but that seems so heavy handed to handle the insert of a single column of a single entity only some of the time.

4

My solution for the time being:

defp invalid_timezone(changeset) do
  Ecto.Changeset.add_error(changeset, :time_zone, "Invalid time zone")
end

# When I insert or update.....
changeset = build_changeset()

try do
  Repo.insert(changeset)
rescue
  pg_error in Postgrex.Error ->
    case pg_error do
      %Postgrex.Error{postgres: %{pg_code: "22023"}} -> {:error, invalid_timezone(changeset)}
      _ -> reraise pg_error, __STACKTRACE__
    end
end

The downside is that every invalid_parameter_value is treated as an invalid time zone. That’s fine for now as this is the only place that error can occur, but it’s not ideal for when the project grows and the table becomes more complex

5

You could also write a db function to do the view tz name lookup and use it in the check

CREATE OR REPLACE FUNCTION is_valid_timezone(tz_name text) RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 
        FROM pg_timezone_names 
        WHERE name = tz_name
    );
END;
$$ LANGUAGE plpgsql; 

ALTER TABLE your_table
ADD CONSTRAINT valid_time_zone CHECK (is_valid_timezone(time_zone));
6

thanks