INSERT IF NOT EXISTS

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:

---

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:

---

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 ? 8O

bonjour,

quel est votre sgbd ?

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

Citation:

---

Envoyé par al1_24

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).

---

Citation:

---

Envoyé par spirzouf

Pourquoi ? 8O

---

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

Merci pour vos réponses !

Citation:

---

Envoyé par aieeeuuuuu

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)

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.

Citation:

---

Envoyé par spirzouf

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.

Citation:

---

Envoyé par spirzouf

Pour info, j'utilise postgreSQL 9.2

---

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

Code:

---

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 ) ;

---

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 +

Citation:

---

Envoyé par 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 !

---

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

Code:

---

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 ?

Citation:

---

Envoyé par SQLpro

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é :mouarf:

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 +

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:

---

1 2 3 4 5

BEGIN INSERT INTO ...; EXCEPTION WHEN unique_violation THEN UPDATE ... END;

---

La commande MERGE est là pour ça

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é)

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

Citation:

---

Envoyé par skuatamad

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) :ccool:
Je passe en résolu.

Citation:

---

Envoyé par spirzouf

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 +

Code:

---

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 ?

Citation:

---

Envoyé par spirzouf

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 +