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 VARCHAR NOT 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 =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_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@xxxxxxxxxxx
