Run trigger after transaction, not on each row - PostgreSQL 9.4

Question

I have this trigger to ensure that the total sum of all votes does not pass 100:

CREATE FUNCTION limit_votes() RETURNS TRIGGER AS $limit_votes$
    DECLARE
        votes_total int;
    BEGIN
        votes_total := NEW.vote + (SELECT SUM(vote) FROM cars WHERE portfolio_id = NEW.portfolio_id AND car != NEW.car);
        IF votes_total > 100 THEN
            RAISE EXCEPTION 'votes_limit_exceeded';
        END IF;
        RETURN NEW;
    END;
$limit_votes$ LANGUAGE plpgsql;
CREATE TRIGGER limit_votes BEFORE INSERT OR UPDATE ON cars FOR EACH ROW EXECUTE PROCEDURE limit_votes();

It should be mentioned that a "vote" can be updated, so it's not only INSERTs. This is where my problem occur, as one user could change the votes between cars and then the trigger gets issued as an update on a single row could exceed the limit off 100, before the other updates decrease the other votes.

I hope it make sense.

I would like the trigger to be called AFTER a BEGIN ... COMMIT, so I can updates multiple rows before checking everything is in order - but also raise an exception and not save the result if a user tries to update the votes to more than 100.

Answer 1

According to https://www.postgresql.org/docs/13/sql-createtrigger.html

Since this should be a deferrable constraint and you want to trigger the procedure execution after the transaction you need the following,

INITIALLY DEFERRED

CREATE CONSTRAINT TRIGGER limit_votes
AFTER INSERT OR UPDATE ON cars
DEFERRABLE
INITIALLY DEFERRED 
FOR EACH ROW EXECUTE PROCEDURE limit_votes();

INITIALLY IMMEDIATE

If we have wanted to trigger the procedure execution after each statement.

CREATE CONSTRAINT TRIGGER limit_votes
AFTER INSERT OR UPDATE ON cars
DEFERRABLE
INITIALLY IMMEDIATE 
FOR EACH ROW EXECUTE PROCEDURE limit_votes();