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

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
betweenone 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
recordsand 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
pertransaction, 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
secondand following ones? I could do that by creating a temporary table on commit drop, but it seems overkill. Is there
alighter solution? 

-Tobia

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?

-- 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();

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Sándor,
I'd rather have the application developers use regular DML, which could become quite complex, and just perform my check
onthe database side, at transaction commit time. 

Andreas,
thanks, but I need to avoid duplicate executions on different rows too.

I just came up with this "hack" which seems to be working:

    create or replace function my_trigger() returns trigger as $$
    begin
        create temporary table my_trigger() on commit drop;

        -- perform expensive test here and raise error if it fails
        if ... then
            raise ...;
        end if;

        return null;
    exception when duplicate_table then
        -- already ran in the current transaction, skip test
        return null;
    end;
    $$ language 'plpgsql';

    create constraint trigger my_trigger after insert or update or delete on my_table
    initially deferred for each row execute procedure my_trigger();

Any improvement is welcome.

-Tobia

Sándor,
I'd rather have the application developers use regular DML, which could become quite complex, and just perform my check on the database side, at transaction commit time.

Andreas,
thanks, but I need to avoid duplicate executions on different rows too.

I just came up with this "hack" which seems to be working:

create or replace function my_trigger() returns trigger as $$
begin
create temporary table my_trigger() on commit drop;

-- perform expensive test here and raise error if it fails
if ... then
raise ...;
end if;

return null;
exception when duplicate_table then
-- already ran in the current transaction, skip test
return null;
end;
$$ language 'plpgsql';

create constraint trigger my_trigger after insert or update or delete on my_table
initially deferred for each row execute procedure my_trigger();

Any improvement is welcome.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com