Vous n'êtes pas identifié(e).

Pages : 1

#1 30/09/2011 14:13:26

K-LiBR3
Membre

Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Bonjour,

J'aimerai poser une question qui vous paraitra peut être un peu simple mais qui a beaucoup d'importance pour moi.
J'ai toujours créer mes id de table différemment pendant mon apprentissage de postgresql, mais maintenant, je voudrai savoir :

Quel est la meilleur manière de créer un id de table ?

avant que vous ne répondiez, je vais expliquer ma façon de voir les choses :
je créer mon id de cette manière :

CREATE TABLE firstLevel
(
    id bigserial primary key, -- ou serial peu importe
    nom varchar(255) NOT NULL
);


Ensuite, pour insérer un enregistrement, 2 solutions :

- soit je fait :
INSERT INTO firstLevel(nom) VALUES('blabla');
Problème : il prend pas toujours le dernier id, en fait si par exemple j'ai 3 enregistrements, et que je supprime le 3eme, que ensuite j'en rajoute un de cette manière, il va automatiquement prendre l'id 4 --> il y aura donc un trou à l'id 3. Pire : j'en suis pas sur mais je crois qu'un fois il m'a ajouté mon enregistrement en milieu de table, dans un trou (je ne sait plus dans quel contexte j'étais) alors que justement je veux respecté un certain ordre !

- donc je créer un algo en php qui me donne le dernier id et j’insère de cette manière :
INSERT INTO firstLevel(id, nom) VALUES(monIdPHP, 'blabla'); -- Mauvais niveau optimisation !

Pourrait on créer un id d'une meilleur manière ? peut être un "integer NOT NULL" ou je ne sais pas ? Cela reviendra au même par rapport au dernier id ?


J’espère vous avoir bien exposé ma problématique, merci d'avance pour vos réponses !

Dernière modification par K-LiBR3 (30/09/2011 14:16:48)

Hors ligne

#2 30/09/2011 15:49:16

gleu
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Le meilleur moyen est d'utiliser serial ou bigserial. Certaines valeurs auront l'air d'être sautées, c'est normal dans le fonctionnement de la séquence. Tout autre moyen ralentira forcément l'ajout de lignes (que ce soit par un trigger, par un code applicatif, etc.) et ne sera pas forcément exempt de bugs.

Guillaume.

Hors ligne

#3 30/09/2011 16:03:49

flo
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

gleu a écrit :

Certaines valeurs auront l'air d'être sautées, c'est normal dans le fonctionnement de la séquence. Tout autre moyen ralentira forcément l'ajout de lignes (que ce soit par un trigger, par un code applicatif, etc.) et ne sera pas forcément exempt de bugs.

Je vais aller encore plus loin dans ce sens : pourquoi vouloir une série sans trous? Je ne connais guère qu'un cas où cela est nécessaire, et il est rare, c'est celui des numéros de facture (pour des raisons légales). Mais on parle là d'une clé qui a une signification fonctionnelle, ce qui ne semble pas être le cas de ce que vous cherchez à faire.
Pour tous les autres cas, serial ou bigserial.

NB : attention la solution de votre programme en php ne fonctionne pas à cause de la concurrence. 2 éléments de la table peuvent se retrouver avec le même id (enfin je suppose que vous aurez défini l'id comme clé primaire, donc vous aurez sans doute plutôt une erreur de violation de l'unicité)

Hors ligne

#4 30/09/2011 18:00:31

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

flo a écrit :

Je vais aller encore plus loin dans ce sens : pourquoi vouloir une série sans trous?

En fait j'ai une table qui pourrai potentiellement être très grande et du coup je ne pourrai plus rien entré au bout d'un moment à cause d'un id trop grand pour être stocké sur 32 bits ou autre (je dis ça mais je suis conscient que il y a peu de chance que ça arrive, c'est juste histoire de faire quelque chose de propre).


flo a écrit :

NB : attention la solution de votre programme en php ne fonctionne pas à cause de la concurrence. 2 éléments de la table peuvent se retrouver avec le même id (enfin je suppose que vous aurez défini l'id comme clé primaire, donc vous aurez sans doute plutôt une erreur de violation de l'unicité)

Je n'ai pas très bien compris se que tu a essayé de me dire, mais en gros je trouve le plus grand id avec des requêtes php, j'ajoute 1, et j'envoie mon insert avec ce nouvel id. Donc ça marche !



