psql upsert приводит к непрерывному идентификатору

Question

У меня есть таблица postgresql (>9.5) с primary_key id и уникальный ключ col, Когда я использую

INSERT INTO table_a (col) VLUES('xxx') ON CONFLICT(col) DO NOTHING;

чтобы выполнить upsert, скажем, строка с идентификатором 1 генерируется.

Если я снова запускаю sql, ничего не произойдет, но на самом деле идентификатор 2 будет создан и заброшен.

Затем, если я вставлю новую запись, например,

INSERT INTO table_a (col) VLUES('yyy') ON CONFLICT(col) DO NOTHING;

Еще один ряд с идентификатором 3 будет создан и идентификатор 2 впустую!

Есть ли способ избежать этих отходов?

Answer 1

Предположительно id это serial, Под капотом это вызывает nextval() вызов из последовательности. Число nextval() однажды вернувшийся никогда не вернется снова. И зов nextval() происходит до проверки на возможные конфликты.

Из "9.16. Функции управления последовательностями":

nextval

(...)

Важное замечание: Во избежание блокировки параллельных транзакций, которые получают числа из той же последовательности, nextval операция никогда не откатывается; то есть, как только значение было получено, оно считается использованным и больше не будет возвращено. Это верно, даже если окружающая транзакция позднее прерывается, или если вызывающий запрос заканчивается тем, что не использует значение. Например, INSERT с ON CONFLICT предложение будет вычислять вставляемый кортеж, включая выполнение любых необходимых nextval звонки, прежде чем обнаружить какой-либо конфликт, который заставил бы его следовать ON CONFLICT Править вместо. Такие случаи оставят неиспользованные "дыры" в последовательности присвоенных значений. Таким образом, объекты последовательностей PostgreSQL не могут использоваться для получения последовательностей "без промежутков".

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