postgresql - 由于引用表中的并发删除而忽略插入错误

Question

我正在使用“insert ... select ... where exists ...”将行批量插入表中，同时忽略 FK 约束无效的行。但是，当有一个并发事务刚刚删除了引用表中的一行时，这不起作用。

考虑以下 psql session :

第 1 节:

coudy=# create table a (x int primary key);
CREATE TABLE
coudy=# create table b (x int, foreign key (x) references a);
CREATE TABLE
coudy=# insert into a values (1);
INSERT 0 1
coudy=# begin;
BEGIN
coudy=# delete from a where x = 1;
DELETE 1

第 2 节:

coudy=# begin;
BEGIN
coudy=# insert into b select v.x from (values (1)) v (x) where exists (select 1 from a where a.x = v.x);

这正确地阻止了 session 2。但是，在 session 1 中提交后， session 2 现在意外失败:

ERROR:  insert or update on table "b" violates foreign key constraint "b_x_fkey"
DETAIL:  Key (x)=(1) is not present in table "a".

我本以为该行会被过滤掉，因此不会被插入。在我的实际场景中，这是一个批量插入，所以理想情况下我想避免重试。

提高隔离级别没有帮助。

Answer 1

您可以更改 session 2 以执行以下操作:

INSERT INTO b
   SELECT x FROM (VALUES (1)) v(x)
   WHERE EXISTS (SELECT 1 FROM a
                 WHERE a.x = v.x
                 FOR SHARE SKIP LOCKED);

内部SELECT将跳过所有持有独占锁的行，因为它们正在被更新或删除。

这不会阻塞或出错，但它可能无法插入一些本来可以正常工作的行(想象一下 session 1 回滚事务)。

如果您不介意等待锁并且不想错过这些极端情况，则必须使用 SAVEPOINT s 并使用 ROLLBACK TO SAVEPOINT 处理错误以避免重试整个交易。