Merci pour vos réponse, alors je résume, en gros il est préférable d'utiliser un bigserial plutôt qu'un integer,
et comme il ne va pas toujours à l’élément tout à fait en dernier, je suis obligé de faire mon script php si vraiment je veux bien placé mon enregistrement comme ça ?

autre question : c'est mieux de mettre "id bigserial NOT NULL primary key" ?

Dernière modification par K-LiBR3 (30/09/2011 18:01:02)

Hors ligne

#5 30/09/2011 18:09:03

Marc Cousin
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

En fait j'ai une table qui pourrai potentiellement être très grande et du coup je ne pourrai plus rien entré au bout d'un moment à cause d'un id trop grand pour être stocké sur 32 bits ou autre (je dis ça mais je suis conscient que il y a peu de chance que ça arrive, c'est juste histoire de faire quelque chose de propre).

Serial est un int signé, donc environ 2 milliards de numéros disponibles.
Bigserian, c'est un bigint signé, donc 9223372036854775807 numéros disponibles. Ça laisse le temps de voir venir… quelques dizaines de millions d'années à raison de 1000 numéros à la seconde, je crois.

Je n'ai pas très bien compris se que tu a essayé de me dire, mais en gros je trouve le plus grand id avec des requêtes php, j'ajoute 1, et j'envoie mon insert avec ce nouvel id. Donc ça marche !

Sauf si deux sessions le font au même moment (ça arrive fréquemment…): les deux récupèrent le max en même temps (ou du moins la seconde récupère le max avant que la première ait inséré).



Par contre, si, les types serial et bigserial vont toujours au «dernier» nombre libre. Sauf en cas de plusieurs sessions en parallèle qui pourraient ne pas récupérer les nombres à partir de l'objet séquence associé au serial exactement dans le même ordre qu'elles les insèrent. Mais on parle de microsecondes là. Ou alors il faut avoir mis une option cache à la séquence, ce qui n'est pas le cas par défaut.

Marc.

Hors ligne

#6 01/10/2011 15:32:17

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Merci pour ta réponse Marc, effectivement quelques dizaines de millions d'années à raison de 1000 numéros à la seconde ça fait beaucoup !

Alors, j'ai étudier tout ce que vous m'avez dit depuis hier, et il subsiste un vrai problème :
Je suis d'accord sur le fait qu'il faut utiliser des sequences (serial) pour les id, mais le probleme est que, lorsque l'on fait un update d'un id de la table en question, la variable last_value de la sequence n'est pas mis à jour (voir http://dgriessinger.developpez.com/post … equences/).

Je m'explique avec un exemple :

si on fait

CREATE TABLE table1
(
    id serial primary key,
    nom varchar(255) NOT NULL
);
INSERT INTO table1(nom) VALUES('blabla1'); -- id = 1
UPDATE table1 SET id = 2 where id = 1; on met la première ligne à l'id 2
INSERT INTO table1(nom) VALUES('blabla1'); -- erreur, clé dupliqué car last_value de la séquence n'a pas été mis à jour

J'ai donc décider de creer une procedure qui met à jour cette valeur lors d'un update :

CREATE OR REPLACE FUNCTION updateNextvalTable1()
RETURNS TRIGGER
AS $$
DECLARE
	val bigint;
BEGIN
	SELECT max(id) INTO val FROM table1;
	val = val + 1;
	ALTER SEQUENCE table1_id_seq RESTART WITH val; -- erreur
	RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER TriggerUpdateNextvalTable1
AFTER UPDATE
ON table1
FOR EACH ROW EXECUTE PROCEDURE updateNextvalTable1();

Probleme : il ne veut pas que je mette val mais un nombre (et non une variable)




Voici le code complet pour tester :

DROP TRIGGER IF EXISTS TriggerUpdateNextvalTable1 on table1;
DROP TABLE IF EXISTS table1 CASCADE;

CREATE TABLE table1
(
    id serial primary key,
    nom varchar(255) NOT NULL
);


CREATE OR REPLACE FUNCTION updateNextvalTable1()
RETURNS TRIGGER
AS $$
DECLARE
	val bigint;
BEGIN
	SELECT max(id) INTO val FROM table1;
	val = val + 1;
	ALTER SEQUENCE table1_id_seq RESTART WITH val;
	RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER TriggerUpdateNextvalTable1
AFTER UPDATE
ON table1
FOR EACH ROW EXECUTE PROCEDURE updateNextvalTable1();



INSERT INTO table1(nom) VALUES('blabla1');
INSERT INTO table1(nom) VALUES('blabla1');
UPDATE table1 SET id = 3 where id = 2;
INSERT INTO table1(nom) VALUES('blabla1');
SELECT * from table1;

