如何在PostgreSQL中实现“获取或创建”操作以及为何这一操作极易出错-阿里云开发者社区

[本文翻译自：https://hakibenita.com/postgresql-get-or-create]（https://hakibenita.com/postgresql-get-or-create）

“获取或创建”是数据库中同步数据的常见操作，但正确实现它的难度可能远超预期。若曾在高负载的真实系统中落地该操作，你或许曾忽略过潜在的竞争条件、并发问题，甚至是表膨胀！

本文将探讨PostgreSQL中实现“获取或创建”的多种方法。

实现“获取或创建”

假设你有一个允许用户为物品打标签的系统，需创建如下标签表：

db=# CREATE TABLE tags (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);
CREATE TABLE

该表包含自增主键与标签名字段。为实现标签复用，需为name字段添加唯一约束：

db=# ALTER TABLE tags ADD CONSTRAINT tags_name_unique UNIQUE(name);
ALTER TABLE

向表中添加标签的基础操作很简单：

db=# INSERT INTO tags (name) VALUES ('A'), ('B') RETURNING *;
 id │ name
────┼──────
  1 │ A
  2 │ B
(2 rows)

INSERT 0 2

若尝试插入已存在的标签名，会触发错误：

db=# INSERT INTO tags (name) VALUES ('B') RETURNING *;
ERROR:  duplicate key value violates unique constraint "tags_name_key"
DETAIL:  Key (name)=(B) already exists.

应用程序为物品打标签时，无需关心标签是否已存在，核心要求是操作具备幂等性——输入相同（标签名），输出始终一致（标签对象）。普通INSERT不满足幂等性，重复执行相同输入会触发错误：
❌ 该操作不具备幂等性

要实现幂等性，需处理两种场景：

标签已存在：返回现有标签
标签不存在：创建标签并返回其名称与新ID

这类同步功能通常被称为“获取或创建”。

“更新或插入”与“获取或创建”的区别

“更新或插入”（又称UPSERT）与“获取或创建”不同：UPSERT会更新表中已存在的匹配行，而“获取或创建”需避免修改现有数据。正如本文后续所述，这一限制对实现方式有显著影响。

实现幂等性

如前所述，插入已存在的标签会触发唯一约束冲突导致失败。要实现幂等性，需仅创建不存在的标签，并返回所有已存在的标签。

调整逻辑，先检查标签是否存在再创建：

CREATE OR REPLACE FUNCTION get_or_create_tag(tag_name tags.name%TYPE)
RETURNS SETOF tags AS {mathJaxContainer[0]} LANGUAGE plpgsql;

该函数先查询同名标签，存在则直接返回，不存在则创建后返回。它返回行集合，使用方式如下：

db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
(2 rows)

db=# SELECT * FROM get_or_create_tag('C');
 id │ name
────┼──────
  3 │ C
(1 row)

db=# SELECT * FROM get_or_create_tag('C');
 id │ name
────┼──────
  3 │ C
(1 row)

首次执行'C'时创建标签并分配ID 3，重复执行则直接返回已有标签，操作具备幂等性✅！

该函数可使用任意语言编写，借助PL/pgSQL的返回行集合函数特性，还能一次性批量创建标签：

db=# SELECT tags.*
FROM
  (VALUES ('B'), ('C'), ('D')) AS new(tag),
  get_or_create_tag(tag) AS tags;

 id │ name
────┼──────
  2 │ B
  4 │ C
  5 │ D
(3 rows)

通过VALUES子句生成标签集合，再传入函数即可批量处理。

该方案达成首个目标：
✅ 操作具备幂等性
重复执行相同输入，输出始终一致。但存在一个易被忽略的潜在问题。

处理潜在竞争

上述函数看似能通过多数代码评审，实则存在一个隐蔽的边缘场景，可能导致操作失败。

若两个并发进程同时尝试为同一标签执行“获取或创建”，可能触发唯一约束冲突。以下是进程A、B同时插入标签foo的场景：

时间	进程A	进程B
T	检查`foo`是否存在标签不存在	-
T+1	-	检查`foo`是否存在标签不存在
T+2	插入`foo` ✅ 成功	-
T+3	-	插入`foo` 💥 唯一约束冲突

两个进程均先检查标签不存在，随后进程A先完成插入，进程B则因冲突失败。

参考Django的get_or_create实现，可通过捕获异常解决竞争问题：

CREATE OR REPLACE FUNCTION get_or_create_tag(tag_name tags.name%TYPE)
RETURNS SETOF tags AS {mathJaxContainer[1]} LANGUAGE plpgsql;

该方案通过捕获插入时的唯一约束异常，假设其他进程已插入该行，随后查询并返回。

此方案同时满足：
✅ 操作具备幂等性
✅ 并发执行安全
但在部分场景下，该方案存在性能浪费问题。

利用唯一约束冲突

上述方案虽实现幂等性且支持并发，但存在性能损耗：

插入已存在标签：仅执行1次查询，性能良好
插入新标签：至少执行2次查询（先检查+再插入），若多数标签为新标签，该方案效率低下

若预期多数标签为新标签，可移除初始检查，直接尝试插入：

CREATE OR REPLACE FUNCTION get_or_create_tag(tag_name tags.name%TYPE)
RETURNS SETOF tags AS {mathJaxContainer[2]} LANGUAGE plpgsql;

函数先直接插入标签，若触发唯一约束冲突，再查询并返回已有标签：

db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
  3 │ C
(2 rows)

db=# SELECT * FROM get_or_create_tag('D');
 id │ name
────┼──────
  4 │ D
(1 row)

db=# SELECT * FROM get_or_create_tag('D');
 id │ name
────┼──────
  4 │ D
(1 row)

该方案同时满足：
✅ 操作具备幂等性
✅ 并发执行安全
但存在一个不便之处：所有唯一约束冲突都会被记录，若习惯查看数据库日志，会产生大量无关日志，造成干扰。

直接插入并处理冲突的方式虽满足幂等性与并发安全，但存在隐藏弊端。

滥用唯一约束冲突

为演示依赖唯一约束冲突的潜在问题，先查看表当前大小：

db=# SELECT pg_size_pretty(pg_relation_size('tags'));
 pg_size_pretty
────────────────
 8192 bytes

表体积极小。接下来，禁用表的自动清理（autovacuum）（后续解释原因），并插入5万条已存在的标签：

db=# ALTER TABLE tags SET (autovacuum_enabled = false);
SET

db=# \timing on
Timing is on.

db=# SELECT get_or_create_tag('A') FROM generate_series(1, 50000);
 get_or_create_tag
───────────────────
 (1,A)
 (1,A)
 ...
 (1,A)
 (1,A)
(50000 rows)

Time: 12077.786 ms (00:12.078)

插入5万条已存在的标签耗时约12秒，性能极差。再次检查表大小：

db=# SELECT pg_size_pretty(pg_relation_size('tags'));
 pg_size_pretty
────────────────
 1776 kB
(1 row)

表体积扩大了200余倍！注意，我们仅插入了已存在的标签，未新增任何数据，额外空间从何而来？查询表的死元组数量可找到线索：

db=# SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'tags';
 relname │ n_dead_tup │ n_live_tup
─────────┼────────────┼────────────
 tags    │      50000 │          3
(1 row)

每次插入已存在的标签，都会产生一条死元组。亚马逊RDS团队的文章对此解释得很清晰：INSERT命令会先将行写入表（堆页），再检查是否存在唯一约束冲突。若检测到重复，会抛出异常并将该行标记为死元组。死元组会占用空间，直到表被清理，进而引发表膨胀，导致表体积极剧增大。

手动清理表膨胀即可恢复空间：

db=# VACUUM tags;
VACUUM

db=# SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'tags';
 relname │ n_dead_tup │ n_live_tup
─────────┼────────────┼────────────
 tags    │          0 │          2
(1 row)

db=# SELECT pg_size_pretty(pg_relation_size('tags'));
 pg_size_pretty
────────────────
 8192 bytes
(1 row)

数据库内置后台进程autovacuum，会在达到阈值时自动触发清理。本次实验禁用它，仅为演示问题。

清理表膨胀

我曾撰写过如何清理表与索引中表膨胀的方法。

该方案虽满足前两个目标，但存在新问题：
✅ 操作具备幂等性
✅ 并发执行安全
❌ 操作会引发表膨胀

表膨胀需尽量避免，接下来探讨无表膨胀的“获取或创建”实现方式。

理解子语句可见性

在重复数据极少的场景中，依赖捕获唯一约束冲突实现“获取或创建”完全可行；但在重复数据频发的场景中，表膨胀会大量消耗存储空间，给数据库带来性能压力。

为避免此类表膨胀，可从源头规避唯一约束冲突。最初的实现是通过单独查询检查标签是否存在，还有另一种方式。

以下是仅插入不存在标签的查询示例：

db=# INSERT INTO tags (name)
    SELECT name
    FROM (VALUES ('B'), ('C')) AS t(name)
    WHERE NOT EXISTS (
        SELECT 1
        FROM tags
        WHERE tags.name = t.name
    )
RETURNING *;
 id │ name
────┼──────
  4 │ C
(1 row)

标签B已存在，因此仅插入了不存在的C。

若要同时获取已有标签与新标签，可在公用表表达式（CTE）中执行INSERT，再查询表获取数据：

db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
(2 rows)

db=# WITH new_tags AS (
    INSERT INTO tags (name)
    SELECT name
    FROM (VALUES ('B'), ('C')) AS t(name)
    WHERE NOT EXISTS (
        SELECT 1
        FROM tags
        WHERE tags.name = t.name
    )
)
SELECT *
FROM tags
WHERE name IN ('B', 'C');
 id │ name
