AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Postgres show all tables8/6/2023 ![]() ![]() Run on other session: UPDATE public.main SET super_id_fkey = (SELECT id FROM public.super WHERE name = '3') WHERE name = '1.1' UPDATE public.employee SET report_to_child_id_fkey = (SELECT id FROM public.child WHERE name = '1.1.1') WHERE id = 1 (SELECT id FROM public.child WHERE name = '1.1.2') (SELECT id FROM public.main WHERE name = '1.1'), (SELECT id FROM public.super WHERE name = '1'), INSERT INTO public.employee (report_to_super_id_fkey, report_to_main_id_fkey, report_to_child_id_fkey) VALUES ![]() INSERT INTO public.child (main_id_fkey, name) VALUES INSERT INTO public.main (super_id_fkey, name) VALUES Testcase: INSERT INTO public.super (name) VALUES ('1'), ('2'), ('3') RAISE EXCEPTION 'Reporting only to consistent hierarchy allowed' ĬREATE TRIGGER trigger_insert_update_employee_fkeysīEFORE INSERT OR UPDATE OF report_to_super_id_fkey, report_to_main_id_fkey, report_to_child_id_fkey ON public.employeeĮXECUTE FUNCTION public.check_hierarchy_consistency() (NEW.report_to_child_id_fkey IS NULL OR = NEW.report_to_child_id_fkey) (NEW.report_to_main_id_fkey IS NULL OR = NEW.report_to_main_id_fkey) AND (NEW.report_to_super_id_fkey IS NULL OR = NEW.report_to_super_id_fkey) AND PERFORM FROM public.child WHERE id = NEW.report_to_child_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.super WHERE id = NEW.report_to_super_id_fkey FOR NO KEY UPDATE We have to lock the referenced tree line - from top to bottom WHEN (OLD.main_id_fkey IS DISTINCT FROM NEW.main_id_fkey)ĮXECUTE FUNCTION public.check_child_reference() ĬREATE OR REPLACE FUNCTION public.check_hierarchy_consistency() WHEN (OLD.super_id_fkey IS DISTINCT FROM NEW.super_id_fkey)ĮXECUTE FUNCTION public.check_main_reference() ĬREATE TRIGGER trigger_insert_update_child_main_id_fkeyīEFORE UPDATE OF main_id_fkey ON public.child JOIN public.child ON _id_fkey = ĮXISTS (SELECT FROM data WHERE employee.report_to_main_id_fkey = data.main_id) ORĮXISTS (SELECT FROM data WHERE employee.report_to_child_id_fkey = data.child_id)ĬREATE TRIGGER trigger_insert_update_super_id_fkeyīEFORE UPDATE OF super_id_fkey ON public.main SELECT AS super_id, AS main_id, AS child_id FROM public.super RAISE EXCEPTION 'Organizations units or child units having referenced employees can''t be moved' ĬREATE OR REPLACE FUNCTION public.check_child_reference() JOIN public.main ON _id_fkey = ĮXISTS (SELECT FROM data WHERE employee.report_to_super_id_fkey = data.super_id) ORĮXISTS (SELECT FROM data WHERE employee.report_to_main_id_fkey = data.main_id) SELECT AS super_id, AS main_id FROM public.super Requirement 3: CREATE OR REPLACE FUNCTION public.check_main_reference() (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) (report_to_super_id_fkey IS NOT NULL AND report_to_main_id_fkey IS NOT NULL) OR Report_to_child_id_fkey BIGINT REFERENCES public.child(id), Report_to_main_id_fkey BIGINT REFERENCES public.main(id), Report_to_super_id_fkey BIGINT REFERENCES public.super(id), Main_id_fkey BIGINT NOT NULL REFERENCES public.main(id), Super_id_fkey BIGINT NOT NULL REFERENCES public.super(id), Id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, Question: Can somebody review the solution in terms of concurrency correctness? The UPDATE example / test below worked as expected (2nd transaction was blocked) - but are there any other cases to test? CREATE TABLE public.super Another solution would be to check check each affected employee after a change I don't think that i need to lock something for R3 - in worst case somebody changes an employee before COMMIT - but it should be still consistent.R4: employees must always reference to a consistent tree -> There must be a path from bottom to top / no foreign units are allowed to being referenced.R3: If an unit or its parent references an employee, they are not allowed to being moved.R2: An employe can report to: (super, main, child) or (super, main) or (super).R1: Organisation units: super -> main -> child. ![]()
0 Comments
Read More
Leave a Reply. |