Fixing Sequences/ru

From PostgreSQL wiki

Jump to: navigation, search

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;
Personal tools