我有以下要求：

• R1：组织单位：超级->主->子。它们是有层次联系的
• R2：雇员可以向以下部门报告：（super，main，child）或（super，main）或（super）
• 一致性：
• R3：如果单位或其上级引用员工，则不允许移动它们
• R4：员工必须始终引用一致的树->必须有从下到上的路径/不允许引用外部单位

Comments on我的尝试：

• 我不认为我需要为R3锁定某些内容-在最坏的情况下，有人在COMMIT之前更换了员工-但它应该仍然是一致的
• R4和R3必须“一起”工作。另一个解决方案是在变更后检查每个受影响的员工

问：有人能从并发正确性的Angular 回顾一下解决方案吗？下面的UPDATE示例/测试按预期工作（第二个事务被阻止）-但是否还有其他情况需要测试？

CREATE TABLE public.super
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE public.main
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  super_id_fkey BIGINT NOT NULL REFERENCES public.super(id),
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE public.child
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  main_id_fkey BIGINT NOT NULL REFERENCES public.main(id),
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE public.employee
(
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,    
  report_to_super_id_fkey BIGINT REFERENCES public.super(id),
  report_to_main_id_fkey BIGINT REFERENCES public.main(id),
  report_to_child_id_fkey BIGINT REFERENCES public.child(id),
  CHECK
  (
    (report_to_super_id_fkey IS NOT NULL) OR
    (report_to_super_id_fkey IS NOT NULL AND report_to_main_id_fkey IS NOT NULL) OR
    (report_to_super_id_fkey IS NOT NULL AND report_to_main_id_fkey IS NOT NULL AND report_to_child_id_fkey IS NOT NULL)
  )
);

要求三：

CREATE OR REPLACE FUNCTION public.check_main_reference()
  RETURNS TRIGGER
  AS $$
BEGIN  
  IF 
    (
      WITH data AS
      (
        SELECT public.super.id AS super_id, public.main.id AS main_id FROM public.super
        JOIN public.main ON public.main.super_id_fkey = public.super.id
        WHERE public.main.super_id_fkey = OLD.id
      )
      SELECT EXISTS
      (
        SELECT FROM public.employee WHERE
        EXISTS (SELECT FROM data WHERE employee.report_to_super_id_fkey = data.super_id) OR
        EXISTS (SELECT FROM data WHERE employee.report_to_main_id_fkey = data.main_id)
      )
    )
  THEN
    RAISE EXCEPTION 'Organizations units or child units having referenced employees can''t be moved';
  END IF;
   
  RETURN NEW;
END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE OR REPLACE FUNCTION public.check_child_reference()
  RETURNS TRIGGER
  AS $$
BEGIN  
  IF 
    (
      WITH data AS
      (
        SELECT public.super.id AS super_id, public.main.id AS main_id, public.child.id AS child_id FROM public.super
        JOIN public.main ON public.main.super_id_fkey = public.super.id
        JOIN public.child ON public.child.main_id_fkey = public.main.id
        WHERE public.child.main_id_fkey = OLD.id
      )
      SELECT EXISTS
      (
        SELECT FROM public.employee WHERE
        EXISTS (SELECT FROM data WHERE employee.report_to_super_id_fkey = data.super_id) OR
        EXISTS (SELECT FROM data WHERE employee.report_to_main_id_fkey = data.main_id) OR
        EXISTS (SELECT FROM data WHERE employee.report_to_child_id_fkey = data.child_id)
      )
    )
  THEN
    RAISE EXCEPTION 'Organizations units or child units having referenced employees can''t be moved';
  END IF;
   
  RETURN NEW;
END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TRIGGER trigger_insert_update_super_id_fkey 
BEFORE UPDATE OF super_id_fkey ON public.main
FOR EACH ROW 
WHEN (OLD.super_id_fkey IS DISTINCT FROM NEW.super_id_fkey)
EXECUTE FUNCTION public.check_main_reference();

CREATE TRIGGER trigger_insert_update_child_main_id_fkey 
BEFORE UPDATE OF main_id_fkey ON public.child
FOR EACH ROW 
WHEN (OLD.main_id_fkey IS DISTINCT FROM NEW.main_id_fkey)
EXECUTE FUNCTION public.check_child_reference();

要求四：

CREATE OR REPLACE FUNCTION public.check_hierarchy_consistency()
  RETURNS TRIGGER
  AS $$
BEGIN
  -- We have to lock the referenced tree line - from top to bottom
  PERFORM FROM public.super WHERE id = NEW.report_to_super_id_fkey FOR NO KEY UPDATE;
  PERFORM FROM public.main WHERE id = NEW.report_to_main_id_fkey FOR NO KEY UPDATE;
  PERFORM FROM public.child WHERE id = NEW.report_to_child_id_fkey FOR NO KEY UPDATE;
 
  IF 
    (
      SELECT NOT EXISTS
      (
        SELECT FROM public.super
        LEFT JOIN public.main ON public.main.super_id_fkey = public.super.id
        LEFT JOIN public.child ON public.child.main_id_fkey = public.main.id
        WHERE
          (NEW.report_to_super_id_fkey IS NULL OR public.super.id = NEW.report_to_super_id_fkey) AND
          (NEW.report_to_main_id_fkey IS NULL OR public.main.id = NEW.report_to_main_id_fkey) AND
          (NEW.report_to_child_id_fkey IS NULL OR public.child.id = NEW.report_to_child_id_fkey)
      )
    )
  THEN
    RAISE EXCEPTION 'Reporting only to consistent hierarchy allowed';
  END IF;

 RETURN NEW;
END;
$$
LANGUAGE plpgsql
VOLATILE;

CREATE TRIGGER trigger_insert_update_employee_fkeys 
BEFORE INSERT OR UPDATE OF report_to_super_id_fkey, report_to_main_id_fkey, report_to_child_id_fkey ON public.employee
FOR EACH ROW 
EXECUTE FUNCTION public.check_hierarchy_consistency();

测试用例：

INSERT INTO public.super (name) VALUES ('1'), ('2'), ('3');

INSERT INTO public.main (super_id_fkey, name) VALUES
  (1, '1.1'), (1, '1.2'),
  (2, '2.1'), (2, '2.2'), (2, '2.3'),
  (3, '3.1'), (3, '3.2');

INSERT INTO public.child (main_id_fkey, name) VALUES
  ((SELECT id FROM public.main WHERE name = '1.1'), ('1.1.1')),
  ((SELECT id FROM public.main WHERE name = '1.1'), ('1.1.2')),
  ((SELECT id FROM public.main WHERE name = '1.2'), ('1.2.1')),
  ((SELECT id FROM public.main WHERE name = '2.1'), ('2.1.1'));
 
 
INSERT INTO public.employee (report_to_super_id_fkey, report_to_main_id_fkey, report_to_child_id_fkey) VALUES
(
    (SELECT id FROM public.super WHERE name = '1'),
    (SELECT id FROM public.main WHERE name = '1.1'),
    (SELECT id FROM public.child WHERE name = '1.1.2')
);
BEGIN;
  UPDATE public.employee SET report_to_child_id_fkey = (SELECT id FROM public.child WHERE name = '1.1.1') WHERE id = 1;
  -- Run on other session: UPDATE public.main SET super_id_fkey = (SELECT id FROM public.super WHERE name = '3') WHERE name = '1.1';
COMMIT;