────┼──────
  2 │ B

奇怪的是，CTE中明明插入了标签C，但后续查询却只返回B。难道C未插入？再次查询验证：

db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
  3 │ C
(3 rows)

命令执行完成后，表中同时存在B与C，为何查询只返回B？官方文档解释：

WITH中的子语句会与主查询并行执行。

主查询无法“看到”CTE中对表的修改，这就是仅返回B的原因。文档还进一步说明了依赖CTE中数据修改语句结果的风险：

因此，使用WITH中的数据修改语句时，指定更新的实际执行顺序不可预测。所有语句均基于同一快照执行，因此无法相互看到对目标表的影响。

这意味着，查询执行期间，对标签表的修改对其他查询部分不可见，直到查询整体完成。但这并不意味着无法使用CTE子语句的结果，RETURNING子句是不同CTE子语句与主查询间传递修改结果的唯一方式。

修改示例如下：

db=# WITH new_tags AS (
    INSERT INTO tags (name)
    SELECT name
    FROM (VALUES ('B'), ('C')) AS t(name)
    WHERE NOT EXISTS (
        SELECT 1
        FROM tags
        WHERE tags.name = t.name
    )
    RETURNING *
)
SELECT * FROM new_tags;
 id │ name
────┼──────
 3  │ C
(1 row)

