[9i pl/sql] Tester une contrainte de clé étrangère avant son activation

Version imprimable

Voir 40 message(s) de cette discussion en une page

19/11/2007, 16h29
Vld44

[9i pl/sql] Tester une contrainte de clé étrangère avant son activation

Bonjour,

J'ai une procédure pl/sql qui s'articule comme suit :

1/ Désactive les FK d'un jeu de tables
2/ Execution d'opérations DML
3/ Réactivation des FK du jeu de table

Mon besoin :
Lorsque la réactivation déconne (point 3), je souhaite rollback le DML

Mon problème
A. Le DDL commit illico donc impossible
B. La mise en place d'une PRAGMA AUTONOMOUS_TRANSACTION dans la procédure de réactivation des contraintes implique un deadlock car la table dont la contrainte doit être réactivée est verrouillée sous la main transaction

Ma question
Comment feriez-vous pour tester si la contrainte peut être réactivée ?
Si vous avez d'autres suggestions, n'hésitez pas.

Bonne journée,
Seb
19/11/2007, 17h28
mnitu
Je peux imaginer deux axes d’actions
1. ne pas désactiver les contraintes
2. enregistrer les modifications DML pour faire un « rollback » fonctionnel
  (suppression par delete des enregistrements ajoutés)
Ensuite plusieurs solutions sont envisageable au moins pour le cas b) vues matérialisés, trigger d’audit, colonne timestamp, etc.
Pour a) il faut étudier la faisabilité en fonction de besoins.
19/11/2007, 18h08
orafrance

Tu peux pas faire un VALIDATE sur la contrainte dans une autre session ?
19/11/2007, 18h31
Vld44

Je ne sais pas comment seulement valider une contrainte, c'est à dire sans exécuter d'opération DLL commitante ...
19/11/2007, 19h20
LeoAnderson

Mis à part faire manuellement les select de vérifications des FK... :oops:
(par contre, pour les perfs, c'est la mort !)

Mais une table d'exceptions (http://download.oracle.com/docs/cd/B...eral.htm#13282) ne pourrait-elle pas convenir ?
19/11/2007, 19h26
Vld44

Super cet article ! Je m'y attache dès demain matin et vous tiens au courant.

Merci pour le coup de main,
Seb
19/11/2007, 20h46
mnitu

La table d'exception va enregistrer seulement les exceptions :) donc je ne vois pas comment ensuite on pourrait faire le «*rollback*».

Est-il acceptable de garder les données incohérentes ainsi chargée et les résoudre plus tard ?
Pourriez-vous mieux préciser vos besoins ?
20/11/2007, 09h36
Vld44

errrr :-(

J'avais au départ créé une réponse pour dire que j'avais déjà essayé, puis en parcourant l'article cité j'ai cru (à tord) qu'il s'agissait d'une solution sans ré enable des contraintes.

La solution de la table d'exceptions ne convient pas, car elle passe par un "alter table" qui est d'ordre DDL, qui commit donc tout DML précédent.

Pour répondre à mnitu, le use case est assez classique (:oops:), car il s'agit d'une réplication des tables de dev vers préprod. Le mode opératoire ne l'est pas moins : disable all constraints, for each table, merge into ... enable constraints.

Je pourrais effectivement faire un algorithme de calcul et d'ordonnancement des tables de façon à ne pas avoir à désactiver les contraintes, mais je me demande si dans l'absolu il ne serait pas possible de tomber sur un cas insolvable.

A+ merci de votre aide
20/11/2007, 10h13
orafrance

1°) qu'est ce qui pourrait expliquer que la contrainte est KO dans la cible si c'est OK dans la source
2°) pourquoi ne pas utiliser la réplication Oracle ?
3°) aurais-tu un exemple de code à nous proposer pour mieux comprendre le process ?
20/11/2007, 10h27
pifor

J'au du mal à comprendre le besoin. D'abord pourquoi ne pas laisser les contraintes activées ? Quel est le message d'erreur complet que vous avez si les contraintes restes activées ? Un exemple (avec le DDL complet des contraintes) serait le bienvenu ;)
20/11/2007, 10h31
Vld44

Bonjour,

