更新特定列后的PostgreSQL触发器 [英] PostgreSQL Trigger after update of a specific column

问题描述

我正在探索触发器，并希望创建一个在 game_saved 列上的Update事件后触发的触发器.正如我在 PostgreSQL文档中阅读的那样，可以创建触发器用于列.该列包含 boolean 值，因此用户可以将游戏添加到其收藏夹中或将其删除.因此，我希望触发函数能够为特定用户计算 game_saved 列中设置为TRUE的游戏数量.然后在 game_collection 表中更新 total_game_count .

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

game_collection

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

game_info

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();

如果我将 game_saved的AFTER UPDATE更改为 game_info的AFTER UPDATE更改(列)(表)，则触发器可以正常工作.因此，专门为列更新创建触发器存在一些问题.

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?

推荐答案

语法为:

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

(如手册中所述.)

但是整个方法令人怀疑.在并发写入负载下，通过触发器使聚合保持最新状态很容易出错.

But the whole approach is dubious. Keeping aggregates up to date via trigger is prone to errors under concurrent write load.

并且没有并发写入负载，有更简单的解决方案:只需从当前总数中加/减1 ...

And without concurrent write load, there are simpler solutions: just add / subtract 1 from the current total ...

VIEW > 将是可靠的选择.完全删除列 game_collection.total_game_count -甚至整个表 game_collection ，这似乎没有其他用途.而是创建一个 VIEW :

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;

这将返回在 game_info 中至少具有1行的所有用户，其中 game_saved IS TRUE (并忽略所有其他用户).

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

对于非常大的表，您可能需要 MATERIALIZED VIEW 或相关解决方案来提高阅读性能.

For very big tables you might want a MATERIALIZED VIEW or related solutions to improve read performance.

这篇关于更新特定列后的PostgreSQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！