Discussion :

[HaraKity]

Bonjour à tous,


Je n'arrive pas à trouver pourquoi une fonction de fsmrel ne marche pas du premier coup.
Il est probable à 99,90% que je fasse une erreur qui mène à cette incohérence.
Les réponses de fsmrel sont une mine d'or permettant de comprendre et de se documenter, et je le salue bien bas pour la patience et la précision avec lesquelles il répond aux problèmes posés.

Voilà la fonction qui me pose problème.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
CREATE FUNCTION AFFECTATION_SALLE_ASSERT_01_FN()
      RETURNS TRIGGER AS
$le_trigger$ 
 
    DECLARE Erreur                       VARCHAR ; 
 
    BEGIN
        IF 0 < (SELECT COUNT(*) 
                FROM 
                     (SELECT x.Id_Salle     
                      FROM   AFFECTER AS x JOIN AFFECTER AS y ON x.Id_Salle = y.Id_Salle 
                                                             AND x.Id_Examen <> y.Id_Examen AND x.Id_Examen < y.Id_Examen
                                           JOIN EXAMEN AS z ON x.Id_Examen = z.Id_Examen
                                           JOIN EXAMEN AS t ON y.Id_Examen = t.Id_Examen
                      WHERE  x.Id_Salle = NEW.Id_Salle
                        AND  z.Date_Examen = t.Date_Examen  
                        AND (t.Heure_Debut_Examen >= z.Heure_Debut_Examen AND t.Heure_Debut_Examen <= z.Heure_Fin_Examen   
                         OR t.Heure_Debut_Examen <= z.Heure_Debut_Examen AND t.Heure_Fin_Examen >=  z.Heure_Debut_Examen)) AS maTable)  
            THEN
                Erreur = 'A un instant t, La salle = '''|| NEW.Id_Salle || ''' ne peut être affectée qu''à un seul examen.' ;
                RAISE EXCEPTION SQLSTATE '45001' USING MESSAGE = Erreur ;
        END IF ;
 
        RETURN NEW ;
 
    END ;
 
$le_trigger$ 
  LANGUAGE plpgsql ;
 
CREATE TRIGGER AFFECTATION_SALLE_ASSERT_01_TR BEFORE INSERT OR UPDATE ON AFFECTER
    FOR EACH ROW EXECUTE PROCEDURE AFFECTATION_SALLE_ASSERT_01_FN() ;

La source est http://www.developpez.net/forums/d1525592/general-developpement/alm/modelisation/schema/mcd-planing-examens/#post8281595]ici.


Le jeu d'essai fonctionne fourni par fsmrel fonctionne, mais avec des tests perso les problèmes surviennent.

Voilà le jeu d'essai que j'utilise :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
INSERT INTO EXAMEN (Date_Examen,Heure_Debut_Examen, Heure_Fin_Examen) VALUES ('2015-05-02', '09:00:00', '10:00:00');
INSERT INTO EXAMEN (Date_Examen,Heure_Debut_Examen, Heure_Fin_Examen) VALUES ('2015-05-02', '09:00:00', '10:00:00');
INSERT INTO EXAMEN (Date_Examen,Heure_Debut_Examen, Heure_Fin_Examen) VALUES ('2015-05-02', '08:30:00', '10:30:00');
INSERT INTO EXAMEN (Date_Examen,Heure_Debut_Examen, Heure_Fin_Examen) VALUES ('2015-05-02', '09:15:00', '09:45:00');
INSERT INTO EXAMEN (Date_Examen,Heure_Debut_Examen, Heure_Fin_Examen) VALUES ('2015-05-02', '09:00:00', '11:00:00');

Et ensuite les insertions dans la table Affecter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
INSERT INTO AFFECTER (Id_Examen, Id_Salle) VALUES (1, 1) ; -- première entrée donc tout va bien
INSERT INTO AFFECTER (Id_Examen, Id_Salle) VALUES (2, 1) ; -- Postgresql accepte l'enregistrement sans broncher !?
INSERT INTO AFFECTER (Id_Examen, Id_Salle) VALUES (3, 1) ; -- cette fois_ci la fonction renvoie l'erreur

J'ai passé une bonne partie de la journée d'hier a essayer de comprendre le pourquoi du comment mais je ne vois vraiment pas où est le problème.
Voilà ce que je pense avoir compris :
Plage horaire à tester : de z.Heure_Debut_Examen à z.Heure_Fin_Examen
Plage horaire déjà enregistrée : de t.Heure_Debut_Examen à t.Heure_Fin_Examen

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
(t.Heure_Debut_Examen >= z.Heure_Debut_Examen AND t.Heure_Debut_Examen <= z.Heure_Fin_Examen   
                           OR t.Heure_Debut_Examen <= z.Heure_Debut_Examen AND t.Heure_Fin_Examen >=  z.Heure_Debut_Examen)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
[nouvelle heure début(z) <= [-------- plage horaire déjà enregistrée(t) --------[
                                      >= nouvelle heure fin(z)[
OR
[nouvelle heure début(z) >=
[-------- plage horaire déjà enregistrée(t) --------[
                     <= nouvelle heure début(z)[

bon, il est possible que ce que je décrive ci-dessus soit embrouillé car moi-même je ne m'y retrouve plus dans mon raisonnement à force de cogiter.
A ce stade je pourrais tout modifier mais je souhaite vraiment comprendre où je cafouille en utilisant cette fonction.

Si quelqu'un pouvait m'aider à y voir plus clair... merci d'avance

[aieeeuuuuu]

Bonjour,

La fonction utilisée par le trigger vérifie qu'il n'existe pas plus d'une affectation pour le couple (examen-salle) pour la salle en cours d'insertion.

Mais le trigger est un trigger BEFORE. Lors de l'insertion de la deuxième ligne, il se déclenche donc avant l'insertion de la ligne qui devrait poser problème.
Lors de l'insertion de la troisième ligne, l'erreur qui est provoquée est en fait dûe uniquement aux lignes 1 et 2 qui sont en conflit.

Changez pour un trigger AFTER, et tout devrait rentrer dans l'ordre.

[HaraKity]

Je ne cherchais pas au bon endroit...

Ça fonctionne maintenant.

Merci beaucoup d'avoir éclairci ça pour moi

[HaraKity]

Bonjour,
lignes 1 et 2 qui sont en conflit.

Merci aussi pour cette précision, j'ai passé un temps fou à chercher la logique de ces deux lignes.
Je commençais à douter de mes capacités d'abstraction.

[estofilo]

Il faut rappeler quand même qu'un trigger ne voit pas ce qu'a fait une autre transaction non encore validée,
donc dans un scénario d'insertions concurrentes, ce genre de test n'offre aucune garantie.

En PostgreSQL moderne (9.2 et plus) il vaut mieux utiliser un type intervalle avec une clause
d'exclusion mutuelle réellement garantie en plus d'être beaucoup plus simple à exprimer.

La réservation de salle est d'ailleurs l'exemple utilisé dans la doc:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation
(
  room TEXT,
  during TSRANGE,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);
 
INSERT INTO room_reservation VALUES
  ( '123A', '[2010-01-01 14:00, 2010-01-01 15:00)' );
 
INSERT INTO room_reservation VALUES
  ( '123A', '[2010-01-01 14:30, 2010-01-01 15:30)' );

Résultat:
ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL: Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

[HaraKity]

Bonsoir et merci pour cette précision

Par contre, le modernisme n'est pas encore arrivé chez OVH pour les offres mutualisées (version 8.4).

Bon, il faut que je me penche sur une autre solution alors