错误时递增

Question

当发生错误时，如何抑制该表中的“id”增加？

db=> CREATE TABLE test (id serial primary key, info text, UNIQUE(info));
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_info_key" for table "test"
CREATE TABLE

db=> INSERT INTO test (info) VALUES ('hello') ;
INSERT 0 1

db=> INSERT INTO test (info) VALUES ('hello') ;
ERROR:  duplicate key violates unique constraint "test_info_key"

db=> INSERT INTO test (info) VALUES ('hello') ;
ERROR:  duplicate key violates unique constraint "test_info_key"

db=> INSERT INTO test (info) VALUES ('goodbye') ;
INSERT 0 1

db=> SELECT * from test; SELECT last_value from test_id_seq;

 id |  info   
----+---------
  1 | hello
  4 | goodbye
(2 rows)

 last_value 
------------
          4
(1 row)

Answer 1

您不能抑制这一点 - 在ID值中差距没有错。

主键是一个完全毫无意义的值，仅用于唯一识别表中的一行。

您不能依赖 ID 永远不会有任何间隙 - 试想一下如果删除一行会发生什么。

简单地忽略它 - 没有什么错

编辑
只是想提一下，手册中也明确说明了这种行为：

为了避免阻塞从同一序列获取数字的并发事务，nextval 操作永远不会回滚

http://www.postgresql.org/docs/current/static/functions-sequence.html
（滚动至底部）

Answer 2

您的问题归结为：“我可以从 PostgreSQL 序列回滚下一个值吗？”

答案是：“你不能。” PostgreSQL 文档说

为了避免阻止从同一序列获取数字的并发事务， nextval 操作永远不会回滚。 。 .

Answer 3

想象一下两个不同的事务要插入。事务 A 获取 id=1 事务 B 获取 id=2。事务 B 提交。事务A回滚。现在我们该怎么办？如何回滚 A 的序列而不影响 B 或后续事务？

Answer 4

我想通了。

我需要围绕 INSERT 语句编写一个包装函数。

数据库通常一次只有一个用户，因此“争夺下一个 id”的情况很少见。我担心的是，当我的（未提及的）“从远程数据库表中提取行”功能会尝试将不断增长的远程数据库表重新插入主数据库表中时。我正在显示行 ID，但我不希望用户将编号间隙视为缺失数据。

无论如何，这是我的解决方案：

CREATE FUNCTION testInsert (test.info%TYPE) RETURNS void AS '
BEGIN
  PERFORM info FROM test WHERE info=$1;
  IF NOT FOUND THEN
    INSERT INTO test (info) VALUES ($1);
  END IF;
END;' LANGUAGE plpgsql;