TLDR: Database CHECK constraint and TRIGGER not working when inserting data with Ecto, but do work when inserting data with PSQL (on same database).

I ran into a problem while setting up a database migration in a Phoenix project. I wanted to add some CHECK constraints and TRIGGERs, like what I have been doing from my terminal for another, Postgres-only project. However, some of the CHECK constraints and TRIGGERs that I have added do not work as expected when I use Ecto to insert data into my database. However, when I use PSQL from my terminal on the same database, all constraints and triggers work as expected.

Here is an example of one of the culprit constraints.

create constraint(:node, :no_node_reference_to_self, check: "id <> node_id")

In my migration file you can find it under the :node table.

defmodule MyApp.Repo.Migrations.AddTreeModel do
  use Ecto.Migration

  def change do
    create table(:tree) do
      add :title, :string, size: 40
      timestamps()
    end

    create table(:node) do
      add :tree_id, references(:tree)
      add :node_id, references(:node)
      timestamps()
    end
    create constraint(:node, :one_outgoing_edge, check: "num_nonnulls(tree_id, node_id) = 1")
    create constraint(:node, :no_node_reference_to_self, check: "id <> node_id")

    create table(:stop) do
      add :node_id, references(:node), null: false
      timestamps()
    end
    create unique_index(:stop, [:node_id])

    create table(:constrainer) do
      add :tree_id, references(:tree)
      add :node_id, references(:node)
      add :branching, :boolean, null: false, default: false
      add :private, :boolean, null: false, default: true
      timestamps()
    end
    create constraint(:constrainer, :one_outgoing_edge, check: "num_nonnulls(tree_id, node_id) = 1")
    create constraint(:constrainer, :tree_constrainer_only_option, check: "NOT (private AND tree_id IS NULL)")
    create unique_index(:constrainer, [:tree_id])
    create unique_index(:constrainer, [:node_id])

    # Add PostgreSQL triggers to ensure that a node is not referenced by both a node and a stop
    execute create_trigger_function_for_node(), drop_trigger_function_for_node()
    execute create_trigger_for_node(), drop_trigger_for_node()
    execute create_trigger_function_for_stop(), drop_trigger_function_for_stop()
    execute create_trigger_for_stop(), drop_trigger_for_stop()
  end

  defp create_trigger_function_for_node do
    """
    CREATE OR REPLACE FUNCTION validate_node_not_referenced_by_stop()
    RETURNS TRIGGER AS $$
    DECLARE
      referenced_by_stop BOOLEAN;
    BEGIN
      SELECT INTO referenced_by_stop EXISTS(SELECT FROM stop WHERE node_id = NEW.node_id);
      IF referenced_by_stop THEN
        RAISE EXCEPTION 'You are trying to reference from a node to the node with id %. However, that node is already referenced by a stop. A node cannot simultaneously be referenced by a node and a stop.', NEW.node_id;
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """
  end

  defp drop_trigger_function_for_node do
    """
    DROP FUNCTION validate_node_not_referenced_by_stop();
    """
  end

  defp create_trigger_for_node do
    """
    CREATE TRIGGER validate_node_not_referenced_by_stop
    BEFORE INSERT OR UPDATE ON node
    FOR EACH ROW EXECUTE PROCEDURE validate_node_not_referenced_by_stop();
    """
  end

  defp drop_trigger_for_node do
    """
    DROP TRIGGER validate_node_not_referenced_by_stop ON node;
    """
  end

  defp create_trigger_function_for_stop do
    """
    CREATE OR REPLACE FUNCTION validate_node_not_referenced_by_node()
    RETURNS TRIGGER AS $$
    DECLARE
      referenced_by_node BOOLEAN;
    BEGIN
      SELECT INTO referenced_by_node EXISTS(SELECT FROM node WHERE node_id = NEW.node_id);
      IF referenced_by_node THEN
        RAISE EXCEPTION 'You are trying to reference from a stop to the node with id %. However, that node is already referenced by a node. A node cannot simultaneously be referenced by a node and a stop.', NEW.node_id;
      END IF;
      RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """
  end

  defp drop_trigger_function_for_stop do
    """
    DROP FUNCTION validate_node_not_referenced_by_node();
    """
  end

  defp create_trigger_for_stop do
    """
    CREATE TRIGGER validate_node_not_referenced_by_node
    BEFORE INSERT OR UPDATE ON stop
    FOR EACH ROW EXECUTE PROCEDURE validate_node_not_referenced_by_node();
    """
  end

  defp drop_trigger_for_stop do
    """
    DROP TRIGGER validate_node_not_referenced_by_node ON stop;
    """
  end
end

I wrote some tests. The last seven tests correspond to the constraints and triggers that are not working as expected in my Phoenix project. I have manually tested the same scenarios in my terminal with PSQL on the same database. All the manual tests passed.

