修复序列

从表中更新序列值

管理片段

修复序列

适用于 PostgreSQL

8.4+

用

SQL

取决于

无

在复制或重新创建数据库时，一个常见的问题是数据库序列不会仅仅通过在使用该序列的表中插入记录来更新。如果您希望所有序列都从已经存在的任何值之后开始，您可以像这样为大多数常见配置做到这一点

SELECT 
    'SELECT SETVAL(' ||
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend 
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid 
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute 
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname;

典型用法如下

• 将此保存到一个文件，例如 'reset.sql'
• 运行该文件并将它的输出以不包含通常的标题的方式保存，然后运行该输出。示例

psql -Atq -f reset.sql -o temp
psql -f temp
rm temp

您需要了解这段代码片段的一些限制

• 它只对由表拥有的序列起作用。如果您的序列不是拥有的，请先运行以下脚本

修复序列所有权

此脚本将具有 OWNED BY 的序列更改为它们引用的表和列。注意！被多个表或列引用的序列将被忽略。

(查询的某些部分厚颜无耻地从 OmniTI 的 美味佳肴库 中窃取，作者是 Robert Treat)

select 'ALTER SEQUENCE '|| quote_ident(min(schema_name)) ||'.'|| quote_ident(min(seq_name))
       ||' OWNED BY '|| quote_ident(min(table_name)) ||'.'|| quote_ident(min(column_name)) ||';'
from (
    select 
        n.nspname as schema_name,
        c.relname as table_name,
        a.attname as column_name,
        substring(d.adsrc from E'^nextval\\(''([^'']*)''(?:::text|::regclass)?\\)') as seq_name 
    from pg_class c 
    join pg_attribute a on (c.oid=a.attrelid) 
    join pg_attrdef d on (a.attrelid=d.adrelid and a.attnum=d.adnum) 
    join pg_namespace n on (c.relnamespace=n.oid)
    where has_schema_privilege(n.oid,'USAGE')
      and n.nspname not like 'pg!_%' escape '!'
      and has_table_privilege(c.oid,'SELECT')
      and (not a.attisdropped)
      and d.adsrc ~ '^nextval'
) seq
group by seq_name having count(*)=1;

---

此片段查找没有被任何列拥有的孤立序列。运行它可能会有帮助，可以双重检查上面的查询是否正确地执行了它的工作。

select ns.nspname as schema_name, seq.relname as seq_name
from pg_class as seq
join pg_namespace ns on (seq.relnamespace=ns.oid)
where seq.relkind = 'S'
  and not exists (select * from pg_depend where objid=seq.oid and deptype='a' and classid = 'pg_class'::regclass)
order by seq.relname;