Forums PostgreSQL.fr

Geo-x

Membre

Modification séquence depuis trigger

Bonjour @ tous,

Je souhaiterais modifier ma séquence (si nécessaire) via un trigger lors de l'insertion d'un objet.

Pour faire clair, je souhaiterais que la valeur actuelle de la séquence, soit équivalente à la valeur Max dans ma table. J'ai donc créé un code dasn le style :

CREATE OR REPLACE FUNCTION update_sequence()
  RETURNS trigger AS
$BODY$
DECLARE max_id integer;
DECLARE seq_id integer;
BEGIN

	SELECT MAX(NEW.id) FROM table INTO max_id;
	SELECT last_value FROM "table_id_seq" INTO seq_id;
	
	IF
		max_id<>seq_id
	THEN
		SELECT setval('table_id_seq', max_id, true);
	END IF;
	RETURN NULL;
  
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION update_sequence() OWNER TO postgres;

CREATE TRIGGER update_sequence
  BEFORE UPDATE
  ON table
  FOR EACH ROW
  EXECUTE PROCEDURE update_sequence();

Mais là, ça fait FLOP...

C'est le SELECT setval que j'ai du mal à gérer depuis un trigger, étant donné qu'il veut systématiquement un RETURN...

Merci

Marc Cousin

Membre

Re : Modification séquence depuis trigger

Ce n'est pas du tout une bonne idée de faire ça à la séquence. Quel est le but de l'opération ? Que se passe-t-il si plusieurs transactions déclenchent le trigger en même temps ? (sachant que les opérations sur les séquences se font hors transaction)

---

Marc.

Geo-x

Membre

Re : Modification séquence depuis trigger

Bonjour Marc,

Sur les champs utilisés dans les séquences nous avons eu des "vide" suite à la suppression d'enregistrements par exemple.

Dans le trigger exposé, ce n'est pas BEFORE UPDATE mais BEFORE INSERT que je voulais mettre bien sûr.

Donc je ne vois pas comment il pourrait y avoir plusieurs insertions au même moment.

Marc Cousin

Membre

Re : Modification séquence depuis trigger

C'est tout à fait possible, avec deux transactions effectuant des insertions en même temps. Avec la méthode indiquée, les deux transactions verront le même max, feront toutes les deux un setval en même temps,

De toutes façons, les séquences ne sont pas faites pour avoir des suites sans trou.

Par ailleurs, SELECT MAX(NEW.id) FROM table INTO max_id; n'a pas trop d'intérêt: dans new, il n'y a qu'un seul enregistrement. Ça va donc retourner la valeur de l'id dans l'enregistrement en cours d'insertion.

Si vous voulez absolument un compteur sans trou, il faut utiliser une table fournissant un compteur. Mais là, c'est les performances qui vont en prendre un coup.

---

Marc.

Geo-x

Membre

Re : Modification séquence depuis trigger

Ce que je voulais dire, c'est qu'au vue de notre configuration il n'est que très peu probable d'avoir deux insertions au même moment même si techniquement c'est possible.

Je creuse donc pour trouver ma solution, je vous tiens au courant si je trouve.

gleu

Administrateur

Re : Modification séquence depuis trigger

Quel est le problème à avoir des trous dans la séquence ?

---

Guillaume.

Geo-x

Membre

Re : Modification séquence depuis trigger

1- Au niveau des trous, ce qui nous pose problème, c'est que les id dépendant des séquences sont utilisés pour créer des identifiants unique et théoriquement, à la suite (puisqu'ils dépendent d'objets géographiques dont nous avons la gestion derrière)

2- Ce que je n'ai pas évoqué, c'est que nous avons eu le cas de séquences ayant des id inférieurs à la valeur maximale de l'id (suite à des INSERT INTO automatiques)

Donc double problème.

gleu

Administrateur

Re : Modification séquence depuis trigger

1- Au niveau des trous, ce qui nous pose problème, c'est que les id dépendant des séquences sont utilisés pour créer des identifiants unique et théoriquement, à la suite (puisqu'ils dépendent d'objets géographiques dont nous avons la gestion derrière)

