触发器被击中后，PostgreSQL的INSERT或UPDATE值给出了SELECT结果 [英] PostgreSQL INSERT or UPDATE values given a SELECT result after a trigger has been hit

问题描述

这是我的结构(带有值):

Here is my structure (with values):

user_eval_history table

 user_eval_id | user_id | is_good_eval                             
--------------+---------+--------------
            1 |       1 |            t
            2 |       1 |            t
            3 |       1 |            f
            4 |       2 |            t

user_metrics table

 user_metrics_id | user_id | nb_good_eval | nb_bad_eval
-----------------+---------+--------------+-------------
               1 |       1 |            2 |           1
               2 |       2 |            1 |           0

出于访问时间(性能)的原因，我希望避免一次又一次地从历史记录中重新计算用户评估.我想每次给用户一个新评估时都存储/更新(对于给定用户)评估总和(意味着每次要在user_eval_history表中有一个INSERT时，我都要为相应的user_id更新user_metrics表).

For access time (performance) reasons I want to avoid recomputing user evaluation from the history again and again. I would like to store/update the sums of evaluations (for a given user) everytime a new evaluation is given to the user (meaning everytime there is an INSERT in the user_eval_history table I want to update the user_metrics table for the corresponding user_id).

我觉得我可以通过触发器和存储过程来实现，但是我找不到正确的语法.

I feel like I can achieve this with a trigger and a stored procedure but I'm not able to find the correct syntax for this.

我认为我需要执行以下操作:

I think I need to do what follows:

1.根据用户指标创建触发器:

CREATE TRIGGER update_user_metrics_trigger AFTER INSERT
    ON user_eval_history
        FOR EACH ROW
    EXECUTE PROCEDURE update_user_metrics('user_id');

2.创建一个存储过程update_user_metrics

2.1从user_eval_history表中为user_id计算指标

SELECT 
  user_id,
  SUM( CASE WHEN is_good_eval='t' THEN 1 ELSE 0) as nb_good_eval,
  SUM( CASE WHEN is_good_eval='f' THEN 1 ELSE 0) as nb_bad_eval
FROM user_eval_history
WHERE user_id = 'user_id' -- don't know the syntax here

2.2.1创建尚不存在的user_metrics条目

INSERT INTO user_metrics
  (user_id, nb_good_eval, nb_bad_eval) VALUES
  (user_id, nb_good_eval, nb_bad_eval) -- Syntax?????

2.2.2更新user_metrics条目(如果已存在)

UPDATE user_metrics SET
  (user_id, nb_good_eval, nb_bad_eval) = (user_id, nb_good_eval, nb_bad_eval)

我认为我已经接近需要的东西，但是不知道如何实现.特别是我不了解语法.

I think I'm close to what is needed but don't know how to achieve this. Especially I don't know about the syntax.

有什么主意吗?

注意:请，没有"RTFM"答案，我查了几个小时，除了琐碎的例子外什么都没找到.

Note: Please, no "RTFM" answers, I looked up for hours and didn't find anything but trivial examples.

推荐答案

首先，请重新考虑以下假设，即始终保持最新的实例化视图可显着提高性能.您增加了很多开销，并使对 user_eval_history 的写入更加昂贵.这种方法仅在写入稀有而读取更为常见的情况下才有意义.否则，请考虑 VIEW > ，这对于读取来说比较昂贵，但始终是最新的.在 user_eval_history 上使用适当的索引，总体上来说可能会更便宜.

First, revisit the assumption that maintaining an always current materialized view is a significant performance gain. You add a lot of overhead and make writes to user_eval_history a lot more expensive. The approach only makes sense if writes are rare while reads are more common. Else, consider a VIEW instead, which is more expensive for reads, but always current. With appropriate indexes on user_eval_history this may be cheaper overall.

接下来，考虑一个实际的 MATERIALIZED VIEW user_metrics 的code> (Postgres 9.3+)，而不是手动保持最新状态，尤其是对 user_eval_history 的写操作为时非常罕见.棘手的部分是何时刷新MV.

Next, consider an actual MATERIALIZED VIEW (Postgres 9.3+) for user_metrics instead of keeping it up to date manually, especially if write operations to user_eval_history are very rare. The tricky part is when to refresh the MV.

您的方法很有意义， user_eval_history 的大小不重要，您需要 user_metrics 来反映当前情况准确且接近实时地陈述.

Your approach makes sense if you are somewhere in between, user_eval_history has a non-trivial size and you need user_metrics to reflect the current state exactly and close to real-time.