Je sais que si source est ok, dest le sera. Mais étant donné la criticité je préfère (au cas où il se passerait n'importe quoi ... par expérience, je ne peux pas tout prévoir :P) mettre en place un garde fou.

La réplication oracle ? Il faut que je regarde ce que c'est ... Mais à mon avis cela ne conviendra pas, car je fais également un select en source sur une colonne qui peut avoir pour valeur "delete", ce qui implique le cas échéant un delete dml en dest. Cela, à mon avis, Oracle ne le prévoit pas nativement.

A propos du code, je ne peux pas trop coller ça ici car cela fait 5-6 pages en wide screen alors vous imaginez en pages de forum !

Bon en liquidant l'inutile, voilà ce que ça donne :

Code:

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CREATE OR REPLACE PROCEDURE APPLYCHANGES(I_strSourceSchema IN VARCHAR2,I_strDestSchema IN VARCHAR2)
AS
    [...] Déclaration des curseurs etc.          
BEGIN       
    -- Init :
    j := 0;
 
    -- ******* Disable all triggers & foreign keys :
    -- je tape dans ALL_constraints etc. puis je build/exécute le ddl dans cette fonction :
 
    ENABLE_TABLES_FK_AND_TRIGGERS(0,I_strDestSchema);
 
    SELECT t.table_name BULK COLLECT INTO colTables FROM all_tables t
    WHERE t.owner = I_strSourceSchema    
    AND ...
 
    FOR i IN colTables.FIRST..colTables.LAST LOOP 
        strTableName := colTables(i);
 
        strInsertSubQuery_D := '';
        strInsertSubQuery_S := '';
 
        -- 1st, make a massive update/insert using the MERGE INTO statement
        -- 2nd, delete the elements removed from intermediate model by, for instance, a baseline restore
 
        strDeleteQuery := 'DELETE FROM ' || I_strDestSchema || '.' || strTableName || ' d WHERE (';
 
        strQuery := 'MERGE INTO ' || I_strDestSchema || '.' || strTableName || ' d ' ||
                    'USING (SELECT ';        
 
        -- [...] Compilation de la requêtes
 
        -- DML execution started : :        
        j := 1;
        EXECUTE IMMEDIATE strDeleteQuery;
        EXECUTE IMMEDIATE strQuery;               
 
    END LOOP; 
 
    j := 2;
 
    -- ******* Re-enable all triggers & foreign keys :
    ENABLE_TABLES_FK_AND_TRIGGERS(1,I_strDestSchema);    
 
EXCEPTION
    WHEN OTHERS THEN
        IF j = 1 THEN
            ROLLBACK;
        ELSIF j = 2 THEN
            RAISE_APPLICATION_ERROR(-20001,'Erreur lors de la réactivation des contraintes + triggers, vérifiez l''intégrité de la base destination de la réplication');
        ELSE
            RAISE; 
        END IF;
END;
/

ps : soyez indulgents j'ai du apprendre le pl/sql sur le tas pour faire des proc stocks de ce genre .. :)

20/11/2007, 10h58
pifor
Si vous construisez votre propre système de réplication, il faut être sûr qu'il applique les instructions SQL exactement comme sur la base source:
- il faut que la transaction n°2 soit exécutée après la transaction n°1 et avant la transaction n°3
- il faut que l'ordre des instructions SQL dans chaque transaction soit conservé
- il faut que que chaque instruction qui modifie les données (INSERT, UPDATE, DELETE) dans une transaction sur la base source fasse effectivement partie de la transaction sur la base cible.
Difficile de dire si ses conditions sont remplies si vous avez du code générique. Quels sont les critères d'ordonnancement des instructions SQL ?
20/11/2007, 11h02
orafrance

Citation:

Envoyé par Vld44

La réplication oracle ? Il faut que je regarde ce que c'est ... Mais à mon avis cela ne conviendra pas, car je fais également un select en source sur une colonne qui peut avoir pour valeur "delete", ce qui implique le cas échéant un delete dml en dest. Cela, à mon avis, Oracle ne le prévoit pas nativement.

Bien sûr que si ;)

heureusement quand même :D

Oracle prend en charge tous les DML et même le DDL :)

Tu peux même gérer les conflits :yaisse2:
20/11/2007, 11h04
orafrance

Citation:

