Clever Geek Handbook

Antipatterns PostgreSQL: ID uniques

Très souvent, un développeur doit générer des identifiants uniques pour les enregistrements de table PostgreSQL - à la fois lors de l'insertion d'enregistrements et lors de leur lecture.





Table de comptoir



Il semblerait - qu'est-ce qui est plus facile? Nous y avons installé une assiette séparée - une entrée avec un compteur. Nous avons besoin d'un nouvel identifiant - lu à partir de là pour écrire une nouvelle valeur - faites-le UPDATE...



Ne faites pas ça ! Parce que demain, vous devrez résoudre des problèmes:





Objet SEQUENCE



Pour de telles tâches, PostgreSQL fournit une entité distincte - SEQUENCE. Elle n'est pas transactionnelle , c'est-à-dire qu'elle ne provoque pas de verrous , mais deux transactions "parallèles" recevront certainement des valeurs différentes .



Pour obtenir l'ID suivant d'une séquence, utilisez simplement la fonction nextval: 



SELECT nextval('seq_name'::regclass);


Parfois, vous devez obtenir plusieurs identifiants à la fois - pour l'enregistrement en continu via COPY, par exemple. Utiliser pour cela setval(currval() + N)est fondamentalement faux ! Pour la simple raison qu'entre les appels aux fonctions "interne" ( currval) et "externe" ( setval), une transaction concurrente peut changer la valeur actuelle de la séquence. La bonne façon est d'appeler le nextvalnombre de fois requis: 



SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);


Pseudo série



Il n'est pas très pratique de travailler avec des séquences en mode "manuel". Mais notre tâche typique est d'assurer l'insertion d'un nouvel enregistrement avec un nouvel ID de séquence! Particulièrement dans ce but, PostgreSQL est inventé serial, qui, lors de la génération d'une table, "se développe" en quelque chose comme . Il n'est pas nécessaire de se souvenir du nom de la séquence générée automatiquement liée au champ, il existe une fonction pour cela . La même fonction peut être utilisée dans vos propres substitutions - par exemple, s'il est nécessaire de créer une séquence commune pour plusieurs tables à la fois. Cependant, étant donné que l'utilisation de la séquence n'est pas transactionnelle, si l'identifiant de celle-ci a été reçu par une transaction annulée, la séquence d'identifiants dans les enregistrements de table sauvegardés sera "fuyante"id integer NOT NULL DEFAULT nextval('tbl_id_seq')



pg_get_serial_sequence(table_name, column_name)DEFAULT



...



Colonnes générées



À partir de PostgreSQL 10 , il est possible de déclarer une colonne d'identité ( GENERATED AS IDENTITY) conforme à la norme SQL: 2003. Dans la variante, le GENERATED BY DEFAULTcomportement est équivalent serial, mais avec GENERATED ALWAYStout ce qui est plus intéressant: 



CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);


INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.


Oui, afin d'insérer une valeur spécifique "à travers" une telle colonne, vous devez travailler davantage avec OVERRIDING SYSTEM VALUE: 



INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .


Notez que maintenant nous avons deux valeurs identiques dans la table id = 1- c'est-à-dire que GENERATED n'impose pas de conditions et d'indices UNIQUE supplémentaires , mais est purement une déclaration, ainsi que serial.



En général, sur les versions modernes de PostgreSQL, l' utilisation de serial est déconseillée, avec le remplacement préféré de GENERATED. Sauf, peut-être, la situation de prise en charge des applications multi-versions fonctionnant avec PG inférieur à 10.



UUID généré



Tout va bien tant que vous travaillez dans une seule instance de base de données. Mais lorsqu'il y en a plusieurs, il n'y a pas de moyen adéquat de synchroniser les séquences (cependant, cela ne vous empêche pas de les synchroniser «mal» , si vous le souhaitez vraiment). C'est là que le type UUIDet les fonctions pour générer des valeurs pour lui viennent à la rescousse . Je l'utilise généralement uuid_generate_v4()comme le plus "occasionnel".



Champs système masqués



tableoid / ctid



Parfois, lors de l'extraction d'enregistrements à partir d'une table, vous devez d'une manière ou d'une autre adresser un enregistrement "physique" spécifique, ou trouver à partir de quelle section particulière un enregistrement particulier a été obtenu lors de l'accès à la table "parent" en utilisant l' héritage .



Dans ce cas, les champs système cachés présents dans chaque enregistrement nous aideront :



  • tableoidstocke le oid-id de la table - c'est-à-dire tableoid::regclass::textdonne le nom d'une section de table particulière
  • ctid - adresse "physique" de l'enregistrement au format (<>,<>)


Par exemple, ctidil peut être utilisé pour des opérations avec une table sans clé primaire , mais tableoidpour l'implémentation de certains types de clés étrangères.



oid



Il était possible de déclarer jusqu'à 11 PostgreSQL lors de la création de la table attributaire WITH OIDS: 



CREATE TABLE tbl(id serial) WITH OIDS;


Chaque entrée de cette table reçoit un champ caché supplémentaire oidavec une valeur globalement unique dans la base de données - comme il était organisé pour les tables système comme pg_class, pg_namespace...



Lorsque vous insérez un enregistrement dans une table, la valeur générée est renvoyée immédiatement au résultat de la requête: 



INSERT INTO tbl(id) VALUES(DEFAULT);


  :   OID 16400   11 .


Un tel champ est invisible pour une requête de table "normale": 



SELECT * FROM tbl;


id
--
 1


Comme les autres champs système, il doit être demandé explicitement: 



SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;


tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1


Certes, la valeur oidn'est que de 32 bits , il est donc très facile d'obtenir un débordement, après quoi oidil ne sera même pas possible de créer une table (il en faut une nouvelle !). Par conséquent, depuis PostgreSQL 12, il WITH OIDSn'est plus pris en charge .



Heure "juste" clock_timestamp



Parfois, lors d'une longue exécution d'une requête ou d'une procédure, vous souhaitez lier l'heure "actuelle" à l'enregistrement. L'échec attend quiconque essaie d'utiliser la fonction pour ce faire now()- il renverra la même valeur tout au long de la transaction .



Pour obtenir l'heure «maintenant», il y a une fonction clock_timestamp()(et un autre groupe de ses frères). La différence entre le comportement de ces fonctions peut être vue sur l'exemple d'une requête simple: 



SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);


              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03


More articles:


All Articles