为了绝对确保第一个表中的单行存在，并且返回它的 ID，您可以创建一个如下所示的函数：
- 函数中的 SELECT 或 INSERT 是否容易出现竞争条件？ https://stackoverflow.com/a/15950324/939860
确保该行也stays在交易期间，只需确保其已锁定即可。如果你INSERT行，无论如何它都被锁定了。如果你SELECT现有的id，你必须显式锁定它 - 就像你建议的那样。FOR KEY SHARE https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS只要有（非部分、非功能性）就足以满足我们的目的UNIQUE索引于(scope, name)，考虑到您的情况，这是安全的假设ON CONFLICT clause.
CREATE OR REPLACE FUNCTION f_object_id(_scope text, _name text, OUT _object_id int)
LANGUAGE plpgsql AS
$func$
BEGIN
LOOP
SELECT id FROM object
WHERE scope = $1
AND name = $2
-- lock to prevent deletion in the tiny time frame before the next INSERT
FOR KEY SHARE
INTO _object_id;
EXIT WHEN FOUND;
INSERT INTO object AS o (scope, name)
VALUES ($1, $2)
ON CONFLICT (scope, name) DO NOTHING
RETURNING o.id
INTO _object_id;
EXIT WHEN FOUND;
END LOOP;
END
$func$;
您实际上只需要在可以想象并发事务可能会发生的情况下锁定该行DELETE它（你不UPDATE）在极短的时间范围内SELECT和下一个INSERT陈述。
另外，如果您有FOREIGN KEY约束来自object_member.object_id to object.id（这似乎是可能的），无论如何，引用完整性是得到保证的。如果您不添加显式锁，并且在中间删除了该行，则会出现外键冲突，并且INSERT to object_member连同整个交易一起被取消。否则，与DELETE必须等到事务完成，然后被相同的 FK 约束取消，因为依赖行现在已经存在（除非它被定义为CASCADE...）因此，通过锁定（或不锁定），您可以决定是否阻止DELETE or the INSERT在这种情况下。
然后你的电话就只剩下：
query(
`WITH o(id) AS (SELECT f_object_id($1, $2))
INSERT INTO object_member (object_id, key, value)
SELECT o.id, UNNEST($3::text[]), UNNEST($4::int[])
FROM o;`
[$scope, $name, $keys, $values]
)
由于您显然将多行插入到object_member， 我搬家了f_object_id($1, $2)到 CTE 以避免重复执行 - 这会work，但毫无意义的昂贵。
在 Postgres 12 或更高版本中我会通过添加来明确这一点MATERIALIZED https://www.postgresql.org/docs/current/sql-select.html#SQL-WITH（自从INSERT隐藏在函数中）：
WITH o(id) AS MATERIALIZED (SELECT f_object_id($1, $2)) ...
旁白：对于多个unnest() in the SELECT列表中，请确保您使用的是 Postgres 10 或更高版本。看：
- SELECT 子句中多个返回集合的函数的预期行为是什么？ https://stackoverflow.com/questions/39863505/what-is-the-expected-behaviour-for-multiple-set-returning-functions-in-select-cl/39864815#39864815
细节问题
在同一事务中使用多个查询在应用程序逻辑中执行此操作是否会产生任何影响（除了执行时间之外）？
基本上没有。唯一的区别是性能。嗯，代码短，可靠性高。客观上，每个循环在数据库和客户端之间来回更容易出错。但除非你的交易竞争非常激烈，否则你几乎不会循环。
另一个考虑是：事情很棘手，大多数开发人员不理解。封装在服务器端函数中，它不太可能被下一个应用程序程序员（或您自己）破坏。您还必须确保它也被实际使用。无论哪种方式，正确记录你这样做的原因......
我真的想知道我的第二个片段是否安全，或者为什么不安全（给出关于可见性的引用SELECT之后INSERT).
Mostly安全，但不是绝对。而接下来单独SELECT将看到与先前的 UPSERT 竞争的事务的（现已提交的）行，同时没有什么可以阻止第三个事务再次删除它。该行尚未被锁定，当它不可见时您无法执行此操作，并且 Postgres 中没有可用的通用谓词锁定。
考虑一下（T1、T2、T3 是并发事务）：
T2: BEGIN transaction
T1: BEGIN transaction
T2: INSERT object 666
T1: UPSERT object 666
unique violation?
-> wait for T2
T2: COMMIT
T1: unique violation -> NO ACTION
finish statement
can't return invisible object 666
T3: DELETE object 666 & COMMIT
T1: SELECT object 666 -> no row!
BOOM!
通常情况下，这种情况发生的可能性极小。
但这是可能的。因此就有了循环。
另一种选择是SERIALIZABLE事务隔离 https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE。通常更昂贵，并且您需要为序列化失败做好准备。第 22 条。
