Type composite en clé primaire [Résolu]

[genamiga]

Bonjour,

J'ai un type composite que je voudrais utilisé en clé primaire.

Code :

1
2
3
4
5
CREATE TYPE "public"."super_id" AS (
  "annee" "public"."annee",
  "mois" "public"."mois",
  "num" INTEGER
);

Mai j'ai un beau message d'erreur

Code :

1
2
3
4
ERREUR:  le type de données super_id n'a pas de classe d'opérateurs par défaut pour la
méthode d'accès « btree »
HINT:  Vous devez spécifier une classe d'opérateur pour l'index ou définir une
classe d'opérateur par défaut pour le type de données.

Je ne vois pas trop ce que devrais faire ?

Si qlq pourrait m'aidez...

Par ailleurs, "Num" devrait être gerer par une sequence réinitialisée en début d'année. Un trigger ferait-il l'affaire ? là aussi un peu d'aide serait la bien venue...


Merci d'avance.

[genamiga]

Cela me semble inutile mais pour info voici la définition de mes DOMAIN annee et mois...

Code :

1
2
3
4
5
6
7
8
9
CREATE DOMAIN "public"."annee" AS
  smallint NULL;
ALTER DOMAIN "public"."annee"
  ADD CONSTRAINT "annee_chk" CHECK ((VALUE > 999) AND (VALUE <= 9999));
 
CREATE DOMAIN "public"."mois" AS
  smallint NULL;
ALTER DOMAIN "public"."mois"
  ADD CONSTRAINT "mois_chk" CHECK ((VALUE > 0) AND (VALUE <= 12));

Ce type "super_id" sera utilisé comme clé primaire dans différentes tables : ventes, livraisons, commandes... et aussi comme clé étrangère bien-sûr.

Merci d'avance.

[hpalpha]

Bonjour,

en fait pour creer un index sur une clef composite implique que pg doit comprendre comment indexer les données de ce type, je m'explique

comment pg va determiner que : 2008::annee 2::mois 7::int est au plus grand que 2008::annee 12::mois 3::int ?
il faut donc déterminer le comportement de ton type en créant :

CREATE OR REPLACE FUNCTION super_idlt(super_id, super_id) ....
.... pour <
CREATE OR REPLACE FUNCTION super_idgt(super_id, super_id) ....
.... pour >

....

CREATE OR REPLACE FUNCTION btsuper_idcmp(super_id,super_id)
RETURNS super_id AS ....
... pour le btree

...

apres les classes d'operateur :
CREATE OPERATOR CLASS super_id_ops DEFAULT
FOR TYPE super_id USING btree AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 btsuper_idcmp(super_id, super_id),


enfin un truc dans le genre

[genamiga]

Merci bcp pour la réponse rapide...

Mais ça à l'air quand même assez complexe, je vais voir si je ne peux pas gerer tout cela autrement.

[genamiga]

Volià après qlq réflexion j'ai trouvé ue autre methode pour faire ce que je veux...

Au lieu du type composite super_id j'ai testé avec un BIGINT que je construit moi même, je m'explique. Le but est d'avoir un un numéro de vente sous la forme AAAMM999999, par ex 200807000214

Donc une colonne vente_id en BIGINT, une sequence pour le numéro et un TRIGGER BEFORE INSERT qui construit tout ça et qui réinitialise la sequence pour la 1ère vente de l'année.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE FUNCTION "public"."calc_vente_id" () RETURNS TRIGGER AS
$body$
DECLARE
	an_dern_rec BIGINT;
	an BIGINT;
    mois BIGINT;
    nbid INTEGER := 100000; -- nombre d'id max par an
BEGIN
    an := EXTRACT(year FROM now());
    mois := EXTRACT(month FROM now());
 
    SELECT INTO an_dern_rec EXTRACT(year FROM max("date_vente")) FROM ventes;
 
    IF (an_dern_rec IS NULL) OR (an > an_dern_rec) THEN ALTER SEQUENCE vente_id_seq RESTART WITH 1;
	end IF;
 
    NEW.vente_id := (an * nbid * 100) + (mois * nbid) + NEXTVAL('vente_id_seq');
 
    RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

el le trigger

Code :

1
2
3
CREATE TRIGGER "ajout_vente" BEFORE INSERT 
ON "public"."ventes" FOR EACH ROW 
EXECUTE PROCEDURE "public"."calc_vente_id"();

Tout cela fonctionne très bien...mais si l'INSERT échoue la sequence est quand même incrémentée.

