主页 / dba / 问题 / 111823
Accepted
rubik
Asked: 2015-08-22 06:21:05 +0800 CST

在 PostgreSQL 中压缩序列

  • 772

我id serial PRIMARY KEY在 PostgreSQL 表中有一个列。id由于我删除了相应的行，因此缺少许多s。

现在我想通过重新启动序列并以保留id原始顺序的方式重新分配 s来“压缩”表。id可能吗？

例子：

  • 现在：

 id | data  
----+-------
  1 | hello
  2 | world
  4 | foo
  5 | bar
  • 后：

 id | data  
----+-------
  1 | hello
  2 | world
  3 | foo
  4 | bar

我尝试了 StackOverflow 答案中的建议，但没有奏效：

# alter sequence t_id_seq restart;
ALTER SEQUENCE
# update t set id=default;
ERROR:  duplicate key value violates unique constraint t_pkey
DETAIL:  Key (id)=(1) already exists.
postgresql primary-key

1 个回答

  1. Best Answer

    首先，序列中的间隙是可以预料的。问问自己是否真的需要删除它们。如果你只是忍受它，你的生活会变得更简单。为了获得无间隙的数字，（通常更好的）替代方法是使用 a VIEWwith row_number()。此相关答案中的示例：

    以下是一些消除差距的方法。

    1.新的，原始的桌子

    避免了独特的违规和表膨胀的并发症，并且速度很快。仅适用于不受 FK 引用、表上的视图或其他依赖对象或并发访问约束的简单情况。一次交易即可避免意外：

    BEGIN;
LOCK tbl; -- optionally: IN SHARE MODE to allow concurrent reads

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL);

INSERT INTO tbl_new -- no target list in this case
SELECT row_number() OVER (ORDER BY id), data  -- all columns in default order
FROM   tbl;

ALTER SEQUENCE tbl_id_seq OWNED BY tbl_new.id;  -- make new table own sequence

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

    CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL)复制结构，包括。来自原始表的约束和默认值。然后使新表列拥有序列：

    并将其重置为新的最大值：

    这样做的好处是新表不会膨胀并且聚集在id.

    2.UPDATE到位

    这会产生很多死行，并且VACUUM稍后需要（自动）。

    如果该serial列也是PRIMARY KEY（如您的情况）或具有UNIQUE约束，则必须避免该过程中的唯一违规行为。PK / UNIQUE 约束的（更便宜的）默认值是NOT DEFERRABLE，它强制在每一行之后进行检查。此相关问题下关于 SO 的所有详细信息：

    您可以将约束定义为DEFERRABLE（这使得它更昂贵）。
    或者您可以删除约束并在完成后将其添加回来：

    BEGIN;

LOCK tbl;

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

COMMIT;

    当您有引用列的约束时，两者都不可能，FOREIGN KEY因为（根据文档）：

    被引用的列必须是被引用表中不可延迟的唯一或主键约束的列。

    您需要（锁定所有涉及的表并）删除/重新创建 FK 约束并手动更新所有 FK 值（请参阅选项3。）。或者，您必须在一秒钟内将值移开UPDATE以避免冲突。例如，假设您没有负数：

    BEGIN;
LOCK tbl;

UPDATE tbl SET id = id * -1;  -- avoid conflicts

UPDATE tbl t
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id DESC) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

    缺点如前所述。

    3. 临时表, TRUNCATE,INSERT

    如果您有足够的 RAM，还有另一种选择。这结合了前两种方式的一些优点。几乎与选项1一样快。您将获得一个没有膨胀的原始新表，但保留所有约束和依赖项，就像选项2中一样。
    但是，根据文档：

    TRUNCATE 不能用于具有 来自其他表的外键引用的表，除非所有此类表也在同一命令中被截断。在这种情况下检查有效性将需要表扫描，而重点不是这样做。

    大胆强调我的。

    您可以暂时删除 FK 约束并使用数据修改 CTE 更新所有 FK 列：

    SET temp_buffers = 500MB;   -- example value, see 1st link below

BEGIN;

CREATE TEMP TABLE tbl_tmp AS
SELECT row_number() OVER (ORDER BY id) AS new_id, *
FROM   tbl
ORDER  BY id;  -- order here to use index (if one exists)

-- drop FK constraints in other tables referencing this one
-- which takes out an exclusive lock on those tables

TRUNCATE tbl;

INSERT INTO tbl
SELECT new_id, data  -- list all columns in order
FROM tbl_tmp;        -- rely on established order in tbl_tmp
-- ORDER BY id;      -- only to be absolutely sure (not necessary)

--  example for table "fk_tbl" with FK column "fk_id"
UPDATE fk_tbl f
SET    fk_id = t.new_id  -- set to new ID
FROM   tbl_tmp t
WHERE  f.fk_id = t.id;   -- match on old ID

-- add FK constraints in other tables back

COMMIT;

    相关，更多细节：

    • 10

相关问题