还在船上吗?好的.首先，您需要定义 完全 允许/可能的和不允许的.是否可以删除 user_eval_history 中的行?是否可以删除 user_eval_history 中用户的最后一行?即使您回答否"，可能也是.是否可以更新 user_eval_history 中的行?可以更改 user_id 吗?可以更改 is_good_eval 吗?如果是，则需要为每种情况做好准备.

Still on board? OK. First you need to define exactly what's allowed / possible and what's not. Can rows in user_eval_history be deleted? Can the last row of a user in user_eval_history be deleted? Probably yes, even if you would answer "No". Can rows in user_eval_history be updated? Can user_id be changed? Can is_good_eval be changed? If yes, you need to prepare for each of these cases.

假设情况很简单:仅 INSERT .没有 UPDATE ，没有 DELETE .您一直在使用@ sn00k4h讨论可能的竞赛条件.您找到了答案，但这确实是 INSERT或SELECT 的目的，经典的 UPSERT 问题: INSERT或UPDATE :

Assuming the trivial case: INSERT only. No UPDATE, no DELETE. There is still the possible race condition you have been discussing with @sn00k4h. You found an answer to that, but that's really for INSERT or SELECT, while you have a classical UPSERT problem: INSERT or UPDATE:

FOR UPDATE 是这里的银弹. UPDATE user_metrics ... 锁定无论如何更新的行.有问题的情况是，两个 INSERT 尝试同时为新的 user_id 创建一行.您不能锁定在Postgres中唯一索引中不存在的键值. FOR UPDATE 无济于事.您需要为可能的独特违规做准备，然后按这些链接的答案中所述进行重试:

FOR UPDATE like you considered in the comments is not the silver bullet here. UPDATE user_metrics ... locks the row it updates anyway. The problematic case is when two INSERTs try to create a row for a new user_id concurrently. You cannot lock key values that are not present in the unique index, yet, in Postgres. FOR UPDATE can't help. You need to prepare for a possible unique violation and retry as discussed in these linked answers:

• 通过交易进行增补
• 如何在PostgreSQL中进行UPSERT(MERGE，INSERT ... ON DUPLICATE UPDATE)的操作?

假设这些表定义:

CREATE TABLE user_eval_history (
   user_eval_id serial PRIMARY KEY
 , user_id int NOT NULL
 , is_good_eval boolean NOT NULL
);

CREATE TABLE user_metrics (
   user_metrics_id -- seems useless
 , user_id int PRIMARY KEY
 , nb_good_eval int NOT NULL DEFAULT 0
 , nb_bad_eval int NOT NULL DEFAULT 0
);

首先，您需要先使用触发功能，然后才能创建触发器.

First, you need a trigger function before you can create a trigger.

CREATE OR REPLACE FUNCTION trg_user_eval_history_upaft()
   RETURNS trigger
   LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
   IF NEW.is_good_eval THEN
      UPDATE user_metrics
      SET    nb_good_eval = nb_good_eval + 1
      WHERE  user_id = NEW.user_id;
   ELSE
      UPDATE user_metrics
      SET    nb_bad_eval = nb_bad_eval + 1
      WHERE  user_id = NEW.user_id;
   END IF;
   EXIT WHEN FOUND;

   BEGIN  -- enter block with exception handling
      IF NEW.is_good_eval THEN
         INSERT INTO user_metrics (user_id, nb_good_eval)
         VALUES (NEW.user_id, 1);
      ELSE
         INSERT INTO user_metrics (user_id, nb_bad_eval)
         VALUES (NEW.user_id, 1);
      END IF;
      RETURN NULL;  -- returns from function, NULL for AFTER trigger

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- user_metrics.user_id is UNIQUE
      RAISE NOTICE 'It actually happened!'; -- hardly ever happens
   END;    
END LOOP;
RETURN NULL;  -- NULL for AFTER trigger
END
$func$;

尤其是，您不会将 user_id 作为参数传递给触发函数.特殊变量 NEW 自动保存触发行的值.此处的手册中的详细信息.

In particular, you don't pass user_id as parameter to the trigger function. The special variable NEW holds values of the triggering row automatically. Details in the manual here.

触发:

CREATE TRIGGER upaft_update_user_metrics
AFTER INSERT ON user_eval_history
FOR EACH ROW EXECUTE PROCEDURE trg_user_eval_history_upaft();

这篇关于触发器被击中后，PostgreSQL的INSERT或UPDATE值给出了SELECT结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！