J'ai testé avec currval('vente_id_seq')+1 dans le TRIGGER BEFORE INSERT et nextval('vente_id_seq') dans un TRIGGER AFTER INSERT mais ça ne fonctionne pas, au currval erreur "la sequence n'est pas initialisée dans cette sesson".

Plusieures questions :

1. Si je n'utilise pas de sequence mais que j'extrait le numéro de la dernière vente et que je l'incremente dans le TRIGGER BEFORE INSERT, dans une utilsation multi-utilisateur est ce que je n'aurais pas de problème de doublons ?

2.Comment puis-je me faire une fonction independante (calc_id(table) ou calc_id(table, sequence)) du trigger qui prendrait en paramètre la table ou son nom et eventuellement la sequence ou son nom et puis je ferais un trigger différent pour chaque table qui appelerait la fonction avec différents paramètres.

Ou si vous savez une autre idée merci d'avance.

[genamiga]

Voici le résultat de mes recherches...

Je gère tout moi même...pas de séquence parce que si l'INSERT échoue la sequence est quand même incrémentée et que je veux pas de trous dans mes n°... ça fonctionne nickel mais pour les accès concurents...

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DECLARE
	an BIGINT;		-- année courante
	mois BIGINT;		-- mois courant
 
	dern_vent BIGINT := 0;	-- vente_id de la dernière vente    
	an_dern_vent_str TEXT;	-- année de la dernière vente en string
	dern_vent_str TEXT;	-- vente_id de la dernière vente en string    
	an_dern_vent SMALLINT;	-- année de la dernière vente
 
	nbdigit SMALLINT := 5;  -- nombre de digit max pour le numéro de vente
	nbid INTEGER;
BEGIN
	nbid := 10 ^ nbdigit;	-- nombre max pour le numéro de vente    
    an := EXTRACT(year FROM now());
    mois := EXTRACT(month FROM now());
 
    -- on récupére la dernière vente en string pour extraire facilement l'année et le numéro
	SELECT INTO dern_vent_str '' || max("vente_id") FROM ventes;
 
    -- s'il n'y a pas de vente, on ne fait rien, dern_vent == 0
    IF (dern_vent_str IS NOT NULL) THEN     
    	-- extraction de l'année et convertion en SMALLINT pour comparaison avec l'année courante
        an_dern_vent_str := SUBSTRING(dern_vent_str, 1,4);        
        an_dern_vent := CAST (an_dern_vent_str AS SMALLINT);
 
        -- si la dernière vente n'est pas dans l'année courante, on ne fait rien, dern_vent == 0
        IF (an_dern_vent <= an) THEN 
        	-- extraction du nuémro de la dernière vente de l'année et convertion en SMALLINT pour incrémentation
            dern_vent_str := SUBSTRING(dern_vent_str, 7, nbdigit);
        	dern_vent := CAST(dern_vent_str AS BIGINT);
        END IF;
    END IF;        
	-- calcul de vente_id
    NEW.vente_id := (an * nbid * 100) + (mois * nbid) + dern_vent + 1;    
	RETURN NEW;
END;

...pour tester les accès concurents je me suis fais un petit programme(que je lance plusieur fois) en java avec une boucle d'INSERT massif. Mais j'ai dû mettre un

Code sql :

LOCK TABLE ventes IN EXCLUSIVE MODE

au début de ma transaction.

Cela fonctionne exactement comme je veux mais je n'aime pas utilisé le LOCK...pas portable et un peu "trop". D'après la doc de PostgreSQL on devrait pouvoir faire la même chose avec SET TRANSACTION mais je ne vois pas comment...


Si qlq a une idée avec SET TRANSACTION je suis preneur...

Merci d'avance.

[SQLpro]

SET TRANSACTION faite partie de la norme SQL.

Lisez ce que j'ai écrit à ce sujet : http://sqlpro.developpez.com/cours/s...chniques/#L1.3

A +

[rbaraer]

LOCK TABLE en mode EXCLUSIVE est effectivement trop contraignant car tu vas bloquer toutes les modifs sur la table alors que tu ne veux fonctionnellement que bloquer les autres insertions potentielles (qui vont devoir générer aussi un ID).

De plus, MAX est une opération qui peut être coûteuse sur une grande table, même si on peut l'optimiser avec un index.

Bref, je te proposes plutôt une autre solution :

- Crée une autre table ventes_id avec une colonne Annee et une colonne Numero, toutes deux des entiers. Je propose Annee puisque tu numérotes de façon unique par année.

