Search code examples
sqlpostgresqldatabase-designtriggers

PostgreSQL Trigger after update of a specific column

I'm on my way of exploring triggers and want to create one that fires after an Update event on a game_saved column. As I have read in PostgreSQL docs it is possible to create triggers for columns. The column contains boolean values so the user may either add game to his collection or remove it. So I want the trigger function to calculate the number of games set to TRUE in the game_saved column for a certain user. And then update total_game_count in a game_collection table.

game_collection

id - BIGSERIAL primary key
user_id - INTEGER REFERENCES users(id)
total_game_count - INTEGER

game_info

id - BIGSERIAL primary key
user_id - INTEGER REFERENCES users(id)
game_id - INTEGER REFERENCES games(id)
review - TEXT
game_saved - BOOLEAN

Here is my trigger (which is not working and I want to figure out why):

CREATE OR REPLACE FUNCTION total_games() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE game_collection 
SET total_game_count = (SELECT COUNT(CASE WHEN game_saved THEN 1 END)
                        FROM game_info WHERE game_collection.user_id = game_info.user_id)
WHERE user_id = NEW.user_id; 
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql; 

CREATE TRIGGER tr_total_games 
AFTER UPDATE OF game_saved FOR EACH ROW 
EXECUTE PROCEDURE total_games();

If I change AFTER UPDATE OF game_saved (column) to AFTER UPDATE ON game_info (table) the trigger works correctly. So there is some problem with creating a trigger specifically for a column update.

Is it a good idea to fire the trigger on the column update or should I look for another approach here?

Solution

  • The syntax would be (as documented in the manual):

    CREATE TRIGGER tr_total_games 
AFTER UPDATE OF game_saved ON game_info
FOR EACH ROW 
EXECUTE FUNCTION total_games();

    For Postgres 10 or older, use:

    ...
EXECUTE PROCEDURE total_games();

    See:

    But the whole approach is dubious. Keeping aggregates up to date with a trigger is prone to errors under concurrent write load. And without concurrent write load, there are simpler solutions: just add / subtract 1 from the current total ...

    A VIEW would be a reliable alternative. Remove the column game_collection.total_game_count altogether - and maybe the whole table game_collection, which does not seem to have any other purpose. Create a VIEW instead:

    CREATE VIEW v_game_collection AS
SELECT user_id, count(*) AS total_game_count
FROM   game_info
WHERE  game_saved
GROUP  BY user_id;

    This returns all users with at least 1 row in game_info where game_saved IS TRUE (and omits all others).

    For very big tables you might want a MATERIALIZED VIEW or related solutions to improve read performance. It's a trade-off between performance, storage / cache footprint, and being up to date.