sql - 替代 Postgres SERIAL 字段以解决在 ON CONFLICT 导致更新时递增的值

Question

我最近被搞糊涂了，因为我没有意识到无论是否插入数据，SERIAL 字段都会递增的问题。

我读过的关于这个问题的大部分答案都讨论了防止列中出现漏洞，我相当确定在大多数情况下这不是所提出的问题所关心的，而且肯定不是我的情况。

我的情况是，我的软件的特定用户正在以一种导致在单个记录上执行数百万次更新插入的方式使用功能。该记录用作状态信息，在我的天真中，当 INTEGER id 字段 nextval() 达到其限制时，我很幸运地没有意识到即将发生的故障，即以下错误:

错误:整数超出范围SQL状态:22003

所以我的问题是，在冲突回滚的情况下，如何防止 id 字段递增下一个序列值。

我期待其他人将他们的知识添加到我的解决方案中。

Answer 1

我对这个问题的直接解决方案是将列更改为 BIGINT，如下所示:

ALTER TABLE MyTable ALTER COLUMN idMyTable TYPE BIGINT;

在我的案例中，记录的数量非常少 (<1000)，所以这是一个微不足道的更改。

一旦解决了这个问题，就该寻找根本问题的解决方案了。我的解决方案不太可能像使用 SERIAL 字段那样高效，因此如果您要实现与我所做的类似的事情，请根据您的用例牢记这一点 - 总会有一些折衷。

考虑下表和结果数据插入/查询:

CREATE TABLE TestTable ( id SERIAL PRIMARY KEY NOT NULL, Key TEXT UNIQUE NOT NULL, Val TEXT );
INSERT INTO TestTable (Key,Val) VALUES ('Fruit', 'banana') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
INSERT INTO TestTable (Key,Val) VALUES ('Fruit', 'apple') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
INSERT INTO TestTable (Key,Val) VALUES ('Fruit', 'peach') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
INSERT INTO TestTable (Key,Val) VALUES ('Animal', 'horse') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
SELECT * FROM TestTable;
id Key    Val
1  Fruit  peach
4  Animal horse

在这种情况下，Fruit 更新期间的每个冲突都会增加 SERIAL 值，即使它没有在 TestTable 中创建任何新记录。

现在这是我目前正在使用的解决方法。如果有人知道如何将表名连接到“NEW.id”，我很想听听，因为我喜欢将我的 ID 列命名为 idTablename 以保持一致性。

CREATE OR REPLACE FUNCTION IncrementSerial()
RETURNS trigger AS $fn$
BEGIN
  EXECUTE format('SELECT COALESCE( MAX( id ), 0 ) + 1 FROM %I.%I;',TG_TABLE_SCHEMA,TG_TABLE_NAME) INTO NEW.id;
  RETURN NEW;
END
$fn$ LANGUAGE 'plpgsql'

CREATE TABLE TestTable ( id INTEGER PRIMARY KEY NOT NULL, Key TEXT UNIQUE NOT NULL, Val TEXT );

CREATE TRIGGER trgIncrementSerial
BEFORE INSERT ON TestTable
  FOR EACH ROW
    EXECUTE PROCEDURE IncrementSerial()

INSERT INTO TestTable (Key,Val) VALUES ('Fruit', 'banana') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
INSERT INTO TestTable (Key,Val) VALUES ('Fruit', 'apple') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
INSERT INTO TestTable (Key,Val) VALUES ('Fruit', 'peach') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
INSERT INTO TestTable (Key,Val) VALUES ('Animal', 'horse') ON CONFLICT( Key ) DO UPDATE SET Val=EXCLUDED.Val;
SELECT * FROM TestTable;
id Key    Val
1  Fruit  peach
2  Animal horse

如您所见，SERIAL id 现在只使用下一个最高的 id 号，这对我的大多数用例来说都是理想的。

显然，如果 id 键必须始终是唯一的，这将成为一个问题，因为删除最后一条记录将释放该 id。如果这不是问题(即 id 仅用于引用和级联)，那么这对您来说可能是一个很好的解决方案。