Les fonctionnalités des mécanismes internes de PostgreSQL lui permettent d'être très rapide dans certaines situations et moins rapide dans d'autres. Aujourd'hui, nous nous attarderons sur l'exemple classique d'un conflit entre le fonctionnement du SGBD et ce que le développeur en fait - UPDATE vs MVCC .Tracez brièvement un excellent article :Lorsqu'une ligne est modifiée avec la commande UPDATE, deux opérations sont réellement effectuées: DELETE et INSERT. Dans la version actuelle de la ligne , xmax est défini comme égal au numéro de la transaction qui a effectué UPDATE. Ensuite, une nouvelle version de la même ligne est créée; sa valeur xmin correspond à la valeur xmax de la version précédente.
Quelque temps après la fin de cette transaction, l'ancienne ou la nouvelle version, selon celle-ci COMMIT/ROOLBACK, sera reconnue comme «morte» (tuples morts) lors du passage VACUUMdans la table et nettoyée.
Mais cela ne se produira pas tout de suite, mais les problèmes avec les «morts» peuvent être acquis très rapidement - avec des mises à jour multiples ou en masse des enregistrements dans une grande table, et un peu plus tard, face à une situation que VACUUM ne pourra pas aider .# 1: J'aime le déplacer
Supposons que votre méthode sur la logique métier fonctionne pour elle-même et se rend soudain compte qu'il serait nécessaire de mettre à jour le champ X dans un enregistrement:UPDATE tbl SET X = <newX> WHERE pk = $1;
Puis, au fur et à mesure de sa progression, il découvre que le champ Y doit également être mis à jour:UPDATE tbl SET Y = <newY> WHERE pk = $1;
... et puis aussi Z - pourquoi chier quelque chose?UPDATE tbl SET Z = <newZ> WHERE pk = $1;
Combien de versions de cet enregistrement ont maintenant dans la base de données? Ouais, 4 pièces! Parmi ceux-ci, un est pertinent, et 3 devront ramasser [auto] VIDE pour vous.Ne fais pas ça! Utilisez la mise à jour de tous les champs dans une seule demande - presque toujours la logique de la méthode peut être modifiée comme ceci:UPDATE tbl SET X = <newX>, Y = <newY>, Z = <newZ> WHERE pk = $1;
# 2: Utilisez EST DISTINCT DE, Luke!
Donc, vous vouliez toujours mettre à jour de très nombreux enregistrements dans la table (lors de l'utilisation d'un script ou d'un convertisseur par exemple). Et quelque chose comme ça vole dans le script:UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2;
Dans environ ce formulaire, une requête est rencontrée assez souvent et presque toujours pour ne pas remplir un nouveau champ vide, mais pour corriger certaines erreurs dans les données. De plus, l' exactitude des données déjà existantes n'est pas du tout prise en compte - mais en vain! Autrement dit, le dossier est en cours de réécriture, même si c'était exactement ce que je voulais - pourquoi? Correct:UPDATE tbl SET X = <newX> WHERE pk BETWEEN $1 AND $2 AND X IS DISTINCT FROM <newX>;
Beaucoup de gens ne sont pas conscients de l'existence d'un opérateur aussi merveilleux, alors voici une feuille de triche pour d' IS DISTINCT FROMautres opérateurs logiques pour vous aider:... et un peu sur les opérations sur les ROW()expressions complexes :# 3: Je reconnais ma chérie en ... bloquant
Exécutez deux processus parallèles identiques , dont chacun est destiné à la marque d'enregistrement, qu'il est "en fonctionnement":UPDATE tbl SET processing = TRUE WHERE pk = $1;
Même si ces processus font substantiellement des choses indépendamment les uns des autres, mais dans le cadre d'un identifiant, sur cette demande, le deuxième client se "verrouille" jusqu'à ce que la première transaction soit terminée.Solution n ° 1 : la tâche est réduite à la précédente.Ajoutez simplement à nouveau IS DISTINCT FROM:UPDATE tbl SET processing = TRUE WHERE pk = $1 AND processing IS DISTINCT FROM TRUE;
Dans ce formulaire, la deuxième demande ne changera tout simplement rien dans la base de données, elle est déjà «tout est comme il se doit» - par conséquent, le blocage ne se produira pas. De plus, le fait de la "non-existence" de l'enregistrement est déjà traité dans l'algorithme appliqué.Décision numéro 2 : verrous consultatifsUn grand sujet pour un article séparé dans lequel vous pouvez lire sur les méthodes d'application et le "rake" des verrous de recommandation .Solution n ° 3 : sans [d] appels intelligentsMais exactement, exactement, vous devriez avoir un travail simultané avec le même enregistrement ? Ou avez-vous toujours gâché les algorithmes d'appel de logique métier côté client, par exemple? Et si vous y pensez? ..