SQL - 依赖于INSERT期间的服务器错误

问题描述：

我正在使用PostgreSQL 9.1。假设我有一张表，其中一些列有约束条件UNIQUE。最简单的例子：SQL - 依赖于INSERT期间的服务器错误

CREATE TABLE test (
    value INTEGER NOT NULL UNIQUE 
);

现在，插入一些值的时候，我不得不单独处理的情况下，在要插入的值已经在表中。我有两个选择：

做一个SELECT事先保证的值不在表中，或：
执行INSERT，看服务器可能会返回任何错误。

使用PostgreSQL数据库的应用程序是用Ruby编写的。下面是我将如何编写第二个选项：

require 'pg' 

db = PG.connect(...) 

begin 
    db.exec('INSERT INTO test VALUES (66)') 
rescue PG::UniqueViolation 
    # ... the values are already in the table 
else 
    # ... the values were brand new 
end 

db.close

这里是我的想法：让我们假设我们做一个SELECT第一，插入前。 SQL引擎必须扫描行并返回任何匹配的元组。如果没有，我们做一个INSERT，这可能是另一次扫描，看是否约束不会被任何机会侵犯。所以，从理论上讲，第二种选择会使执行速度提高50％。这是PostgreSQL实际上的行为方式吗？

当涉及到异常本身时（例如，我们只有一个UNIQUE约束），我们假设没有歧义。

这是常见的做法吗？或者有任何警告吗？还有更多的选择吗？

答

这取决于 - 如果您的应用程序UI通常允许输入重复值，那么强烈建议您在插入前进行检查。因为任何错误都会使当前事务无效，消耗顺序/序列值，填充错误消息等日志。

但是，如果您的用户界面不允许重复，并且插入重复只能在有人使用技巧时进行（例如在漏洞研究）或极不可能的，那么我会允许插入而不先检查。

由于唯一性约束强制创建索引，因此此检查并不慢。但肯定比插入和检查罕见错误稍慢。 Postgres 9.5 would have on conflict do nothing support，这将是既快速又安全。您将检查插入的行数以检测重复项。

+1'ON CONFLICT'和检查数量的插入行。 “ON CONFLICT”的官方文档是[here]（http://www.postgresql.org/docs/devel/static/sql-insert.html#SQL-ON-CONFLICT）。 –

UI反映了数据库本身的结构 - 并且由于我在数据库中声明了'UNIQUE'约束，因此UI不允许重复。建议的'ON CONFLICT'语法和检查插入行的数量看起来像是一个完美的SQL-only-ish选择。太糟糕了，我不会有任何机会把它放在它上面。 – Tomalla

答

你没有（也不应该）必须先测试;你可以测试而插入。只需将测试作为where子句添加即可。以下插入插入零或一个元组，取决于具有相同值的行的存在。（它肯定是不较慢）：

INSERT INTO test (value) 
SELECT 55 
WHERE NOT EXISTS (
    SELECT * FROM test 
    WHERE value = 55 
    );

虽然你的错误驱动方法可能外观典雅从客户端，从数据库方面，它是一个近乎灾难：当前事务隐式回滚+所有游标（包括预准备语句）都关闭。（因此：您的应用程序将不得不重建完整的交易，但没有错误并重新开始。）

增加：增加多行的时候就可以把VALUES()成CTE和参考CTE中插入查询：

WITH vvv(val) AS (
    VALUES (11),(22),(33),(44),(55),(66) 
    ) 
INSERT INTO test(value) 
SELECT val FROM vvv 
WHERE NOT EXISTS (
    SELECT * 
    FROM test nx 
    WHERE nx.value = vvv.val 
    ); 

-- SELECT * FROM test;

感谢您使用替代SQL语法。尽管我没有在问题中明确地说出它，但我还在单个INSERT语句中插入了多行，如下所示：INSERT INTO VALUES（66），（67）;'。您提供的语句是否可以修改为允许插入多行？关于错误驱动的解决方案：一旦SQL服务器返回错误，客户端请求正在处理**完成**并返回。它不会从错误中恢复，因为错误本身会导致请求不正确。所以这种方法仍然很高，因为它看起来更快，语义更准确。 – Tomalla

SQL - 依赖于INSERT期间的服务器错误

相关推荐