Qu'il y ait des trous n'empêche pas d'avoir des identifiants uniques. Si vous supprimez un élément, il faut évidemment supprimer les références ailleurs dans la base (qui peut se faire automatiquement)

2- Ce que je n'ai pas évoqué, c'est que nous avons eu le cas de séquences ayant des id inférieurs à la valeur maximale de l'id (suite à des INSERT INTO automatiques)

Ce qui peut arriver avec un setval fait manuellement. C'est à l'application de gérer le cas où un identifiant à déjà été attribué (et donc qu'un INSERT échoue).

---

Guillaume.

rjuju

Administrateur

Re : Modification séquence depuis trigger

1- La suppression d'une valeur dans la table liée à la séquence créera certes un "trou", mais les identifiants seront toujours uniques, mais ça ne devrait pas poser de soucis.

2- C'est le problème usuel avec les séquences, il faut s'assurer que tous les insert se font via la séquence (sans préciser le champ ou en utilisant "default") sans calculer de valeur max+1.

Edit: encore grillé par gleu, et oui effectivement aussi le problème des setval

Dernière modification par rjuju (15/03/2012 14:34:16)

---

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

Geo-x

Membre

Re : Modification séquence depuis trigger

Il n'empêche, dans la mesure ou je souhaiterais lancer une requête de type 'SELECT setval ('table',1054)' dans un trigger,
j'essaie d'utiliser le PERFORM, mais ça n'a pas l'air de fonctionner...

J'ai également essayé le EXECUTE SELECT (...)

mais pas mieux...

Vous savez comment faire?

Merci!

Geo-x

Marc Cousin

Membre

Re : Modification séquence depuis trigger

CREATE OR REPLACE FUNCTION set_seq ( val int )
RETURNS boolean
LANGUAGE plpgsql

AS $function$
BEGIN
PERFORM setval('seq_test',val,true);
return true;
END;

$function$
;
CREATE FUNCTION

marc=# SELECT set_seq(250);
set_seq
---------
t
(1 ligne)

marc=# SELECT * from seq_test ;
sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
seq_test      |        250 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t
(1 ligne)

Donc ce n'est pas un problème de syntaxe, c'est bien PERFORM qu'il faut faire.

Il va nous falloir un autre message d'erreur que «FLOP».

Par ailleurs, est-ce qu'on a déjà signalé que c'était une mauvaise idée, de faire ça avec une séquence ? (oui on est un peu lourds, mais c'est vraiment une mauvaise idée).

---

Marc.

Geo-x

Membre

Re : Modification séquence depuis trigger

Mais non vous n'êtes pas lourd, vous êtes aussi têtus que moi, mais avec la sagesse en plus  ;-)

Après il est vrai que FLOP, n'est pas un message d'erreur trés répandu,je qualifierais le FLOP de terme générique signifiant qu'il ne s'agit pas là de message d'erreur mais juste d'un non fonctionnement, donc, FLOP!

En tout cas je te remercie pour ta fonction, par contre je vois que tu fais un RETURNS en booléen, si on fait un RETURNS en TRIGGER dans ce cas là, on fait un RETURN NEW ?

En tout cas merci pour votre aide, et vos conseils avisés !

Marc Cousin

Membre

Re : Modification séquence depuis trigger

Oui, bien sûr, dans un trigger, on fait return new. Là c'était juste un exemple minimaliste avec la syntaxe qui marche.

Pour le debugging, n'hésite pas à utiliser des RAISE (genre RAISE DEBUG) dans le code du trigger. Ensuite, il suffit de faire un set client_min_messages to debug dans la session, et on a les traces.

---

Marc.

Geo-x

Membre

Re : Modification séquence depuis trigger

En fait, ce que je me rend compte avec cette manipulation, c'est qu'en effectuant un trigger codé avec BEFORE INSERT, la séquence s'incrémente avant le trigger !

Par conséquent je me pose vraiment la question, de l'ordre dans lesquelles s'exécutent les différentes fonctions appelées.

Dernière modification par Geo-x (19/03/2012 17:32:25)