主查询直接引用CTE的结果，而非再次查询标签表，因此能获取到新插入的C。

调整查询，从CTE获取新标签，从原表获取已有标签，即可得到预期结果：

db=# WITH
new_tags AS (
    INSERT INTO tags (name)
    SELECT name
    FROM (VALUES ('B'), ('C')) AS t(name)
    WHERE NOT EXISTS (
        SELECT 1
        FROM tags
        WHERE tags.name = t.name
    )
    RETURNING *
)
SELECT * FROM tags WHERE name IN ('B', 'C')
UNION ALL
SELECT * FROM new_tags;

 id │ name
────┼──────
  2 │ B
  3 │ C
(2 rows)

拆解逻辑：

插入新标签：通过NOT EXISTS排除已有标签，创建新标签并通过RETURNING返回插入行。CTE结果可能包含多行、一行（无新标签则为空）。
查询已有标签：提前未知哪些标签已存在，因此直接查询所有匹配标签。
查询新标签：查询期间新标签对主查询不可见，因此通过CTE结果获取。

UNION 与 UNION ALL 的区别

UNION与UNION ALL均用于拼接结果集，但UNION会去重，通常需执行耗时的排序或哈希操作；而本查询已确保结果集无重复，因此使用UNION ALL更高效。

该查询达成两个目标：
✅ 操作具备幂等性
✅ 无表膨胀
⚠️ 并发执行是否安全？
接下来验证该方案在高并发场景下的表现。

规避并发问题

目前，我们通过NOT EXISTS规避了唯一约束冲突，又借助RETURNING结合CTE解决了可见性问题，实现了无表膨胀。但此前有专业人士指出，该方案仍存在并发隐患——高并发下执行，可能出现意外错误。

为测试并发执行，调整命令以批量插入随机标签：

-- get_or_create.sql
WITH
-- 生成随机数量的随机标签
tags_to_insert AS (
    SELECT 'tag' || round(random() * 1000) AS name
    FROM generate_series(0, (random() * 10)::int)
),
-- 后续逻辑与前文一致
new_tags AS (
    INSERT INTO tags (name)
        SELECT name
        FROM tags_to_insert t
        WHERE NOT EXISTS (
            SELECT 1
            FROM tags
            WHERE tags.name = t.name
        )
    RETURNING *
)
SELECT * FROM new_tags
UNION ALL
SELECT * FROM tags WHERE name IN (
    SELECT name
    FROM new_tags
);

通过pgbench工具并发执行该脚本：

$ pgbench --client=4 --time=30 -f get_or_create.sql
pgbench (15.4)
pgbench: error: client 1 script 0 aborted in command 0 query 0: ERROR:  duplicate key value violates unique constraint "tags_name_key"
DETAIL:  Key (name)=(tag625) already exists.
pgbench: error: client 0 script 0 aborted in command 0 query 0: ERROR:  duplicate key value violates unique constraint "tags_name_key"
DETAIL:  Key (name)=(tag950) already exists.
pgbench: error: client 3 script 0 aborted in command 0 query 0: ERROR:  duplicate key value violates unique constraint "tags_name_key"
DETAIL:  Key (name)=(tag522) already exists.
pgbench: error: client 2 script 0 aborted in command 0 query 0: ERROR:  duplicate key value violates unique constraint "tags_name_key"
DETAIL:  Key (name)=(tag869) already exists.
[...]
pgbench: error: Run was aborted; the above results are incomplete.

