插入每个分区的无间隙序列号

我有一个带有触发器的表，该触发器设置每个客户的订单号。我经常在这个表中遇到唯一的冲突错误:

SQLSTATE[23505]: Unique violation: 7
ERROR: duplicate key value violates unique constraint "order_customer_unique"

触发器定义为BEFORE INSERT ON orders FOR EACH ROW。

有什么技术可以解决表中的并发性问题吗?
理想情况下，每个客户的号码是一个序列，并且不会丢失号码。

CREATE OR REPLACE FUNCTION "public"."trigger_set_order_customer_number"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
BEGIN
NEW.sequence = (
SELECT COALESCE( MAX ( order.sequence), 0 ) + 1
FROM orders
WHERE orders.customer_type = NEW.customer_type
AND orders.customer_id = NEW.customer_id
AND orders.model_id = NEW.model_id LIMIT 1);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100

在继续之前，请考虑而不是这样做。在并发写负载下，每个分区的无间隙序列号有许多含义，并且可能成为一项代价高昂的工作。很大程度上取决于您的设置和需求的细节。看到:

复合键的每组行序列号

对于并发写，您拥有的触发器本质上是不安全的。冲突是预料之中的，因为每个事务都看到它自己的表快照，以及计算出来的"下一个数字"。对于多个并发插入，结果可能相同。

您可以升级到SERIALIZABLE事务隔离。但这是昂贵的，而且您必须为序列化失败做好准备，就像您现在必须为唯一的违例做好准备一样。赶上22。

更便宜的方法是添加父表每个分区只有一行(每个"唯一客户帐户")。然后可以在那里锁定一行，以便每次只能由单个事务操作每个唯一的客户帐户。这通常是一条可行的路径，而且性能更好。但这要看情况……相关:

获取在并发事务中执行的INSERT的不同行集

同时，我们可以将order_count放入父表中，以使一切更简单，更便宜。可以像这样:

CREATE TABLE unique_customer_account(
customer_id   int  -- replace with actual data types
, customer_type int
, model_id      int
, order_count   int NOT NULL DEFAULT 0
, PRIMARY KEY (customer_id, customer_type, model_id)
);
CREATE OR REPLACE FUNCTION public.trigger_set_order_customer_number()
RETURNS trigger
LANGUAGE plpgsql AS
$func$
BEGIN
-- this locks the row and makes concurrent transactions targeting
-- the same unique_customer_account wait till this one is done
INSERT INTO unique_customer_account AS u
(    customer_type,     customer_id,     model_id, order_count)
VALUES (NEW.customer_type, NEW.customer_id, NEW.model_id, 1)
ON     CONFLICT (customer_type, customer_id, model_id) DO UPDATE  -- ①
SET    order_count = u.order_count + 1    -- ②
RETURNING u.order_count
INTO   NEW.sequence;  -- ③
IF NOT FOUND THEN
RAISE EXCEPTION 'Row in "unique_customer_account" not found!'; -- ④
END IF;
RETURN NEW;
END
$func$;
CREATE TRIGGER orders_insbef
BEFORE INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION public.trigger_set_order_customer_number();

db<此处小提琴>

具有并发插入的快速本地测试也像宣传的那样工作。

①UPDATE隐式地对行执行排他锁，与SELECT FOR UPDATE相同。

②没有COALESCE，因为order_count被定义为NOT NULL。

③可以方便地将增量计数器直接赋值给NEW.sequence。

④unique_customer_account中的行必须存在。我在扳机里装了UPSERT。或者你们可以分开处理。无论哪种方式，如果多个并发事务希望同时在unique_customer_account中输入新行，则会出现另一种(不太常见的)竞争条件，这可能导致UPSERT中没有行。可以解决。看到:

如何使用返回与ON冲突在PostgreSQL?

下一个问题的一个简单而干净的解决方案是在插入任何订单之前，在单独的命令中INSERT ... ON CONFLICT DO NOTHINGunique_customer_account中的新行。可以在同一个事务中，但不能在同一个命令的CTE中。

那么您可以假设unique_customer_account中的行存在，并在触发器中简化为普通的UPDATE。所有的比赛条件都消失了。(除了外来的)

就像我说的，很多复杂的细节…

YES!不要使用扳机。将sequence列定义为integer generated always as identity。当Postgres处理赋值时，这消除了对触发器的复杂需求;你没有——事实上你不能。仅供参考，将值赋值为MAX + 1几乎可以保证创建重复项。不幸的是，这会使丢失数字，因为在某些点上会有空白。生成无间隙序列相当困难，而且几乎没有任何收益。除非有合法的，否则你最好习惯它有丢失的值，然后继续前进。

相关内容

最新更新

热门标签：