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

[nvv]

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

[Kilor]

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

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

[Tangeman]

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

[Kilor]

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

Или если база-источник доступна только в read only по соображениям безопасности.

[nvv]

Бэкап без данных --schema-only не подойдёт для получения служебных данных, в т.ч. последовательностей?

[Kilor]

В общем случае, имя последовательности на том же столбце той же таблицы может отличаться в другой базе.
И --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;

[nvv]

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

[oxff]

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

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

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

[Kilor]

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

[oxff]

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

[Kilor]

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

[oxff]

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

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

[Kilor]

сами по себе эти ключи не несут никакой смысловой нагрузки

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

[oxff]

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

[Kilor]

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

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

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

[oxff]

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

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

[Kilor]

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

[oxff]

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

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

[Kilor]

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

[oxff]

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

[Kilor]

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

[Kilor]

не та ветка