De temps en temps, le développeur doit
passer un ensemble de paramètres à la demande ou même une sélection entière de "input". Des solutions très étranges à ce problème se présentent parfois.
Allons «du contraire» et voyons comment cela ne vaut pas la peine, pourquoi et comment vous pouvez faire mieux.
Insertion directe de valeurs dans le corps de la demande
Cela ressemble généralement à ceci:
query = "SELECT * FROM tbl WHERE id = " + value
... ou alors:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)
À propos de cette méthode, il est dit, écrit et
même dessiné en abondance:
Il s'agit presque toujours d'un
chemin direct vers l'injection SQL et d'une charge supplémentaire sur la logique métier, qui est forcée de «coller» votre chaîne de requête.
Une telle approche ne peut être partiellement justifiée que s'il est nécessaire d'
utiliser la sectionnement dans les versions de PostgreSQL 10 et inférieures pour obtenir un plan plus efficace. Dans ces versions, la liste des sections scannées est déterminée même sans tenir compte des paramètres transmis, uniquement sur la base du corps de la requête.
$ n arguments
L'utilisation d'
espaces réservés pour les paramètres est bonne, elle vous permet d'utiliser des
ÉTATS PRÉPARÉS , ce qui réduit la charge à la fois sur la logique métier (une chaîne de requête est générée et transmise une seule fois) et sur le serveur de base de données (l'analyse et la planification pour chaque instance de la demande ne sont pas requises).
Nombre d'arguments variable
Des problèmes nous attendent lorsque nous voulons passer à l'avance un nombre inconnu d'arguments:
... id IN ($1, $2, $3, ...)
Si vous laissez la demande dans ce formulaire, alors même si cela nous évitera des injections potentielles, cela entraînera néanmoins 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 vous pouvez vous en passer.
Il suffit de passer un seul paramètre contenant la
représentation sérialisée du tableau :
... id = ANY($1::integer[])
La seule différence est la nécessité de convertir explicitement l'argument en le type de tableau souhaité. Mais cela ne pose pas de problème, car nous savons déjà à l'avance où nous nous adressons.
Transfert d'échantillons (matrices)
Habituellement, ce sont toutes sortes d'options pour transférer des ensembles de données à insérer dans la base de données «en une seule demande»:
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...
En plus des problèmes décrits ci-dessus avec le «re-collage» de la demande, cela peut également entraîner un
manque de mémoire et un crash 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 logique métier de la liste de souhaits appliquée. Dans les cas particulièrement cliniques, il fallait voir des
arguments «numérotés» supérieurs à 9 000 $ - pas la peine de le faire.
Nous réécrivons la demande, en appliquant la
sérialisation «à deux niveaux» :
INSERT INTO tbl SELECT unnest[1]::text k , unnest[2]::integer v FROM ( SELECT unnest($1::text[])::text[]
Oui, dans le cas de valeurs "complexes" à l'intérieur du tableau, elles doivent être entourées de guillemets.
Il est clair que vous pouvez ainsi "étendre" la sélection avec un nombre arbitraire de champs.
unnest, unnest, ...
Périodiquement, il existe des options de transmission au lieu d'un «tableau de tableaux» de plusieurs «tableaux de colonnes», que j'ai mentionné
dans un article précédent :
SELECT unnest($1::text[]) k , unnest($2::integer[]) v;
Avec cette méthode, en faisant une erreur lors de la génération de listes de valeurs pour différentes colonnes, il est très simple d'obtenir
des résultats complètement
inattendus , qui dépendent également de la version du serveur:
Json
À partir de la version 9.3, PostgreSQL a introduit des fonctions à part entière pour travailler avec le type json. Par conséquent, si la définition des paramètres d'entrée dans votre navigateur a lieu, vous pouvez créer un
objet json pour la requête SQL ici:
SELECT key k , value v FROM json_each($1::json);
Pour les versions précédentes, la même méthode peut être utilisée pour
chacun (hstore) , mais la "convolution" correcte avec des objets complexes s'échappant dans hstore peut provoquer des problèmes.
json_populate_recordset
Si vous savez à l'avance que les données du tableau json «d'entrée» iront pour remplir une sorte de table, vous pouvez économiser beaucoup en «déréférençant» les champs et en les convertissant en types nécessaires à l'aide de la fonction json_populate_recordset:
SELECT * FROM json_populate_recordset( NULL::pg_class , $1::json
json_to_recordset
Et cette fonction «étend» simplement le tableau d'objets transférés dans la sélection, sans se fier au format de la table:
SELECT * FROM json_to_recordset($1::json) T(k text, v integer);
TABLE TEMPORAIRE
Mais si la quantité de données dans l'échantillon transmis est très grande, il est difficile, et parfois impossible, de les jeter dans un paramètre sérialisé, car cela nécessite une
allocation unique
d'une grande quantité de mémoire . Par exemple, vous devez collecter un grand paquet de données sur les événements d'un système externe pendant très, très longtemps, puis vous souhaitez le traiter une fois du côté de la base de données.
Dans ce cas, la meilleure solution serait d'utiliser
des 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 rare de grandes quantités de données.
Du point de vue de la description de la structure de ses données, la table temporaire ne diffère de la table «normale» que par une fonction
dans la table système pg_class , et dans
pg_type, pg_depend, pg_attribute, pg_attrdef, ... - 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 sont supprimés avec la connexion à la base de données fermée. Par conséquent, l'
utilisation incontrôlée de TEMP TABLE entraîne le «gonflement» des tables dans pg_catalog et ralentit de nombreuses opérations qui les utilisent.
Bien sûr, cela peut être combattu à l'aide du
passage périodique VACUUM FULL dans les tables du catalogue système.
Variables de session
Supposons que le traitement des données du cas précédent soit assez compliqué pour une seule requête SQL, mais vous voulez le faire assez souvent. Autrement dit, nous voulons utiliser le traitement procédural dans le
bloc DO , mais l'utilisation du transfert de données via des tables temporaires sera trop coûteuse.
Nous ne pourrons pas non plus utiliser les paramètres $ n pour le transfert vers le bloc anonyme. Les variables de session et la fonction
current_setting nous aideront à sortir de cette situation.
Avant la version 9.2, il était nécessaire de préconfigurer un
espace de
noms custom_variable_classes pour «vos» 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;
D'autres langages de procédure pris en charge peuvent trouver d'autres solutions.
Connaissez-vous d'autres moyens? Partagez dans les commentaires!