PostgreSQL 递增表中行的值

Question

我目前正在 PHP 构建一个脚本，该脚本必须在完成其目的时更新统计信息。该脚本通过网络浏览器访问，并且根据流量，它可能会同时执行。我必须保证统计数据是正确的。

为了给您提供图片，假设我们有一个表：

CREATE TABLE statistics(
  user_id      integer NOT NULL,
  date         integer NOT NULL, -- for unix time
  stat1        integer NOT NULL DEFAULT 0,
  stat2        integer NOT NULL DEFAULT 0,
  stat3        integer NOT NULL DEFAULT 0  -- and so on...
);

-- Let's insert some testing data for a couple of users and days...
-- Day one
INSERT INTO statistics(1, 1303520820, 1, 1, 1);
INSERT INTO statistics(2, 1303520820, 1, 1, 1);
-- Day two
INSERT INTO statistics(1, 1303603200, 1, 1, 1);
INSERT INTO statistics(2, 1303603200, 1, 1, 1);
-- Day three
INSERT INTO statistics(1, 1303689600, 1, 1, 1);
INSERT INTO statistics(2, 1303689600, 1, 1, 1);

每天都会在表中插入一个新行，这样我们就可以获得每日、每周、每月、每年的统计数据。我必须确保每个 user_id 每天只插入一行。此外，每当执行 UPDATE 查询时，它都会相应地增加 stat1、stat2、stat3 列。

该脚本预计会有相当多的流量，我想弄清楚当脚本执行并且有几个实例同时工作时如何使事情正常工作。您认为哪种方法/技术最适合此类任务？

Answer 1

最简单的解决方案是添加唯一约束，

CREATE TABLE statistics(
  user_id      integer NOT NULL,
  date         integer NOT NULL, -- for unix time
  stat1        integer NOT NULL DEFAULT 0,
  stat2        integer NOT NULL DEFAULT 0,
  stat3        integer NOT NULL DEFAULT 0,  -- and so on...
  UNIQUE(user_id,date)
);

无论您采取什么其他措施，您都应该这样做。

Answer 2

正如其他人所说，您需要对 user_id 和 date 对进行唯一约束。

为了在复合键（user_id、date）不存在时不进行算术插入，并在复合键存在存在时进行算术更新，您需要编写一些代码。非正式地，这称为“更新插入”。有不止一种方法。

PosgreSQL 文档有一个使用异常处理实现此类需求的函数示例。函数的问题在于你不能强迫应用程序代码或数据库女孩每次都无一例外地使用它。

您可以（我认为）使用 suppress_redundant_updates_trigger()< /a>.触发器的优点是它们不会被应用程序代码或数据库管理员意外绕过。我自己没有使用过这种技术，所以我无法进一步评论。此触发器记录在此处。

您还可以使用用户定义的触发器处理更新插入逻辑< /a>.

Answer 3

您还可以为日期值添加 CHECK 以确保它是 1 天的倍数：

ALTER TABLE "statistics" ADD CONSTRAINT "1day_quantum" CHECK ("date" = ("date" / 86400)::INTEGER * 86400);

如果尝试插入错误的日期值，则会抛出异常。

如果日期字段类型为 TIMESTAMP 或 TIMESTAMPTZ，则 CHECK 会更复杂：

ALTER TABLE "statistics" ADD CONSTRAINT "1day_quantum" CHECK ("date" = TIMESTAMP 'epoch' + ((EXTRACT(EPOCH FROM "date") / 86400)::INTEGER * 86400) * INTERVAL '1 second');

通过更改 86400（秒数），您可以将约束调整为各种量程：例如 900 为 15 分钟。