Comments 24
Обычно обновление Postgres до новой основной версии включает создание нового экземпляра Postgres на желаемой версии и настройку репликации с текущего экземпляра на новый.
Вы описали один из вариантов, и, по моему опыту, далеко не самый частый. Можно ещё:
Остановить мастер, снять снапшот, обновить данные через pg_upgrade, запустить мастер.
Сделать реплику через физическую репликацию, проделать предыдущий пункт на ней, превратить её в мастер, направить клиентов на неё.
Сделать дамп из старой версии, залить в новую
Использовать сторонние инструменты типа bucardo
Значение последовательности не реплицируется.
Да, и это описано в документации: https://www.postgresql.org/docs/current/logical-replication-restrictions.html
Sequence data is not replicated. The data in serial or identity columns backed by sequences will of course be replicated as part of the table, but the sequence itself would still show the start value on the subscriber.
Спасибо за дополнение!
do
$$
declare
rec record;
l_seq bigint;
l_tab_max bigint;
begin
for rec in (select format('%s.%s', table_schema, table_name) as seq, data_type from information_schema.columns
where column_name = 'id' and data_type in ('integer', 'bigint') order by 1)
loop
begin
SELECT nextval(pg_get_serial_sequence(rec.seq, 'id')) into l_seq;
execute 'select coalesce(max(id), 0) from ' || rec.seq::varchar into l_tab_max;
if l_tab_max > 0 and l_seq < l_tab_max then
execute format('alter sequence %s restart with %s', pg_get_serial_sequence(rec.seq, 'id'), l_tab_max);
raise notice 'corrected table % seq % tabmax %', rec.seq, l_seq, l_tab_max;
end if;
exception
when others then
null;
end;
end loop;
end;
$$Вот таким скриптом пользуюсь при миграции или после version upgrade. Все identity columns называются id.
В таких случаях можно использовать триггеры в таблице на стороне подписчика, которые с помощью параметра RESTART команды ALTER SEQUENCE будут обновлять последовательность принудительно.
Забыли добавить, что проблема использования UUID в качестве первичного ключа - фрагментация индекса (aka "проблема неравномерного индекса").
Поэтому нужно использовать UUID v7.
Поэтому нужно использовать UUID v7.
Который пока что только черновик стандарта, практически нигде не реализован и всё еще в 2 раза больше чем автоинкремент
Да, но давайте взглянем поближе.
"нигде не реализован" - вполне себе где надо реализован, в т.ч. во вполне себе up-to-date-либах. Все стандартные типы совместимы (т.к. это всё ещё UUID), но алгоритм генерации, конечно, приходится втаскивать. Имхо, небольшая цена за стратегически хорошее решение.
"в 2 раза больше, чем автоинкремент" - да - но в относительном выражении, это может быть 5% размера вашей базы, если не меньше. Опять же, цена не слишком высока, и выпячивание этого факта как основного можно рассматривать как premature optimisation и кусочничанье не там где надо.
Зато вы избавляетесь от жирного нарушения архитектурных границ, а именно - ваши данные перестают зависеть от механизма выбранного движка хранения этих данных (sic!). Обычно автоинкременты сильно аукаются при рефакторинге и всяких других эволюционно-обусловленных штуках (денормализация данных, вынесение микросервиса, перенос части данных в другое более подходящее для данного паттерна доступа решение).
Ну или например, бывают ситуации, когда нужно быстро вставить в таблицу большое количество свежесгенерированных данных с релейшенами. Когда для того, чтобы вставить релейшен нужно знать id родителя, который нужно вставить до этого. С автоинкрементом я фиг знает как такое сделать (наверное, как-то можно, но я даже думать о таком отказываюсь), а с PK, которые не зависят от хранилища, это довольно простой, хоть и жирный, batch insert, который БД быстренько скушает и обработает.
Короче - автоинкременты для сайтов на вордпрессе пойдёт - но для более серьёзных продуктов имеет смысл пожертвовать небольшую цену, чтобы держать систему гибкой и поддерживаемой.
С автоинкрементом я фиг знает как такое сделать
Автоинкремент не запрещает указывать id из приложения.
Можно, но не нужно.
Так разговор же как раз о том, что нужно. Данные для интеграционных тестов так и делают, с заданными id, хотя в таблице используется автоинкремент.
А при чем здесь тесты? Речь шла про пакетную вставку вообще, а не в изолированном однопользовательском окружении.
Слово "свежесгенерированных" указывает на то, что значения были сгенерированы в приложении, такая ситуация обычно бывает в тестах. Ситуации, когда снаружи приходит большое количество реальных данных, обычно таким словом не описывают.
Когда данные приходят снаружи, главные сущности обычно обрабатывают по одной с вызовом нужных бизнес-проверок в коде. Если открывать транзакцию и коммитить каждые N сущностей, то по скорости это даже не слишком отличается от пакетной вставки. Так чтобы можно было просто сохранять без проверок происходит настолько редко, что нелогично отказываться из-за автоинкремента только из-за этого. И даже если нужно, в данных обычно есть внешний ключ, и после вставки N записей можно сделать один запрос $map = Query::from('table')->select(['id', 'uniq_key'])->where(['in', 'uniq_key', $batchExternalKeys])->indexBy('uniq_key')->all() и указать нужный id в релейшенах.
Чем короче первичный ключ, тем компактнее ссылки на него и индексы. Гуиды - сильное расточительство. Только для межсистемных (втч репликационных) взаимодействий.
Ожидал увидеть реальные проблемы типа "при распределенном мастер-мастер-мастер-мастер кластере транзакция будет долго согласовывать уникальность автоинкремента" или "при сплит-брейне кластера я долго восстанавливал целостность автоинкрементов и удалял дубликаты, а вот с UUID этого бы не"... или "с UUID нельзя перебрать все объекты итерируясь по номеру, а с int ключом это было бы уязвимостью" и тп.
Оказалось что просто автор напоминает о sequences (PG) и обсасывает довольно тривиальные вопросы.
Удачи автору в общении с клиентами.
За использование guid в качестве номера заказа -автора бить будут все и долго и возможно ногами.
Если использовать guid, то потом все равно придётся прикрутить какой-нибудь user-friendly идентификатор.
Далее поскольку номер заказ должен быть уникальным, то все равно придётся использовать ту же sequence и получить те же проблемы.
Как выше отметили guid как вариант если у вас распределённая система. И то хотелось бы чего-то более практичного -челевекочитаемого и удобно поддерживаемого.
Кстати для PostgreSQL sequence 8 byte т.е можно пихнуть
18,446,744,100,000,000,000 Значений.
Guid -16 Byte куда вам столько?
автоинкремент в InnoDB работает так: при перезагрузке сервера он выясняет, какой следующий идентификатор будет использоваться, выполняя этот запрос
Вы знаете, я не просто удивлён, а даже где-то изумлён... Это MyISAM работает приблизительно по описанному алгоритму - но никак не InnoDB! Не говоря уж о том, что у таблицы в метаданных есть атрибут AUTO_INCREMENT...
К сожалению, в ближайшие две недели мне ничего, кроме online fiddle, не будет доступно, а их не перегрузишь... но я бы проверил, не является ли наблюдаемое вами следствием того, что всё происходит в контейнере докера.
Нет, докер там не при чём - он создает volume в самом начале и его использует.
Я MySQL не трогал лет 10 уже, но после вашего комментария мне стало интересно и я залез в доки. Там, действительно, описывается какие костыли для автоинкремента в базе приделаны - аж три разных режима.
Там вся страница достойна прочтения, но относительно вашего замечания будет существенным вот этот отрывок:
In MySQL 5.7 and earlier, the auto-increment counter is stored in main memory, not on disk. To initialize an auto-increment counter after a server restart,
InnoDBwould execute the equivalent of the following statement on the first insert into a table containing anAUTO_INCREMENTcolumn.
SELECT MAX(ai_col) FROM table_name FOR UPDATE;In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and saved to the data dictionary on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.
On a server restart following a normal shutdown,
InnoDBinitializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary.
Этот существенный отрывок говорит следующее: если в версии 5.7 или ранее создать InnoDB таблицу, указав некое значение опции AUTO_INCREMENT, а затем перестартовать сервер, то, поскольку показанный запрос вернёт NULL, то следующая запись получит в качестве значения автоинкремента единицу, а значение указанной выше опции будет проигнорировано. И аналогично - для случаев удаления записей с топом перед рестартом.
Однако я что-то не припоминаю подобных фортелей на практике. И, наоборот, прекрасно помню, что SHOW CREATE TABLE показывал значение опции, которое корректировалось с каждым потенциально-генерирующим запросом и прекрасно переживало рестарт сервера.
Нет-нет, всё правда. Точнее как раз наоборот - в myisam последний выданный id хранился перед перезагрузками, а вот в innodb-то как раз и нет. Меня в свое время это тоже поразило. И хорошо, что наконец-то приделали сохранение.
Описанная проблема с mysql, если она существует, то давно не актуальна - вы же не будете в новых проектах использовать древнюю версию mysql ) и уж точно из-за практически нулевой вероятности проблем менять первичные ключи на guid - это it преступление )
Потенциальные проблемы с автоинкрементным ключом. MySQL <8.0, PostgreSQL