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()