Envoyé par Vld44

Bon en liquidant l'inutile, voilà ce que ça donne

Tu réinventes la roue là... non seulement c'est pas évident (comme tu le vois :mrgreen:) mais en plus ce sera terriblement lent :?

Quelle version d'Oracle tu as ?

Edit : 9i j'avais pas vu :oops:

Enfin, réinventer la standby logique c'est un peu lourd :? Bon courage :king:
20/11/2007, 11h10
Vld44
Citation:
Envoyé par pifor

Si vous construisez votre propre système de réplication, il faut être sûr qu'il applique les instructions SQL exactement comme sur la base source:

il faut que la transaction n°2 soit exécutée après la transaction n°1 et avant la transaction n°3
il faut que l'ordre des instructions SQL dans chaque transaction soit conservé
il faut que que chaque instruction qui modifie les données (INSERT, UPDATE, DELETE) dans une transaction sur la base source fasse effectivement partie de la transaction sur la base cible.

Difficile de dire si ses conditions sont remplies si vous avez du code générique. Quels sont les critères d'ordonnancement des instructions SQL ?
Bonjour,

Je ne suis pas certain de comprendre ce que vous entendez par "transaction sur la base source"

Il ne s'agit pas une réplication à la volée, mais batch. Il n'y a aucune transaction explicitement ouverte sur la base source car il n'y a que des selects.

Les ordres DML sont construits table par table, pour chacune il y a un DELETE et un MERGE INTO avec clauses WHEN MATCHED + WHEN NOT MATCHED.

Les tables dest sont (seront : en cours d'implémentation) totues lockées (exclusive) par le batch.

Je ne me suis pas soucié de la question d'ordre du DML, car j'ai opté pour la solution de désactivation des contraintes. Pourriez-vous préciser ce à quoi vous faites allusion ? Merci de vous pencher sur mon cas :)

Seb
Note : je viens de m'apercevoir que j'ai dit une ânerie dans mon dernier post, il s'agit bien d'une réplication bête et méchante ... (c'est ça de faire plusieurs procédures en même temps) Je me penche donc sur la fonctionnalité de réplication d'oracle.
20/11/2007, 11h42
orafrance

c'est dommage, STREAMS en 10g fonctionne super bien... en 9i, il parait que c'est plus capricieux :(
20/11/2007, 11h46
Vld44

je viens de jeter un oeil sur la réplication, mais ça m'a l'air d'être une sacré usine à gaz pour ce que je veux faire ...

Moi j'appuie sur un bouton dans le soft fait en VB, et le contenu des tables de user 1 sont copiées dans celles de user 2

user 2 n'est JAMAIS modifié, c'est forward only ... Donc les conflits et tout le bazar c'est pas mon problème.

Ensuite pour la lenteur, d'une part nos volumétries sont raisonnables donc le problème ne se pose pas (trop), d'autre part en ce qui concerne le delete uniquement je suis effectivement confronté à des perfs médiocres (jointure entre big tables pour savoir ce qui manque) mais j'ai un workaround de derrière les faggots.

Dès que j'ai le temps je regarde en détail la réplication sous 9i
20/11/2007, 12h03
orafrance

Citation:

Envoyé par Vld44

Moi j'appuie sur un bouton dans le soft fait en VB, et le contenu des tables de user 1 sont copiées dans celles de user 2

Sauf que ça ne marche pas pour le moment a priori :aie:

Citation:

Envoyé par Vld44

user 2 n'est JAMAIS modifié, c'est forward only ... Donc les conflits et tout le bazar c'est pas mon problème.

Et des MV ça ne conviendrait pas ?

Si tu ne géres pas les conflits la réplication est extrêment simple... le concept est un peu délicat à appréhender mais la mise en oeuvre est d'une simplicité enfantine ;)
20/11/2007, 12h13
Vld44

MV = mat view ?

Les modèles sont un peu différents, et on a vraiment une organisation désynchronisée : la réplication se fait à la demande, en temps voulu ...
En fait je suis en train de brancher mon produit sur un autre produit du client, donc on ne doit pas toucher un poil de leur modèle / organisation (ce qui est normal) Donc à mon avis les mat view sont inenvisageables.

Voir 40 message(s) de cette discussion en une page