DBA: перенос значений SEQUENCE между базами PostgreSQL

    Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение «автоинкремент»-поля типа serial, если в таблице могли быть какие-то удаления, и «просто подставить max(pk)» уже не подходит?

    Мало кто знает, что хоть PG и не предоставляет до версии 10 функций, чтобы узнать последнее значение последовательности для такого поля из другого сеанса, это все-таки можно сделать.



    PostgreSQL поддерживает «автоинкремент»-поля, определяемые псевдотипом serial:
    Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД).
    Технически, это всего лишь автоматическая увязка DEFAULT-определения для поля и генерируемых объектом SEQUENCE значений. И иногда в эти значения хочется заглянуть — например, для переноса в аналогичную по структуре базу.

    Но сделать это непросто:
    currval
    Возвращает значение, выданное при последнем вызове nextval для этой последовательности в текущем сеансе. (Если в данном сеансе nextval ни разу не вызывалась для данной последовательности, возвращается ошибка.) Так как это значение ограничено рамками сеанса, эта функция выдаёт предсказуемый результат вне зависимости от того, вызвалась ли впоследствии nextval в других сеансах или нет.

    Системное представление pg_sequences
    Начиная с PostgreSQL 10 появилось системное представление pg_sequences, в котором эта информация уже видна без особых усилий.

    Давайте попробуем эту информацию все-таки вытащить:

    CREATE TABLE tst(
      id
        serial -- исследуемое поле
    , val
        integer
    );
    
    INSERT INTO tst(val) VALUES(1),(2),(4),(8);
    

    TABLE tst;

    id | val
    --------
     1 |   1
     2 |   2
     3 |   4
     4 |   8

    Вот это значение id = 4 мы и хотим получить. Но кто-то удалил часть записей, и в таблице его больше нет:

    DELETE FROM tst WHERE id > 2;

    id | val
    --------
     1 |   1
     2 |   2

    Сначала узнаем, как называется последовательность, соответствующая нашему полю:

    SELECT pg_get_serial_sequence('tst', 'id');

    pg_get_serial_sequence
    ----------------------
    public.tst_id_seq

    Теперь используем полученное имя последовательности в качестве таблицы в запросе:

    SELECT * FROM public.tst_id_seq;

    last_value | log_cnt | is_called
    --------------------------------
             4 |      29 | t

    Собственно, поле last_value и хранит то самое «последнее» значение, которое успела сгенерировать последовательность на момент нашего вызова.

    Теперь соберем простой скрипт для переноса значений последовательностей из одной базы в другую с помощью модуля dblink:

    -- потабличная синхронизация сиквенсов, запускать на источнике
    SELECT
      (
        SELECT
          nlv
        FROM
          dblink(
            'host=... port=5432 dbname=... user=... password=...'
          , $q$
              SELECT setval(pg_get_serial_sequence('$q$ || quote_ident(sequence_schema) || $q$.$q$ || quote_ident(sequence_table) || $q$', '$q$ || sequence_column || $q$'), $q$ || lv || $q$)
            $q$
          ) T(nlv bigint) -- восстанавливаем значение
      ) nlv
    , *
    FROM
      (
        SELECT
          (
            SELECT
              relname
            FROM
              pg_class
            WHERE
              oid = (dp).refobjid
          ) sequence_table
        , (
            SELECT
              attname
            FROM
              pg_attribute
            WHERE
              (attrelid, attnum) = ((dp).refobjid, (dp).refobjsubid)
          ) sequence_column
        , *
        FROM
          (
            SELECT
              -- получаем значение на базе-источнике
              (
                SELECT
                  lv
                FROM
                  dblink(
                    'dbname=' || current_database()
                  , $q$
                      SELECT last_value FROM $q$ || quote_ident(sequence_schema) || $q$.$q$ || quote_ident(sequence_name) || $q$
                    $q$
                  ) T(lv bigint) -- извлекаем последнее значение, подставляя имя "таблицы"-последовательности
              ) lv
            , (
                SELECT
                  dp
                FROM
                  pg_depend dp
                WHERE
                  (classid, objid, refclassid, deptype) = ('pg_class'::regclass, (quote_ident(sequence_schema) || '.' || quote_ident(sequence_name))::regclass, 'pg_class'::regclass, 'a')
                LIMIT 1
              ) dp -- ассоциированное с последовательностью поле таблицы
            , *
            FROM
              information_schema.sequences
            WHERE
              sequence_schema IN ('public') -- перечень обрабатываемых схем
          ) T
      ) T
    WHERE
      sequence_table !~ '^_'; -- исключаемые из синхронизации последовательностей таблицы
    

    Но помните, что при наличии какой-то активности на базе-источнике результат будет неопределен!
    Тензор
    Разработчик системы СБИС

    Комментарии 22

      +1

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

        0
        Например, когда уже выданный идентификатор нельзя повторно переиспользовать, даже если запись с ним была удалена — если на использованный ID в какой-то внешней базе уже была ссылка. В качестве примера можно рассмотреть пару «основная база — база аудита». В этом случае вторая база получит запись «ID = nnn удален». Очевидно, что допустить повторное использование его уже нельзя.

        Ну а ситуация такая может возникать при частичной синхронизации базы с боя в тестовый контур. «Взять из бэкапа» в этом случае может быть достаточно дорого, если объем баз сотни гигабайт.
          +1
          И что в этом сценарии не позволяет просто взять nextval и начать с него в новой базе? Раз уж активность мы прибили, зачем точное значение currval? Да, возможно будет «дырка» в один инкремент, но они вроде как неважны, насколько я могу судить.

            0
            Например, если вы дублируете в два контура операции, а не данные. Тогда это расхождение в единицу все сломает.

            Или если база-источник доступна только в read only по соображениям безопасности.
            0
            Бэкап без данных --schema-only не подойдёт для получения служебных данных, в т.ч. последовательностей?
              0
              В общем случае, имя последовательности на том же столбце той же таблицы может отличаться в другой базе.
              И --schema-only создает CREATE SEQUENCE… START WITH 1 всегда — на примере из статьи это выглядит примерно так:
              CREATE TABLE public.tst (
                  id integer NOT NULL,
                  val integer
              );
              
              ALTER TABLE public.tst OWNER TO postgres;
              
              CREATE SEQUENCE public.tst_id_seq
                  AS integer
                  START WITH 1
                  INCREMENT BY 1
                  NO MINVALUE
                  NO MAXVALUE
                  CACHE 1;
              
              ALTER TABLE public.tst_id_seq OWNER TO postgres;
              
              ALTER SEQUENCE public.tst_id_seq OWNED BY public.tst.id;
          0

          Чем не подходит nextval?

            +1

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


            Суррогатные авто-инкрементные ключи нужны для обеспечения ссылочной целостности и только. Никакого смысла с точки зрения бизнеса они не должны нести, и соответственно их можно при миграции менять как угодно и генерировать по любому алгоритму: хоть со знаком "минус", хоть по убыванию, хоть с пропусками по 100500 тыщ.


            Что, кстати, бывает очень полезно при использовании параллельных вставок с большого количества сессий, с предварительным кешированием ID порциями. При этом вставленные позже записи совсем не обязательно получают больший ID.

              0
              Все правильно, для ссылочной целостности, но она не ограничивается рамками одной БД. Если по причинам нагрузки разделены, например, данные о счетах и их наименованиях, то ID счета уже нельзя будет менять «просто так» при переносе в копию.
              В этом плане суррогатный автоинкремент ничем не хуже, чем генерируемый uuid, если у нас есть возможность выдавать его на стороне базы, поскольку решают одну задачу — позволить не использовать сложный естественный ключ.
              А проблемы в пропусках значений сиквенса нет — лишь бы они были одинаковыми на всех связанных сторонах.
                0

                А нельзя ли в таком случае разделить ответственность и назначить один из узлов мастером, ответственным за генерацию ID? И в принципе не суть важно откуда в нём берутся новые значения. Сиквенс подойдёт, конечно, лучше всего, поскольку мы говорим о БД.

                  0
                  Например, мы взяли копию мастера N-дневной давности, развернули в тестовом контуре, и начинаем прогонять одни и те же операции ради оценки нагрузки. Чтобы новые данные совпадали по ключам, нам и надо будет переинициализировать счетчик.
                    0

                    Вот именно, в том то и дело, что новые данные совсем не должны совпадать по ключам, потому что сами по себе эти ключи не несут никакой смысловой нагрузки, как мы с вами сказали выше. Совпадать должны значащие с точки зрения бизнеса атрибуты.


                    Хотя, конечно, программисту, который пишет тестовые сценарии, проверки по ID запилить намного проще. Однако, это является антипаттерном, и за это кое-где могут сделать атата. А как же быть? Да просто посчитать агрегаты по суммам, количеству и т.п. и проверять эти числа вместо ID.

                      0
                      сами по себе эти ключи не несут никакой смысловой нагрузки
                      Если сама «операция» выглядит как «дай мне наименования счета с ID=12345», то совпадение идентификаторов становится критично.
                        0

                        А должно было быть так: "дай мне наименование счета с номером АБВ-12345-001"

                          0
                          Тут мы ступаем на очень зыбкую почву использования естественных ключей.
                          Я так-то сторонник их использования, но до разумных пределов — потому что потом выясняется, что «нумерация счетов неуникальна», «и даже в пределах одной организации неуникальна».
                          Взять хотя бы правила нумерации счетов-фактур:
                          Период возобновления нумерации организация может установить в учетной политике самостоятельно в зависимости от количества оформляемых ею документов. Например, возобновлять нумерацию можно с начала очередного года, квартала, месяца.

                          И уже получается, что в API пролезут (Дата, Номер), возможно, еще и ID организации, от которой документ выставлен. Оп-с… не ID, а естественный ключ должен быть — то есть ИНН+КПП…
                          И такой вызов будет избыточно сложен — вместо одного ID_фактуры получили (Дата, Номер, ИНН, КПП), а зачем?
                            +1

                            Вы совершенно правы в том, что использование естественных ключей сложнее, так как они часто бывают составными и это требует больше кода. Но вот "зыбкой почвой" и моветоном как раз таки считается использование айдишников. Ох, не зря звенел звоночек :)


                            "Зачем?" — вы спрашиваете. Хотя бы для того, чтобы не городить эту синхронизацию сиквенсов. Для вашего кейса (нагрузочное тестирование) это может быть и не так важно, и действительно можно было бы закрыть глаза на это (хотя я бы такую имплементацию даже для тестов зарубил на корню на код-ревью). Но я видел как люди такое пихают в продакшн. Это просто недопустимо. Признайте хотя бы это :)

                              0
                              Мы сравниваем два подхода к использованию ключей, а надо бы — удобство каждого из подходов для решения конкретных задач, потому что не бывает «абсолютных решений».
                              В описанном мной выше случае естественные ключи адово добавляют проблем. PK организации может расшириться новыми полями — например, кодом филиала — и тогда переконвертировать все связанные таблицы?.. Не-не.
                                0

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


                                Вы решили упростить себе жизнь при написании тестов, захардкодили айдишники? Имеете полное право, хозяин-барин как говорится. Но только не нужно, пожалуйста, утверждать что этот подход имеет право на жизнь в прочих условиях, и особенно когда речь касается продакшна.

                                  0
                                  Таки я же несколько раз писал — берем из продакшена и переносим в тестовый контур. Обратно — я даже не могу придумать зачем может быть надо.
                                  Кажется, мы друг друга наконец поняли.
                                  0

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

                                    0
                                    Вот тут — полностью согласен. Только «идентификация с точки зрения бизнеса» — это не то же самое, что адресация с точки зрения приложения.
                0
                не та ветка

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое