Organization structure

CREATE MATERIALIZED VIEW m_org_closure AS
WITH RECURSIVE org_h (
    ancestor_oid, -- ref.targetoid
    descendant_oid --ref.owner_oid
    -- paths -- number of different paths, not used for materialized view version
    -- depth -- possible later, but cycle detected must be added to the recursive term
) AS (
    -- non-recursive term:
    -- Gather all organization oids from parent-org refs and initialize identity lines (o => o).
    -- We don't want the orgs not in org hierarchy, that would require org triggers too.
    SELECT o.oid, o.oid FROM m_org o
        WHERE EXISTS(
            SELECT 1 FROM m_ref_object_parent_org r
                WHERE r.targetOid = o.oid OR r.owner_oid = o.oid)
        -- It's possible to exclude orgs not in parent-org-refs (either owner or target!),
        -- but it's not a big deal and makes things simple for JOINs (no outer needed).
    UNION
    -- recursive (iterative) term:
    -- Generate their parents (anc => desc, that is target => owner), => means "is parent of".
    SELECT par.targetoid, chi.descendant_oid -- leaving original child there generates closure
        FROM m_ref_object_parent_org as par, org_h as chi
        WHERE par.owner_oid = chi.ancestor_oid
)
SELECT * FROM org_h;

-- unique index is like PK if it was table
CREATE UNIQUE INDEX m_org_closure_asc_desc_idx
    ON m_org_closure (ancestor_oid, descendant_oid);
CREATE INDEX m_org_closure_desc_asc_idx
    ON m_org_closure (descendant_oid, ancestor_oid);

-- The trigger for m_ref_object_parent_org that flags the view for refresh.
CREATE OR REPLACE FUNCTION mark_org_closure_for_refresh()
    RETURNS trigger
    LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'TRUNCATE' OR OLD.owner_type = 'ORG' OR NEW.owner_type = 'ORG' THEN
        INSERT INTO m_global_metadata VALUES ('orgClosureRefreshNeeded', 'true')
        ON CONFLICT (name) DO UPDATE SET value = 'true';
    END IF;

    -- after trigger returns null
    RETURN NULL;
END $$;

CREATE TRIGGER m_ref_object_parent_mark_refresh_tr
    AFTER INSERT OR UPDATE OR DELETE ON m_ref_object_parent_org
    FOR EACH ROW EXECUTE PROCEDURE mark_org_closure_for_refresh();
CREATE TRIGGER m_ref_object_parent_mark_refresh_trunc_tr
    AFTER TRUNCATE ON m_ref_object_parent_org
    FOR EACH STATEMENT EXECUTE PROCEDURE mark_org_closure_for_refresh();

-- This procedure for conditional refresh when needed is called from the application code.
-- The refresh can be forced, e.g. after many changes with triggers off (or just to be sure).
CREATE OR REPLACE PROCEDURE m_refresh_org_closure(force boolean = false)
    LANGUAGE plpgsql
AS $$
DECLARE
    flag_val text;
BEGIN
    SELECT value INTO flag_val FROM m_global_metadata WHERE name = 'orgClosureRefreshNeeded';
    IF flag_val = 'true' OR force THEN
        -- We use advisory session lock only for the check + refresh, then release it immediately.
        -- This can still dead-lock two transactions in a single thread on the select/delete combo,
        -- (I mean, who would do that?!) but works fine for parallel transactions.
        PERFORM pg_advisory_lock(47);
        BEGIN
            SELECT value INTO flag_val FROM m_global_metadata WHERE name = 'orgClosureRefreshNeeded';
            IF flag_val = 'true' OR force THEN
                REFRESH MATERIALIZED VIEW m_org_closure;
                DELETE FROM m_global_metadata WHERE name = 'orgClosureRefreshNeeded';
            END IF;
            PERFORM pg_advisory_unlock(47);
        EXCEPTION WHEN OTHERS THEN
            -- Whatever happens we definitely want to release the lock.
            PERFORM pg_advisory_unlock(47);
            RAISE;
        END;
    END IF;
END; $$;

CREATE TABLE m_ref_object_parent_org (
    owner_oid UUID NOT NULL REFERENCES m_object_oid(oid) ON DELETE CASCADE,
    referenceType ReferenceType GENERATED ALWAYS AS ('OBJECT_PARENT_ORG') STORED,

    -- TODO wouldn't (owner_oid, targetOid, relation_id) perform better for typical queries?
    PRIMARY KEY (owner_oid, relation_id, targetOid)
)
    INHERITS (m_reference);

