Discussion :

[Mouette62]

Bonjour à tous,

après plusieurs tentatives et des recherches sur mon ami google je reste sur un échec ! je me tourne alors vers vous pour m'aider.

j'ai deux tables avec plusieurs champs mais je ne cite que ceux qui m'intéresse :
sb_data_44 avec comme identifiant rowid(serial) et un champ id_tblcommerce (integer)
tbl_commerce_test avec un champ identifiant id_commerce(serial) et un champ rowid_tbldata44

mon objectif est que chaque table reçoit l'identifiant de l'autre après une insertion (le tout en gérant éventuellement des accès simultanés)
Hors je n'arrive pas à recevoir l'id ( id_commerce) de la table tbl_commerce_test dans la table sb_data_44 (sur le champ id_tblcommerce).
tbl_commerce_test reçoit bien lui l'identifiant de sb_data_44.

je soupçonne mon update en fin de fonction, je l'ai placé ailleurs mais rien ...

voici ma fonction

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
CREATE OR REPLACE FUNCTION base_sig.fct_synchro()
  RETURNS trigger AS
$BODY$
DECLARE idc integer;
DECLARE rid_dyn integer;
 
BEGIN
 
IF (TG_OP = 'DELETE') THEN
		DELETE FROM sch_t1_commerce.tbl_commerce_test WHERE rowid_tbldata44=OLD.rowid;
		IF NOT FOUND THEN RETURN NULL; END IF;
		RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
		INSERT INTO sch_t1_commerce.tbl_commerce_test (rowid_tbldata44) VALUES (rid_dyn)	;
 
		RETURN NEW;	
END IF;
 
RETURN NULL; 
 
 
SELECT tbl_commerce_test.id_commerce into idc FROM sch_t1_commerce.tbl_commerce_test, base_sig.sb_data_44 WHERE tbl_commerce_test.rowid_tbldata44=rid_dyn;
UPDATE base_sig.sb_data_44 SET id_tblcommerce = idc WHERE sb_data_44.rowid=rid_dyn;
 
 
END;
 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION base_sig.fct_synchro()
  OWNER TO postgres;

et mon trigger :

Code :

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

1
2
3
4
5
6
CREATE TRIGGER tg_synchro
AFTER INSERT OR UPDATE OR DELETE
ON base_sig.sb_data_44
FOR EACH ROW
EXECUTE PROCEDURE base_sig.fct_synchro();
ALTER TABLE base_sig.sb_data_44;

Merci pour vos conseils

[SQLpro]

Vous avez une erreur dans votre modèle.

Tout cela se simplifierait si vous utilisiez le modèle en héritage.

Ajoutez donc une table mère dans laquelle vous n'aurez que la colonne SERIAL. Redéfinissez vos deux tables pour qu'elles aient en clef primaire la colonne SERIAL de la table mère et que cette colonne soit à la fois PK et FK.

Pour l'insertion, définissez un trigger pour chaque table qui préalablement insère dans la table mère, récupère l'id et insère dans les deux tables fille !

Pour le DELETE, jouez sur l'intégrité référentielle en mode cascade et en propageant le delete au niveau de la mère.

Mieux, utilisez des vues mise à jourables (pas évidentes avec le concept de rule propre à PG et donc dommage qu'il ne propose pas les triggers INSTEAD OF...)

A +

[Mouette62]

merci pour la réponse mais malheureusement je ne peux pas modifier ce modèle : une partie est utilisée (table sb_data_44) et gérée par un logiciel et l'autre par un autre .... et je dois assurer une correspondance des données entre les deux (en attendant une évolution majeure du premier, qui n'est pas pour tout de suite).



j'ai bien conscience d'être face à un modèle perfectible mais je dois faire avec ...

[tse_jc]

Bonjour,

Pour le delete, suivez le conseil de SQLpro c'est la meilleure solution je m'y étend donc pas.

