Discussion :

[spirzouf]

Bonjour,
J'essaie de comprendre une astuce permettant l'insertion uniquement si la clé primaire est absente (afin de ne pas déclencher l'inévitable erreur de clé dupliquée).
Dans l'exemple donné sur ce post, il est bien précisé de faire le "WHERE NOT EXISTS" sur une table bidon :

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
INSERT INTO
    matable
    (   maclefprimaire
    ,   maclefetrangere
    ,   monattribut
    ) 
SELECT  1
    ,   1
    ,   'valeurtexte'
FROM    tablebidon
WHERE   NOT EXISTS
        (   SELECT  0
            FROM    matable
            WHERE   maclefprimaire = 1
        )
;

Effectivement, si on utilise matable au lieu de tablebidon:

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
INSERT INTO
    matable
    (   maclefprimaire
    ,   maclefetrangere
    ,   monattribut
    ) 
SELECT  1
    ,   1
    ,   'valeurtexte'
FROM    matable
WHERE   NOT EXISTS
        (   SELECT  0
            FROM    matable
            WHERE   maclefprimaire = 1
        )
;

... on obtient une erreur de clé dupliquée si la clé en question n'est pas présente (par contre, cela fonctionne bien si la clé est présente).

Pourquoi ?

[punkoff]

bonjour,

quel est votre sgbd ?

[al1_24]

Relis bien ce qui était écrit dans le message auquel tu fais référence :

Il te suffit d'une table bidon contenant un enregistrement (comme dual sous Oracle, ou une table quelconque sur laquelle tu sélectionnes un enregistrement et un seul).

[aieeeuuuuu]

Pourquoi ?

Dans le lien que vous citez, il est bien précisé que la "table bidon" doit contenir une (et une seule) ligne.

Là vous essayez d'insérer autant de fois la même ligne qu'il y en a dans la table... d'où le message indiquant la violation de la contriainte

[spirzouf]

Merci pour vos réponses !

Là vous essayez d'insérer autant de fois la même ligne qu'il y en a dans la table... d'où le message indiquant la violation de la contrainte

D'accord, déjà, en terme de performance, je vois bien l'intérêt d'une et une seule ligne.

Mais alors concernant la condition, elle devrait si la clé n'est PAS présente :
- déclencher l'insertion lors de la 1ère ligne,
- passer outre les fois suivantes,

Sauf s'il s'agit du COMMIT qui ne se fait qu'à la fin, du coup, à la 2nde ligne, l'insertion est faite mais non détectable par la condition, et on entre en conflit avec l'insertion que l'on vient juste de faire ? Ce qui expliquerait l'autre cas de la clé présente dès le début, où aucune erreur
n'est levée, car dès la première ligne, la condition empêche l'insertion et idem les lignes suivantes.

Pour info, j'utilise postgreSQL 9.2

(On est bien d'accord que c'est du triturage de cervelle, mais c'est juste pour comprendre comment cela fonctionne)

[punkoff]

Ceci serait vrai si vous effectuiez une deuxième requête dans la même transaction (sans commit intermédiaire).

Dans votre cas, la requête de sélection s’exécute dans un premier temps, puis vient l'insert.

[aieeeuuuuu]

Mais alors concernant la condition, elle devrait si la clé n'est PAS présente :
- déclencher l'insertion lors de la 1ère ligne,
- passer outre les fois suivantes,

Non, car le SQL est ensembliste : toutes les lignes sont insérées d'un coup.

Essayez simplement la partie SELECT de votre deuxième requête (sans le INSERT) et vous verrez ce que vous êtes en train d'essayer d'insérer.

[aieeeuuuuu]

Pour info, j'utilise postgreSQL 9.2

Alors vous n'avez même pas besoin de table bidon, faites simplement

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
 
INSERT INTO
    matable
    (   maclefprimaire
    ,   maclefetrangere
    ,   monattribut
    ) 
SELECT  1
    ,   1
    ,   'valeurtexte'
WHERE   NOT EXISTS
        (   SELECT  0
            FROM    matable
            WHERE   maclefprimaire = 1
        )
;

[SQLpro]

cela dit en terme de performances, la solution lambda en force brut sans sous requête sera notablement plus rapide et surtout, la durée du verrouillage étant moins longue donc on y gagnera en concurrence !

Pourquoi est-ce que les développeurs aiment faire des usines à gaz là ou des choses simples sont naturellement performantes ?

A +

[spirzouf]

cela dit en terme de performances, la solution lambda en force brut sans sous requête sera notablement plus rapide et surtout, la durée du verrouillage étant moins longue donc on y gagnera en concurrence !

Vous voulez dire une fonction anonyme PLPGSQL dans ce style (vous me faites découvrir ce concept sous postgreSQL) ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DO LANGUAGE PLPGSQL $$
DECLARE id_en retour INTEGER;
BEGIN
    SELECT id FROM matable WHERE id = XXX RETURNING INTO id_en retour;
    IF id_en retour IS NULL THEN
        INSERT INTO matable ... ;
    ELSE 
        UPDATE matable ... ;
    END IF;
END;
$$;

J'avais pensé faire une fonction enregistrée au début, mais laissé tomber pensant que justement, cela faisait plus usine à gaz qu'une sous-requête... Ce serait encore plus performant qu'une fonction lambda que l'on redéclare à chaque fois, non ?

Mais j'ai dû comprendre de travers, car là aussi, on a un SELECT avant l'INSERT, donc ce doit être similaire à la sous-requête en terme de performances ?

Pourquoi est-ce que les développeurs aiment faire des usines à gaz là ou des choses simples sont naturellement performantes ?

Je suis en pleine formation, alors je ne me sens pas (encore) visé