该UPDATE
语法 要求 显式命名目标列。避免这种情况的可能原因:
"All columns"
必须以匹配顺序和匹配数据类型表示 “目标表的所有列” (或至少 “表的前导列” )。否则,您仍然必须提供目标列名称的列表。
测试表:
CREATE TABLE tbl (
id int PRIMARY KEY
, text text
, extra text
);
INSERT INTO tbl AS t
VALUES (1, 'foo')
, (2, 'bar');
仅适用于 “目标表的所有列” 。尽管语法甚至适用于前导子集,但目标表中多余的列将使用DELETE
和重置为NULL INSERT
。
INSERT ... ON CONFLICT ...
需要UPSERT()来避免在并发写入负载下的并发/锁定问题,这仅是因为在Postgres中没有通用的方法来锁定尚不存在的行(值锁定 )。
您的特殊要求仅会影响UPDATE
零件。可能的复杂性不适用于影响 现有 行的地方。那些被正确锁定。简化一些,您可以将案例减少为DELETE
和INSERT
:
WITH data(id) AS ( -- Only 1st column gets explicit name!
VALUES
(1, 'foo_upd', 'a') -- changed
, (2, 'bar', 'b') -- unchanged
, (3, 'baz', 'c') -- new
)
, del AS (
DELETE FROM tbl AS t
USING data d
WHERE t.id = d.id
-- AND t <> d -- optional, to avoid empty updates
) -- only works for complete rows
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO NOTHING
RETURNING t.id;
在Postgres的MVCC模型,一个UPDATE
是大致相同DELETE
,并INSERT
反正(除了某些极端情况并发,HOT更新和大列值线的存储出)。由于您仍然想替换所有行,因此只需删除之前的冲突行INSERT
。删除的行将保持锁定状态,直到提交事务。在INSERT
可能只找到冲突行对以前不存在的键值,如果并发事务发生并发插入(在后DELETE
,但在此之前的INSERT
)。
在这种特殊情况下,您将丢失受影响的行的其他列值。没有异常。但是,如果竞争查询具有相同的优先级,那么这几乎不是问题:另一个查询在 某些 行中胜出。另外,如果另一个查询是类似的UPSERT,则它的替代方法是等待该事务提交,然后立即进行更新。“获胜”可能是痛苦的胜利。
关于“空更新”: