Re: Having more than one constraint trigger on a table

[Andreas Joseph Krogh]

På torsdag 24. oktober 2019 kl. 16:59:42, skrev Adrian Klaver <
adrian.kla...@aklaver.com >: 
On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote:
 > På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>>:
 >
 > [snip]
 > 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?:
 >
 > (I know they were not /declared/ in that order, but..)
 > Yes, all "trigger_1_*" are the "actuall triggers triggering the logic",
 > trigger_2 and trigger_3 are only there as part of the "make
 > constraint-triggers fire only once"-mechanism, in which the function in
 > the first trigger is the function performing the actual logic.
 > So, being I want 2 "logical chunks" to happen I have two
 > "trigger_1"-triggers (there is no established terminilogy for this
 > AFAIK), each calling a function performing the logick which is to happen
 > only once (per row).
 >
 > "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.
 >
 > The point is; I want to functions to be called
 > - update_company_fts_tf()
 > - company_parent_no_cycle()
 > , each only once, as constraint-triggers on the same table. So they are
 > called by the "level 1 triggers" which must fire first.

 To be clear the order they fire relative to each other is not important? 


Correct, these main functions may fire in any order. 



> Is it clearer now what I'm trying to achieve?

 Sort of, though I am still not entirely what the whole process is trying
 to achieve. What the mix of deferred and un-deferred triggers and
 'logical' and housekeeping functions are doing is not clear to me. That
 is why I suggested a schematic representation of the trigger flow would
 be helpful. Leave out the fine details and create a flow chart of what
 you want to happen. 


Normally, CONSTRAINT TRIGGERs will fire once for each UPDATE. That means, if 
you do CREATE, the 2 UPDATES, then the trigger(s) will fire 3 times for each 
row. I'm trying to make these triggers fire only ONCE per row, and at COMMIT 
(being CONSTRAINT TRIGGER). 

I'm using the trick mentioned here to achieve this: 

https://stackoverflow.com/questions/8937203/execute-deferred-trigger-only-once-per-row-in-postgresql#answer-8954058
 



But I'm trying to have more than one CONSTRAINT TRIGGER on the same table, 
each one doing dirfferent things and reacting (triggering) on different 
columns, and I'm wondering if I can "re-use" the "cleanup-triggers" 2 and 3 as 
I mentioned, having trigger 2 firing on the sum of all involved COLUMNS (name, 
duns_number, parent_id) ? 

-- 
Andreas Joseph Krogh 
CTO / Partner - Visena AS 
Mobile: +47 909 56 963 
andr...@visena.com  
www.visena.com  
 

Re: Having more than one constraint trigger on a table

[Adrian Klaver]

On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote:
På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver 
mailto:adrian.kla...@aklaver.com>>:


[snip]
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?:

(I know they were not /declared/ in that order, but..)
Yes, all "trigger_1_*" are the "actuall triggers triggering the logic", 
trigger_2 and trigger_3 are only there as part of the "make 
constraint-triggers fire only once"-mechanism, in which the function in 
the first trigger is the function performing the actual logic.
So, being I want 2 "logical chunks" to happen I have two 
"trigger_1"-triggers (there is no established terminilogy for this 
AFAIK), each calling a function performing the logick which is to happen 
only once (per row).


"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.

The point is; I want to functions to be called
- update_company_fts_tf()
- company_parent_no_cycle()
, each only once, as constraint-triggers on the same table. So they are 
called by the "level 1 triggers" which must fire first.


To be clear the order they fire relative to each other is not important?


Is it clearer now what I'm trying to achieve?


Sort of, though I am still not entirely what the whole process is trying 
to achieve. What the mix of deferred and un-deferred triggers and 
'logical' and housekeeping functions are doing is not clear to me. That 
is why I suggested a schematic representation of the trigger flow would 
be helpful. Leave out the fine details and create a flow chart of what 
you want to happen.



--
Andreas Joseph Krogh



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Having more than one constraint trigger on a table

[Andreas Joseph Krogh]

På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver <
adrian.kla...@aklaver.com >: 
[snip]
 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?: 

(I know they were not declared in that order, but..) 
Yes, all "trigger_1_*" are the "actuall triggers triggering the logic", 
trigger_2 and trigger_3 are only there as part of the "make constraint-triggers 
fire only once"-mechanism, in which the function in the first trigger is the 
function performing the actual logic. 
So, being I want 2 "logical chunks" to happen I have two "trigger_1"-triggers 
(there is no established terminilogy for this AFAIK), each calling a function 
performing the logick which is to happen only once (per row). 


"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. 

The point is; I want to functions to be called 

- update_company_fts_tf() 
- company_parent_no_cycle() 

, each only once, as constraint-triggers on the same table. So they are called 
by the "level 1 triggers" which must fire first. 

Is it clearer now what I'm trying to achieve? 


--
 Andreas Joseph Krogh

Re: Having more than one constraint trigger on a table

[Adrian Klaver]

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 

Having more than one constraint trigger on a table

[Andreas Joseph Krogh]

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 
companycomp 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 FUNCTIONindex_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 functionupdate_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 triggersCREATE 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 FUNCTIONtrigger_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 TRIGGERtrigger_2 AFTER INSERT OR UPDATE of name, duns_number, 
parent_idON company -- NOT DEFERRED FOR EACH ROW  WHEN (NEW.t_updated IS NULL) 
EXECUTE PROCEDUREtrigger_function_set_updated(); CREATE CONSTRAINT TRIGGER 
trigger_3AFTER 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  
FROMcompany 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 ofparent_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