有时得到不正确的喜欢和不喜欢的总数

Question

我在PostgresQL中有一个表feed_item_likes_dislikes（feed_item_id，user_id，vote），其中

• feed_item_id是uuid
• 用户id为整数
• 投票=正确那么这是一个类似
• 投票=FALSE那么这是一种厌恶
• vote=NULL表示用户最初喜欢或不喜欢，但通过取消通知返回并删除了投票

我还有另一个表feed_item_likes_dislikes_aggregate（feed_item_id，likes，dislikes），我想在其中维护每个帖子的喜欢和不喜欢的总数

当用户在feed_item_likes_dislikes表中添加新的喜欢

INSERT INTO feed_item_likes_dislikes VALUES('54d67b62-9b71-a6bc-d934-451c1eaae3bc', 1, TRUE);

我想更新聚合表中的赞总数。类似的情况也需要在不喜欢的情况下处理，当用户通过将投票设置为null来取消投票时

用户也可以更新他们的喜欢一个不喜欢反之亦然在每个条件喜欢和不喜欢的总数为该职位需要保持

我编写了以下触发器函数来实现这一点

CREATE OR REPLACE FUNCTION update_votes() RETURNS trigger AS $$
    DECLARE
        feed_item_id_val uuid;
        likes_val integer;
        dislikes_val integer;
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            -- when a row is deleted, store feed_item_id of the deleted row so that we can update its likes and dislikes count
            feed_item_id_val:=OLD.feed_item_id;
        ELSIF (TG_OP = 'UPDATE') OR (TG_OP='INSERT') THEN
            feed_item_id_val:=NEW.feed_item_id;
        END IF;
        -- get total number of likes and dislikes for the given feed_item_id
        SELECT COUNT(*) FILTER(WHERE vote=TRUE) AS likes, COUNT(*) FILTER(WHERE vote=FALSE) AS dislikes INTO likes_val, dislikes_val FROM feed_item_likes_dislikes WHERE feed_item_id=feed_item_id_val;
        -- update the aggregate count for only this feed_item_id
        INSERT INTO feed_item_likes_dislikes_aggregate (feed_item_id, likes, dislikes) VALUES (feed_item_id_val, likes_val, dislikes_val) ON CONFLICT(feed_item_id) DO UPDATE SET likes=likes_val, dislikes=dislikes_val;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_votes_trigger AFTER INSERT OR UPDATE OR DELETE ON feed_item_likes_dislikes FOR EACH ROW EXECUTE PROCEDURE update_votes();

但当我在feed_item_likes_dislikes中大量插入时，有时喜欢和不喜欢的总数是不正确的。

有人能告诉我怎么解决这个问题吗？

更新1

我试着创建一个视图，但在我的生产数据集上需要花费很多时间，这里是db fiddlehttps://www.db-fiddle.com/f/2ZAkjQhUydMaV9o5xvLgMT/17

问题#1

EXPLAIN ANALYZE SELECT f.feed_item_id,pubdate,link,guid,title,summary,author,feed_id,COALESCE(likes, 0) AS likes,COALESCE(dislikes, 0) AS dislikes,COALESCE(bullish, 0) AS bullish,COALESCE(bearish, 0) AS bearish FROM feed_items f LEFT JOIN likes_dislikes_aggregate l ON f.feed_item_id = l.feed_item_id LEFT JOIN bullish_bearish_aggregate b ON f.feed_item_id = b.feed_item_id  ORDER BY pubdate DESC, f.feed_item_id DESC LIMIT 10;

查看DB Fiddle

Answer 1

保持聚合运行的尝试总是充满陷阱，几乎总是不值得付出努力。解决方案是不尝试存储聚合，而是根据需要派生它们。通过创建视图而不是表来实现这一点。然后，这将删除所有附加处理，尤其是在本例中，因为触发器基本上包含生成视图所需的查询。（见这里的演示）

create or replace VIEW likes_dislikes_aggregate as 
   select id
        , count(*) filter(where vote) as likes
        , count(*) filter(where not vote) as dislikes   
        , count(*) filter(where vote is null) as no_vote
     from likes_dislikes
    group by id;

没有触发器，没有额外的代码，通过标准DML查看所有内容。请注意，实体视图基本上只是计数查询，没有触发开销和维护。

SELECT COUNT(*) FILTER(WHERE vote=TRUE) AS likes, COUNT(*) FILTER(WHERE vote=FALSE) AS dislikes INTO likes_val, dislikes_val FROM feed_item_likes_dislikes WHERE feed_item_id=feed_item_id_val;