当Postgres的主键序列不同步时，如何重置它？

30 浏览
0 Comments

当Postgres的主键序列不同步时，如何重置它？

我遇到了一个问题，我的主键序列与我的表行不同步。

也就是说，当我插入一行新数据时，取得的序列号会返回一个已经存在的数字，从而导致主键冲突。

这似乎是由于导入/还原操作没有正确地维护序列而引起的。

admin 更改状态以发布 2023年5月24日
0

2 答案

0 Comments

pg_get_serial_sequence 可以用来避免对序列名称做出任何错误的假设。用这种方式可以一次性重置序列:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

或更简洁地:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

然而，这种形式无法正确处理空表，因为max(id)是null，而将setval设置为0也不行，因为它会超出序列的范围。解决这个问题的一种方法是利用ALTER SEQUENCE语法，即:

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

但是，ALTER SEQUENCE的用途有限，因为不能使用表达式指定序列名称和重置值。

似乎最佳的通用解决方案是使用false作为第三个参数调用setval，允许我们指定“下一个要使用的值”：

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

这符合我的所有要求:

  1. 避免硬编码序列名称
  2. 正确处理空表
  3. 处理具有现有数据的表，并且不会在序列中留下空缺

最后，需要注意的是，仅当序列由列拥有时，pg_get_serial_sequence才有效。如果自己手动添加了序列，那么必须确保还执行了ALTER SEQUENCE .. OWNED BY。

例如，如果在表的创建中使用了serial类型，则应该全部可行：

CREATE TABLE t1 (
  id serial,
  name varchar(20)
);
SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'
-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

但是，如果序列是手动添加的，则应该这样做：

CREATE TABLE t2 (
  id integer NOT NULL,
  name varchar(20)
);
CREATE SEQUENCE t2_custom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);
ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence
SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'
-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

0
0 Comments 

-- Login to psql and run the following
-- What is the result?
SELECT MAX(id) FROM your_table;
-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');
-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)
BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

来源 - Ruby论坛

0