Автоинкремент postgres не обновляется при вставке явных идентификаторов

У меня есть следующая таблица в postgres:

CREATE TABLE "test" (
    "id" serial NOT NULL PRIMARY KEY,
    "value" text
)

Я делаю следующие вставки:

insert into test (id, value) values (1, 'alpha')
insert into test (id, value) values (2, 'beta')

insert into test (value) values ('gamma')

В первых 2 вставках я явно упоминаю идентификатор. Однако указатель автоматического увеличения таблицы в этом случае не обновляется. Следовательно в 3-й вставке я получаю ошибку:

ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

Я никогда не сталкивался с этой проблемой в Mysql как в движках MyISAM, так и в INNODB. Явный или нет, mysql всегда обновляет указатель автоинкремента на основе максимального идентификатора строки.

Какой обходной путь для этой проблемы в postgres? Мне это нужно, потому что я хочу более жесткий контроль для некоторых идентификаторов в моей таблице.

ОБНОВЛЕНИЕ: мне это нужно, потому что для некоторых значений мне нужно иметь фиксированный идентификатор. Для других новых записей я не против создания новых.

Я думаю, что это возможно, вручную увеличивая nextval указатель на max(id) + 1 всякий раз, когда я явно вставляю идентификаторы. Но я не уверен, как это сделать.

Источник

4 ответа

Решение

Вот как это должно работать - next_val('test_id_seq') вызывается только тогда, когда системе нужно значение для этого столбца, а вы его не указали. Если вы укажете значение, такой вызов не будет выполнен и, следовательно, последовательность не будет "обновлена".

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

SELECT setval('test_id_seq', (SELECT MAX(id) from "test"));
Источник

В последней версии Django эта тема обсуждается в документации:

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

Ссылка: https://docs.djangoproject.com/en/dev/ref/databases/

Также есть команда управления manage.py sqlsequencereset app_label ... который может генерировать операторы SQL для сброса последовательностей для заданных имен приложений

Ссылка: https://docs.djangoproject.com/en/dev/ref/django-admin/

Например, эти операторы SQL были сгенерированы manage.py sqlsequencereset my_app_in_my_project:

BEGIN;
SELECT setval(pg_get_serial_sequence('"my_project_aaa"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_aaa";
SELECT setval(pg_get_serial_sequence('"my_project_bbb"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_bbb";
SELECT setval(pg_get_serial_sequence('"my_project_ccc"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "my_project_ccc";
COMMIT;
Источник

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

CREATE OR REPLACE FUNCTION set_serial_id_seq()
RETURNS trigger AS
$BODY$
  BEGIN
   EXECUTE (FORMAT('SELECT setval(''%s_%s_seq'', (SELECT MAX(%s) from %s));',
   TG_TABLE_NAME,
   TG_ARGV[0],
   TG_ARGV[0],
   TG_TABLE_NAME));
    RETURN OLD;
   END;
$BODY$
LANGUAGE plpgsql;  

CREATE TRIGGER set_mytable_id_seq
AFTER INSERT OR UPDATE OR DELETE
ON mytable
FOR EACH STATEMENT
  EXECUTE PROCEDURE  set_serial_id_seq('mytable_id');

Функцию можно повторно использовать для нескольких таблиц. Замени "mytable" на интересующую таблицу.

Для получения дополнительной информации о триггерах:

https://www.postgresql.org/docs/9.1/plpgsql-trigger.html

https://www.postgresql.org/docs/9.1/sql-createtrigger.html

Источник

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

https://www.postgresql.org/docs/15/sql-createtable.html

PostgreSQL: серийный номер против идентичности

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial

Источник
Другие вопросы по тегам