PostgreSQL™ assure les contraintes d'unicité SQL en utilisant des index d'unicité, qui sont des index qui refusent les entrées multiples à clés identiques. Une méthode d'accès qui supporte cette fonctionnalité initialise pg_am.amcanunique à true. (À ce jour, seul B-tree le supporte).
Du fait de MVCC, il est toujours nécessaire de permettre à des entrées dupliquées d'exister physiquement dans un index : les entrées peuvent faire référence à des versions successives d'une même ligne logique. Le comportement qu'il est réellement souhaitable d'assurer est qu'aucune image MVCC n'inclut deux lignes avec les mêmes clés d'index. Cela se résume aux cas suivants, qu'il est nécessaire de vérifier à l'insertion d'une nouvelle ligne dans un index d'unicité :
si une ligne valide conflictuelle a été supprimée par la transaction courante, pas de problème. (En particulier, comme un UPDATE supprime toujours l'ancienne version de la ligne avant d'insérer la nouvelle version, cela permet un UPDATE sur une ligne sans changer la clé) ;
si une ligne conflictuelle a été insérée par une transaction non encore validée, l'inséreur potentiel doit attendre de voir si la transaction est validée. Si la transaction est annulée, alors il n'y a pas de conflit. Si la transaction est validée sans que la ligne conflictuelle soit supprimée, il y a violation de la contrainte d'unicité. (En pratique, on attend que l'autre transaction finisse et le contrôle de visibilité est effectué à nouveau dans son intégralité) ;
de façon similaire, si une ligne valide conflictuelle est supprimée par une transaction non encore validée, l'inserant potentiel doit attendre la validation ou l'annulation de cette transaction et recommencer le test.
De plus, immédiatement avant de lever une violation d'unicité en fonction des règles ci-dessus, la méthode d'accès doit revérifier l'état de la ligne en cours d'insertion. Si elle est validée tout en étant morte, alors aucune erreur ne survient. (Ce cas ne peut pas survenir lors du scénario ordinaire d'insertion d'une ligne tout juste créée par la transaction en cours. Cela peut néanmoins arriver lors d'un CREATE UNIQUE INDEX CONCURRENTLY.)
La méthode d'accès à l'index doit appliquer elle-même ces tests, ce qui signifie qu'elle doit accéder à l'en-tête pour vérifier le statut de validation de toute ligne présentée avec une clé dupliquée au regard du contenu de l'index. C'est sans aucun doute moche et non modulaire, mais cela permet d'éviter un travail redondant : si un test séparé est effectué, alors la recherche d'une ligne conflictuelle dans l'index est en grande partie répétée lors de la recherche d'une place pour insérer l'entrée d'index de la nouvelle ligne. Qui plus, est, il n'y a pas de façon triviale d'éviter les conflits, sauf si la recherche de conflit est partie intégrante de l'insertion de la nouvelle entrée d'index.
Si la contrainte unique est déferrable, il y a une complication supplémentaire : nous devons être capable d'insérer une entrée d'index pour une nouvelle ligne mais de déferrer toute erreur de violation de l'unicité jusqu'à la fin de l'instruction, voire même après. Pour éviter des recherches répétées et inutiles de l'index, la méthode d'accès de l'index doit faire une vérification préliminaire d'unicité lors de l'insertion initiale. Si cela montre qu'il n'y a pas de conflit avec une ligne visible, nous avons terminé. Sinon, nous devons planifier une nouvelle vérification quand il sera temps de forcer la contrainte. Si, au moment de la nouvelle vérification, la ligne insérée et d'autres lignes de la même clé sont vivantes, alors l'erreur doit être reportée. (Notez que, dans ce contexte, « vivant » signifie réellement « toute ligne dans la chaine HOT de l'entrée de l'index est vivante ».) Pour implanter ceci, la fonction aminsert reçoit un paramètre checkUnique qui peut avoir une des valeurs suivantes :
UNIQUE_CHECK_NO indicates that no uniqueness checking should be done (this is not a unique index).
UNIQUE_CHECK_YES indique qu'il s'agit d'un index unique non déferrable et la vérification de l'unicité doit se faire immédiatement, comme décrit ci-dessus.
UNIQUE_CHECK_PARTIAL indique que la contrainte unique est déferrable. PostgreSQL™ utilisera ce mode pour insérer l'entrée d'index de chaque ligne. La méthode d'accès doit autoriser les entrées dupliquées dans l'index et rapporter tout dupliquat potentiel en renvoyant FALSE à partir de aminsert. Pour chaque ligne pour laquelle FALSE est renvoyé, une revérification déferrée sera planifiée.
La méthode d'accès doit identifier toute ligne qui pourrait violer la contrainte unique, mais rapporter des faux positifs n'est pas une erreur. Cela permet de faire la vérification sans attendre la fin des autres transactions ; les conflits rapportés ici ne sont pas traités comme des erreurs, et seront revérifiés plus tard, à un moment où ils ne seront peut-être plus en conflit.
UNIQUE_CHECK_EXISTING indique que c'est une revérification déferrée d'une ligne qui a été rapportée comme en violation potentielle d'unicité. Bien que cela soit implanté par un appel à aminsert, la méthode d'accès ne doit pas insérer une nouvelle entrée d'index dans ce cas. L'entrée d'index est déjà présente. À la place, la méthode d'accès doit vérifier s'il existe une autre entrée d'index vivante. Si c'est le cas et que la ligne cible est toujours vivante, elle doit rapporter une erreur.
Il est recommendé que, dans un appel à UNIQUE_CHECK_EXISTING, la méthode d'accès vérifie en plus que la ligne cible ait réellement une entrée existante dans l'index et de rapporter une erreur si ce n'est pas le cas. C'est une bonne idée car les valeurs de la ligne d'index passées à aminsert auront été recalculées. Si la définition de l'index implique des fonctions qui ne sont pas vraiment immutables, nous pourrions vérifier la mauvaise aire de l'index. Vérifier que la ligne cible est trouvée dans la revérification permet de s'assurer que nous recherchons les mêmes valeurs de la ligne comme elles ont été utilisées lors de l'insertion originale.
Considérations sur le
verrouillage d'index