Best way to validate Tree modeled as adjacency list, TRIGGER vs. CHECK constraint

Question

I am using PostgreSQL for modeling Tree data structures using adjacency list approach. And i would like to be able to store extra data on each leaf indicating to which group this tree belong:

CREATE TABLE groups (
  group_id integer PRIMARY KEY
);

CREATE TABLE leafs (
  leaf_id   integer PRIMARY KEY,
  parent_id integer REFERENCES leafs ON DELETE CASCADE ON UPDATE CASCADE,

  group_id integer REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE NOT NULL
);

I also would like to ensure that each leaf could only be connected to the same group. It looks like this could be done by either creating a TRIGGER or CHECK constraint. I have two questions:

1. What is the most efficient/right way to handle this particular case, TRIGGER or CHECK constraint? (and what is rule of thumbs for choosing between these two)

2. Is there is a better way to enforce consistency of this model (or perhaps alternative ways to model such groups of trees).

Thanks,

Code for TRIGGER version:

CREATE OR REPLACE FUNCTION after_leaf_update()
  RETURNS trigger AS
$$
BEGIN
IF (NEW.parent_id IS NOT NULL) AND (SELECT group_id FROM leafs WHERE leaf_id=NEW.parent_id) <> NEW.group_id THEN
    RAISE EXCEPTION 'group_id of node/leaf does not match!!!';
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';


CREATE TRIGGER leafs_consistency_check
  BEFORE INSERT OR UPDATE
  ON leafs
  FOR EACH ROW
  EXECUTE PROCEDURE after_leaf_update();

Code for CHECK constraint version:

CREATE OR REPLACE FUNCTION leafs_consistency_check_constraint(prn_id integer, grp_id integer) RETURNS BOOL AS
$$
BEGIN
    IF (prn_id IS NOT NULL) AND (SELECT group_id FROM leafs WHERE leaf_id=prn_id) <> grp_id THEN
    RAISE EXCEPTION 'CHECK: group_id of node/leaf does not match!!!';
END IF;
RETURN TRUE;

END;
$$ LANGUAGE plpgsql;

ALTER TABLE leafs ADD constraint group_id_constraint check ( leafs_consistency_check_constraint(parent_id, group_id) );

Answer 1

Use the trigger.

Generally speaking, Postgres devs don't recommend putting functions which run queries in CHECK constraints. Among other things, under some circumstances the CHECK constraint might get checked more than once for a query, and that's a pretty expensive check. More importantly, there's a lot of careful engineering around row visibility and similar considerations for Triggers which simply doesn't exist for Constraints.

I'll also recommend that you consider an alternate approach: instead of throwing an error, what if you use a BEFORE trigger, ignore the user input, and automatically give the new node the same group_id as its parent?

Or even better, since the group_ids are affiliated with the parents and not with the children, how about putting group_ids in a separate table which is linked just to the parent nodes?