php - 使用数据库触发器生成无间隙数字

Question

我正在与我的团队一起开发一项生成发票编号的功能。要求说:

• 发票号之间不能有空缺
• 每年的数字应该从 0 开始(连同年份我们将有一个唯一的键)
• 发票编号应根据发票创建时间增长

我们正在使用 php 和 postgres。我们 tought 通过以下方式实现这一点:

• 每次在数据库中保存新发票时，我们都会使用 BEFORE INSERT 触发器
• 触发器执行一个函数，该函数从 postgres 序列中检索一个新值并将其作为编号写入发票

考虑到在同一笔交易中可能会创建多张发票，我的问题是:这是一种足够安全的方法吗？它的缺陷是什么？您建议如何改进它？

Answer 1

介绍

我认为这里最关键的一点是:

• there should be no gaps between invoice numbers

在这种情况下，您不能使用序列和自动递增字段(正如其他人在评论中建议的那样)。自动递增字段在后台使用序列，无论交易成功还是失败，nextval(regclass) 函数都会递增序列的计数器(您自己指出)。

---

更新:

我的意思是您根本不应该使用序列，尤其是您提出的解决方案并没有消除间隙的可能性。您的触发器获得新的序列值，但 INSERT 仍可能失败。

---

序列以这种方式工作，因为它们主要用于 PRIMARY KEYs 和 OIDs 值生成，其中唯一性和非阻塞机制是最终目标，值之间的差距是真的没什么大不了的。

然而，在您的情况下，优先级可能不同，但有几件事需要考虑。

简单的解决方案

您的问题的第一个可能解决方案是返回新数字作为当前现有数字的最大值。它可以在你的触发器中完成:

NEW.invoice_number =
        (SELECT foo.invoice_number
         FROM invoices foo
         WHERE foo._year = NEW._year
         ORDER BY foo.invoice_number DESC NULLS LAST LIMIT 1
        ); /*query 1*/

这个查询可以使用你的复合 UNIQUE INDEX 如果它是用“正确”的语法和列顺序创建的，首先是“year”列，例如:

CREATE UNIQUE INDEX invoice_number_unique
ON invoices (_year, invoice_number DESC NULLS LAST);

在 PostgreSQL 中，UNIQUE CONSTRAINTs 被简单地实现为 UNIQUE INDEXes，因此大多数时候您将使用哪个命令没有区别。但是，使用上面介绍的特定语法可以定义该索引的顺序。这是一个非常好的技巧，如果发票表变大，它使 /*query 1*/ 比简单的 SELECT max(invoice_number) FROM invoices WHERE _year = NEW.year 更快。​​

这是一个简单的解决方案，但有一个很大的缺点。当两个事务尝试同时插入发票时，可能会出现竞争条件。两者都可以获得相同的最大值，UNIQUE CONSTRAINT 将阻止第二个提交。尽管如此，在一些具有特殊插入策略的小型系统中它可能就足够了。

更好的解决方案

你可以创建表

CREATE TABLE invoice_numbers(
   _year INTEGER NOT NULL PRIMARY KEY,
   next_number_within_year INTEGER
);

存储特定年份下一个可能的数字。然后，在 AFTER INSERT 触发器中，您可以:

1. 锁定 invoice_numbers，其他交易甚至无法读取该号码 LOCK TABLE invoice_numbers IN ACCESS EXCLUSIVE;
2. 获取新发票编号 new_invoice_number = (SELECT foo.next_number_within_year FROM invoice_numbers foo where foo._year = NEW.year);
3. 更新新添加的发票行的数值
4. 递增 UPDATE invoice_numbers SET next_number_within_year = next_number_within_year + 1 WHERE _year = NEW._year;

因为表锁在事务提交之前持有，这可能应该是最后触发的触发器(read more about trigger execution order here)

---

更新:

不用LOCK命令检查link而不是锁定整个表由 Craig Ringer 提供

---

这种情况下的缺点是INSERT操作性能下降---当时只有一个事务可以执行插入。