Багнетка v0.2: Головоломка с переполнением SERIAL в PostgreSQL

Правили как в игре "данетки": я даю вводные, далее вы должны догадаться что произошло, в процессе можете задавать мне вопросы, на которые я могу отвечать "да", "нет", "неважно", "golang"

Эта багнетка в 2 этапа:

(1) Есть таблица:

CREATE TABLE controller_data (
id SERIAL,
data JSONB,
controller_id INT, -- это foreign key
created_at TIMSTAMP WITH TIMEZONE
)

Логика работы с ней следующая: если контроллер прислал новые данные, мы удаляем старые (DELETE) и вставляем новые (INSERT), почему так не спрашивайте, предположим, что данность

Контроллеров десятки тысячь

Через какое-то время данные начали только удаляться, но новые перестали добавляться, что произошло и как бы вы это починили?

(2) Вторую фаза этой багнетки расскажу после того, как найдете ответ на первую

Это связано с полем created at и таймзонами?

Нет

SERIAL переполнился.

В точку)

Теперь, как бы ты это лечил?

sequence?

Не понимаю

Либо менять на bigserial, либо вообще отказаться id, достаточно controller_id.

Прекрасные варианты)

Теперь фаза 2: какой-то разработчик что-то сделал (не то, что ты написал) и появление новых данных заработало, но на короткий промежуток времени

Что он сделал?

Если разработчик сбросил счетчик SERIAL, ему надо руки оторвать. :)

Ахахахаха и ты опять прав!)

А теперь последняя и самая хитрая часть: когда я это обнаружил этот разработчик предложил фикс, который позволил без изменения кода вернуть систему в рабочее состояние и выиграть нам очень много времени на нормальный фикс

Что он предложил?

Ну как вариант заменить SERIAL на BIGSERIAL - времени пофиксить код будет предостаточно.

В таком случае в коде Node.js числовое поле превратиться в строковое и это может привести к ошибкам (хоть и маловероятно), поэтому мы отказались от этой идеи

Другой фикс еще более хитрый)

С Node.js да, попроще с этим.

что-то вроде id BIGINT GENERATED ALWAYS AS IDENTITY ? И\Или что-то с отрицательными числами?

Транкейтить таблицу время от времени 💅

Неа)

Радикальненько, но нет)

я б сказал генерить в поле id какой нибуть uuid. Но если где-то в коде есть чувствительная к типу логика то это не сработает =\\
Еще приходит на ум сделать это ид композитным типа controller_id + (конкатенация) counter который будет инкремент, но для каждого контроллера свой, что б не менять код можно такое через хранимку реализовать, но чет ппц сложно выходит.

Да, uuid не сработает

Композитный PRIMARY KEY (id, controller_id) хорошая идея, но имеет по-прежнему имеет вероятность ошибки, потому что может уже быть такой id + controller_id

Подсказка: суть \"данеток\" в том при N кол-ве вопросов, на которые я могу отвечать \"да\", \"нет\", \"неважно\", \"golang\", человек который вообще ничего не знает про IT будет способен прийти к правильному решению

Поэтому в этой игре важнее задавать вопросы, нежели предлагать варианты ответа

я имел ввиду не просто + а именно конкатенация как строк, типа контроллер_ид 93, ид инкремент 14 на выходе 9314, но из ответа вонял что направление не верное)

Да, всеравно слишком высокая вероятность ошибки)

попытаться как-то переиспользовать ид удалённой записи?)

Неа)

Ладно, пойдём вопросоми)
Это связано с изменением типа данных поля id?

Не связано)

таймстамп играет роль в предложенном решении?

Нет

И эти данные должны быть доступны всегда, то есть просто раз в какой-то период времени сгружать всё из controller_data куда-то не вариант?

Нет

Этот способ включает в себя какоую-то логику типа дополнительных скриптов или хранимок?

Нет

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

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

Ну резюмируя у меня получается следующее:
Проблема - большое количество записей переполняло id SERIAL, был предложен временный фикс который:
- не вынуждает менять код
- не меняет тип поля id
- все данные остаются в таблице всегда
- не переиспользует id удалённых записей
- не включает логику ввиде доп скриптов или хранимых процедур
- никак не свзан с таймстампами

Это что-то, что было сделано оно было сделано с базой? То есть решение не в коде приложения?

Ты упустил момент: после переполнения разработчик сбросил serial sequence в 1

И про «переиспользование» можно сказать что да, но в такой формулировке как у тебя то нет

Верно, даже больше: это 1 SQL запрос

неужели просто по ошибке записи, сбрасывать ?))

Нет)

Решение хоть и временное, но работать будет

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

Хороооош!

Мы правда сделали наоборот: мы взяли все записи и проапдейили им id поставив их в конец, тем самым дали возможность писать сначала

Но твой способ лучше

Есть еще бонусная секция: написать дефрагментацию со сдвигом вначало / вконец в 1 SQL запрос