SERIAL увеличивает ID, даже если срабатывает CONSTRAINT

Question

Есть ID с типом SERIAL, то есть он автоинкрементирует число, после внесения данных. Также есть второй столбец, условимся Name (varchar).

У Name есть ограничение на уникальность данных.

CREATE TABLE IF NOT EXISTS users (
ID SERIAL PRIMARY KEY,
Name varchar(150) NOT NULL UNIQUE);

Допустим в таблице было:

| ID | Name |
| 1  | Pavel|

Затем я пытаюсь внести данные, которые заведомо не подойдут по ограничению.

INSERT INTO users (Name) VALUES ('Pavel');

Сработает ошибка уникальности и данные не внесутся.

Далее я вношу новые, но уже подходящие данные.

INSERT INTO users (Name) VALUES ('Kristina');

Таблица принимает такой вид:

| ID |   Name   |
-----------------
| 1  | Pavel    |
| 3  | Kristina |

Суть проблемы: После неудачной попытки внести данные, автоинкремент увеличил значение ID и, при внесении новых данных, ID будет перескакивать порядок (пропущен ID со значением 2).

Конечно, это только эстетическая проблема по большей части, но мне хотелось бы знать, есть ли адекватный способ исправить это стандартным или не очень sql способом.

Так, как я использую в проекте python, то получилось "костыльно" решить задачу, но меня это не устравивает.

def setval():
    cur.execute("SELECT ID, Name FROM users ORDER BY id;")
    data = cur.fetchall()
    cur.execute("DELETE FROM users;")
    for index, row in enumerate(data):
        cur.execute("""
            INSERT INTO users (ID, Name)
            VALUES (%s, %s)
        """, (index + 1, row[1]))
    cur.execute("""SELECT setval('users_ID_seq', (SELECT MAX(id) FROM users));""")
    adb.commit()

Answer 1

Типы smallserial, serial и bigserial используют sequences и это нормально, что они оставляют пропуски в последовательности. В документации об этом сказано.

Так же там сказано, что это сделано не просто так и имеют под собой обоснование, выделенное как "осторожно". В частности для работы многопоточной обработки и избегания блокировок при вставке данных. Так же дополнительные сведения приведены в FAQ.

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

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

UPD

К примеру, решение из того же FAQ с таблицей последовательности, которое ведет на вопрос stackoverflow.

Создаем функцию, которая будет вытаскивать ID из таблицы и обновлять их.

CREATE OR REPLACE FUNCTION get_next_id(countertable regclass, countercolumn text) RETURNS integer AS $$
DECLARE
    next_value integer;
BEGIN
    EXECUTE format('UPDATE %s SET %I = %I + 1 RETURNING %I', countertable, countercolumn, countercolumn, countercolumn) INTO next_value;
    RETURN next_value;
END;
$$ LANGUAGE plpgsql;

Создаем таблицу и используем функцию.

CREATE TABLE example_seq ( last_id integer not null );
INSERT INTO example_seq VALUES (0);

create table example(
    id integer primary key not null default get_next_id('example_seq', 'last_id'),
    name text unique
);

Или можно найти другие решения по gapless sequences, к примеру: https://www.postgresql.org/message-id/[email protected]

Или попробовать max(id) + 1

БЛОГ НА HUSL

SERIAL увеличивает ID, даже если срабатывает CONSTRAINT

Ответы (1 шт):