[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Having more than one constraint trigger on a table
From: Andreas Joseph Krogh <andreas () visena ! com>
Date: 2019-10-24 15:30:16
Message-ID: VisenaEmail.59.2ba90a5fb05e6c3.16dfe631bb5 () tc7-visena
[Download RAW message or body]
[Attachment #2 (multipart/related)]
[Attachment #4 (multipart/alternative)]
På torsdag 24. oktober 2019 kl. 16:59:42, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto: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:
https://stackoverflow.com/questions/8937203/execute-deferred-trigger-only-once-per-row-in-postgresql#answer-8954058
<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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
[Attachment #7 (text/html)]
<div>
<div>På torsdag 24. oktober 2019 kl. 16:59:42, skrev Adrian Klaver <<a \
href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>>:</div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt \
0.8ex; padding-left: 1ex;"> <div style="display: inline; font-family: monospace;">On \
10/22/19 8:26 AM, Andreas Joseph Krogh wrote:<br> > På tirsdag 22. oktober 2019 \
kl. 17:12:59, skrev Adrian Klaver<br> > <adrian.klaver@aklaver.com \
<mailto:adrian.klaver@aklaver.com>>:<br> ><br>
> [snip]<br>
> No.<br>
> When I sort the triggers I get:<br>
><br>
> test=# create table trg_str(fld_1 varchar);<br>
> CREATE TABLE<br>
> test=# insert into trg_str values ('trigger_1_update_fts'),<br>
> ('trigger_2'), ('trigger_3'), ('trigger_1_check_nocycle');<br>
> INSERT 0 4<br>
> test=# select * from trg_test order by fld_1 ;<br>
> id | fld_1<br>
> ----+-------<br>
> (0 rows)<br>
><br>
> test=# select * from trg_str order by fld_1 ;<br>
> fld_1<br>
> -------------------------<br>
> trigger_1_check_nocycle<br>
> trigger_1_update_fts<br>
> trigger_2<br>
> trigger_3<br>
><br>
> Is this how you want them to fire as it does not match what you say<br>
> above?:<br>
><br>
> (I know they were not /declared/ in that order, but..)<br>
> Yes, all "trigger_1_*" are the "actuall triggers triggering the \
logic",<br> > trigger_2 and trigger_3 are only there as part of the \
"make<br> > constraint-triggers fire only once"-mechanism, in which the \
function in<br> > the first trigger is the function performing the actual \
logic.<br> > So, being I want 2 "logical chunks" to happen I have \
two<br> > "trigger_1"-triggers (there is no established terminilogy for \
this<br> > AFAIK), each calling a function performing the logick which is to \
happen<br> > only once (per row).<br>
><br>
> "The first "main" trigger-function is \
update_company_fts_tf() ... The<br> > second "main" \
trigger-function is company_parent_no_cycle()"<br> ><br>
> It might be easier to understand if sketch out a schematic version of<br>
> what you are trying to achieve.<br>
><br>
> The point is; I want to functions to be called<br>
> - update_company_fts_tf()<br>
> - company_parent_no_cycle()<br>
> , each only once, as constraint-triggers on the same table. So they are<br>
> called by the "level 1 triggers" which must fire first.<br>
<br>
To be clear the order they fire relative to each other is not important?</div>
</blockquote>
<div style="display: inline; font-family: monospace;"> </div>
<div style="display: inline; font-family: monospace;"> </div>
<div style="display: inline; font-family: monospace;">Correct, these main functions \
may fire in any order.</div>
<div style="display: inline; font-family: monospace;"> </div>
<div style="display: inline; font-family: monospace;"> </div>
<div style="display: inline; font-family: monospace;"> </div>
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt \
0.8ex; padding-left: 1ex;"> <div style="display: inline; font-family: \
monospace;">> Is it clearer now what I'm trying to achieve?<br> <br>
Sort of, though I am still not entirely what the whole process is trying<br>
to achieve. What the mix of deferred and un-deferred triggers and<br>
'logical' and housekeeping functions are doing is not clear to me. That<br>
is why I suggested a schematic representation of the trigger flow would<br>
be helpful. Leave out the fine details and create a flow chart of what<br>
you want to happen.</div>
</blockquote>
<div> </div>
<div> </div>
<div>Normally, CONSTRAINT TRIGGERs will fire once <em>for each </em>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).</div>
<div> </div>
<div>I'm using the trick mentioned here to achieve this:</div>
<div><a href="https://stackoverflow.com/questions/8937203/execute-deferred-trigger-onl \
y-once-per-row-in-postgresql#answer-8954058">https://stackoverflow.com/questions/89372 \
03/execute-deferred-trigger-only-once-per-row-in-postgresql#answer-8954058</a></div>
<div> </div>
<div class="visena-email-signature" \
data-visena-message-id="<VisenaEmail.58.75b2e7fc270d1606.16dfe5abf0e@tc7-visena>"> \
<div>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) ?</div>
<div> </div>
<div>--</div>
<div><span style="font-family: arial, helvetica, sans-serif;"><strong>Andreas Joseph \
Krogh</strong></span></div>
<div><span style="font-family: arial, helvetica, sans-serif;">CTO / Partner</span> - \
Visena AS</div>
<div><span style="font-family: arial, helvetica, sans-serif;">Mobile: +47 909 56 \
963</span></div>
<div><a href="mailto:andreas@visena.com">andreas@visena.com</a></div>
<div><a href="https://www.visena.com">www.visena.com</a></div>
<div style="padding-top: 5px;"><a href="https://www.visena.com"><img alt="" \
style="max-width: 100%;" src="cid:part_F1341151210059RQ0XRZ"></a></div> </div>
</div>
[Attachment #8 (image/png)]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic