PostgreSQL Trigger after update of a specific column

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-11-02 22:23:51Z

48

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:

Trigger uses a procedure or a function?

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.

edited Nov 2, 2023 at 22:23

answered Apr 23, 2017 at 14:39

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

4 Comments

samba Over a year ago

Thank you for the brilliant answer. It helped me to understand a better approach, learn about Views and incorporate them in my project.

Awesome-o Over a year ago

If triggers are executed within the same transaction as the update with default isolation level then what errors can occur under concurrent write load?

Aman Seth Over a year ago

How about invoking the trigger function on updating more than one column ? @erwin

Erwin Brandstetter Over a year ago

@sam Please ask your question as question (with details to make it clear). Comments are not the place.

Collectives™ on Stack Overflow

PostgreSQL Trigger after update of a specific column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related