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

På onsdag 06. april 2016 kl. 19:00:05, skrev Tobia Conforto <
tobia(dot)conforto(at)gruppo4(dot)eu <mailto:tobia(dot)conforto(at)gruppo4(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>