-- region org-closure
-- Closure is handled by two views - one materialized (m_org_closure_internal) and one with return
-- rule (m_org_closure). Only the second one is used from the outside.
-- Trigger on m_ref_object_parent_org creates flag that the materialized view must be refreshed
CREATE MATERIALIZED VIEW m_org_closure_internal AS
WITH RECURSIVE org_h (
    ancestor_oid, -- ref.targetoid
    descendant_oid --ref.owner_oid
) AS (
    -- gather all organizations with parents
    SELECT r.targetoid, r.owner_oid
        FROM m_ref_object_parent_org r
        WHERE r.owner_type = 'ORG'
    UNION
    -- generate their parents
    SELECT par.targetoid, chi.descendant_oid -- leaving original child there generates closure
        FROM m_ref_object_parent_org as par, org_h as chi
        WHERE par.owner_oid = chi.ancestor_oid
)
SELECT * FROM org_h;

-- unique index is like PK if it was table
CREATE UNIQUE INDEX m_org_closure_internal_asc_desc_idx
    ON m_org_closure_internal (ancestor_oid, descendant_oid);
CREATE INDEX m_org_closure_internal_desc_asc_idx
    ON m_org_closure_internal (descendant_oid, ancestor_oid);

-- no keys/indexes needed, this is backed by m_org_closure_internal
CREATE TABLE m_org_closure (
    ancestor_oid UUID, -- ref.targetoid
    descendant_oid UUID --ref.owner_oid
);

CREATE OR REPLACE PROCEDURE m_refresh_org_closure()
    LANGUAGE plpgsql
AS $$
DECLARE
    flag_val text;
BEGIN
    -- We use advisory session lock only for the check + refresh, then release it immediately.
    -- This can still dead-lock two transactions in a single thread on the select/delete combo,
    -- (I mean, who would do that?!) but works fine for parallel transactions.
    PERFORM pg_advisory_lock(47);
    SELECT value INTO flag_val FROM m_global_metadata WHERE name = 'orgClosureRefreshNeeded';
    IF flag_val = 'true' THEN
        REFRESH MATERIALIZED VIEW m_org_closure_internal;
        DELETE FROM m_global_metadata WHERE name = 'orgClosureRefreshNeeded';
    END IF;
    PERFORM pg_advisory_unlock(47);
EXCEPTION WHEN OTHERS THEN
    -- Whatever happens we definitely want to release the lock.
    PERFORM pg_advisory_unlock(47);
END; $$;

-- Function implementing select for rule on m_org_closure, refreshing backing view if needed.
CREATE OR REPLACE FUNCTION m_return_org_closure()
    RETURNS TABLE (
        ancestor_oid UUID, -- ref.targetoid
        descendant_oid UUID --ref.owner_oid
    )
    LANGUAGE plpgsql
AS $$
DECLARE
    flag_val text;
BEGIN
    -- No lock here, if the view is OK, we don't want any locking at all.
    SELECT value INTO flag_val FROM m_global_metadata WHERE name = 'orgClosureRefreshNeeded';
    IF flag_val = 'true' THEN
        CALL m_refresh_org_closure();
    END IF;

    RETURN QUERY SELECT * FROM m_org_closure_internal;
END $$;

CREATE RULE "_RETURN" AS ON SELECT TO m_org_closure DO INSTEAD SELECT * from m_return_org_closure();

-- The trigger for m_ref_object_parent_org that flags the view for refresh.
CREATE OR REPLACE FUNCTION mark_org_closure_for_refresh()
    RETURNS trigger
    LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.owner_type = 'ORG' OR NEW.owner_type = 'ORG' THEN
         INSERT INTO m_global_metadata VALUES ('orgClosureRefreshNeeded', 'true')
         ON CONFLICT (name) DO UPDATE SET value = 'true';
    END IF;

    -- after trigger returns null
    RETURN NULL;
END $$;

CREATE TRIGGER m_ref_object_parent_org_closure1_tr
AFTER INSERT OR UPDATE OR DELETE ON m_ref_object_parent_org
FOR EACH ROW EXECUTE PROCEDURE mark_org_closure_for_refresh();
CREATE TRIGGER m_ref_object_parent_org_closure2_tr
AFTER TRUNCATE ON m_ref_object_parent_org
FOR EACH STATEMENT EXECUTE PROCEDURE mark_org_closure_for_refresh();
-- endregion

-- The trigger for m_ref_object_parent_org that refreshes the view.
CREATE OR REPLACE FUNCTION m_org_closure_refresh()
    RETURNS trigger
    LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'TRUNCATE' OR OLD.owner_type = 'ORG' OR NEW.owner_type = 'ORG' THEN
        REFRESH MATERIALIZED VIEW m_org_closure;
    END IF;

    -- after trigger returns null
    RETURN NULL;
END $$;

CREATE TRIGGER m_ref_object_parent_org_refresh_tr
    AFTER INSERT OR UPDATE OR DELETE ON m_ref_object_parent_org
    FOR EACH ROW EXECUTE PROCEDURE m_org_closure_refresh();
CREATE TRIGGER m_ref_object_parent_org_trunc_refresh_tr
    AFTER TRUNCATE ON m_ref_object_parent_org
    FOR EACH STATEMENT EXECUTE PROCEDURE m_org_closure_refresh();