脚本执行瞬间就触发了唯一约束冲突！在真实系统中，这会导致难以排查的异常错误。

我们的查询本应确保不会触发唯一约束冲突，为何仍会发生？原因在于：在执行NOT EXISTS检查与实际插入的极短时间内，其他进程可能已插入了相同标签，导致冲突。这与最初的竞争问题类似，只是本次冲突发生在单个查询内部。

总结该方案：
✅ 操作具备幂等性
✅ 无表膨胀
❌ 并发执行不安全
接下来探讨如何实现高并发下的安全操作。

优雅处理冲突

过程式编程语言中有两种主流编码风格：

先查后做（LBYL）：执行操作前显式检查前置条件，Java、C等语言常用。
求赦而非求许（EAFP）：先执行操作，再处理可能的异常，Python等语言推荐。

SQL虽不常被归为传统编程语言，但我们可将前文方案归为这两类：

直接插入+异常处理：先插入标签，再捕获唯一约束冲突，属于求赦而非求许。
INSERT WHERE NOT EXISTS：先检查标签不存在再插入，属于先查后做。

先查后做的实现常存在检查到使用的时间竞争（TOC-TOU）问题：当检查条件与依赖该条件的操作之间，底层数据被其他进程修改，就会导致操作失败。本方案中，我们先检查标签不存在，再尝试插入，而期间其他进程可能已插入同名标签，最终触发冲突。

参考《无锁处理并发》一文，了解更多关于TOC-TOU问题的解决方法。

那么，SQL中对应的求赦而非求许方案是什么？

要解决TOC-TOU问题，可使用INSERT语句的冲突处理子句：

db=# INSERT INTO tags (name) VALUES ('B'), ('C')
ON CONFLICT (name) DO NOTHING;

INSERT 0 1

ON CONFLICT子句允许指定插入冲突时的处理逻辑：此处指定遇到标签名冲突时不执行任何操作。该命令成功完成，仅插入了1行新数据。

添加RETURNING子句后：

db=# INSERT INTO tags (name) VALUES ('B'), ('C')
ON CONFLICT (name) DO NOTHING
RETURNING *;
 id │ name
────┼──────
  4 │ C
(1 row)

INSERT 0 1

需注意两点关键变化：

仅返回修改行：新标签C被返回，已有标签B未返回。
序列出现间隙：PostgreSQL在尝试插入B时，从序列中获取了下一个值3；因B已存在，触发冲突，ON CONFLICT DO NOTHING丢弃了该序列值；随后插入C，获取序列值4，成功创建标签。最终ID序列出现间隙。

PostgreSQL序列不保证无间隙，这仅是现象，并非该方案的严格缺陷。

从上述示例可知，PostgreSQL仅返回被成功修改的行，INSERT文档也明确说明：

仅返回成功插入或更新的行。

我们已解决该问题，调整查询，在CTE中执行INSERT，再在主查询中拼接已有标签：

db=# WITH
new_tags AS (
    INSERT INTO tags (name)
    VALUES ('B'), ('C')
    ON CONFLICT (name) DO NOTHING
    RETURNING *
)
SELECT * FROM tags WHERE name IN ('B', 'C')
UNION ALL
SELECT * FROM new_tags;

 id │ name
────┼──────
  2 │ B
  4 │ C
(2 rows)

该查询符合预期，实现了幂等性目标——无论执行多少次，输出始终一致。

为验证无表膨胀的目标，再次禁用autovacuum，插入5万条已存在的标签：

db=# ALTER TABLE tags SET (autovacuum_enabled = false);
SET

-- 生成包含5万个'A'的数组，存入psql变量names
db=# SELECT array_agg('A'::text) AS names FROM generate_series(1, 50000) \gset

db=# \timing on
Timing is on.

db=# WITH
new_tags AS (
    INSERT INTO tags (name)
    SELECT unnest(:'names'::text[])
    ON CONFLICT (name) DO NOTHING
    RETURNING *
)
SELECT * FROM tags WHERE name = ANY(:'names'::text[])
UNION ALL
SELECT * FROM new_tags;

 id │ name
────┼──────
  1 │ A
(1 row)

Time: 96.028 ms

未新增任何标签，且命令执行耗时仅96毫秒，性能优异。检查表大小与死元组：

db=# SELECT pg_size_pretty(pg_relation_size('tags'));
 pg_size_pretty
────────────────
 8192 bytes
(1 row)

db=# SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'tags';
 relname │ n_dead_tup │ n_live_tup
─────────┼────────────┼────────────
 tags    │          0 │          2
(1 row)

表体积与死元组均无变化，无表膨胀目标达成✅。

推测性插入

ON CONFLICT子句使用了名为推测性插入的特殊冲突处理机制。与普通插入不同，推测性插入会在写入行前先检查唯一约束与排除约束，从而在冲突时避免表膨胀。该机制的原始提交信息包含更多细节。

最后，验证高并发场景下的安全性，编写脚本批量插入随机标签：

-- get_or_create_on_conflict.sql
WITH
-- 生成随机数量的随机标签
tags_to_insert AS (
    SELECT 'tag' || round(random() * 1000) AS name
    FROM generate_series(0, (random() * 10)::int)
),
-- 后续逻辑与前文一致
new_tags AS (
    INSERT INTO tags (name)
      SELECT name FROM tags_to_insert
    ON CONFLICT DO NOTHING
    RETURNING *
)
SELECT * FROM new_tags
UNION ALL
SELECT * FROM tags WHERE name IN (SELECT name FROM tags_to_insert);

通过pgbench并发执行：

$ pgbench --client=4 --time=30 -f get_or_create_on_conflict.sql
pgbench (15.4)
end.
transaction type: get_or_create_on_conflict.sql
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 109499
number of failed transactions: 0 (0.000%)
latency average = 1.095 ms
initial connection time = 24.556 ms
tps = 3652.536348 (without initial connection time)

完美！使用ON CONFLICT达成了三个核心目标：
✅ 操作具备幂等性
✅ 无表膨胀
✅ 并发执行安全
接下来探讨“获取或创建”的其他细节。

误入歧途的实现

在上述所有方案中，我们本质上对表进行了两次操作：一次插入并忽略冲突，一次查询已有数据。若和我一样，会觉得这不够优雅。

我曾产生一个想法：ON CONFLICT仅返回被修改的行，那为何不忽略冲突时执行无意义的更新，让行被“修改”后返回？实现如下：

db=# INSERT INTO tags (name) VALUES ('B'), ('C')
ON CONFLICT (name) DO UPDATE SET id = EXCLUDED.id
RETURNING *;
 id │ name
────┼──────
  3 │ B
  4 │ C
(2 rows)

INSERT 0 2

该方案看似优雅，遇到已有标签时，将ID更新为自身值——看似无意义的更新，却能让所有受影响的行被RETURNING *返回。

该方案看似仅单次触达行，但存在严重弊端！

在PostgreSQL中，更新行本质上是删除旧行并插入新行。被删除的行被标记为死元组，占用空间直到清理，最终引发表膨胀。

为演示该方案的表膨胀问题，先查看表与索引的初始大小：

db=# SELECT
    pg_size_pretty(pg_relation_size('tags')) table_size,
    pg_size_pretty(pg_total_relation_size('tags') - pg_relation_size('tags')) indexes_size;
 table_size │ indexes_size
────────────┼──────────────
 8192 bytes │ 32 kB
(1 row)

使用该“优雅”方案，5万次“获取或创建”标签：

db=# ALTER TABLE tags SET (autovacuum_enabled = false);
ALTER TABLE

db=# DO $$
BEGIN
    FOR i in 1..50000 loop
        INSERT INTO tags (name) VALUES ('C'), ('D')
        ON CONFLICT (name)
        DO UPDATE SET name = EXCLUDED.name;
    END LOOP;
END $$;

Time: 57331.141 ms (00:57.331)

操作耗时约1分钟，数据几乎无变化：

db=# SELECT * FROM tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
  3 │ C
  4 │ D

操作后检查表与索引大小：

db=# SELECT
    pg_size_pretty(pg_relation_size('tags')) table_size,
    pg_size_pretty(pg_total_relation_size('tags') - pg_relation_size('tags')) indexes_size;
 table_size │ indexes_size
────────────┼──────────────
 3544 kB    │ 64 kB

表体积扩大约50倍，索引大小翻倍。清理表膨胀需执行VACUUM FULL与REINDEX：

db=# VACUUM FULL tags;
VACUUM

db=# REINDEX TABLE tags;
REINDEX

db=# SELECT
    pg_size_pretty(pg_relation_size('tags')) table_size,
    pg_size_pretty(pg_total_relation_size('tags') - pg_relation_size('tags')) indexes_size;
 table_size │ indexes_size
────────────┼──────────────
 8192 bytes │ 32 kB
(1 row)

该方案回归原始问题：
✅ 操作具备幂等性
❌ 会引发表膨胀
✅ 并发执行安全
总结：切勿使用此方案！

避免模式变更

INSERT ON CONFLICT功能强大，但存在一个值得注意的限制：要匹配行，目标表的匹配列必须存在唯一约束或排除约束。

假设标签表无name字段的唯一约束：

db=# ALTER TABLE tags DROP CONSTRAINT tags_name_key;
ALTER TABLE

此时使用INSERT ON CONFLICT同步标签会触发错误：

db=# INSERT INTO tags (name) VALUES ('B'), ('C') ON CONFLICT (name) DO NOTHING;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

部分场景下，匹配列存在唯一约束；但部分场景下，可能不存在，或因匹配条件复杂，难以通过约束表达，无法添加约束。排除约束可用于复杂条件的约束，但需维护索引，在数据仓库等环境中，仅为约束维护索引可能并非最优选择。

PostgreSQL 15起新增了MERGE语句，可解决此问题，其与INSERT ON CONFLICT存在关键差异：

功能范围不同：INSERT ON CONFLICT仅支持插入或更新，MERGE还支持删除。
假设输入包含标签名与删除标记，需同步数据：源中删除的标签，目标表要么删除，要么不创建。INSERT ON CONFLICT需拆分为多个CTE，而MERGE可单语句完成：
```
db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
( 2 rows)

db=# MERGE INTO tags USING (VALUES
    ('A', false),
    ('B', true),
    ('C', false),
    ('D', true)
) AS t(name, deleted)
    ON t.name = tags.name
    WHEN MATCHED AND deleted THEN DELETE
    WHEN MATCHED AND NOT deleted THEN DO NOTHING
    WHEN NOT MATCHED AND deleted THEN DO NOTHING
    WHEN NOT MATCHED AND NOT deleted THEN INSERT (name) VALUES (t.name);
MERGE 1

db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  3 │ C
(2 rows)
```
该MERGE语句优雅处理了四种场景：

| 标签 | 源数据 | 目标表 | 执行动作 |
|------|--------|--------|----------|
| "A" | 未删除 | 存在 | 跳过（无操作） |
| "B" | 已删除 | 存在 | 删除 |
| "C" | 未删除 | 不存在 | 插入 |
| "D" | 已删除 | 不存在 | 忽略（无操作） |

这类同步逻辑在ETL（抽取-转换-加载）过程中极为常见，MERGE能大幅简化开发。
约束依赖不同：INSERT ON CONFLICT要求匹配行存在唯一约束，MERGE无此限制。

如前文所示，无匹配列约束时，INSERT ON CONFLICT会报错；而MERGE可正常执行：
```
db=# MERGE INTO tags USING (VALUES ('B'), ('C')) AS t(name)
ON tags.name = t.name
WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name);
MERGE 0

db=# TABLE tags;
 id │ name
────┼──────
  1 │ A
  2 │ B
  4 │ C
(3 rows)
```
MERGE支持自定义匹配条件，而INSERT ON CONFLICT仅接受列名或约束名。自定义匹配条件的能力极为强大，可适配无法通过唯一/排除约束表达的复杂匹配场景，例如缓慢变化维度的数据同步。

回到标签表场景，MERGE的能力INSERT ON CONFLICT均可实现，但在复杂同步逻辑的真实场景中，MERGE会更便捷。遗憾的是，PostgreSQL 16及更早版本的MERGE不支持RETURNING子句，无法在CTE中结合RETURNING使用。该问题将在PostgreSQL 17中解决，MERGE将支持RETURNING：

db=# WITH
new_tags AS (
  MERGE INTO tags
  USING (VALUES ('B'), ('C')) AS t(name)
  ON tags.name = t.name
  WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name)
  RETURNING tags.*
)
SELECT * FROM tags WHERE name IN ('B', 'C')
UNION ALL
SELECT * FROM new_tags;

 id │ name
────┼──────
  4 │ C
  2 │ B
(2 rows)

MERGE相较于INSERT ON CONFLICT的核心优势是无需目标表存在约束。该方案总结：
✅ 操作具备幂等性
✅ 无表膨胀
✅ 并发执行安全
✅ 无需约束

我们真的解决了幂等性与并发问题吗？

本节内容添加于2024年8月23日

本文发布后，我收到了大量评论。多数读者分享了对MERGE语句的认知，以及在代码库中处理类似场景的方法。但一位细心的读者Tamir Sen指出：即便采用上述方案，在特定场景下，并发执行时仍无法保证幂等性与结果可预测性。

事务隔离级别考量

考虑两个并发会话同时插入新标签X的场景：

-- 会话1
db=# BEGIN;
BEGIN
db=*# WITH new_tags AS (
  INSERT INTO tags (name) VALUES ('X')
  ON CONFLICT (name) DO NOTHING
  RETURNING *
)
SELECT * FROM new_tags
UNION ALL
SELECT * FROM tags WHERE name IN ('X');

 id │ name
