Coding的痕迹

一位互联网奔跑者的网上日记

0%

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

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

DB-screenshot

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

项目背景和工作流程

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

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

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

捉 bug 过程

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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;
$$;

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

1
SELECT * FROM pg_trigger;

然而一无所获。

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

1
2
3
room		balance
101101 10.00
101101 10.00

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

总结

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

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

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

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

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