You can't express the constraint that an edge-list must be acyclic as a
It would have to be an
ASSERTION constraint, which PostgreSQL does not support.
You'll have to
LOCK TABLE to prevent concurrent
UPDATEs and check it with an
The resulting lock upgrade issues will result in frequent deadlocks. To prevent them your app will have to lock the table before inserting/updating, e.g.
LOCK TABLE my_table IN EXCLUSIVE MODE;
INSERT INTO my_table (...) VALUES (...);
-- Trigger runs to sanity check table here
You might find it useful to make the trigger a deferred constraint trigger, so the table is allowed to temporarily enter invalid states so long as it's valid again by the time you commit.