一个数据库设计引发的数据错误

中午收到同学转发的用户反馈，称小程序查到的近期寝室用电量值过高，想了下最近好像是有这样的情况。当时的情况是学校QQ群中好多同学吐槽电费高，我心里却担心是小程序的问题，于是登上数据库查看：

DB-screenshot

如图，这里限制了查询某一个寝室的情况。查询结果中，每个时间的记录出现了两条，这可能是导致电费统计结果高的直接原因，如果真是这样，那么程序中显示的统计值应该是实际值的两倍。令我不解的是，这多出来的一条数据从何而来？为什么时间也一模一样？

项目背景和工作流程

先说明一下背景：这是一个查电费余额和使用情况的小程序，我们在数据库中设计了三个表 balance 、 consumption 和 room 表。

balance 表：存储各个房间的余额信息。（简化后）有三个字段 room、balance、ts 用来保存寝室号、余额和时间。房间号 room_id 的编码方式和学校寝室编码规则相同，为一个 10 开头的整数，后面跟楼号再跟寝室号。如 10 号楼 201 为整数 1010201。
consumption 表：存储消费信息，包含更新时间戳ts、寝室号 room 和本次到上一次时间的余额差值 amount。取 amount 为正为充值， amount 为负为消费。由于爬虫大约每 30 分钟工作一次，所以可以大约计算出半小时内寝室的用电和充值情况。但是由于学校平台大约 15 分钟同步一次电费数据，因此该数据常常和实际值会有零点几元的差距。
room 表：由于学校电费接口中会返回一些他们测试用的寝室号，因此在 room 表中规定了合法的房间号，以过滤不用的寝室。

爬虫工作时，直接更新数据库的余额表（balance）数据，通过数据库触发器来更新消费表（consumption）。

捉 bug 过程

解决这类问题首先去查看爬虫的余额存档数据。找一个寝室信息，手动计算一段时间内的消费并查看其和程序计算结果是否一致。根据用户反馈的寝室号，验算后消费确实高出一倍。同时还发现，在按小时的电费统计中，我的 SQL 查询语句使用了 DISTINCT 关键字，而按天的统计中因为写法问题没有使用。因此按小时统计数据是正常的，而按天统计结果不对。

那么是什么原因导致了消费表中的数据重复呢？毫无头绪，因为对数据库触发器不太熟悉，先从触发器函数入手：

CREATE FUNCTION update_consumption_func() RETURNS trigger
    LANGUAGE plpgsql
AS
$$
BEGIN
        -- If the room has no record before, do INSERT.
        IF old IS NULL THEN
            INSERT INTO dormitory.consumption (room, amount)
                 VALUES (new.room, new.total_balance);
            RETURN NEW;
        END IF;

        -- Check whether it's changed or not.
        IF ABS(new.total_balance - old.total_balance) > 0.01 THEN
            INSERT INTO dormitory.consumption (room, amount)
                 VALUES (new.room, new.total_balance - old.total_balance);
            RETURN NEW;
        ELSE
            old.ts = current_timestamp; -- Set update time
            RETURN OLD;
        END IF;

    END;
$$;

每一个分支执行完后函数都正常结束了。再看看触发器是否创建重复：

SELECT * FROM pg_trigger;

然而一无所获。

这时，我打算创建一个测试寝室 0 用来检查问题。我手动更新了测试寝室的余额信息，而消费记录确确实实只有一条。之后我又通过执行 UPDATE 语句更新数据。这时，由于忘了写 WHERE 限制条件，导致所有数据被修改，而消费记录的“重影”再次出现。其实我本该想到的，由于 balance 中寝室信息出现两次，如：

room		balance
101101		10.00
101101		10.00

导致对每个寝室更新余额时，触发器会 FOR EACH ROW 地对每条记录执行一次，从而执行了两遍、在 consumption 中出现了两条相同的消费记录。这也从侧面可以看出，对于 event 为 BEFORE 类型的触发器，只有在所有触发器都执行完后数据库才会对数据进行写入。

总结

这次“运维事故”暴露的更深层次的问题是，为什么对 balance 中的 room 字段没有做唯一性约束？可能是：

我没注意到，忘了。
数据表曾经有过导入、导出和复制，可能在此过程中丢失。比如，DataGrip 的表复制（Copy Table to ..）功能就会丢失索引信息。

我觉得应该是第二种。这也反映出文档的重要性。在数据库设计阶段，建立对字段的约束，并在实施过程中严格执行，以确保数据的完整性。一旦表设计好，便不再轻易更改。不应该过分在数据库配置中依赖 IDE，这些都是为了确保表结构的稳定，并使得对约束的印象更加清晰。

这个问题是什么时候引入的已经无从考证，业务中竟一直显示的是错误的值而无人发现，这也为我们敲响警钟，多留心、多观察、积极思考异常情况。这件事到这里已经告一段落，通过加唯一索引后重新载入数据，现在服务已恢复正常。

后续，在有关群聊中说明了原因。个别依然对用电消费反映强烈的同学会与学校进行进一步的沟通。