Having more than one constraint trigger on a table - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject Having more than one constraint trigger on a table
Date
Msg-id VisenaEmail.1e.6e8e231ce73ef544.16df39f9bb3@tc7-visena
Whole thread Raw
Responses Re: Having more than one constraint trigger on a table
List pgsql-general
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

pgsql-general by date:

Previous
From: Josef Šimánek
Date:
Subject: Re: FW: Re: A question about building pg-libphonenumber
Next
From: Adrian Klaver
Date:
Subject: Re: Having more than one constraint trigger on a table