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

On 6 April 2016 at 18:32, Tobia Conforto <tobia.conforto@gruppo4.eu> wrote:

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?

-Tobia

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Are you continuously feeding data to the db via the app during the
transaction or working on data which is already in the database?
In the second case you can write a db side function which can do the work,
validate and commit or rollback at the end.

Regards,
Sándor

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

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.

-Tobia

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På onsdag 06. april 2016 kl. 19:00:05, skrev Tobia Conforto <
tobia.conforto@gruppo4.eu <mailto:tobia.conforto@gruppo4.eu>>:
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.
 
You are aware that your suggestion also will fire for each row?
 
Your function will also suffer from executing multiple times for the same row
if it's updated multiple times within the same transaction, which my suggestion
specifically avoids.
 
-- 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&gt;
<https://www.visena.com&gt;