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é

[SQLpro]

Non, tout ceci devrait se résumer à un INSERT sans aucune sous requête.

Si l'insert réussit, c'est que la clef n'existait pas. S'il ne réussit pas, c'est que la clef existait. Il ne sert donc à rien de prévérifier quelque chose qui sera vérifié lors de l'insertion de la valeur ! Vous ne faites que surcharger le système et faire une opération inutile en double.

A +

[spirzouf]

OK, merci.
Effectivement, mauvaise logique de programmation de vouloir masquer une erreur en faisant 2 requêtes au lieu d'une. Mieux vaut juste que je change côté client pour gérer les erreurs attendues

Par contre s'il l'on veut systématiquement faire un UPDATE en cas d'échec d'INSERT, ça ne me paraît pas pertinent que le client attende l'erreur puis renvoie une demande d'UPDATE.
La solution suivante est-elle alors logique et performante (inspirée de la doc postgreSQL) ?

Code :

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

1
2
3
4
5
BEGIN
    INSERT INTO ...;
EXCEPTION WHEN unique_violation THEN
    UPDATE ...
END;

[al1_24]

La commande MERGE est là pour ça

[skuatamad]

Alain, Je ne sais pas si MERGE existe sur postgre.

Par contre spirzouf, vous noterez que l'exemple de la doc fait l'INSERT directement, et en cas de violation de contrainte un UPDATE, ce qui est différent de faire un test avec un SELECT (test qui ne garantit pas qu'il n'y aura pas d'erreur de violation de contrainte en concurrence d'accès, donc définitivement inutile)

L'exemple de la doc correspond donc tout à fait à la remarque de SQLpro (même si pour ce genre de besoin MERGE est plus approprié)

[spirzouf]

Bonjour et merci pour cette piste, mais MERGE ne semble pas supporté par postgreSQL (non vu dans la doc)

[spirzouf]

vous noterez que l'exemple de la doc fait l'INSERT directement, et en cas de violation de contrainte un UPDATE, ce qui est différent de faire un test avec un SELECT (test qui ne garantit pas qu'il n'y aura pas d'erreur de violation de contrainte en concurrence d'accès, donc définitivement inutile)

L'exemple de la doc correspond donc tout à fait à la remarque de SQLpro (même si pour ce genre de besoin MERGE est plus approprié)

Merci, je voulais juste avoir confirmation que j'avais bien compris le truc (et l'exemple donné était bien SANS sous-requête, j'avais omis de le préciser)
Je passe en résolu.

[SQLpro]

Bonjour et merci pour cette piste, mais MERGE ne semble pas supporté par postgreSQL (non vu dans la doc)

Effectivement PG est en retard sur ce sujet, mais a proposé quelque chose de tout à fait hors norme avec les CTE de mise à jour combinées.
C'est assez dégueulasse, mais ça marche :
http://vibhorkumar.wordpress.com/201...ostgresql-9-1/

A +

[spirzouf]

Code :

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

1
2
3
4
5
WITH upsert as
(update mytable2 m set sales=m.sales+d.sales, status=d.status from mytable d where m.pid=d.pid
  RETURNING m.*
)
insert into mytable2 select a.pid, a.sales,'NEW' from mytable a where a.pid not in (select b.pid from upsert b);

Si je comprends bien :

Fonctionnement :
1. sous-requête qui fait UPDATE et renvoie les pid concernés
2. requête principale qui fait INSERT en testant d'abord si le pid vient d'être mis à jour.

Avantages :
- aucun code d'erreur renvoyé.
- compacité du code

Inconvénients :
- Plus il y aura eu d'UPDATE, moins la requête sera performante, car pour chaque ligne il faudra vérifier un grand nombre de pid déjà mis à jour : est-ce malgré tout plus performant qu'un bloc contenant EXCEPTION ? j'ai faux, car INSERT uniquement ligne par ligne visiblement avec cette technique.
- ne gère pas les accès concurrents (insertion via un autre processus avant d'arriver sur l'INSERT de ce pid) : quid d'un bloc EXCEPTION ?

[SQLpro]

Inconvénients :
- Plus il y aura eu d'UPDATE, moins la requête sera performante, car pour chaque ligne il faudra vérifier un grand nombre de pid déjà mis à jour : est-ce malgré tout plus performant qu'un bloc contenant EXCEPTION ? j'ai faux, car INSERT uniquement ligne par ligne visiblement avec cette technique.
- ne gère pas les accès concurrents (insertion via un autre processus avant d'arriver sur l'INSERT de ce pid) : quid d'un bloc EXCEPTION ?

EXCEPTION ne fait rien de plus que traquer les erreurs. Cela ne joue pas sur la consistance de la transaction...
En principe une requête => une transaction.
La norme SQL à admis une seule exception, celle concernant les opérations ensemblistes UNION, EXCEPT et INTERSECT.
De toute façon, le problème sera vite réglé avec PG, car il fait du verrouillage optimiste par défaut ce qui veut dire que soit l'opération est réussie, soit elle est en échec, parce que entre la tentative avortée d'UPDATE et l'INSERT, quelqu'un a inséré une ligne en parallèle.

A +