抑制“重复键值违反唯一约束条件”错误

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，它包含了另一个系统生成的键。该ID需要是唯一的，因此我在表格上添加了一个唯一键约束。

现在，如果我尝试插入一个带有重复some_other_id的行，它会失败（很好），并且我会在我的Postgres日志中得到以下输出：

ERROR:  duplicate key value violates unique constraint "some_other_id_key"

问题在于我的应用程序会尝试添加两个相同的ID，这完全是主线操作，我的日志正在被这个“ERROR”消息淹没，这会导致各种问题：文件占用大量磁盘空间，诊断信息在噪音中丢失，Postgres 必须舍弃诊断信息以保持日志文件的大小限制等。请问有人知道我该如何做才能：

• 抑制日志，要么抑制所有关于此键的日志，要么通过在尝试进行插入的事务上指定某些内容来抑制日志。
• 使用其他 Postgres 功能来发现重复的键并不尝试进行插入。我听说过规则和触发器，但我无法使它们工作（尽管我不是 Postgres 专家）。

请注意，任何解决方案都需要与 Rails 配合使用，Rails 的插入方式如下：

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

- Alex Hockey

---

问题在于我的应用程序尝试两次添加相同的ID是完全主流的，这对我来说似乎很奇怪。为什么应用程序会尝试插入重复的值，你认为这是正常的吗？ - wildplasser

顺便提一下，你的插入语句 INSERT INTO test (some_other_id) VALUES (123) RETURNING id; 没有为 id 字段（该字段为 NOT NULL）提供值，因此它应该违反了 id 字段的 NOT NULL 约束。你能给我们提供真实的表定义吗？id 是否是一个序列？ - wildplasser

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 是一个自动获取值的 序列 列。这个过程存在一个非常微小的竞态条件 （在 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$

再次强调，这样做仅存在极小的竞态条件风险。为了消除这种风险，你可以以性能稍微慢一些的代价为代价：

• SELECT或INSERT在函数中容易出现竞态条件吗？

- Erwin Brandstetter

---

我觉得那正是我需要的，谢谢！由于唯一键约束将解决它，所以我并不太关心顺序。 - Alex Hockey

@AlexHockey：请记住，如果在plpgsql函数中出现竞争条件，则会收到异常而不是id。 - Erwin Brandstetter

Answer 2

您可以禁用会话（或全局）错误消息的日志记录，但需要超级用户权限：

运行以下命令：

set log_min_messages=fatal;

只有当会话（=连接）结束或您发出新的set语句重置该值时，才记录致命错误。

但是，由于只有超级用户被允许更改此设置，因此这可能不是一个好的解决方案，因为它需要您的应用程序用户拥有该特权，这是一个重大的安全问题。

- user330315

---

这也可以在 postgresql.conf 中设置，但在那里设置可能不是一个好主意，因为它会掩盖有关配置问题的重要消息等。ALTER USER my_rails_user SET log_min_messages = fatal; 可能不太严重，但仍然会掩盖重要的消息。最好修复您的应用程序，以避免插入重复项。 - Craig Ringer

Answer 3

如果您只想在使用 psql 时抑制这些错误，可以执行以下操作：

SET client_min_messages TO fatal

这将持续到您的会话结束。

- Randall