how to cause a trigger to calculate balances in an accounting software using postgres

Question

Following to my earlier question that was answered, I would like to know how to do the similar balances calculations using postgres triggers.

how to calculate balances in an accounting software using postgres window function

ID   Date         In       Out    Balance
1    1/1       100.00    0.00   100.00
2    2/1        10.00    0.00   110.00
3    3/1         0.00   70.00    40.00
4    5/1         5.00    0.00    45.00
5    6/1         0.00   60.00   -15.00

Now I need a "trigger" which gives me the following result:

ID   Date         In       Out    Balance
1    1/1        100.00    0.00   100.00
2    2/1         10.00    0.00   110.00
3    3/1          0.00   70.00    40.00
6    4/1         20.00    0.00    60.00  <--- inserted new row
4    5/1          5.00    0.00    65.00
5    6/1          0.00   60.00     5.00

How do I create a trigger in postgres to update balances of "successive" rows (dont update the older rows) ?

read [this](http://www.day32.com/MySQL/Meetup/Presentations/postgresql_stored_procedures.pdf) — Vivek S., Aug 04 '14 at 06:42
@dude ... why? What's relevant there? Details please. Yes, this is a bit of a "do it for me" question but I'm not sure that link is helpful. — Craig Ringer, Aug 04 '14 at 09:03
@CraigRinger [this](http://www.day32.com/MySQL/Meetup/Presentations/postgresql_stored_procedures.pdf) pdf file nicely exaplined about `Triggers` with understandable example as you said the question is `"do it for me"` that will helps him to under stand triggers so that he can easly catch when an answer for his question is avaialble — Vivek S., Aug 04 '14 at 09:24

score 5 · Accepted Answer · answered Aug 04 '14 at 09:19

This is actually harder than it looks.

More than one person might concurrently be inserting a row, and their commit orders might not be the same as the order in which the rows are created. That means that a trigger calculating the balance won't see rows it should and the balance will be wrong.

To make this work reliably, you must LOCK TABLE ... IN EXCLUSIVE MODE before doing an INSERT, or you must use SERIALIZABLE isolation in 9.2 or newer. Both introduce the need to re-try failed transactions:

SERIALIZABLE will abort a problematic transaction and force you to re-try it; and
Taking a lock in a trigger involves a lock upgrade, which can deadlock, causing the transaction to be aborted.

so your app must be prepared to re-try failed transactions.

To avoid this you can use READ COMMITTED isolation and have the app explicitly LOCK TABLE ... IN EXCLUSIVE MODE the table of interest before it tries to do anything else with it, even SELECT from it, if it thinks it might later need to INSERT a row into it. However, concurrency will then suck somewhat.

So, given that, you'd write something like:

CREATE OR REPLACE FUNCTION balance_insert_trigger() 
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN 
    -- This can deadlock unless you already took the lock earlier
    -- in the transaction; we put it here only for safety to make
    -- sure to block concurrent inserts.
    LOCK TABLE mytable IN EXCLUSIVE MODE;

    IF tg_op = 'INSERT' THEN
        NEW.balance = (SELECT sum(in) - sum(out) FROM mytable);
        RETURN NEW
    ELSE
        -- It's not safe to DELETE FROM or UPDATE this table because of the
        -- running balance.
        RAISE EXCEPTION '% operation not permitted on this table', tg_op;
    END IF;
END;
$$;

CREATE TRIGGER balance_insert_trigger
BEFORE INSERT OR UPDATE OR DELETE ON mytable 
FOR EACH ROW EXECUTE PROCEDURE balance_insert_trigger();

then always:

BEGIN;
LOCK TABLE mytable IN EXCLUSIVE MODE;
INSERT INTO mytable ...;
COMMIT;

If you want to support UPDATE and DELETE too, then things get exciting.

wow, that looks very elaborate. Just mark in Answared first and try it later. Thanks a lot guys. — Axil, Aug 04 '14 at 14:33

how to cause a trigger to calculate balances in an accounting software using postgres

1 Answers1