I have a question on how to elaborate a schema in PostgreSQL which involves:
- A trigger function used to keep a certain consistency calculation in a table;
- A foreign key whose value in the original table can change, but we want to avoid unnecessary calculations in that case (that is, we want the change to cascade without recalculating everything somehow).
This is better explained with an example. Consider the following:
-- Contains the ids.
create table keys (
id int4 not null,
constraint keys_pk primary key (id)
);
-- Contain values associated with an id.
create table values (
id int4 not null,
value int4 not null,
constraint values_fk foreign key (id) references keys(id) on update cascade
);
-- Keeps track of the sum of all values associated with an id.
create table totals (
id int4 not null,
total int4 not null,
constraint totals_pk primary key (id)
);
The goal here is to have totals contain the sum of all values for every key. We do this by using a trigger such as:
create function calc_totals()
returns trigger language plpgsql as $$
begin
-- subtract old value
if not old is null then
update totals set total = totals.total - old.value where id = old.id;
end if;
-- insert/add new value
if not new is null then
insert into totals values (new.id, new.value) on conflict (id) do update
set total = totals.total + new.value;
end if;
-- return
if not new is null then return new; else return old; end if;
end $$;
create or replace trigger calc_totals_trigger
before insert or update or delete on values
for each row execute function calc_totals();
With the above, editing values in any way (inserting, updating, deleting) will trigger calc_totals() and the totals table will be kept consistent.
Now, if for whatever reason we allow id in keys to change (that is, we want to "rename" an entity, and preserve all foreign key associations in other tables), the above will still work; however the database will recalculate the sums for the entire subset of rows which has its id changed in values (and leave a row with a total sum of zero for the previous id).
I would like to do better: somehow make the change in id in keys propagate to totals as well.
The most natural way would be add a foreign key with on cascade update into totals. This will not work, however, because although the update on id will cascade to totals (effectively renaming the row as intended), the trigger will still fire, causing a recalculation and yielding errors because id on totals have changed before the trigger was run.
Given that changing IDs is a bad idea in general but we still want to do it anyway in this case, is it possible to design this in a better way?
