Having more than one constraint trigger on a table

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

Hi.

 

I have the following schema (question at bottom):

==============================

CREATE TABLE company(id SERIAL PRIMARY KEY, parent_id INTEGER REFERENCES company(id) DEFERRABLE INITIALLY DEFERRED , name VARCHAR NOT NULL, duns_number VARCHAR, fts_all tsvector, t_updated BOOLEAN);

CREATE or replace FUNCTION update_company_fts(p_company_id integer) RETURNS VOID AS
$$
BEGIN
    UPDATE company comp
    SET fts_all = to_tsvector('simple'
        , comp.name
              || ' ' || coalesce(comp.duns_number, '')
        )
    WHERE comp.id = p_company_id;

    raise notice 'Running update of %', p_company_id;
END;
$$ LANGUAGE plpgsql;

-- re-index all:
CREATE OR REPLACE FUNCTION index_company() RETURNS VOID AS
$$
DECLARE
    v_company_id INTEGER;
begin
    FOR v_company_id IN (SELECT id FROM company)
        LOOP
            perform update_company_fts(v_company_id);
        END LOOP;
END;
$$ LANGUAGE plpgsql;

create or replace function update_company_fts_tf() returns TRIGGER AS
$$
declare
    v_company_id INTEGER;
BEGIN
    v_company_id := NEW.id;
    perform update_company_fts(v_company_id);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


-- General cleanup functions for constraint triggers
CREATE OR REPLACE FUNCTION trigger_function_set_updated() returns TRIGGER AS
$$
BEGIN
    update company set t_updated = TRUE WHERE id = NEW.id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trigger_function_clear_updated() returns TRIGGER AS
$$
BEGIN
    update company set t_updated = NULL WHERE id = NEW.id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_1_update_fts
    AFTER INSERT OR UPDATE of name, duns_number
    ON company DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE update_company_fts_tf();

CREATE CONSTRAINT TRIGGER trigger_2
    AFTER INSERT OR UPDATE of name, duns_number, parent_id
    ON company -- NOT DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE trigger_function_set_updated();

CREATE CONSTRAINT TRIGGER trigger_3
    AFTER INSERT OR UPDATE OF t_updated
    ON company DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated)
EXECUTE PROCEDURE trigger_function_clear_updated();

CREATE OR REPLACE FUNCTION company_parent_no_cycle() returns TRIGGER AS
$$
BEGIN
    IF (WITH recursive tr (id, parent_id, all_ids, cycle) AS (
        SELECT id, parent_id, ARRAY [id], false
        FROM company tr
        WHERE id = NEW.id
        UNION ALL
        SELECT t.id, t.parent_id, all_ids || t.id, t.id = ANY (all_ids)
        FROM company t
                 JOIN tr ON t.parent_id = tr.id AND NOT cycle)
        SELECT count(*)
        FROM tr
        where cycle = true) > 0 THEN
        RAISE EXCEPTION 'Cannot have cyclic parent relations for company'
            USING SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, CONSTRAINT = TG_NAME
                , ERRCODE = '23514'/*check_violation*/, COLUMN = 'parent_id';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_1_check_nocycle
    AFTER INSERT OR UPDATE of parent_id
    ON company DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE company_parent_no_cycle();

==============================

 

What I'm after is to have 2 "logical constraint-triggers" perform logic only once (each) on the "company"-table.

To make constraint-triggers fire only once (in PostgreSQL) a common method is to have a schema with 3 triggers, and a "magic" t_updated column, and they must be named so they (the triggers, not the trigger-functions) are fired in lexical order (alphabetically).  And it's important that the 2nd. trigger (here "trigger_2") is NOT deferred.

 

In my schema above I have 2 "logical chuchks" which each perform some stuff and shall only do it once per row at commit-time.

The first "main" trigger-function is update_company_fts_tf() and it updates a column (fts_all) of type tsvector. This is done in a trigger so that it may add stuff (customer-number etc.) from other tables as needed (which is not possible with PG-12's new STORED-columns).

The second "main" trigger-function is company_parent_no_cycle() and  assures there are no parent/child-cycles.

 

Question:

1. I have "re-used" trigger_2 and trigger_3, and trigger_2's "...OR UPDATE OF"-list is the sum of all columns updated(used) in the 2 main-triggers, that is "name", "duns_number" and parent_id. trigger_3 only checks t_updated.

Is this correct usage, can I assume this will work correctly?

2. If I need a 3rd "logical trigger", is it enough to add another trigger named accordingly, for instance "trigger_1_someotherstuff", and add it's column to the "UPDATE OF"-list of "trigger_2" (it it uses a column not already listed there)?

3. Is there some easier way to do this?

 

Is it clear what I'm asking about? :-)

 

Thanks.

 

--
Andreas Joseph Krogh