Emuler une foreign key sur des tables pseudo-héritées [Résolu]

[gorgonite]

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

[skuatamad]

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)

• || ' WHERE Id=' || NEW.MessageId; => Utiliser les variables de liasons pour éviter de parser encore et encore la requête : 39.5.4. Executing Dynamic Commands

[gorgonite]

Envoyé par skuatamad

• || ' WHERE Id=' || NEW.MessageId; => Utiliser les variables de liasons pour éviter de parser encore et encore la requête : 39.5.4. Executing Dynamic Commands

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)

[skuatamad]

Envoyé par gorgonite

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

[gorgonite]

Envoyé par skuatamad

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

[CetTer]

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?

[punkoff]

bonjour,

union all pour la vue.

[gorgonite]

à tous pour ces explications

Finalement, j'ai restructuré différemment les dépendances afin d'éviter ce trigger