Antipatterns PostgreSQL : transmission d'ensembles et de sélections à SQL

De temps en temps, le développeur a besoin passer un jeu de paramètres voire une sélection entière à la requête "à l'entrée". Parfois, il existe des solutions très étranges à ce problème.

Partons "de l'opposé" et voyons comment ne pas le faire, pourquoi et comment vous pouvez le faire mieux.

"Insertion" directe de valeurs dans le corps de la requête

Cela ressemble généralement à ceci :

query = "SELECT * FROM tbl WHERE id = " + value

... ou comme ceci :

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

A propos de cette méthode, il est dit, écrit et même dessiné assez:

C'est presque toujours chemin direct vers l'injection SQL et une charge supplémentaire sur la logique métier, qui est obligée de "coller" votre chaîne de requête.

Cette approche ne peut être partiellement justifiée que si nécessaire. utiliser le partitionnement dans les versions 10 et inférieures de PostgreSQL pour un plan plus efficace. Dans ces versions, la liste des tronçons scannés est déterminée sans tenir compte des paramètres transmis, uniquement sur la base du corps de la requête.

$n arguments

l'utilisation de espaces réservés les paramètres sont bons, cela permet d'utiliser DÉCLARATIONS PRÉPARÉES, réduisant la charge à la fois sur la logique métier (la chaîne de requête est formée et transmise une seule fois) et sur le serveur de base de données (la réanalyse et la planification ne sont pas nécessaires pour chaque instance de la requête).

Nombre variable d'arguments

Des problèmes nous attendront lorsque nous voudrons passer un nombre inconnu d'arguments à l'avance :

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Si vous laissez la demande dans ce formulaire, alors même si cela nous évitera des injections potentielles, cela entraînera toujours la nécessité de coller / analyser la demande pour chaque option à partir du nombre d'arguments. Déjà mieux que de le faire à chaque fois, mais on peut s'en passer.

Il suffit de passer un seul paramètre contenant représentation sérialisée d'un tableau:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

La seule différence est la nécessité de convertir explicitement l'argument dans le type de tableau souhaité. Mais cela ne pose pas de problèmes, puisque nous savons déjà à l'avance où nous nous adressons.

Transfert d'échantillon (matrice)

Il s'agit généralement de toutes sortes d'options pour transférer des ensembles de données à insérer dans la base de données "en une seule requête":

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Outre les problèmes décrits ci-dessus avec le "recollage" de la demande, cela peut aussi nous amener à Mémoire insuffisante et plantage du serveur. La raison est simple - PG réserve de la mémoire supplémentaire pour les arguments et le nombre d'enregistrements dans l'ensemble n'est limité que par la liste de souhaits de l'application de logique métier. Dans des cas particulièrement cliniques, il était nécessaire de voir arguments "numérotés" supérieurs à 9000 $ - ne le faites pas de cette façon.

Réécrivons la requête, en appliquant déjà sérialisation "à deux niveaux":

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

Oui, dans le cas de valeurs "complexes" à l'intérieur d'un tableau, elles doivent être encadrées de guillemets.
Il est clair que de cette manière, vous pouvez "étendre" la sélection avec un nombre arbitraire de champs.

non imbriquée, non imbriquée, …

De temps en temps, il existe des options pour passer au lieu d'un "tableau de tableaux" plusieurs "tableaux de colonnes" que j'ai mentionnés dans le dernier article:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Avec cette méthode, si vous faites une erreur lors de la génération de listes de valeurs pour différentes colonnes, il est très facile d'obtenir complètement résultats inattendus, qui dépendent également de la version du serveur :

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

À partir de la version 9.3, PostgreSQL dispose de fonctions complètes pour travailler avec le type json. Par conséquent, si vos paramètres d'entrée sont définis dans le navigateur, vous pouvez directement là et former objet json pour requête SQL:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Pour les versions précédentes, la même méthode peut être utilisée pour chacun(hstore), mais un "pliage" correct avec échappement d'objets complexes dans hstore peut causer des problèmes.

json_populate_recordset

Si vous savez à l'avance que les données du tableau json "d'entrée" iront remplir une table, vous pouvez économiser beaucoup dans les champs de "déréférencement" et de conversion vers les types souhaités à l'aide de la fonction json_populate_recordset :

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Et cette fonction "étendra" simplement le tableau d'objets transmis dans une sélection, sans s'appuyer sur le format du tableau :

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

TABLE TEMPORAIRE

Mais si la quantité de données dans l'échantillon transmis est très importante, il est difficile, voire impossible, de le jeter dans un paramètre sérialisé, car cela nécessite une seule fois grande allocation de mémoire. Par exemple, vous devez collecter un grand nombre de données d'événement à partir d'un système externe pendant très longtemps, puis vous souhaitez le traiter une seule fois du côté de la base de données.

Dans ce cas, la meilleure solution serait d'utiliser tables temporaires:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

La méthode est bonne pour la transmission peu fréquente de gros volumes données
Du point de vue de la description de la structure de ses données, une table temporaire diffère d'une table « régulière » par une seule caractéristique. dans la table système pg_classet pg_type, pg_depend, pg_attribute, pg_attrdef, ... — et rien du tout.

Par conséquent, dans les systèmes Web avec un grand nombre de connexions de courte durée pour chacun d'eux, une telle table générera à chaque fois de nouveaux enregistrements système, qui seront supprimés lorsque la connexion à la base de données sera fermée. Finalement, l'utilisation incontrôlée de TEMP TABLE conduit à un "gonflement" des tables dans pg_catalog et ralentir de nombreuses opérations qui les utilisent.
Bien sûr, cela peut être combattu avec passage périodique VIDE PLEIN selon les tables du catalogue système.

Variables de session

Supposons que le traitement des données du cas précédent soit assez complexe pour une seule requête SQL, mais que vous vouliez le faire assez souvent. Autrement dit, nous voulons utiliser le traitement procédural dans Blocage DO, mais l'utilisation du transfert de données via des tables temporaires sera trop coûteuse.

Nous ne pouvons pas non plus utiliser $n-paramètres pour passer à un bloc anonyme. Les variables de session et la fonction vont nous aider à sortir de la situation. paramètre actuel.

Avant la version 9.2, vous deviez préconfigurer espace de noms spécial classes_de_variables_personnalisées pour "leurs" variables de session. Sur les versions actuelles, vous pouvez écrire quelque chose comme ceci :

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

Il existe d'autres solutions disponibles dans d'autres langages procéduraux pris en charge.

Connaître plus de façons? Partagez dans les commentaires !

Source: habr.com