- Pour chaque nouvelle vente tu fais un SELECT FOR UPDATE sur ventes_id pour l'année en cours, en récupérant la colonne Numero

- Tu construis ton nouvel ID, tu insères ta nouvelle vente puis tu viens faire un UPDATE sur ventes_id pour incrémenter Numero

- Tu commit, ce qui libères le verrou posé par SELECT FOR UPDATE

Ainsi tu ne verrouilles jamais la table ventes mais tu verrouilles fonctionnellement les insertions entre elles. De plus, la récupération du numéro est beaucoup plus rapide et moins gourmande qu'avec le MAX .

HTH

[genamiga]

Merci pour remarques. J'ai encore fait qlq modifications mineures et aussi fais qlq test de performance puisque rbaraer me met en garde sur le "cout" de MAX (qui est fait sur vente_id BIGINT PRIMARY KEY).

L'idée de devoir passer par une table de plus ne me plaisant pas voici ce que je vais certainement utilisé au final...

J'utilise maintenant LOCK TABLE ventes IN SHARE UPDATE EXCLUSIVE MODE dans le TRIGGER et pas dans le programme Java, ce qui est moins restrictif que le EXCLUSIVE MODE...si j'ai bien compris la doc PG...

L'exemple porte sur une table de ventes mais je vais utiliser la même technique pour différentes tables (envois, receptions, commandes, livraisons, etc...)

Donc d'abord une FUNCTION qui sera utlisée dans tous les TRIGGER.

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE FUNCTION "public"."calc_id" (dern_rec bigint, nbdigit integer) RETURNS bigint AS
$body$
DECLARE
	an BIGINT;		-- année courante
	mois BIGINT;		-- mois courant
 
	dern_rec_str TEXT;	-- dernier id en string    
	dern_id BIGINT := 0;	-- dernier id
	nouv_id BIGINT;		-- nouvel id
 
	an_dern_rec_str TEXT;	-- année correspondante au dernier id en string
	an_dern_rec SMALLINT;	-- année correspondante au dernier id
 
	nbid INTEGER;		-- nombre max d'id
BEGIN
	nbid := 10 ^ nbdigit;	-- nombre max d'id
	an := EXTRACT(year FROM now());
	mois := EXTRACT(month FROM now());
 
	-- s'il n'y a pas de vente, on ne fait rien, dern_id == 0
	IF (dern_rec IS NOT NULL) THEN     
	-- convertion du dernier id en string pour extraire facilement l'année et le numéro
	dern_rec_str := '' || dern_rec;
    	-- extraction de l'année et convertion en SMALLINT pour comparaison avec l'année courante
        an_dern_rec_str := SUBSTRING(dern_rec_str, 1,4);        
        an_dern_rec := CAST (an_dern_rec_str AS SMALLINT);
 
        -- si le dernier id n'est pas dans l'année courante, on ne fait rien, dern_id == 0
	IF (an_dern_rec >= an) THEN 
        	-- extraction du numéro du dernier id de l'année et convertion en BIGINT pour incrémentation
		dern_id := CAST(SUBSTRING(dern_rec_str, 7, nbdigit) AS BIGINT);
		END IF;
	END IF;        
	-- calcul du nouvel id
	nouv_id := (an * nbid * 100) + (mois * nbid) + dern_id + 1;    
	RETURN nouv_id;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

el le TRIGGER BEFORE INSERT sur table ventes

Code sql :

1
2
3
4
5
6
7
8
9
10
DECLARE
	dern_vente BIGINT;	-- vente_id du la dernière vente 
BEGIN
	LOCK TABLE ventes IN SHARE UPDATE EXCLUSIVE MODE;
	-- on récupére la dernière vente
	SELECT INTO dern_vente max("vente_id") FROM ventes;
 
	NEW.vente_id := calc_id(dern_vente, 5);  -- 10 ^ 5 ventes = 99999 max par an
	RETURN NEW;
END;

Quand au performances, avec qlq valeurs de test, l'INSERT de 50000 ventes ce fait en 36 sec sur une table vide, 37 sec sur une table contenant 500000 ventes et 38 sec sur une table contenant 1000000 de ventes...

Cela me convient parfaitement...

[genamiga]

Précision sur les performances...en accès concurents

5 processus lancer en même temps qui créent 20000 ventes chacuns 5min08sec sur une table vide, puis 5min32sec sur une table contenant 100000 ventes et 5min54sec sur une table contenant 200000 ventes...