Re: Having more than one constraint trigger on a table - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Having more than one constraint trigger on a table |
Date | |
Msg-id | 27796c9e-4e3a-72bd-ad69-02733e989ab9@aklaver.com Whole thread Raw |
In response to | Having more than one constraint trigger on a table (Andreas Joseph Krogh <andreas@visena.com>) |
Responses |
Re: Having more than one constraint trigger on a table
|
List | pgsql-general |
On 10/22/19 7:54 AM, Andreas Joseph Krogh wrote: > Hi. > I have the following schema (question at bottom): > ============================== > > CREATE TABLE company(idSERIAL PRIMARY KEY, parent_idINTEGER REFERENCES company(id)DEFERRABLE INITIALLY DEFERRED ,name VARCHARNOT NULL, duns_numberVARCHAR, fts_alltsvector, t_updatedBOOLEAN); > > CREATE or replace FUNCTION update_company_fts(p_company_idinteger)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_idINTEGER; > begin FOR v_company_idIN (SELECT idFROM 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_idINTEGER; > 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 =NULLWHERE 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_updatedIS 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_updatedIS 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 trON 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_updatedIS 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? :-) No. When I sort the triggers I get: test=# create table trg_str(fld_1 varchar); CREATE TABLE test=# insert into trg_str values ('trigger_1_update_fts'), ('trigger_2'), ('trigger_3'), ('trigger_1_check_nocycle'); INSERT 0 4 test=# select * from trg_test order by fld_1 ; id | fld_1 ----+------- (0 rows) test=# select * from trg_str order by fld_1 ; fld_1 ------------------------- trigger_1_check_nocycle trigger_1_update_fts trigger_2 trigger_3 Is this how you want them to fire as it does not match what you say above?: "The first "main" trigger-function is update_company_fts_tf() ... The second "main" trigger-function is company_parent_no_cycle()" It might be easier to understand if sketch out a schematic version of what you are trying to achieve. > Thanks. > -- > Andreas Joseph Krogh -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: