sql - 抑制 "duplicate key value violates unique constraint"错误

Question

我正在开发一个使用 Postgres 作为其数据库的 Rails 3 应用程序。我有如下所示的表格:

             Table "public.test"
    Column     |  Type   | Modifiers
---------------+---------+-----------
 id            | integer | not null
 some_other_id | integer |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "some_other_id_key" UNIQUE CONSTRAINT, btree (some_other_id)

这有两列:

• id，这是主键(由 rails 自动创建)
• some_other_id，其中包含另一个系统生成的 key 。这个 id 需要是唯一的，所以我在表中添加了一个唯一的键约束。

现在，如果我尝试插入具有重复 some_other_id 的行，它会失败(很好)并且我在我的 Postgres 日志中得到以下输出:

ERROR:  duplicate key value violates unique constraint "some_other_id_key"

问题是我的应用程序尝试添加相同的 ID 两次完全是主线，我的日志中充斥着这条“错误”消息，这会导致各种问题:文件占用大量磁盘空间，诊断得到迷失在噪音中，Postgres 不得不丢弃诊断以将日志文件保持在大小限制内，等等。

有谁知道我该怎么做:

• 抑制日志，方法是抑制关于此 key 的所有日志，或者可能通过在尝试执行 INSERT 的事务上指定某些内容。
• 使用其他一些 Postgres 功能来发现重复键，而不是尝试 INSERT。我听说过规则和触发器，但我都无法使用(虽然我不是 Postgres 专家)。

请注意，任何解决方案都需要与 Rails 一起使用，Rails 会像这样插入:

INSERT INTO test (some_other_id) VALUES (123) RETURNING id;

Answer 1

为了避免重复键错误开始于:

INSERT INTO test (some_other_id)
SELECT 123
WHERE  NOT EXISTS (SELECT 1 FROM test WHERE some_other_id = 123)
RETURNING id;

我假设 id 是一个 serial 列，它会自动获取其值。

这受制于非常小的竞争条件(在SELECT 和INSERT 之间的时间段)。但可能发生的最坏情况是您毕竟遇到重复键错误，这几乎不会发生，在您的情况下不应该成为问题。

如果您的框架限制您使用正确语法的选项，您始终可以使用原始 SQL。

或者您可以为此目的创建一个 UDF(用户定义的函数):

CREATE FUNCTION f_my_insert(int)
 RETURNS int LANGUAGE SQL AS
$func$
INSERT INTO test (some_other_id)
SELECT $1
WHERE  NOT EXISTS (SELECT 1 FROM test WHERE some_other_id = $1)
RETURNING id;
$func$

调用:

SELECT f_my_insert(123);

或者，默认为已经存在的 id:

CREATE FUNCTION f_my_insert(int)
 RETURNS int LANGUAGE plpgsql AS
$func$
BEGIN;

RETURN QUERY
SELECT id FROM test WHERE some_other_id = $1;

IF NOT FOUND THEN
   INSERT INTO test (some_other_id)
   VALUES ($1)
   RETURNING id;
END IF;

END
$func$

同样，这为竞争条件留下的可能性很小。您可以以降低性能为代价消除它:

• Is SELECT or INSERT in a function prone to race conditions?