当触发器被激活后，基于SELECT结果插入或更新PostgreSQL中的值。

Question

当触发器被激活后，基于SELECT结果插入或更新PostgreSQL中的值。

sqlpostgresqltriggersplpgsqlpostgresql-9.3

3

这是我的结构体（附带值）：

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 表中插入时），存储/更新评估总和（针对给定的用户），以便更新相应的 user_metrics 表。

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

我认为需要执行以下步骤：

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 如果不存在，创建用户度量的条目

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)

我认为我已经接近所需的内容，但不知道如何实现。特别是我不了解语法。

有任何想法吗？

注意：请不要给出"RTFM"的答案，我查了几个小时也没有找到除了一些基本示例以外的任何东西。

- Nitseg

谢谢。SELECT结果在更新/插入语句中的重用问题没有得到回答，但我已经得到了部分答案。我曾经在官方文档中看到过这个例子。这是否意味着我们可以在函数内部对表进行竞争条件？我认为postGreSQL在函数中透明地使用事务。 - Nitseg

你实际上可以将SELECT查询与UPDATE查询连接起来。请看这里页面底部：http://www.postgresql.org/docs/9.1/static/sql-update.html 。或者你也可以通过循环遍历结果来实现：http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING 。你说得对，整个函数将作为一个事务执行。 - sn00k4h

不，如果有两个事务尝试插入具有相同唯一键的行（我假设你会拥有这些键），其中之一将失败并抛出异常。在示例代码中，它会重试该函数，该函数将首先重新尝试UPDATE查询。为了确保您使用正确的更新值执行UPDATE，您必须确保您的SELECT查询在LOOP内部（或作为UPDATE查询的一部分）。 - sn00k4h

1

如果您将SELECT作为UPDATE查询的一部分，则无需担心执行显式锁定，因为隐式行级锁定已经为您完成，并且从SELECT获取的值将是在运行UPDATE时的“当前”值。 - sn00k4h

实际的表定义（在 psql 中使用\t tbl得到的内容）将非常有用，它展示了数据类型、约束以及其他所有信息。 - Erwin Brandstetter

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

首先，重新审视一下保持始终当前的物化视图是否带来了显著的性能提升的假设。这会增加很多开销，并使对user_eval_history的写入变得更加昂贵。该方法只有在写入操作很少而读取操作更为常见时才有意义。否则，考虑使用VIEW，尽管它对于读取操作来说更加昂贵，但是它始终是最新的。通过在user_eval_history上适当地创建索引，这可能总体上更便宜。

其次，考虑实际的MATERIALIZED VIEW（Postgres 9.3+）用于user_metrics，而不是手动更新它，特别是如果对user_eval_history的写入操作非常少。棘手的部分是何时刷新MV。 如果你处于中间状态，user_eval_history具有非平凡的大小，并且您需要user_metrics精确反映当前状态并接近实时，则你的方法是有道理的。

还在继续吗？好的。首先，您需要准确地定义什么是允许/可能的，以及什么是不允许/不可能的。可以删除user_eval_history中的行吗？用户在user_eval_history中的最后一行可以被删除吗？即使你会回答“否”，也很可能是可以的。可以更新user_eval_history中的行吗？可以更改user_id吗？可以更改is_good_eval吗？如果可以，您需要为每种情况做好准备。

假设简单情况：INSERT操作。没有UPDATE，没有DELETE。仍然存在可能的竞态条件，这是您与@sn00k4h讨论过的问题。您找到了一个解决方案，但那真的是针对INSERT或SELECT，而你面临的是传统的UPSERT问题：INSERT或UPDATE：

FOR UPDATE在评论中提到的并不是解决此问题的万能药。无论如何，UPDATE user_metrics ... 会锁定它更新的行。问题出在当两个INSERT同时为一个新的user_id创建一行时。在Postgres中，您无法锁定唯一索引中不存在的键值。因此，FOR 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 (
   <strike>user_metrics_id</strike> -- seems useless
 , user_id int PRIMARY KEY
 , nb_good_eval int NOT NULL DEFAULT 0
 , nb_bad_eval int NOT NULL DEFAULT 0
);

首先，您需要在创建触发器之前编写一个触发函数。

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自动保存触发行的值。在此处查看手册中的详细信息。

触发器：

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