postgresql - 如何在没有顺序的情况下在 PostgreSQL 中以事务方式插入 MAX(number) + 1 行

Question

我有一个可以简化为这样的 PostgreSQL 表:

CREATE TABLE car(
  garage_id uuid,
  number integer
);

我需要在每个车库中，每辆车都有一个唯一的整数编号(Car N°1，Car N°2)。

第一个有顺序的想法

我的第一个想法是通过触发器为每个车库创建一个 PostgreSQL 序列，然后当我插入汽车时，我只需要在为车库创建的序列上调用 nextval。 (就像看到了 here )

问题是我需要为每个车库创建一个序列。我的车库表很快就会长到 100 万行，根据这个线程:Maximum number of sequences that can be created ，过多的 PostgreSQL 序列确实会损害性能并使 pgdump 失败。

第二个想法

我发现了另一个想法 on stackoverflow .

第二个想法是创建另一个表，例如

CREATE TABLE garage_number(
  garage_id uuid,
  max_number integer
);

这张表会保留最大车数，然后在每次插入时我们选择车库的 max_number，并更新车库的 max_number。

问题: 目前它在并发方面不起作用(如果同时插入，2 辆汽车可能具有相同的编号)。

您是否知道我如何才能使其成为交易证明并大规模工作？也许锁定 garage_number 表？

非常感谢您的帮助，

Answer 1

“我如何才能使它成为交易证明并大规模工作”- 你做不到。最好的办法是尽量减少通过非序列方法生成数字时所需的锁定。

您的 garage_number 表可用于此目的，但我实际上不会在实际的车库表上使用 max()，而是像这样:

create or replace function next_car_number(p_garage uuid)
  returns integer
as
$$
  update garage_number
     set max_number = max_number + 1
  where garage_id = p_garage
  returning max_number;
$$
language sql;

当然，您需要初始化一次garage_number，并且每次创建一个新车库时，您还需要在该表中插入一个新行(值为0 为 max_number)

然后分配一个新的车号，你可以使用这样的东西:

insert into car 
  (garage, number)
values 
  ('a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11', next_car_number('a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11'));

该车库的行将被锁定，直到该插入被提交，并且直到那时其他使用相同功能的插入才能完成。一旦提交，任何等待的事务都将看到新值。

您需要在插入新车之前生成(并知道)车库的 UUID - 但我猜是因为引用了另一个名为 garage 的表通过car表，车库PK在插入新车时已经知道。

这仍然序列化访问单个车库的“生成器”，但会比为每个插入运行 max() 查询更快