Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 8 sur 8
  1. #1
    Rédacteur/Modérateur

    Avatar de gorgonite
    Homme Profil pro Nicolas Vallée
    Ingénieur d'études
    Inscrit en
    décembre 2005
    Messages
    10 194
    Détails du profil
    Informations personnelles :
    Nom : Homme Nicolas Vallée
    Âge : 29
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur d'études
    Secteur : Transports

    Informations forums :
    Inscription : décembre 2005
    Messages : 10 194
    Points : 16 749
    Points
    16 749

    Par défaut Emuler une foreign key sur des tables pseudo-héritées




    J'ai une petite question concernant la cohérence de mon raisonnement.

    J'ai une table Main contenant :
    • un identificateur auto-incrémenté pour faire une clé primaire
    • une clé étrangère CategorieId pointant sur la clé primaire de la table Categories
    • un champ MessageId devant référencer le contenu des différents messages

    Or en fonction de la catégorie, le format du message change, j'ai donc du créer une table par catégorie pour stocker correctement toutes les données...
    En toute logique, ces tables auraient du hériter d'une table Messages ayant une clé primaire sur laquelle aurait pointé MessageId. Mais PostGreSQL ne semble pas gérer cela


    J'ai donc oublié la table Messages mais gardé le concept, en émulant la contrainte via un 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
     
    CREATE FUNCTION main_trigger() 
    RETURNS TRIGGER AS $$
    DECLARE
    	req		varchar;
    	temprec	record;
    	tmpint	integer := 0;
    BEGIN
    	req = 'SELECT * FROM ' || 
    			CASE
    				WHEN NEW.CategorieId=106 THEN 'Message_106'
    				WHEN NEW.CategorieId=131 THEN 'Message_131'
    				ELSE 'Message_Default'
    			END
    			|| ' WHERE Id=' || NEW.MessageId;
    --	RAISE NOTICE 'main_trigger request:';
    --	RAISE NOTICE '%', req;
    	EXECUTE req INTO temprec;
    	GET DIAGNOSTICS tmpint = ROW_COUNT;
    	IF tmpint=0 THEN
    		RAISE EXCEPTION 'Id % not found for Categorie %', 
    			NEW.MessageId, NEW.CategorieId;
    	END IF;
     
    	RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;
     
    CREATE TRIGGER insert_or_update_main_trigger
    	BEFORE INSERT OR UPDATE ON Main
    	FOR EACH ROW EXECUTE PROCEDURE main_trigger();


    Ma question
    Y a-t-il une manière plus élégante et/ou performante de procéder pour arriver au même résultat ?
    (un batch quotidien va importer plusieurs millions d'enregistrements et je souhaiterais que cet import n'handicape pas trop la disponibilité des résultats)



    par avance
    Evitez les MP pour les questions techniques... il y a des forums
    Contributions sur DVP : Mes Tutos | Mon Blog

  2. #2
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 136
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 136
    Points : 3 732
    Points
    3 732

    Par défaut

    En toute logique, ces tables auraient du hériter d'une table Messages ayant une clé primaire sur laquelle aurait pointé MessageId. Mais PostGreSQL ne semble pas gérer cela
    Je ne sais pas ce que Postgresql devrait gérer.
    C'est un modèle en héritage
    Y a-t-il une manière plus élégante et/ou performante de procéder pour arriver au même résultat ?
    Un trigger for each row (donc slow by slow) plombera forcément les perfs lors d'INSERT de masse.
    Sinon 2 points d'amélioration à apporter au code :
    • select * => select id : Comme ça seul l'index est lu (pas besoin d'aller taper sur la table)


  3. #3
    Rédacteur/Modérateur

    Avatar de gorgonite
    Homme Profil pro Nicolas Vallée
    Ingénieur d'études
    Inscrit en
    décembre 2005
    Messages
    10 194
    Détails du profil
    Informations personnelles :
    Nom : Homme Nicolas Vallée
    Âge : 29
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur d'études
    Secteur : Transports

    Informations forums :
    Inscription : décembre 2005
    Messages : 10 194
    Points : 16 749
    Points
    16 749

    Par défaut

    Citation Envoyé par skuatamad Voir le message

    tu parles d'utiliser les USING ?

    J'ai oublié de signaler que je suis avec PostGreSQL 8.3

    en gros, tu conseilles de faire un PREPARE global et ensuite un seul EXECUTE dans la fonction ? (ce qui semble être l'équivalent du using)
    Evitez les MP pour les questions techniques... il y a des forums
    Contributions sur DVP : Mes Tutos | Mon Blog

  4. #4
    Expert Confirmé
    Inscrit en
    août 2008
    Messages
    2 136
    Détails du profil
    Informations forums :
    Inscription : août 2008
    Messages : 2 136
    Points : 3 732
    Points
    3 732

    Par défaut

    Citation Envoyé par gorgonite Voir le message
    en gros, tu conseilles de faire un PREPARE global et ensuite un seul EXECUTE dans la fonction ? (ce qui semble être l'équivalent du using)
    Oui c'est ça, j'ai trouvé cet autre lien dans la doc :
    32.7. Dynamic SQL

  5. #5
    Rédacteur/Modérateur

    Avatar de gorgonite
    Homme Profil pro Nicolas Vallée
    Ingénieur d'études
    Inscrit en
    décembre 2005
    Messages
    10 194
    Détails du profil
    Informations personnelles :
    Nom : Homme Nicolas Vallée
    Âge : 29
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur d'études
    Secteur : Transports

    Informations forums :
    Inscription : décembre 2005
    Messages : 10 194
    Points : 16 749
    Points
    16 749

    Par défaut

    Citation Envoyé par skuatamad Voir le message
    Oui c'est ça, j'ai trouvé cet autre lien dans la doc :
    32.7. Dynamic SQL
    Euh, ça ne concerne que le préprocesseur vers programme C à partir d'une syntaxe SQL intégrée au C.


    En fait, dans le cas du PREPARE, il faudrait faire ceci :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    PREPARE Get_Message_Id(int,int) AS 
       SELECT id FROM (
          SELECT CASE 
             WHEN $2=106 THEN quote_ident('Message_106') 
             WHEN $2=131 THEN quote_ident('Message_131') 
             ELSE quote_ident('Message_Default') 
          END
       ) AS "Messages" 
       WHERE Id=$1 ;
    C'est a priori syntaxiquement correct, mais sémantiquement la compilation ne sait pas déterminer si Id existe

    J'ai loupé un truc
    EDIT: a priori, cela revient à passer par format ce qui n'est pas disponible à la version 8.3
    Evitez les MP pour les questions techniques... il y a des forums
    Contributions sur DVP : Mes Tutos | Mon Blog

  6. #6
    Membre du Club
    Homme Profil pro
    Inscrit en
    mars 2005
    Messages
    65
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations forums :
    Inscription : mars 2005
    Messages : 65
    Points : 53
    Points
    53

    Par défaut

    Bonjour,

    Je n'ai pas vérifié sur un serveur, mais la solution suivante me semble moins coûteuse en performance, car elle évite le trigger et le recours à du SQL dynamique, et devrait passer sur des versions anciennes.

    1)
    Rassembler Message_106 et Message_107 dans un vue:

    Code :
    1
    2
    3
    4
    5
    6
    CREATE VIEW v_messages_general
    AS
    SELECT Id, 106 AS type_message_general FROM Message_106
    UNION
    SELECT Id, 107 AS type_message_general FROM Message_107
    ;
    2) Placer une contrainte NOT NULL sur la clé étrangère MessageId dans la table Main

    3) Faire un INSERT... SELECT avec une condition sur la clé de la vue

    Code :
    1
    2
    3
    4
    INSERT INTO Main (colonne_cle_primaire_de_main, MessageId, CategorieId) 
             SELECT NEXTVAL('la_sequence_de_la_table_main'), Id, $2 FROM v_messages_general WHERE Id=$1 AND type_message_general=$2;
     
    --(j'ai représenté par "$" les variables, car j'ignore si elles proviennent d'un applicatif ou bien d'une autre table).

    Et/ou un UPDATE...FROM comparable pour les mises à jour.

    S'il y a une contraire Not NULL sur la table "main", l'insertion ou la mise à jour devraient être rejetées si la clé correspondante n'est pas trouvée dans les deux tables "messages", tout en fonctionnant si c'est bien le cas.


    Dans le problème décrit, le plus délicat me paraît pas tellement l'insertion, mais de s'assurer que les valeurs des clés des différentes tables "Message_XXX" sont uniques pour toute la base. Par une séquence? Un oid? Par une contrainte d'unicité sur une clé étrangère qui fait office de clé primaires dans les tables messages, et qui pointe à son tour sur une table centralisant les identifiants de tous les messages?

  7. #7
    Expert Confirmé Sénior
    Homme Profil pro
    Inscrit en
    mai 2002
    Messages
    3 006
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 31
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : mai 2002
    Messages : 3 006
    Points : 4 555
    Points
    4 555

    Par défaut

    bonjour,

    union all pour la vue.

  8. #8
    Rédacteur/Modérateur

    Avatar de gorgonite
    Homme Profil pro Nicolas Vallée
    Ingénieur d'études
    Inscrit en
    décembre 2005
    Messages
    10 194
    Détails du profil
    Informations personnelles :
    Nom : Homme Nicolas Vallée
    Âge : 29
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur d'études
    Secteur : Transports

    Informations forums :
    Inscription : décembre 2005
    Messages : 10 194
    Points : 16 749
    Points
    16 749

    Par défaut

    à tous pour ces explications

    Finalement, j'ai restructuré différemment les dépendances afin d'éviter ce trigger
    Evitez les MP pour les questions techniques... il y a des forums
    Contributions sur DVP : Mes Tutos | Mon Blog

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •