Postgresql Trigger behaving weirdly

Question

I have a table:

-- TABLE --
DROP SEQUENCE IF EXISTS my_db.tbl_a_id_seq CASCADE;
DROP TABLE IF EXISTS my_db.tbl_a CASCADE;
CREATE SEQUENCE my_db.tbl_a_id_seq;
CREATE TABLE my_db.tbl_a
(
    id integer NOT NULL DEFAULT nextval('my_db.tbl_a_id_seq'::regclass),
    bill_date timestamp without time zone,
    bill_amt numeric(10,2),
    charge_id integer,
    sp_id integer,
    batch_id integer,
    bal_before numeric(10,2),
    bal_after numeric(10,2),
    CONSTRAINT tbl_a_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
);

DROP INDEX IF EXISTS idx_tbl_a_my_cols CASCADE;
CREATE INDEX idx_tbl_a_my_cols
    ON my_db.tbl_a USING btree
    (bill_date ASC NULLS LAST, bal_before ASC NULLS LAST);

I then populate the table above with two rows seed data so the table now looks as follows:

 id |         bill_date          | bill_amt | charge_id | sp_id | batch_id | bal_before | bal_after
----+----------------------------+----------+-----------+-------+----------+------------+-----------
  1 | 2020-04-13 11:21:26.51637  |    10.00 |         1 |     1 |        2 |     200.00 |    190.00
  2 | 2020-04-13 11:23:37.317907 |    10.00 |         1 |     1 |        2 |     190.00 |    180.00

I then created a trigger function and its trigger, which is supposed to insert bal_before values in the just inserted row from the immediate most previous row's bal_after value. Thereafter then populate just inserted row's bal_after column with the value computed by deducting jusst inserted row's bill_amt column value from same row's bal_before( this is the bal_before value that was computed from the LAG Postgresql window function). The said function and trigger are as follows:

-- TRIGGER FUNCTION --

SET search_path TO 'my_db';
CREATE OR REPLACE FUNCTION func_calc_balances() RETURNS TRIGGER AS
$$
BEGIN
    NEW.bal_before:= 
    (
        WITH filter_table as (
        SELECT id, bill_amt, bal_after
            FROM my_db.tbl_a
            WHERE charge_id = 1                                     
            AND sp_id = 1
            AND batch_id = 2
            ORDER BY id DESC LIMIT 2
        )

        SELECT 
        lag(bal_after, 1) over (order by id)
        FROM filter_table
        ORDER BY id DESC LIMIT 1
    );

    NEW.bal_after := (NEW.bal_before - NEW.bill_amt);

  RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

-- ABOVE FUNCTION'S TRIGGER --

DROP TRIGGER IF EXISTS trigger_calc_balances ON my_db.tbl_a CASCADE;
CREATE TRIGGER trigger_calc_balances
BEFORE INSERT OR UPDATE ON "tbl_a" FOR EACH ROW EXECUTE PROCEDURE func_calc_balances();

Upon inserting rows into the table, the trigger procedure is acting weirdly; at times it populates properly at times it repeats column values meaning it is not fetching the immediate most previous row to use it to populate the immediate next row. A sample of the weird output is as below:

Weird Behaviour on Ubuntu and Windows if I use a generate_series function to populate (alternating rows have null bal_before and bal_after values)

 id |         bill_date          | bill_amt | charge_id | sp_id | batch_id | bal_before | bal_after
----+----------------------------+----------+-----------+-------+----------+------------+-----------
  1 | 2020-04-13 11:21:26.51637  |    10.00 |         1 |     1 |        2 |     200.00 |    190.00
  2 | 2020-04-13 11:23:37.317907 |    10.00 |         1 |     1 |        2 |            |
  3 | 2020-04-13 11:52:11.326197 |    10.00 |         1 |     1 |        2 |     190.00 |    180.00
  4 | 2020-04-13 11:52:13.629896 |    10.00 |         1 |     1 |        2 |            |
  5 | 2020-04-13 11:52:14.977964 |    10.00 |         1 |     1 |        2 |     180.00 |    170.00
  6 | 2020-04-13 11:52:16.062277 |    10.00 |         1 |     1 |        2 |            |

Weird Behaviour on Windows if I populate a little slowly (values not updating in some bal_before)

id  |         bill_date          | bill_amt | charge_id | sp_id | batch_id  | bal_before | bal_after
----+----------------------------+----------+-----------+-------+-----------+------------+-----------
1   | 2020-04-13 11:28:09.667957 |  10.00   |   1       |   1   |   2       |   200.00   |  190.00
2   | 2020-04-13 11:29:09.667957 |  10.00   |   1       |   1   |   2       |   190.00   |  180.00
3   | 2020-04-13 11:30:09.667957 |  10.00   |   1       |   1   |   2       |   180.00   |  170.00
4   | 2020-04-13 11:42:32.655569 |  10.00   |   1       |   1   |   2       |   180.00   |  170.00
5   | 2020-04-13 11:42:43.739626 |  10.00   |   1       |   1   |   2       |   170.00   |  160.00
6   | 2020-04-13 11:42:44.788808 |  10.00   |   1       |   1   |   2       |   170.00   |  160.00

The desired output is as follows:

 id |         bill_date          | bill_amt | charge_id | sp_id | batch_id  | bal_before | bal_after
----+----------------------------+----------+-----------+-------+-----------+------------+-----------
1   | 2020-04-13 11:29:09.667957 |  10.00   |   1       |   1   |   2       |   200.00   |  190.00
2   | 2020-04-13 11:29:09.667957 |  10.00   |   1       |   1   |   2       |   190.00   |  180.00
3   | 2020-04-13 11:30:09.667957 |  10.00   |   1       |   1   |   2       |   180.00   |  170.00
4   | 2020-04-13 11:28:09.667957 |  10.00   |   1       |   1   |   2       |   170.00   |  160.00
5   | 2020-04-13 11:42:32.655569 |  10.00   |   1       |   1   |   2       |   160.00   |  150.00
6   | 2020-04-13 11:42:43.739626 |  10.00   |   1       |   1   |   2       |   150.00   |  140.00
7   | 2020-04-13 11:42:44.788808 |  10.00   |   1       |   1   |   2       |   140.00   |  130.00

I believe there must be an issue in my trigger function. When the function skips some rows (leaving nulls in the desired columns), the update is working properly sequentially, only that it is skipping some rows. Please assist.

Belayer · Accepted Answer · 2020-04-13 21:35:07Z

1

First a Caution. You are trying to maintain a running balance. This is generally a bad (and difficult ) idea. Much easier to compute the value on the fly when needed.

I could not reproduce the results you claim, nor anything close, with Postgres 12 on either Windows 10, nor Ubuntu 19.04. You may then need to create a Minimum, Reproducible Example.
However, that may not be necessary, at least yet. Your trigger has a fundamental flaw caused, I think, by an erroneous assumption. The trigger seems you are trying to compensate by getting the 2nd prior bal_after column. However, the row you are inserting does not exist yet. So you need to retrieve only the last row.
The revised trigger (below) will not work for update but then neither would have your original version. For update you need only to calculate the delta for the row being updated and apply that value. Then apply that same delta to all later rows (subject to charge, sp and batch ids). And this doesn't even consider delete or multiple users hitting the table at the same time. All of which you need to resolve. But if you still want to pursue the running balance the insert trigger function you need:

create or replace function func_calc_balances() 
  returns trigger 
  language plpgsql  
as $$
begin
    new.bal_before:= 
        (select bal_after
           from my_db_tbl_a
          where charge_id = 1                                     
            and sp_id = 1
            and batch_id = 2
          order by id desc limit 1
        );

    new.bal_after := (new.bal_before - new.bill_amt);

  return new;
END;
$$;

answered Apr 13, 2020 at 21:35

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mathiokore Over a year ago

Sure, the issue was the lag window function which was fetching 2 rows behind instead of 1 row, for which a normal select did it, similar to the above. The use case requires a running balance handled at the record insertion stage, so this pretty much suffices, thank you.

Collectives™ on Stack Overflow

Postgresql Trigger behaving weirdly

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related