Postgres autoincrement non mis à jour sur des inserts d'ID explicites

Question

J'ai la table suivante dans postgres:

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

Je fais les insertions suivantes:

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

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

Dans les 2 premières insertions, je mentionne explicitement l'id. Cependant, le pointeur d'auto-incrémentation de la table n'est pas mis à jour dans ce cas. Par conséquent, dans la 3ème insertion, je reçois l'erreur:

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

Je n'ai jamais rencontré ce problème avec MySQL dans les moteurs MyISAM et INNODB. Explicite ou non, MySQL met toujours à jour le pointeur auto-incrémenté en fonction de l'id de ligne maximum.

Quelle est la solution de contournement pour ce problème dans postgres? J'en ai besoin car je veux un contrôle plus strict pour certains ids dans ma table.

MISE À JOUR: J'en ai besoin car pour certaines valeurs, j'ai besoin d'avoir un id fixe. Pour les nouvelles entrées, je ne me dérange pas d'en créer de nouvelles.

Je pense qu'il peut être possible d'augmenter manuellement le pointeur nextval à max(id) + 1 chaque fois que j'insère explicitement les ids. Mais je ne suis pas sûr de comment faire cela.

Answer 1

C'est comme ça que ça devrait fonctionner - next_val('test_id_seq') est appelé uniquement lorsque le système a besoin d'une valeur pour cette colonne et que vous n'en avez pas fourni. Si vous fournissez une valeur, aucun appel de ce type n'est effectué et par conséquent la séquence n'est pas "mise à jour".

Vous pourriez contourner cela en définissant manuellement la valeur de la séquence après votre dernière insertion avec des valeurs explicitement fournies :

SELECT setval('test_id_seq', (SELECT MAX(id) from "test"));

Le nom de la séquence est généré automatiquement et est toujours tablename_columnname_seq.

Answer 2

Dans la version récente de Django, ce sujet est discuté dans la documentation :

Django utilise le type de données SERIAL de PostgreSQL pour stocker les clés primaires auto-incrémentées. Une colonne SERIAL est peuplée avec des valeurs d'une séquence qui garde une trace de la prochaine valeur disponible. Affecter manuellement une valeur à un champ auto-incrémenté ne met pas à jour la séquence du champ, ce qui pourrait causer ultérieurement un conflit.

Réf : https://docs.djangoproject.com/en/dev/ref/databases/#manually-specified-autoincrement-pk

Il y a aussi une commande de gestion manage.py sqlsequencereset app_label ... qui est capable de générer des instructions SQL pour réinitialiser les séquences pour le(s) nom(s) d'application donné(s)

Réf : https://docs.djangoproject.com/en/dev/ref/django-admin/#django-admin-sqlsequencereset

Par exemple ces instructions SQL ont été générées par 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;

Answer 3

Il est possible de le faire automatiquement en utilisant un déclencheur. De cette manière, vous êtes sûr que la plus grande valeur est toujours utilisée comme prochaine valeur par défaut.

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');

La fonction peut être réutilisée pour plusieurs tables. Remplacez "mytable" par la table d'intérêt.

Pour plus d'informations concernant les déclencheurs :

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

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

Answer 4

Légèrement en dehors de la question originale, mais il est maintenant recommandé d'utiliser des colonnes IDENTITY plutôt que SERIAL. En plus de quelques autres avantages, le comportement par défaut est de bloquer les insertions manuelles. Ce qui empêche au moins les problèmes en cas d'insertions manuelles accidentelles. Cependant, la question de l'OP nécessite des nombres spécifiques insérés, ce qui n'est pas une solution réelle ici.

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

PostgreSQL: serial vs identity

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