Dernière modification par K-LiBR3 (01/10/2011 15:38:41)

Hors ligne

#7 01/10/2011 18:01:53

rjuju
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Effectivement lors d'une mise à jour de ce champ la séquence n'est pas mise à jour, mais c'est normal car un champ de type serial n'a pas vocation a être mis à jour. Est-ce dans un soucis de "combler les trous" que tu veux faire un update du champ id ? Car comme le disait Marc vu le nombre de valeur possibles ce n'est vraiment pas nécessaire.

Vraiment laisser la séquence gérer toute seule ce champ est la solution la plus simple et la plus sûre.

Si au pire des cas tu ne dois avoir aucun trou dans la séquence, je te conseillerai plutôt des opérations de maintenance planifiées en dehors de la production pour la remettre à niveau.

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

Hors ligne

#8 01/10/2011 18:08:19

gleu
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Quand vous avez une erreur, merci de donner le message de l'erreur... ça facilite les choses, ça nous évite d'avoir à deviner.

Cela étant dit, votre fonction devrait mieux fonctionner ainsi :

CREATE OR REPLACE FUNCTION updateNextvalTable1()
RETURNS TRIGGER
AS $$
DECLARE
    val bigint;
BEGIN
    SELECT max(id) INTO val FROM table1;
    val = val + 1;
    EXECUTE 'ALTER SEQUENCE table1_id_seq RESTART WITH ' || val;
    RETURN NEW;
END; $$ language 'plpgsql';

Guillaume.

Hors ligne

#9 01/10/2011 18:10:31

gleu
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

rjuju, le problème n'est pas là. Il met à jour une ligne en plaçant la prochaine valeur de la séquence (ce qui est un problème tout à fait possible. Donc le trigger aide à se protéger de ce cas. Cela étant dit, cela ne le protège pas du gars qui va insérer une valeur correspondant à une valeur future de la séquence.

Guillaume.

Hors ligne

#10 01/10/2011 18:41:37

rjuju
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Oui, c'est vrai que je n'ai pas répondu à la question désolé, mais sauf cas exceptionnel je pense qu'il est mieux de garder le fonctionnement de base des séquences et de toucher le moins possible aux champs liés, et c'est en ce sens que j'intervenais.

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

Hors ligne

#11 01/10/2011 18:42:53

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

rjuju a écrit :

Est-ce dans un soucis de "combler les trous" que tu veux faire un update du champ id ? Car comme le disait Marc vu le nombre de valeur possibles ce n'est vraiment pas nécessaire.

En fait pas vraiment, j'ai pris conscience que "combler les trous" ne servait à rien, par contre ce qu'il m'arrive de faire assez souvent, c'est de modifier l'ordre des lignes. Exemple :

ligneA d'id 1
ligneB d'id 2
ligneC d'id 3

Je veux mettre ligneB en dernière position, alors j'obtiendrai ça :

ligneA d'id 1
ligneC d'id 3
ligneB d'id 4 -- erreur lors du prochain insert car last_value n'est pas à jour

Peut être me conseillera tu de mettre un champ "position integer" mais je trouve ça moins propre !



gleu merci beaucoup ça marche et désolé pour le message d'erreur !
Peut on optimiser les choses en faisant une règle ?

DROP RULE IF EXISTS updateNextvalTable1 ON table1;
CREATE RULE updateNextvalTable1
AS ON UPDATE
TO table1
DO ALSO
(
	ALTER SEQUENCE table1_id_seq RESTART WITH (SELECT max(id) INTO val FROM table1) + 1;
);

Message d'erreur:

