PostgreSQL - Experimenting Trigger Under Transaction

Introduction and Motivation

Hello, this blog post will be very light, it’s a small experiment I did to solidify my understanding of triggers under a transaction.

I was working on a piece of code that uses some helper functions, and I was getting a behavior that I wasn’t expecting.

My original guess was that all the trigger runs under the transactions would be done immediately and not after the COMMIT.

I am doing this small experiment to understand the behavior that happened to me when executing a trigger under a transaction within a code with helper methods.

Because this blog is light I will add some comments to make it useful for more advanced programmers hopefully.

Experiment

The idea of the experiement is simple, I will have 3 tables one for players, one for their score and one for each goal they achieve.

CREATE TABLE player (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE player_goals (
    id SERIAL PRIMARY KEY,
    player_id INT REFERENCES player(id),
    points INT
);

CREATE TABLE player_score (
    id SERIAL PRIMARY KEY,
    player_id INT REFERENCES player(id),
    score INT
);

Maybe you are wondering why I am splitting this into 3 tables why not put everything in the player table and directly update the score for the player in one table? When expecting a high load of updates the system will slow down because an update operation will lock the row, causing slowing down to the system, while inserts don't!

after that I will create a trigger, when we create a player we will create a field in score table to keep track of this player score.

CREATE OR REPLACE FUNCTION insert_player_score()
RETURNS TRIGGER AS
$$
BEGIN
    INSERT INTO player_score (player_id, score)
    VALUES (NEW.id, 0);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER after_insert_player
AFTER INSERT ON player
FOR EACH ROW
EXECUTE FUNCTION insert_player_score();

and I will insert a new player to the table to run experiment on.

INSERT INTO player (name) VALUES ('Kanna Kamui');

now I will create a trigger each time a player achieve a goal we will add this to their score.

CREATE OR REPLACE FUNCTION update_player_score()
RETURNS TRIGGER AS
$$
BEGIN
    UPDATE player_score
    SET score = score + NEW.points
    WHERE player_id = NEW.player_id;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER after_insert_goal
AFTER INSERT ON player_goals
FOR EACH ROW
EXECUTE FUNCTION update_player_score();

Time to test our trigger! let’s insert a new record

INSERT INTO player_goals (player_id, points) VALUES (1, 10);

if I select the score now

SELECT player_id, score
FROM player_score
WHERE player_id = 1;

we can see that it’s updated.

okay, now let’s start a new insertion under a transaction without COMMIT or ROLLBACK

BEGIN;
INSERT INTO player_goals (player_id, points) VALUES (1, 20);

running the select score again under same session we run the transaction in.

SELECT player_id, score
FROM player_score
WHERE player_id = 1;

we can see that right away the score have been updated under the transaction. All logical and like what it is expected.

before committing or rollback, let’s open a new session and execute the same select statement.

We can see when we are in a new session the change is not reflected which is totally expected, postgres implements serializable isolation¹ when doing transactions. the change will only be visible after doing a COMMIT.

on ROLLBACK the entire effect of the trigger will be also droped so the score will not be changed.

Conclusion

Okay so all logical until now, but what I was getting in my code was totally different than this which made me write this blog post.

Actually, the helper method I was using was opening a new session, which made next select don’t see the changes of my trigger!

I hope this small blog post teaches you something either by the experiment itself or Kanna’s nuggets.