Apparement il faut créer des

genamiga/ = 12 Juillet, 2008 - 13:49

Apparement il faut créer des OPERATOR pour que PG sache comment trier les super_id...ça semble fort complexe et surtout pas portable...donc Voilà après qlq réflexion j'ai trouvé une autre méthode 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 PRIMARY KEY 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.

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;

et...

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.

Problème de concurrence

SAS/ = 22 Juillet, 2008 - 15:00

Bonjour,

Les séquences ont pour principal avantage d'être modifiées en dehors de toute transaction, ce qui permet d'éviter les doublons.

Si vous vous affranchissez des séquences, vous risquez d'être confronté à des problèmes de doublons dans votre clé primaire (tentative d'insertion de valeur déjà stockée).

La difficulté revient à gérer la concurrence au niveeau de la séquence, à plus forte raison, si vous ne pouvez accepter de trou dans la séquence.

Vous pourriez éventuellement englober votre insertion complète dans une fonction qui teste la réussite ou l'erreur de l'insertion. Dans le dernier cas, réinitialiser la séquence. Mais là encore, vous ne pourrez garantir qu'aucune autre transaction n'a inséré une nouvelle ligne, et donc incrémenté la séquence.

Librement,
Stéphane Schildknecht
Dalibo
PostgreSQLFr

Merci pour remarques. Que

genamiga/ = 23 Juillet, 2008 - 20:12

Merci pour remarques.

Que pensez-vous de ceci ?

J'ai encore fait qlq modifications mineures et aussi fais qlq test de performance vu le "coût" de MAX (qui est fait sur vente_id BIGINT PRIMARY KEY).

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.

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;

et le TRIGGER BEFORE INSERT sur table ventes

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...

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

5 processus lancés 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...

Voilà...;)

Performances

SAS/ = 28 Juillet, 2008 - 11:26

Bonjour,

Comme vous l'avez remarqué, les performances lors d'accès concurrents vont être assez médiocres.

Il n'y a que vous pour savoir jusqu'à quel point cela reste tolérable.

Librement,
Stéphane Schildknecht
Dalibo
PostgreSQLFr