ERREUR:  erreur de syntaxe sur ou près de « ALTER »
LINE 34:  ALTER SEQUENCE table1_id_seq RESTART WITH (SELECT max(id) I...

Je crois qu'on ne peut pas mettre ce type de requetes, en plus il risque de ne pas aimé mon +1, je me trompe ?

Hors ligne

#12 01/10/2011 18:54:09

gleu
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

C'est la sous-requête qu'il n'aime pas. RESTART WITH prend un entier et non pas une expression ou une sous-requête. Bref, pas possible.

Guillaume.

Hors ligne

#13 01/10/2011 19:01:51

rjuju
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

K-LiBR3 a écrit :

En fait pas vraiment, j'ai pris conscience que "combler les trous" ne servait à rien, par contre ce qu'il m'arrive de faire assez souvent, c'est de modifier l'ordre des lignes. Exemple :

ligneA d'id 1
ligneB d'id 2
ligneC d'id 3

Je veux mettre ligneB en dernière position, alors j'obtiendrai ça :

ligneA d'id 1
ligneC d'id 3
ligneB d'id 4 -- erreur lors du prochain insert car last_value n'est pas à jour

Peut être me conseillera tu de mettre un champ "position integer" mais je trouve ça moins propre !

Effectivement mon premier réflexe serait de te conseiller un autre champ pour gérer la position, pour ne pas mélanger un identifiant de ligne avec une valeur fonctionnelle, ou éviter des problème de propagation de clé étrangères (ON UPDATE CASCADE). A tous hasard est-ce un choix d'ordre subjectif ou y aurait-il une autre zone sur laquelle tu pourrais exercer un tri (date, valeur ...) ?

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

Hors ligne

#14 01/10/2011 19:03:18

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

ok merci gleu, une dernière question et je te laisse x)

Ce que je voudrais faire, c'est juste savoir dans ma procédure si l'utilisateur écrit
ça : INSERT INTO table1(id,nom) VALUES(2,'blabla1'); -- à ce moment là je devrai mettre à jour last_value
ou ça : INSERT INTO table1(nom) VALUES('blabla1'); -- le serial fait le travail

Cette procédure ressemblerai à ça :

CREATE OR REPLACE FUNCTION updateNextvalTable1()
RETURNS TRIGGER
AS $$
DECLARE
	val integer;
BEGIN
	if(NEW.id > 0) then
		SELECT max(id) INTO val FROM table1;
		val = val + 1;
		EXECUTE 'ALTER SEQUENCE table1_id_seq RESTART WITH ' || val;
	end if;
	RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER TriggerUpdateNextvalTable1
AFTER INSERT
ON table1
FOR EACH ROW EXECUTE PROCEDURE updateNextvalTable1();

problème : mon if fonctionne dans tout les cas ! Donc last_value se mettra à jour alors qu'il est deja à jour si je tape mon insert sans precisé l'id !

Hors ligne

#15 01/10/2011 19:10:33

rjuju
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

K-LiBR3 a écrit :

ok merci gleu, une dernière question et je te laisse x)

Ce que je voudrais faire, c'est juste savoir dans ma procédure si l'utilisateur écrit
ça : INSERT INTO table1(id,nom) VALUES(2,'blabla1'); -- à ce moment là je devrai mettre à jour last_value
ou ça : INSERT INTO table1(nom) VALUES('blabla1'); -- le serial fait le travail

Cette procédure ressemblerai à ça :

CREATE OR REPLACE FUNCTION updateNextvalTable1()
RETURNS TRIGGER
AS $$
DECLARE
	val integer;
BEGIN
	if(NEW.id > 0) then
		SELECT max(id) INTO val FROM table1;
		val = val + 1;
		EXECUTE 'ALTER SEQUENCE table1_id_seq RESTART WITH ' || val;
	end if;
	RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER TriggerUpdateNextvalTable1
AFTER INSERT
ON table1
FOR EACH ROW EXECUTE PROCEDURE updateNextvalTable1();

problème : mon if fonctionne dans tout les cas ! Donc last_value se mettra à jour alors qu'il est deja à jour si je tape mon insert sans precisé l'id !

Le probème vient que ton trigger se lance après l'insert, c'est-à-dire une fois que la séquence a fait son travail, et il est alors impossible de différencier les 2 utilisations. Peut-être qu'en faisant le trigger BEFORE INSERT tu peux différencier les 2 cas mais je n'ai jamais testé et je ne peux pas te répondre, même si j'aurais tendance à penser que le new.ID serait à NULL et non pas à 0. Peut-être que Guillaume aura une réponse sure.

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

Hors ligne

#16 01/10/2011 19:13:49

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

rjuju a écrit :

Effectivement mon premier réflexe serait de te conseiller un autre champ pour gérer la position, pour ne pas mélanger un identifiant de ligne avec une valeur fonctionnelle, ou éviter des problème de propagation de clé étrangères (ON UPDATE CASCADE). A tous hasard est-ce un choix d'ordre subjectif ou y aurait-il une autre zone sur laquelle tu pourrais exercer un tri (date, valeur ...) ?

Ouai tu a raison je devrai prendre un champ position, la prochaine fois je le fera.
Je ne l'ai pas fait car dans mon php, j'envoie des pages html avec les id en id de balise, donc plus facile pour réutiliser l'id de l'élément. Tu me dira que je peux designer mon élément également grâce à la position du fait que ce champs peut être une clef candidate, et oui tu a raison donc j'y penserai la prochaine fois xD