defmodule MyApp.TreeModelTest do
  use MyApp.DataCase
  alias MyApp.Repo
  alias MyApp.TreeModel.{Tree, Node, Stop, Constrainer}

  test "successfully insert tree with title" do
    assert {:ok, _} = %Tree{title: "Tree Title"} |> Repo.insert()
  end

  test "successfully insert tree without title" do
    assert {:ok, _} = %Tree{} |> Repo.insert()
  end

  test "successfully insert node with tree reference" do
    tree = Repo.insert!(%Tree{})
    assert {:ok, _} = %Node{tree_id: tree.id} |> Repo.insert()
  end

  test "successfully insert node with node reference" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    assert {:ok, _} = %Node{node_id: root_node.id} |> Repo.insert()
  end

  test "successfully insert a node that is referenced by multiple nodes" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    assert {:ok, _} = %Node{node_id: root_node.id} |> Repo.insert()
    assert {:ok, _} = %Node{node_id: root_node.id} |> Repo.insert()
  end

  test "successfully insert a tree that is references by multiple nodes" do
    tree = Repo.insert!(%Tree{})
    assert {:ok, _} = %Node{tree_id: tree.id} |> Repo.insert()
    assert {:ok, _} = %Node{tree_id: tree.id} |> Repo.insert()
  end

  test "successfully insert stop that references a node" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    assert {:ok, _} = %Stop{node_id: root_node.id} |> Repo.insert()
  end

  test "fail to insert node without tree and node reference" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    catch_error %Node{tree_id: tree.id, node_id: root_node.id} |> Repo.insert()
  end

  test "fail to insert stop that references node that is already referenced by a stop" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    Repo.insert!(%Stop{node_id: root_node.id})
    catch_error Repo.insert!(%Stop{node_id: root_node.id}) |> IO.inspect()
  end

  # Not failing in PSQL, but failing in Ecto
  test "fail to insert node that references itself" do
    tree = Repo.insert!(%Tree{})
    node = Repo.insert!(%Node{tree_id: tree.id})
    query = from(
      n in Node,
      where: n.id == ^node.id,
      update: [set: [tree_id: nil, node_id: ^node.id]])

    catch_error Repo.update_all(query, [])
  end

 # Not failing in PSQL, but failing in Ecto
  test "fail to insert node that references node that is already referenced by a stop" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    Repo.insert!(%Stop{node_id: root_node.id})
    catch_error %Node{node_id: root_node.id} |> Repo.insert()
  end

 # Not failing in PSQL, but failing in Ecto
  test "fail to insert stop that references node that is already referenced by a node" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    %Node{node_id: root_node.id} |> Repo.insert()
    catch_error Repo.insert!(%Stop{node_id: root_node.id})
  end

 # Not failing in PSQL, but failing in Ecto
  test "successfully insert node constrainer with branching" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    assert {:ok, _} = %Constrainer{node_id: root_node.id, branching: true} |> Repo.insert()
  end

 # Not failing in PSQL, but failing in Ecto
  test "successfully insert node constrainer without branching" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    assert {:ok, _} = %Constrainer{node_id: root_node.id, branching: false} |> Repo.insert()
  end

  test "fail to insert node constrainer with creator_only" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    catch_error %Constrainer{node_id: root_node.id, creator_only: true} |> Repo.insert()
  end

 # Not failing in PSQL, but failing in Ecto
  test "successfully insert node constrainer without creator_only" do
    tree = Repo.insert!(%Tree{})
    root_node = Repo.insert!(%Node{tree_id: tree.id})
    assert {:ok, _} = %Constrainer{node_id: root_node.id, creator_only: false} |> Repo.insert()
  end
end

Any ideas what could be the problem?

I have tried rolling back the migration and migrating again in mix_env=test. And dropping the database and migrating from scratch. That did not work.

When checking my database with PSQL I do see all the constraints and triggers in my tables. For example the node table.

                                          Table "public.node"
   Column    |              Type              | Collation | Nullable |             Default
-------------+--------------------------------+-----------+----------+----------------------------------
 id          | bigint                         |           | not null | nextval('node_id_seq'::regclass)
 tree_id     | bigint                         |           |          |
 node_id     | bigint                         |           |          |
 inserted_at | timestamp(0) without time zone |           | not null |
 updated_at  | timestamp(0) without time zone |           | not null |
Indexes:
    "node_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "no_node_reference_to_self" CHECK (id <> node_id)
    "one_outgoing_edge" CHECK (num_nonnulls(tree_id, node_id) = 1)
Foreign-key constraints:
    "node_node_id_fkey" FOREIGN KEY (node_id) REFERENCES node(id)
    "node_tree_id_fkey" FOREIGN KEY (tree_id) REFERENCES tree(id)
Referenced by:
    TABLE "constrainer" CONSTRAINT "constrainer_node_id_fkey" FOREIGN KEY (node_id) REFERENCES node(id)
    TABLE "node" CONSTRAINT "node_node_id_fkey" FOREIGN KEY (node_id) REFERENCES node(id)
    TABLE "stop" CONSTRAINT "stop_node_id_fkey" FOREIGN KEY (node_id) REFERENCES node(id)
Triggers:
    validate_node_not_referenced_by_stop BEFORE INSERT OR UPDATE ON node FOR EACH ROW EXECUTE FUNCTION validate_node_not_referenced_by_stop()