Re: Implementing "thick"/"fat" databases

> > Now, if you are doing double-entry bookkeeping this doesn't provide
> > enough consistency, IMO. You can't check inside the function to
> > ensure that the transaction is balanced.
>
> One option to consider is restricting final tables but making staging
> tables available.

I would implement this using triggers.

First, add a boolean "is_balanced" column to the transaction table,
along with the following trigger:

create or replace function check_txn_balance()
returns trigger
language plpgsql
as $$
declare
_amt numeric;
begin
if 'UPDATE' = TG_OP and new.is_balanced then
return null;
end if;

select sum(amt)
into _amt
from line_item
where txn_id = new.id;

if _amt <> 0 then
raise exception 'unbalanced transaction';
end if;

update txn
set is_balanced = true
where id = new.id;

return null;
end;
$$;

create constraint trigger check_txn_balance
after insert or update on txn
deferrable initially deferred
for each row execute procedure check_txn_balance();

Then, whenever we add, remove, or update a line item, unbalance the
parent transaction, which triggers the balance check:

create or replace function unbalance_txn()
returns trigger
language plpgsql
as $$
begin
if 'UPDATE' = TG_OP then
if (new.txn_id, new.amt) = (old.txn_id, old.amt) then
return null;
end if;
end if;

if TG_OP in ('INSERT', 'UPDATE') then
update txn
set is_balanced = false
where (id, is_balanced) = (new.txn_id, true);
end if;

if TG_OP in ('DELETE', 'UPDATE') then
update txn
set is_balanced = false
where (id, is_balanced) = (old.txn_id, true);
end if;

return null;
end;
$$;

create trigger unbalance_txn
after insert or delete or update on line_item
for each row execute procedure unbalance_txn();

At least, this seems to be a fairly efficient and foolproof way to do it
to me.

Karl Nack

Futurity, Inc
5121 N Ravenswood Ave
Chicago, IL 60640
773-506-2007