1

In my system, I have employees that belong to a single department. However, some departments are large enough to have a sub-departments, so I have created a single department table like so:

CREATE TABLE department (
    name varchar(127) NOT NULL primary key,
    parent_department varchar(127) REFERENCES department(name) ON DELETE RESTRICT ON UPDATE CASCADE,
    check (name != parent_department)
);

... and referenced it from the employee table:

CREATE TABLE "employee" (
    id uuid PRIMARY KEY,
    department varchar(127) NOT NULL references department(name) ON DELETE RESTRICT ON UPDATE CASCADE
);

In my use case, a department can only up to one layer of any number of sub-departments (a sub-department can't itself have sub-departments). I thought the easiest way to prevent the possibility of a recursive loop would be to have PostgreSQL (version 14.x) to check that if an updated/inserted department has a parent_department, and if it does, then check that the parent row itself does not have a value within the parent_department field (value should be null). How can I add this check/trigger to the database (bonus points if the solution could easily be adapted to a depth to 3 instead of 2)?

1
  • Make the department PK synthetic, and put a unique constraint on department.name. Then have department_id be part of employee, with a FK constraint. That way, only one row in one table gets changed when you rename a department.
    – RonJohn
    Apr 11 at 0:45

1 Answer 1

0

Have you considered disallowing direct inserts and updates in favor of PL/pgSQL functions to validate and complete inserts and updates?

Either way...

The uniqueness of department.name (both as a primary and foreign key) in conjunction with your name != parent_department constraint should prevent and insertions that would otherwise create circular references. Preventing the update case seems a bit more challenging, given the recursive nature of the inspection (e.g., updating the parent of an existing record to create the circular reference).

To handle the update scenario, and to allow for identically named sub-departments (e.g., in different departments), perhaps a trigger-based solution would work. The following example, uses a before trigger to block inserts and updates that would otherwise create circular references, using a temporary table as a testing ground for a bounded, department-specific counting function. It's perhaps a bit over-the-top, and may be more complex than necessary, though here it is (tested using \i):

\pset expanded off
\pset footer on
\pset format aligned
\pset null '(null)'
\pset pager off
\pset tuples_only off
\set AUTOCOMMIT off
\set ECHO none
\set ECHO_HIDDEN off
\set ON_ERROR_STOP on
\set VERBOSITY DEFAULT
\timing on

\set ON_ERROR_STOP off
SET client_min_messages TO ERROR;
ROLLBACK;
\connect postgres
\set AUTOCOMMIT on
SET client_min_messages TO WARNING;
DROP DATABASE IF EXISTS recurse_test;
\set ON_ERROR_STOP on

CREATE DATABASE recurse_test;
\set AUTOCOMMIT off
\connect recurse_test
SET client_min_messages TO WARNING;
COMMIT;

BEGIN;

CREATE TABLE department
(
    name    varchar PRIMARY KEY,
    parent  varchar DEFAULT null REFERENCES department( name ) ON UPDATE CASCADE ON DELETE RESTRICT,
    CHECK ( name != parent )
);
CREATE UNIQUE INDEX __department_parent_onenull
    ON department( ( parent IS null ) )
    WHERE parent IS null;

CREATE TABLE __department AS SELECT * FROM department LIMIT 0;

CREATE TYPE __department_traverse AS
(
    count   bigint,
    depth   integer
);

CREATE FUNCTION department_traverse( _table varchar, _name varchar, _depth integer, _limit bigint )
    RETURNS __department_traverse
AS $$ DECLARE
    ret __department_traverse;
    s varchar;
    r record;
BEGIN
    RAISE DEBUG 'department_traverse(%,%,%,%)', _table, _name, _depth, _limit;
    IF _depth = _limit THEN
        RAISE NOTICE 'Recursion Limit Reached: %', _limit;
        RETURN ret;
    END IF;

    s := 'SELECT count(1), 0 FROM ' || quote_ident(_table) || ' WHERE parent = $1';
    EXECUTE s INTO ret USING _name;

    IF ret.count >= 1 THEN
        s := 'SELECT (department_traverse($1,name,$3,$4)).* FROM ' || quote_ident(_table) || ' WHERE parent = $2';
        FOR r IN
            EXECUTE s USING _table, _name, _depth + 1, _limit
        LOOP
            IF r.count IS null THEN
                ret.count := null;
                ret.depth := null;
                IF _depth = 0 THEN
                    RAISE EXCEPTION 'Circular Reference Detected';
                END IF;
                RETURN ret;
            END IF;
            ret.count := ret.count + r.count;
            ret.depth := greatest(ret.depth,r.depth);
        END LOOP;
        ret.depth := ret.depth + 1;
    END IF;
    RETURN ret;
END $$ LANGUAGE PLPGSQL;

CREATE FUNCTION department_traverse( _table varchar, _name varchar )
    RETURNS __department_traverse
AS $$ DECLARE
    s varchar;
    r __department_traverse;
BEGIN
    RAISE DEBUG 'department_traverse(%,%)', _table, _name;
    s := 'SELECT (department_traverse($1,$2,0,( SELECT count(1) FROM ' || quote_ident(_table) || '))).*';
    EXECUTE s INTO r USING _table, _name;
    RETURN r;
END $$ LANGUAGE PLPGSQL;

CREATE FUNCTION department_traverse( _name varchar )
    RETURNS __department_traverse
AS $$
    SELECT department_traverse('department',_name)
$$ LANGUAGE SQL;

CREATE FUNCTION __department() RETURNS TRIGGER AS $$
DECLARE
    n bigint;
    r __department_traverse;
BEGIN
    IF TG_OP = 'INSERT' THEN
        RAISE DEBUG '__department(){ op=%, name=% }', TG_OP, new.name;
    ELSIF TG_OP = 'UPDATE' AND old.name = new.name THEN
        RAISE DEBUG '__department(){ op=%, name=% }', TG_OP, old.name;
    ELSIF TG_OP = 'UPDATE' THEN
        RAISE DEBUG '__department(){ op=%, old.name=%, new.name=% }', TG_OP, old.name, new.name;
    END IF;
    TRUNCATE __department;
    INSERT INTO __department SELECT * FROM department;
    INSERT INTO __department ( name, parent ) VALUES ( new.name, new.parent );
    SELECT (department_traverse('__department',name)).* INTO r
        FROM __department d
        WHERE parent IS null;
    TRUNCATE __department;
    IF r.count IS NOT null THEN
        RETURN new;
    ELSE
        RETURN null;
    END IF;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER __department
    BEFORE INSERT OR UPDATE
    ON department
    FOR EACH ROW
    EXECUTE PROCEDURE __department();

COPY department ( parent, name ) FROM stdin;
\N  Company
Company Administration
Administration  Finance
Administration  Human Resources
Company Revenue
Revenue Partners
Revenue Sales
Company Technology
Technology  Development
Technology  Operations
\.

COMMIT;

\set ON_ERROR_STOP off

BEGIN;
\set VERBOSITY TERSE
UPDATE department SET parent = 'Operations' WHERE name = 'Technology';
ROLLBACK;

BEGIN;
SELECT
        parent,
        name,
        (department_traverse(name)).*
    FROM department d
    ORDER BY ctid ASC;
ROLLBACK;

\connect postgres
\set AUTOCOMMIT on
SET client_min_messages TO WARNING;
DROP DATABASE recurse_test;

P.S. Some of the preamble and clean-up facilitated testing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.