────┼──────
  3 │ X
(1 row)

db=*#
-- 会话2
db=# BEGIN;
BEGIN
db=*# WITH new_tags AS (
  INSERT INTO tags (name) VALUES ('X')
  ON CONFLICT (name) DO NOTHING
  RETURNING *
)
SELECT * FROM new_tags
UNION ALL
SELECT * FROM tags WHERE name IN ('X');
-- 阻塞中...

会话1插入标签X并返回结果，但未立即提交；会话2开启事务并尝试插入X，此时被会话1阻塞。

当会话1提交后，观察会话2的结果：

-- 会话1
db=*# COMMIT;
COMMIT;
-- 会话2
-- 阻塞中...

 id │ name
────┼──────
(0 rows)

db=*#

会话1提交后，锁释放，但会话2未返回任何行！标签X已存在，但会话2未获取到结果，不满足幂等性。应用程序期望输入X，始终返回标签X。

在读已提交（READ COMMITTED）隔离级别下，数据库仅保证会话能看到会话启动时已存在的数据。这就是会话2未返回X的原因：其事务开启于会话1提交之前。

根据PostgreSQL文档，其不支持读未提交（READ UNCOMMITTED）隔离级别：

SQL标准定义了额外的读未提交隔离级别，但PostgreSQL会将其视为读已提交。

这意味着，我们也无法通过调整隔离级别解决该问题。

MERGE与INSERT ... ON CONFLICT的不一致性

雪上加霜的是，探究MERGE在该场景下的表现（测试于PostgreSQL 17 Beta 3），结果与INSERT ON CONFLICT不同：

-- 会话1
db=# BEGIN;
BEGIN
db=*# WITH new_tags AS (
  MERGE INTO tags
  USING (VALUES ('X')) AS t(name) ON t.name = tags.name
  WHEN MATCHED THEN DO NOTHING
  WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name)
  RETURNING tags.*
)
SELECT * FROM new_tags
UNION ALL
SELECT * FROM tags WHERE name IN ('X');

 id │ name
────┼──────
  3 │ X
(1 row)

db=*# COMMIT;
COMMIT
-- 会话2
db=# BEGIN;
BEGIN
db=*# WITH new_tags AS (
  MERGE INTO tags
  USING (VALUES ('X')) AS t(name) ON t.name = tags.name
  WHEN MATCHED THEN DO NOTHING
  WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name)
  RETURNING tags.*
)
SELECT * FROM new_tags
UNION ALL
SELECT * FROM tags WHERE name IN ('X');

-- 阻塞中...

ERROR:  duplicate key value violates unique constraint "tags_name_unique"
DETAIL:  Key (name)=(X) already exists.

相同流程下，MERGE会抛出唯一约束冲突异常，而非返回空结果。这一问题存在两个隐患：

WHEN MATCHED DO NOTHING 仍可能触发冲突：在MERGE中显式处理匹配行时，仍可能触发唯一约束冲突，这超出了常规认知。若用MERGE同步表，通常不会考虑捕获该异常。
MERGE WHEN MATCHED DO NOTHING 与 INSERT ON CONFLICT DO NOTHING 行为不一致：直觉上两者等价，但在特定场景下，结果截然不同。

MERGE文档简要提及了这一差异（重点标注）：

当MERGE与其他修改目标表的命令并发执行时，遵循常规事务隔离规则；各隔离级别的行为详见13.2节。你也可考虑使用INSERT ... ON CONFLICT，其支持并发插入时的更新操作。两种语句类型存在诸多差异与限制，不可互换。

面对这种不一致，核心问题浮现：哪种结果是预期的？是接受非幂等结果，还是抛出异常？就我而言，更倾向于抛出异常。静默返回非幂等结果，会引发难以排查的隐性数据错误；而主动抛出异常，虽会中断执行，却能及时发现问题，更易处理。

无约束下MERGE的冲突处理

MERGE的核心优势之一是无需匹配列存在约束，而INSERT ON CONFLICT必须依赖唯一/排除约束。以下探究无约束时，MERGE如何处理并发冲突。

首先删除name字段的唯一约束：

db=# ALTER TABLE tags DROP CONSTRAINT tags_name_key;
ALTER TABLE

并发执行两个MERGE语句，同时插入同一标签：

-- 会话1
db=# BEGIN;
BEGIN

db=*# MERGE INTO tags
USING (VALUES ('X')) AS t(name) ON t.name = tags.name
WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name)
RETURNING tags.*;

 id │ name
────┼──────
  3 │ X
(1 row)

db=*#
-- 会话2
db=# BEGIN;
BEGIN

db=*# MERGE INTO tags
USING (VALUES ('X')) AS t(name) ON t.name = tags.name
WHEN NOT MATCHED THEN INSERT (name) VALUES (t.name)
RETURNING tags.*;

 id │ name