Par contre, vu que vous ne pouvez pas toucher au modèle, et vu qu'il vous faut éviter un deadlock lors de la mise à jour, voici ce que je vous propose.

1) Vous partez de votre table sb_data_44 et faites un trigger AFTER INSERT.
2) Dans ce trigger
a) vous récupérer la valeur du rowid (serial) avec NEW.rowid
b) Vous faites l'insert dans la table tbl_commerce_test avec votre rowid
c) Vous récupérez via une instruction comme celle-ci votre nouvel id_commerce qui viens d'être généré

Code :

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

1
2
 
 SELECT CURRVAL(pg_get_serial_sequence('tbl_commerce','id_commerce')) INTO pnew_idcommerce;

d) vous mettez ensuite à jour la valeur de NEW.id_tblcommerce avec pnew_idcommerce.

voilà

[Mouette62]

merci tse_jc pour la réponse ;

je regarde cette option de plus près ...

en revanche, cette notion de deadlock m'est étrangère, de quoi s'agit-il ?

merci

[estofilo]

Il y a qqs problèmes qui sautent aux yeux dans la fonction trigger:

- ligne 11 et 12 le test ne sert à rien car renvoyer NULL ou OLD ne fait aucune différence pour un trigger AFTER (après l'évenèment).

- ligne 14 on utilise une variable rid_dyn qui n'est jamais initialisée.

- ligne 19 le RETURN NULL inconditionnel fait que les instructions d'après ne seront jamais exécutées.

En l'état le trigger a trop de problèmes.

Mais revenons au départ:

mon objectif est que chaque table reçoit l'identifiant de l'autre après une insertion (le tout en gérant éventuellement des accès simultanés)

Si l'objectif se limite à ça, ça donnerait:

Code :

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

1
2
3
4
CREATE TRIGGER tg_synchro
BEFORE INSERT ON base_sig.sb_data_44
FOR EACH ROW
EXECUTE PROCEDURE base_sig.fct_synchro();

et

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
CREATE OR REPLACE FUNCTION base_sig.fct_synchro()
  RETURNS TRIGGER AS
$BODY$
DECLARE var_id integer;
 
BEGIN
   INSERT INTO sch_t1_commerce.tbl_commerce_test (rowid_tbldata44)
      VALUES (NEW.rowid)
      RETURNING id_commerce INTO var_id;
 
   NEW.id_tblcommerce = var_id;
 
   RETURN NEW;
END;
$BODY$  LANGUAGE plpgsql ;

C'est un trigger BEFORE car il faut écrire dans le champ NEW.id_tblcommerce, et le faire en mode AFTER ça complique inutilement.

[tse_jc]

Bonjour,

C'est un trigger BEFORE car il faut écrire dans le champ NEW.id_tblcommerce, et le faire en mode AFTER ça complique inutilement.

Désolé de vous contredire, ça ne complique pas inutilement. Il y a en effet des traitements qu'il vaut mieux réserver en BEFORE INSERT et d'autres en AFTER INSERT. Ici l'insert est jugé valide et conforme, donc le traitement n'a pas de raison d'être en BEFORE INSERT. Ma recommendation est de faire ainsi, et de réserver les triggers en BEFORE INSERT prioritairement pour écrire vos contraintes d'assertion et parfois selon le contexte pour certaines contraintes check. Chaque chose à sa place, c'est plus facile à gérer et à maintenir sur le long terme

Après chacun fait comme il veut...

++

[tse_jc]

Re,

J'ai bien conscience dans ce que j'ai dit que l'on pourrait justement considérer à certains égards que ce trigger constitue un contrôle d'assertion. Mais il n'en reste pas moins, au sens métier du terme, qu'il ne s'agit rien d'autre qu'une mise à jour de données après insertion.

Voici la modification qu'il faut apporter au code d'estofilo pour exécuter le trigger en AFTER INSERT

Code :

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

1
2
3
 
  NEW.id_tblcommerce = var_id; -- ligne à remplacer
  UPDATE base_sig.sb_data_44 SET id_tblcommerce=var_id WHERE rowid_tbldata44=NEW.rowid; -- ligne de remplacement

[alassanediakite]

Bonjour

...
Mieux, utilisez des vues mise à jourables (pas évidentes avec le concept de rule propre à PG et donc dommage qu'il ne propose pas les triggers INSTEAD OF...)

A +

PostgreSQL propose bien les triggers INSTEAD OF. D'ailleurs c'est uniquement sur le vues. Je suppose que c'est la raison de vos trois points.
@+

[estofilo]

donc le traitement n'a pas de raison d'être en BEFORE INSERT

Justement si, un trigger BEFORE INSERT permet de modifier la ligne à insérer avant qu'elle le soit ce qui est précisément l'objectif ici.

Il y a 2 bonnes raisons de le faire comme ça:

1) en AFTER INSERT, il faudrait faire une deuxième écriture pour corriger la ligne a posteriori, ce qui est moins efficace.

2) Si la colonne à mettre à jour en trigger avait une contrainte NOT NULL, le BEFORE INSERT permet de le faire alors que le AFTER INSERT ne le permet pas, il est trop tard.

