如何删除未使用的序列？ [英] How to delete unused sequences?
问题描述
我们正在使用PostgreSQL。我的要求是从数据库中删除未使用的序列。
例如，如果我通过应用程序创建任何表，将创建一个序列，但是对于删除表，我们也不会删除该序列。如果要创建相同的表，则会创建另一个序列。
We are using PostgreSQL. My requirement is to delete unused sequences from my database. For example, if I create any table through my application, one sequence will be created, but for deleting the table we are not deleting the sequence, too. If want to create the same table another sequence is being created.
示例：表： file ;自动为 id 列创建的序列： file_id_seq
Example: table: file; automatically created sequence for id coumn: file_id_seq
何时我删除表 file 并再次使用相同的名称创建表，正在创建一个新序列（即 file_id_seq1 ）。这样，我已经在应用程序数据库中积累了大量未使用的序列。
When I delete the table file and create it with same name again, a new sequence is being created (i.e. file_id_seq1). I have accumulated a huge number of unused sequences in my application database this way.
如何删除这些未使用的序列？
How to delete these unused sequences?
推荐答案
首先，当删除列（或表所在的列）时，会自动删除为序列列自动创建的序列。您描述的问题不应该从一开始就存在。只有非常的PostgreSQL旧版本没有这样做。 7.4或更早版本？
First off, a sequence that is created automatically for a serial column is deleted automatically, when the column (or table it is in) is deleted. The problem you describe should not exist to begin with. Only very old versions of PostgreSQL did not do that. 7.4 or older?
此查询将生成 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 $ c> c.oid :: regclass 会根据当前 search_path 在必要时自动对序列名称进行模式限定。请参阅：
The cast to regclass in c.oid::regclass automatically schema-qualifies sequence names where necessary according to the current search_path. See:
- How to check if a table exists in a given schema
- How does the search_path influence identifier resolution and the "current schema"
结果：
DROP SEQUENCE foo_id_seq;
DROP SEQUENCE bar_id_seq;
...
执行结果以删除未绑定到序列的所有序列列（或任何其他列）。在此处研究列和表的含义。
Execute the result to drop all sequences that are not bound to a serial column (or any other column). Study the meaning of columns and tables here.
小心！它不是不是，表示这些序列没有被使用。在许多用例中，将序列创建为独立对象。例如，如果您希望多个列共享一个序列。您应该确切地知道自己在做什么。
Careful though! It does not mean those sequences aren't in use otherwise. There are a number of use cases where sequences are created as standalone objects. For instance if you want multiple columns to share one sequence. You should know exactly what you are doing.
但是，您不能删除绑定到序列的序列列这样。因此，在此方面，该操作是安全的。
However, you cannot delete sequences bound to a serial column this way. So the operation is safe in this respect.
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.
这篇关于如何删除未使用的序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