────┼──────
  4 │ X
(1 row)

db=*#

有约束时，会话2会被会话1阻塞；但无约束时，会话2无法看到会话1未提交的X，因此未检测到冲突，最终插入了重复的X。

总结

经过一系列探索，我们得出一个遗憾的结论：仅靠单条SQL语句，无法在高并发环境下保证“获取或创建”的幂等性。那该如何解决？

db=# BEGIN;
BEGIN

db=*# INSERT INTO tags (name) VALUES ('X') ON CONFLICT (name) DO NOTHING;
INSERT 0 1

db=*# COMMIT;
COMMIT

db=*# SELECT * FROM tags WHERE name IN ('X');
 id │ name
────┼──────
  3 │ X
(1 row)

该方案虽不优雅，但几乎满足所有核心要求：
✅ 操作具备幂等性
✅ 无表膨胀
✅ 并发执行安全
❌ 无需约束

虽不喜欢表情包，但此场景下用一张图再合适不过：
（注：此处为原文配图，翻译时保留说明即可）

方案对比

“获取或创建”是OLTP与数据仓库系统中的常见操作，但正确实现难度远超预期。本文从幂等性、并发性、表膨胀、约束依赖四个维度，对比了所有实现方案：

实现方案	幂等性	并发性	表膨胀	约束依赖
普通INSERT	❌	-	✅	❌
SELECT + INSERT	✔️	❌	✅	-
SELECT + INSERT + SELECT	✔️	❌	✅	❌
INSERT + EXCEPT + SELECT	✔️	✅	❌	❌
INSERT WHERE NOT EXISTS	✔️	❌	✅	✅
INSERT ON CONFLICT DO NOTHING	✔️	✅	✅	❌
INSERT ON CONFLICT DO UPDATE	✅	✅	❌	❌
MERGE + RETURNING (PostgreSQL 17+)	✔️	✅	✅	✅
INSERT ON CONFLICT DO NOTHING + 提交后查询	✅	✅	✅	❌

注：✅ 表示满足，❌ 表示不满足；部分方案在本文描述的特定场景下不安全，其余场景正常。

核心要点

从PostgreSQL“获取或创建”的探索过程中，提炼出以下关键结论：

唯一约束冲突会引发表膨胀：行先写入表，再检查重复；冲突时行被标记为死元组，导致膨胀。避免此问题请使用INSERT ON CONFLICT。
CTE子语句并行执行：WITH中的子语句无执行顺序，且基于同一快照，无法相互看到对目标表的修改。
CTE子语句无完全一致性：不同CTE间的数据修改可能引发并发问题。
CTE中的表修改不可见：查询执行期间，CTE对表的修改对其他部分不可见，仅RETURNING子句可传递修改结果。
INSERT ON CONFLICT依赖约束：使用ON CONFLICT子句，匹配列必须存在唯一/排除约束；无约束时请使用MERGE。
MERGE WHEN MATCHED DO NOTHING 会触发冲突：并发插入同一值时，无法检测到匹配，最终导致冲突异常。
无约束时MERGE无法防重：并发插入同一值且无匹配列约束时，无法检测到匹配，会插入重复数据。
MERGE与INSERT ON CONFLICT不等价：无约束时，MERGE可能插入重复值；而INSERT ON CONFLICT因依赖约束，不会产生重复结果。

如何在PostgreSQL中实现“获取或创建”操作以及为何这一操作极易出错

实现“获取或创建”

“更新或插入”与“获取或创建”的区别

实现幂等性

处理潜在竞争

利用唯一约束冲突

滥用唯一约束冲突

清理表膨胀

理解子语句可见性

UNION 与 UNION ALL 的区别

规避并发问题

优雅处理冲突

推测性插入

误入歧途的实现

避免模式变更

我们真的解决了幂等性与并发问题吗？

事务隔离级别考量

MERGE与INSERT ... ON CONFLICT的不一致性

无约束下MERGE的冲突处理

总结

方案对比

核心要点

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

如何在PostgreSQL中实现“获取或创建”操作以及为何这一操作极易出错

实现“获取或创建”

“更新或插入”与“获取或创建”的区别

实现幂等性

处理潜在竞争

利用唯一约束冲突

滥用唯一约束冲突

清理表膨胀

理解子语句可见性

UNION 与 UNION ALL 的区别

规避并发问题

优雅处理冲突

推测性插入

误入歧途的实现

避免模式变更

我们真的解决了幂等性与并发问题吗？

事务隔离级别考量

MERGE与INSERT ... ON CONFLICT的不一致性

无约束下MERGE的冲突处理

总结

方案对比

核心要点

热门文章

最新文章

相关电子书