Fixing Sequences/ru

Administrative Snippets

Fixing Sequences

Works with PostgreSQL

8.4+

Written in

SQL

Depends on

Nothing

Обновление значения последовательности по данным таблицы

Общей проблемой при копировании и повторном создании базы является то, что при вставке записей в таблицы, значения связанных с ними последовательностей не обновляются. Если необходимо установить начальные значения последовательностей, начиная со следующего после последнего существующего в таблице, то для большинства конфигураций это можно достичь следующим путем:

SELECT 'SELECT SETVAL(' ||
     quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
     ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
     quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
     AND S.oid = D.objid
     AND D.refobjid = T.oid
     AND D.refobjid = C.attrelid
     AND D.refobjsubid = C.attnum
     AND T.relname = PGT.tablename
ORDER BY S.relname;

---

Сценарий использования метода следующий:

• Сохраните запрос в файл, например, 'reset.sql'
• Выполните полученный скрипт и сохраните результат его выполнения, исключив заголовки
• Выполните полученный скрипт

Пример:

---

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

---

Существует несколько важных моментов при таком подходе:

• Это работает лишь для последовательностей, принадлежащих таблицам.
• Если последовательности не принадлежат таблицам, первоначально потребуется выполнить другой скрипт:

Как исправить принадлежность последовательности

Данный скрипт заменяет последовательности с OWNED BY на таблицы, колонки которых ссылаются на них. Внимание! Последовательности, на которые ссылаются несколько таблиц одновременно, игнорируются.

(Запрос частично позаимствован с OmniTI Tasty Treats repository Роберта Трита (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')
order by seq.relname;