Thread: Having more than one constraint trigger on a table

Having more than one constraint trigger on a table

From
Andreas Joseph Krogh
Date:
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

Re: Having more than one constraint trigger on a table

From
Adrian Klaver
Date:
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



Re: Having more than one constraint trigger on a table

From
Andreas Joseph Krogh
Date:
På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver <adrian.klaver@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

From
Adrian Klaver
Date:
On 10/22/19 8:26 AM, Andreas Joseph Krogh wrote:
> På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com



Re: Having more than one constraint trigger on a table

From
Andreas Joseph Krogh
Date:
På torsdag 24. oktober 2019 kl. 16:59:42, skrev Adrian Klaver <adrian.klaver@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
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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:
 
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
Attachment