På onsdag 06. april 2016 kl. 18:32:50, skrev Tobia Conforto < tobia.confo...@gruppo4.eu <mailto:tobia.confo...@gruppo4.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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
