Re: Multi-row constraints, how to avoid unnecessary trigger execution?

På onsdag 06. april 2016 kl. 18:32:50, skrev Tobia Conforto <
tobia(dot)conforto(at)gruppo4(dot)eu <mailto:tobia(dot)conforto(at)gruppo4(dot)eu>>:
I have a complex data validation requirement that spans many rows and possibly
more than one table.

The application must be able to perform several data manipulation statements
that could invalidate the requirement between one another, and only have the
database check this requirement at transaction commit time.

Ideally I would have some sort of after trigger that is deferred to commit
time, have it search for invalid or missing records and raise errors if any is
found.

If I'm reading the manual correctly, the only kind of trigger that can be
deferred to commit time is a constraint trigger. The problem is that this
trigger must be declared for each row, but I need to only perform the
validation once per transaction, because it executes complex queries.

What is the best way to address this issue?

Is there any other way, other than a constraint trigger, to defer execution
of a piece of code at transaction commit time?

Otherwise, can I check for repeated invocations of my trigger function in the
same transaction and return early on the second and following ones? I could do
that by creating a temporary table on commit drop, but it seems overkill. Is
there a lighter solution?
 
Hi.
 
(note that my answer here only prevents executing the trigger-logic more than
oncefor each row, so it will fire for each row affected at commit, just not
multiple times for the same row it it's updated several times in the same
transaction)
 
The trick is to use constraint-triggers, and to have a condition (column) to
test for so that it does the actual work only once.
 
Triggers in PG is fired in alphabetical order so a good naming-scheme for such
triggers is <trigger-name>_1, <trigger-name>_2, <trigger-name>_3
 
It's the first trigger which does the actual work (in this case
index_email_1_tf()).
 
I use a special column, t_updated, for checking. This column has no other
purpose than to help the triggers. Note that trigger _2 is NOT DEFERRED, this
is important.
 
Here is what I use:
 
-- Trigger function to index email CREATE OR REPLACE FUNCTION index_email_1_tf
()returns TRIGGER AS $$ declare v_email message; BEGIN SELECT * FROM message
WHERE entity_id = NEW.entity_id INTO v_email; perform index_email(v_email);
RETURN NEW;END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION index_email_2_tf
()returns TRIGGER AS $$ BEGIN update message set t_updated = TRUE WHERE
entity_id = NEW.entity_id; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE OR
REPLACE FUNCTIONindex_email_3_tf() returns TRIGGER AS $$ BEGIN update message
set t_updated = NULL WHERE entity_id = NEW.entity_id; RETURN NULL; END; $$
LANGUAGEplpgsql; CREATE CONSTRAINT TRIGGER index_email_1_t AFTER INSERT OR
UPDATE OFre_index ON message DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN
(NEW.t_updatedIS NULL) EXECUTE PROCEDURE index_email_1_tf(); CREATE CONSTRAINT
TRIGGERindex_email_2_t AFTER INSERT OR UPDATE OF re_index ON message -- NOT
DEFERREDFOR EACH ROW WHEN (NEW.t_updated IS NULL) EXECUTE PROCEDURE
index_email_2_tf(); CREATE CONSTRAINT TRIGGER index_email_3_t AFTER INSERT OR
UPDATE OFt_updated ON message DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN
(NEW.t_updated)EXECUTE PROCEDURE index_email_3_tf();
 
Hope this helps
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>