[tse_jc]

Bonjour,

Loin de moi la polémique, mais mon but ici est de faire avancer les choses.

Justement si, un trigger BEFORE INSERT permet de modifier la ligne à insérer avant qu'elle le soit ce qui est précisément l'objectif ici.

Puisque il est question d'objectif et de contexte, n'oublions pas tout d'abord que le modèle est inadapté pour ne pas dire défaillant.

J'aimerais rappeler que la base de données doit s'assurer elle-même qu'au niveau transaction, les insertions effectuées soient valides et conformes aux contraintes de domaine et de vérification sur les tables ciblées (via une gestion d'erreur et non des triggers).

Donc quand vous dites "ce qui est précisément l'objectif ici", vous avez de la chance d'avoir de telles certitudes car cela dépends d'une chose essentiellement à savoir si la colonne "id_tblcommerce" de la table initiale possède une FK référençant la table commerce, auquel cas cela est justifié, mais comme je l'ai dit précedemment, ce contrôle d'assertion (qui dans ce contexte en est vraiment un) est alors contre productif dû justement au modèle défaillant.

Mais par rapport au modèle éxistant et à ce contrôle d'assertion qui n'a pas lieu d'être, il ne faut pas mettre de FK sur la colonne id_tblcommerce de la table initiale (en la gardant indexée), le contrôle d'intégrité se faisant de toute manière via ce trigger cette fois en AFTER INSERT. (Le traitement supplémentaire via l'UPDATE est le prix à payer compte tenu du modèle, mais reste faible à mon humble avis)

Ceci à donc l'avantage de ne pas retarder l'écriture de la ligne et de laisser la place nette au niveau du BEFORE INSERT pour de vrais contrôles d'assertions au niveau de la table.

Ensuite ne pas faire ainsi reste possible voire éventuellement même une nécessité je vous l'accorde, mais alors uniquement dans un contexte d'optimisation selon comment la base réagit au stress qui lui est infligé en production.

Cordialement,

Jc.

[estofilo]

L'objectif est mentionné dans la question initiale:

mon objectif est que chaque table reçoit l'identifiant de l'autre après une insertion

C'est on ne peut plus simple et clair, et je ne vois pas l'intérêt d'en faire des tonnes avec un sermon de curé prétendant que le "modèle est défaillant" et des "contrôles d'assertion contre-productifs" qui n'ont rien à voir avec la question.

[alassanediakite]

Ce qui me fait peur c'est...

J'aimerais rappeler que la base de données doit s'assurer elle-même qu'au niveau transaction, les insertions effectuées soient valides et conformes aux contraintes de domaine et de vérification sur les tables ciblées (via une gestion d'erreur et non des triggers).

@+