Forums PostgreSQL.fr

unbewusst

Membre

problème d'insertion avec RETURNING rowid

j'ai une base 'items' où je supprime des lignes, ce qui crée des trous dans la succession de ma séquence rowid.
et je ne parviens pas à m'en dépatouiller.

pourtant, je sélection le max(rowid) et je l'attribue à ma séquence 'items_rowid_seq' si et seulement si last_value de items_rowid_seq est inférieur à max(rowid).

le code PHP :

        $max_rowid=-1;
        $sql="SELECT MAX(rowid) FROM items;";
        $ret=$db->query($sql);
        while($row=$ret->fetch()){
          $max_rowid=$row['max'];
        }
        $last_value=-1;
        $sql="SELECT last_value FROM items_rowid_seq;";
        $ret=$db->query($sql);
        while($row=$ret->fetch()){
          $last_value=$row['last_value'];
        }
        if($last_value<$max_rowid){
          $sql="SELECT setval('items_rowid_seq', max(rowid)) FROM items;";
          $ret=$db->query($sql);
          while($row=$ret->fetch()){
            $last_value=$row['setval'];
          }
          if($last_value<$max_rowid){
            // générer une erreur
          }
        }
        $rowid=-1;
        $sql="INSERT INTO items (ctime, [...], infos) VALUES (
             '".$dat."', '".$dat."',  [...]', '".str2sql(quoteAsAre($_GET["infos"]))."') RETURNING rowid;";
        $ret=$db->query($sql);
	while($row=$ret->fetch()){
          $rowid=$row['rowid'];
	}

Pour PHP, c'est  $ret qui n'est pas un objet, je ne peux donc faire $ret->fetch().

Une idée pour me dépatouiller ?
NB : rowid est de type :
CREATE TABLE items (rowid SERIAL PRIMARY KEY, ...

rjuju

Administrateur

Re : problème d'insertion avec RETURNING rowid

Bonjour.
Sauf rares exceptions, il n'est pas nécessaire de modifier à la main la valeur d'une séquence pour enlever les "trous" qui peuvent survenir à la suite d'un delete dans la table associée. Ce genre de traitement amène de nombreux problèmes à cause des transactions concurrentes.

Le plus simple et le plus sur est de laisser la séquence faire son travail, et de s'assurer que toutes les insertions dans cette table passent par la séquence.

---

Julien.
https://rjuju.github.io/

unbewusst

Membre

Re : problème d'insertion avec RETURNING rowid

Oui, je suis tout à fait d'accord.
Si j'ai manipulé ma 'items_rowid_seq' c'est parce que j'ai cru (?) y voir un problème.
Maintenant, après quelques de dilétion / insertion, ça roule, même avec des trous dans le rowid.
Du coup, je ne pige pas pourquoi ça déconnait avant...
Peut-être parce que ma database était ouverte en CLI ?

flo

Membre

Re : problème d'insertion avec RETURNING rowid

Je ne comprends pas bien : quel problème avez-vous vu sur votre séquence?
Que veut dire que votre database était "ouverte en CLI"?

unbewusst

Membre

Re : problème d'insertion avec RETURNING rowid

Disons que dans la liste des IDs il y avait un trou à 8.
Après une insertion psql avait occupé ce trou, du coup à l'insertion suivante donc ID à 9, il y avait collision d'ID car cet ID était occupé.
Donc j'ai fait un setval de la séquence au max(id) et ça roule.
Mais aussi j'utilisais la base -simultanément- par php, ou ruby ET en CLI (Command Line Interface).
Depuis j'ai nettoyé mon code SQL qui provenait d'une précédente adaptation à SQLite.

Postgres.0

Membre

Re : problème d'insertion avec RETURNING rowid

Pour quoi tu ne fais pas un VACUUM.

unbewusst

Membre

Re : problème d'insertion avec RETURNING rowid

Bonne idée, je vais regarder ça !
Merci !

unbewusst

Membre

Re : problème d'insertion avec RETURNING rowid

Pour quoi tu ne fais pas un VACUUM.

Ben, je viens de le faire :

      $sql='VACUUM VERBOSE ANALYZE items;';
      echo "$sql<br />";
      $ret=$db->exec($sql);
      if($ret){
        while($row=$ret->fetch()){
          print_r($row);
      	  echo "<br />";
        }
      }

J'y ai même ajouté une réindexation :

      $sql='REINDEX TABLE items;';
      echo "$sql<br />";
      $ret=$db->exec($sql);
      if($ret){
        while($row=$ret->fetch()){
          print_r($row);
      	  echo "<br />";
        }
      }

Résultat décevant : rien de neuf, j'ai toujours des trous, mon "rowid" passe de 26 à 40...

rjuju

Administrateur

Re : problème d'insertion avec RETURNING rowid

Un vacuum permettra de réutiliser de l'espace vide dans la table mais ne changera jamais les données de la table.

---

Julien.
https://rjuju.github.io/

flo

Membre

Re : problème d'insertion avec RETURNING rowid

Mais pourquoi donc supprimer les trous? En avez-vous réellement besoin?
Comme dit rjuju, dans 99% des cas, on laisse la séquence faire son travail (elle garantit l'unicité, pas l'absence de trous)
Si on a vraiment besoin de ne pas avoir de trous, on fait autrement (mais attention alors à bien gérer la concurrence, et ne pas oublier la possibilité d'un rollback)

Dernière modification par flo (10/05/2012 10:09:12)

unbewusst

Membre

Re : problème d'insertion avec RETURNING rowid

Mais pourquoi donc supprimer les trous? En avez-vous réellement besoin?
Comme dit rjuju, dans 99% des cas, on laisse la séquence faire son travail (elle garantit l'unicité, pas l'absence de trous)
Si on a vraiment besoin de ne pas avoir de trous, on fait autrement (mais attention alors à bien gérer la concurrence, et ne pas oublier la possibilité d'un rollback)

Non, pas du tout si ce n'est que j'ai le sentiment que la séquence automatique a buggé au moins une fois justement en remplaçant un trou.