Et non il n'y a pas d'autre zone sur laquelle je pourrai exercer un tri (je n'ai pas bien compris où tu voulais en venir en fait dsl !).

Hors ligne

#17 01/10/2011 19:16:37

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

rjuju a écrit :

Le probème vient que ton trigger se lance après l'insert, c'est-à-dire une fois que la séquence a fait son travail, et il est alors impossible de différencier les 2 utilisations. Peut-être qu'en faisant le trigger BEFORE INSERT tu peux différencier les 2 cas mais je n'ai jamais testé et je ne peux pas te répondre, même si j'aurais tendance à penser que le new.ID serait à NULL et non pas à 0. Peut-être que Guillaume aura une réponse sure.

Ce n'est vraiment pas bête ce que tu dis, d'ailleurs j'y avait pensé mais je ne me rappel plus ce qui s'est passé, je réessaye et je te tiens au courant.

Hors ligne

#18 01/10/2011 19:23:12

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Malheureusement le if ne fonctionne pas :

CREATE OR REPLACE FUNCTION updateNextvalTable1()
RETURNS TRIGGER
AS $$
DECLARE
	val integer;
BEGIN
	if(NEW.id = NULL) then
		raise exception 'Vous navez pas mentionné d id dans votre INSERT';
	else
		raise exception 'Vous avez mentionné un id dans votre INSERT';
	end if;
	RETURN NEW;
END; $$ language 'plpgsql';

CREATE TRIGGER TriggerUpdateNextvalTable1
BEFORE INSERT
ON table1
FOR EACH ROW EXECUTE PROCEDURE updateNextvalTable1();

il me renvoie toujours : Vous avez mentionné un id dans votre INSERT.


Il faudrait une instruction du genre :

if(string(NEW.numero1) = "id") then ....

mais je doute que ça existe !

Dernière modification par K-LiBR3 (01/10/2011 19:25:28)

Hors ligne

#19 01/10/2011 19:35:14

rjuju
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

Je viens de tester et la séquence fait son travail avant même le déclenchement du trigger BEFORE INSERT.
Si tu veux vraiment continuer sur ce principe, il te faut donc faire un trigger AFTER INSERT comme tu avais fait précédemment (qui se lancera alors même si la séquence a été utilisée), ou alors coder ton application de manière a ce que seule la séquence soit utilisée en INSERT, et gérer de façon propre tes UPDATE pour qu'il n'y ait pas de soucis.

Dernière modification par rjuju (01/10/2011 19:39:28)

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

Hors ligne

#20 01/10/2011 19:47:56

gleu
Administrateur

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

À mon avis, le meilleur moyen de gérer ça est applicatif. Le changement d'id ne doit pas être fréquent, donc à chaque fois que cela survient, il faut aussi faire un changement de la valeur de la séquence (si cela se révèle nécessaire).

Guillaume.

Hors ligne

#21 02/10/2011 02:32:29

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

J'ai bien étudié tout ce que vous m'avez dit et j'ai rédiger un petit article histoire de pas oublier :

http://hayj.free.fr/index.php?id_slvl=35

Dsl je ne me suis pas encore très bien relu donc il doit y avoir des fautes !

Hors ligne

#22 02/10/2011 09:08:21

cedric
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

bonjour,
je n'ai pas lu tout le fil de discussion. Toutefois, en réponse à l'article:
Construire toute un logique de mise à jour de la valeur d'une séquence me semble complètement inutile et montre le problème de normalisation du schéma SQL prévu.
Si le but est de changer le tri des données, on utilise une colonne spécifique.

Dernière modification par cedric (02/10/2011 09:09:47)

Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Hors ligne

#23 02/10/2011 14:31:11

K-LiBR3
Membre

Re : Optimisation d'un id de table et l'insertion d'une nouvelle ligne

cedric a écrit :

bonjour,
je n'ai pas lu tout le fil de discussion. Toutefois, en réponse à l'article:
Construire toute un logique de mise à jour de la valeur d'une séquence me semble complètement inutile et montre le problème de normalisation du schéma SQL prévu.
Si le but est de changer le tri des données, on utilise une colonne spécifique.

Ouep je suis tout à fait d'accord ! Mais bon au cas où, sa peut servir, moi par exemple j'ai un site déjà tout fait et je suis obligé de passé par là wink

Hors ligne

Pages : 1

Pied de page des forums

Propulsé par FluxBB