Rollback d'une séquence [Résolu]

[benazur]

Bonjour,
D'après la documentation, lorsque l'application annule une transaction, les numéros de séquences générées ne sont pas annulées. Ce qui crée des trous dans la liste des numéros. Existe-t-il une option dans pg qui oblige le rollback aussi des séquences et retourne le système à son état initial ?
Merci

[CetTer]

Bonjour,

Oui en effet, cette limitation n'est pas liée au système, mais à la règle ACID.
Si deux transactions ont lieu en parallèle sur la même table, et qu'une des deux continue après le rollback de celle qui a échoué, rien ne garantit l'unicité des identifiants insérés par le processus déjà lancé, et le fait de réinitialiser la séquence risquerait de faire planter toutes les insertions ultérieures à leur tour.

Par contre vous pouvez manuellement réinitialiser la valeur initiale de la séquence sur la valeur maximale d'une colonne (+1), comme le montre la doc, mais il faut être sûr que ni la séquence ni la table ne sont accédées en écriture au moment de le faire:

http://www.postgresql.org/docs/8.1/s...esequence.html

Code :

1
2
3
4
 
BEGIN;
SELECT SETVAL('serial', max(id)) FROM distributors;
END;

En fait si vous voulez à tout prix des listes continues et que le système est très sollicité en écriture, il faut peut-être mieux séparer sur deux colonnes les valeurs des clés primaires et étrangères et laisser les séquences s'en occuper, et les valeurs continues qui permettent d'indexer "visuellement" les données, qui, peuvent être gérés dans des requêtes de type

Code :

1
2
 
INSERT INTO XXX (cle_primaire, cle_visuelle ...) SELECT NEXTVAL('sequence'), COALESCE(MAX(cle_visuelle)+1,1), ...FROM XXX:;

[benazur]

Merci CetTer pour la réponse.
Peux tu expliciter un peu plus la deuxième solution, je n'ai pas bien compris.
En fait c'est pour une application de facturation, et des trous sont inacceptables.

[CetTer]

Tu peux utliser l'instruction "setval" qui permet de forcer la modification de la valeur courante de la séquence.
http://dgriessinger.developpez.com/p...sql/sequences/

...et lui demander de réinitialiser la séquence sur la dernière valeur encodée dans la table où tu comptes insérer les données (le "MAX(id)" de l'exemple).

Code :

1
2
3
4
5
 
BEGIN;
SELECT SETVAL('serial', max(id)) FROM distributors;
END;
--(je me suis trompé, le "+1" n'a pas lieu d'être dans ma réponse précédente)

Si par exemple la dernière clé primaire "id" de la table "distributors" a la valeur "456", "nextval("serial")" retournera "457" après l'exécution de ce bout de code. Mais il faut être sûr que la séquence n'est pas modifiée à nouveau entre sa réinitialisation et la prochaine insertion, sinon on a à nouveau un trou...

Je n'ai jamais programmé quelque chose de ce type personnellement, mais il est sans doute possible de lancer cette instruction dans un trigger avec l'option DEFERABLE INITIALLY DEFERRABLE pour qu'elle soit exécutée juste une fois, juste avant la fin la transaction. Normalement cela devrait éviter les trous.

Un peu comme dans:
http://stackoverflow.com/questions/8...-in-postgresql
(mais l'exemple lance ici une boucle exécuté autant de fois qu'il y avait de lignes dans la transaction, alors que dans le cas que tu mentionnes la séquence ne devrait être modifiée qu'une fois à la fin de la transaction)

[benazur]

Merci CetTer pour ton aide.

[estofilo]

Si c'est pour réinitialiser la séquence à chaque fois qu'on en a besoin, la séquence n'est plus utile, d'autant plus qu'on perd la propriété de sérialisation du générateur de valeur.
C'est-à-dire que 2 sessions qui font select max(id) au même moment pourront se retrouver avec la même valeur.

Autant utiliser un vrai verrou:

Code :

1
2
3
4
5
BEGIN;
LOCK TABLE facture;
SELECT 1+max(numero_facture) FROM facture INTO variable;
.. créer la facture avec le nouveau numéro
COMMIT;