如何删除未使用的序列？

Question

我们正在使用 PostgreSQL。我的要求是从我的数据库中删除未使用的序列。 例如，如果我通过应用程序创建任何表，则会创建一个序列，但为了删除该表，我们也不会删除该序列。如果想要创建相同的表，则正在创建另一个序列。

示例：表：文件；自动为 id coumn 创建的序列：file_id_seq

当我删除表 file 并再次使用相同名称创建它时，正在创建一个新序列（即file_id_seq1）。通过这种方式，我在我的应用程序数据库中积累了大量未使用的序列。

如何删除这些未使用的序列？

Answer 1

当删除列（或其表）时，为serial 列自动创建的序列将自动删除。你所描述的问题一开始就不应该存在。只有非常版本的 PostgreSQL 没有这样做。 7.4 或更早版本？

问题的解决方案

此查询将生成DDL 命令来删除执行该查询的数据库中的所有“未绑定”序列：

SELECT string_agg('DROP SEQUENCE ' || c.oid::regclass, '; ') || ';' AS ddl
FROM   pg_class       c
LEFT   JOIN pg_depend d ON d.refobjid = c.oid
                       AND d.deptype <> 'i'
WHERE  c.relkind = 'S'
AND    d.refobjid IS NULL;

在c 中转换为regclass。 oid::regclass 根据当前的 search_path 在必要时自动对序列名称进行模式限定。请参阅：

• 如何检查是否给定模式中存在一个表
• search_path 如何影响标识符分辨率和“当前架构”

结果：

DROP SEQUENCE foo_id_seq;
DROP SEQUENCE bar_id_seq;
...

执行结果以删除所有未绑定到串行列（或任何其他列）的序列。在这里研究一下列和表的含义。

小心！这些序列可能会被使用。在某些用例中，序列被创建为独立对象。例如，如果您希望多列共享一个序列。你应该确切地知道你在做什么。

但是，您无法通过这种方式删除绑定到serial列的序列。因此，在这方面操作是安全的。

DROP SEQUENCE test_id_seq;

结果：

ERROR:  cannot drop sequence test_id_seq because other objects depend on it
DETAIL:  default for table test column id depends on sequence test_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Answer 2

如果您使用 pgAdmin，您可以选择序列并选中“取决于”选项卡。它将列出依赖于该序列的任何对象。

另一种方法是尝试删除序列。如果表引用它，pgAdmin 将抛出一个错误，指出某些内容取决于此序列。如果您能够毫无错误地删除序列，则不存在依赖性。

请务必在某处进行测试。

Answer 3

我所做的是首先获得所有序列，然后将这些结果保存到文件中，然后在 psql 中运行该文件：下面的内容以文件名 del_seq_all.sql 保存，然后在 中列出序列>test1.sql 。我不知道这是否是正确的解决方案。但结果正如预期的那样到来。

\o d:/test1.sql
SELECT 'drop sequence ' || c.relname || ';' FROM pg_class c WHERE
(c.relkind = 'S');
\o

\i d:/test1.sql

Answer 4

请谨慎操作，“删除序列sequence_name_here”将成功删除序列，即使它作为表列的默认 nextval() 值附加也是如此。这里似乎存在一些脱节，特别是如果序列是单独创建的。我还在寻找一款完美的衬管来清理 100% 未使用的序列。

Answer 5

以 @erwin 的答案为基础：

DO $ DECLARE
    r text;
BEGIN
    FOR r IN (
            SELECT cl.relname
            FROM pg_class cl
            LEFT JOIN pg_namespace ns ON ns."oid" = cl.relnamespace
            LEFT JOIN pg_depend d ON d.refobjid = cl."oid" AND d.deptype <> 'i'
            WHERE ns.nspname = 'public' 
                AND cl.relkind = 'S'
                AND d.refobjid IS NULL
        ) LOOP
            -- dangerous, test before you execute!
            RAISE NOTICE '%',  -- once confident, comment this line ...
      --    EXECUTE         -- ... and uncomment this one
                'DROP SEQUENCE ' || quote_ident(r);
    END LOOP;
END $;

这将实际执行查询并产生预期的结果