Forums PostgreSQL.fr

gilou974

Membre

Comment récupérer les 'trous' dans une séquence

Bonjour à tous,

je voudrais savoir s'il existe un moyen de récupérer les id non utilisés correspondant à une séquence :

j'ai une table pièce_comptable avec comme clé primaire id_pct qui correspond à la séquence piece_comptable_id_seq.

Dans ma table ligne_ecriture je retrouve mon id_pct comme clé étrangère, la contrainte étant définie ainsi :

      CONSTRAINT fk_ligne_ec_rlsh_129_piece_co FOREIGN KEY (id_pct)
      REFERENCES piece_comptable (id_pct) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,.

A aujourd'hui je me retrouve avec des bonds de 4500 lignes dans la table piece_comptable.

Existe-t-il un moyen en sql de refaire une numérotation sans "trous"... dans les deux tables ?

Si quelqu'un a une idée, je le remercie par avance.

Marc Cousin

Membre

Re : Comment récupérer les 'trous' dans une séquence

Bonjour,

C'est impossible avec les séquences, ces objets ne sont pas faits pour cela.

Je m'explique:

Ce que vous voulez, c'est générer des identifiants comptables (des factures par exemple), qui n'ont pas de 'trou' entre les identifiants.

Les séquences quant à elles sont des objets dont le seul but est de vous fournir des identifiants uniques, le plus rapidement possible, et sans blocage entre les sessions.

Le 'sans blocage entre les sessions' est ce qui pose problème : il est impossible de garantir l'absence de trou sans que les sessions ne soient obligées d'attendre la fin de la génération de la facture précédente (pensez 300 utilisateurs saisissant en même temps une facture), pour savoir si elle a effectivement été enregistrée.

Il est possible de renuméroter à posteriori, en SQL, si c'est ce que vous souhaitez faire. Mais tant que vous utiliserez une séquence pour le numéro de facture, vous retomberez dans ce problème.

Pour faire une numérotation sans trou, dès le départ, vous n'avez pas beaucoup de solutions :

- Avoir une table générant le numéro de facture, et l'incrémenter manuellement, en prenant bien soin de verrouiller l'enregistrement tant que la facture n'est pas générée. Cela vous garantira que personne d'autre n'essayera de vous prendre votre numéro de facture. C'est la version verrouillage pessimiste.
- Générer des factures en prenant le max d'identifiant de la table facture, et en l'incrémentant de 1. Dans ce cas, il faut être capable de supporter une erreur au moment où vous voudrez insérer (puisque quelqu'un d'aurte aura fait la même chose). C'est la version verrouillage optimiste.

---

Marc.

gilou974

Membre

Re : Comment récupérer les 'trous' dans une séquence

Merci pour votre réponse,

oui effectivement j'avais bien assimilé le fait qu'avec les séquences je ne pouvais éviter ce problème de trous.

Par contre, ce n'est pas vraiment génant pour moi, je m'explique : c'est une fonction que j'ai mise en place qui s'exécute sur un cron qui me crée ces trous mais je vais la modifier en fait.

"Il est possible de renuméroter à posteriori, en SQL, si c'est ce que vous souhaitez faire."

Oui c'est mon souci pour l'instant; savoir comment je pourrais renuméroter les lignes dans les deux tables après quoi je réinitialiserai ma séquence avec un setval(max(id_pct) ).

Marc Cousin

Membre

Re : Comment récupérer les 'trous' dans une séquence

Ok. La difficulté provient de la foreign key, qui n'est pas déclarée comme deferrable. On ne peut donc pas corriger les ids des tables tel quel.

Le plus simple est évidemment de dropper la contrainte, faire les modifs et réactiver la contrainte. Est-ce que c'est possible ? (on peut faire l'ensemble dans une transaction, et donc prendre un verrou sur les tables le temps de la transaction).

L'idéal aurait été que la contrainte ait été déclarée comme deferrable (http://docs.postgresql.fr/8.4/sql-createtable.html)

---

Marc.

gilou974

Membre

Re : Comment récupérer les 'trous' dans une séquence

Merci de nouveau pour votre réponse.

Alors j'avoue déjà dans un premier temps ne pas connaître le terme déferrable ;-) mais je vais voir à quoi il correspond.

A part celà, oui je pourrais faire un DROP de la contrainte une fois que les utilisateurs s'en vont l'informatique profite pour ce genre de choses ...

Je vais jeter un oeil sur le lien que vous m'avez mis, merci. (je ne suis toujours pas en 8.4 mais j'espère y passer avant la fin de l'année - A moins que ce soit un saut à la 9 ???? )

MERCI encore à vous.

Marc Cousin

Membre

Re : Comment récupérer les 'trous' dans une séquence

Sinon, si vous avez un assez faible volume de données, vous pouvez tenter un :
BEGIN;
DROP  de la contrainte
série d'updates
CREATE de la contrainte
COMMIT

Si le script ne dure que quelques secondes, c'est probablement tout à fait jouable.

---

Marc.

gilou974

Membre

Re : Comment récupérer les 'trous' dans une séquence

Je vais tenter çà et vous donne mon retour.

Nbre de lignes :

select count(id_lec ) from ligne_ecriture = 333270

Merci