Re: Multi-row constraints, how to avoid unnecessary trigger execution? - Mailing list pgsql-general
På onsdag 06. april 2016 kl. 18:32:50, skrev Tobia Conforto <tobia.conforto@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 once for 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 FUNCTION index_email_3_tf() returns TRIGGER AS $$
BEGIN
update message set t_updated = NULL WHERE entity_id = NEW.entity_id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER index_email_1_t
AFTER INSERT OR UPDATE OF re_index ON message DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
WHEN (NEW.t_updated IS NULL)
EXECUTE PROCEDURE index_email_1_tf();
CREATE CONSTRAINT TRIGGER index_email_2_t
AFTER INSERT OR UPDATE OF re_index ON message -- NOT DEFERRED
FOR 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 OF t_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
Attachment
pgsql-general by date: