De vez en cuando, el desarrollador necesita
pasar un conjunto de parámetros a la solicitud o incluso una selección completa de "entrada". A veces surgen soluciones muy extrañas a este problema.
Vayamos "desde el opuesto" y veamos cómo no vale la pena hacerlo, por qué y cómo puedes mejorar.
Inserción directa de valores en el cuerpo de la solicitud
Por lo general, se parece a esto:
query = "SELECT * FROM tbl WHERE id = " + value
... más o menos:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)
Sobre este método se dice, se escribe y
hasta se dibuja abundantemente:
Casi siempre, esta es una
ruta directa a la inyección de SQL y una carga adicional en la lógica de negocios, que se ve obligada a "pegar" su cadena de consulta.
Tal enfoque puede justificarse parcialmente solo si es necesario
usar el seccionado en versiones de PostgreSQL 10 y versiones anteriores para obtener un plan más eficiente. En estas versiones, la lista de secciones escaneadas se determina incluso sin tener en cuenta los parámetros transmitidos, solo en función del cuerpo de la solicitud.
$ n argumentos
El uso de
marcadores de posición de parámetros es bueno, le permite usar
DECLARACIONES PREPARADAS , lo que reduce la carga tanto en la lógica empresarial (se genera una cadena de consulta y se transmite solo una vez) como en el servidor de la base de datos (no es necesario volver a analizar ni programar para cada instancia de la solicitud).
Numero variable de argumentos
Los problemas nos esperarán cuando queramos pasar por adelantado una cantidad desconocida de argumentos:
... id IN ($1, $2, $3, ...)
Si deja la solicitud en este formulario, aunque nos salvará de posibles inyecciones, sin embargo, dará lugar a la necesidad de pegar / analizar la solicitud
de cada opción a partir del número de argumentos . Ya es mejor que hacerlo cada vez, pero puedes prescindir de él.
Es suficiente pasar solo un parámetro que contenga la
representación serializada de la matriz :
... id = ANY($1::integer[])
La única diferencia es la necesidad de convertir explícitamente el argumento al tipo de matriz deseado. Pero esto no causa problemas, ya que ya sabemos de antemano a dónde nos dirigimos.
Transferencia de muestra (matrices)
Por lo general, estas son todo tipo de opciones para transferir conjuntos de datos para su inserción en la base de datos "en una sola solicitud":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...
Además de los problemas descritos anteriormente con la "reposición" de la solicitud, esto también puede provocar la falta
de memoria y el bloqueo del servidor. La razón es simple: PG reserva memoria adicional para los argumentos, y el número de registros en el conjunto está limitado solo por la lógica comercial de la lista de deseos aplicada. En casos especialmente clínicos, uno tenía que ver
argumentos "numerados" superiores a $ 9,000 , sin necesidad de hacerlo.
Reescribimos la solicitud, aplicando la
serialización de "dos niveles" :
INSERT INTO tbl SELECT unnest[1]::text k , unnest[2]::integer v FROM ( SELECT unnest($1::text[])::text[]
Sí, en el caso de valores "complejos" dentro de la matriz, deben estar entre comillas.
Está claro que de esta manera puede "expandir" la selección con un número arbitrario de campos.
desagradable, desagradable, ...
Periódicamente, hay opciones de transmisión en lugar de una "matriz de matrices" de varias "matrices de columnas", que mencioné
en un artículo anterior :
SELECT unnest($1::text[]) k , unnest($2::integer[]) v;
Con este método, al cometer un error al generar listas de valores para diferentes columnas, es muy sencillo obtener
resultados completamente
inesperados , que también dependen de la versión del servidor:
Json
A partir de la versión 9.3, PostgreSQL introdujo funciones completas para trabajar con el tipo json. Por lo tanto, si se lleva a cabo la definición de los parámetros de entrada en su navegador, puede crear un
objeto json para la consulta SQL allí mismo:
SELECT key k , value v FROM json_each($1::json);
Para versiones anteriores, se puede usar el mismo método para
cada (hstore) , pero la "convolución" correcta con el escape de objetos complejos en hstore puede causar problemas.
json_populate_recordset
Si sabe de antemano que los datos de la matriz json "input" irán a llenar algún tipo de tabla, puede ahorrar mucho en "desreferenciar" los campos y convertir a los tipos necesarios utilizando la función json_populate_recordset:
SELECT * FROM json_populate_recordset( NULL::pg_class , $1::json
json_to_recordset
Y esta función simplemente "expande" el conjunto de objetos transferidos a la selección, sin depender del formato de tabla:
SELECT * FROM json_to_recordset($1::json) T(k text, v integer);
TABLA TEMPORAL
Pero si la cantidad de datos en la muestra transmitida es muy grande, entonces arrojarlos en un parámetro serializado es difícil, y a veces imposible, porque requiere una
asignación única
de una gran cantidad de memoria . Por ejemplo, debe recopilar un gran paquete de datos sobre eventos de un sistema externo durante mucho, mucho tiempo, y luego desea procesarlo una vez en el lado de la base de datos.
En este caso, la mejor solución sería usar
tablas temporales :
CREATE TEMPORARY TABLE tbl(k text, v integer); ... INSERT INTO tbl(k, v) VALUES($1, $2);
El método es bueno
para la transmisión rara de grandes cantidades de datos.
Desde el punto de vista de describir la estructura de sus datos, la tabla temporal difiere de la característica "normal" solo por una
en la tabla del sistema pg_class , y en
pg_type, pg_depend, pg_attribute, pg_attrdef, ... - nada en absoluto.
Por lo tanto, en los sistemas web con una gran cantidad de conexiones de corta duración para cada uno de ellos, dicha tabla generará nuevos registros del sistema cada vez, que se eliminarán con la conexión a la base de datos cerrada. Como resultado, el
uso incontrolado de TEMP TABLE conduce a la "expansión" de las tablas en pg_catalog y ralentiza muchas operaciones que las usan.
Por supuesto, esto se puede combatir con la ayuda del
pase periódico VACUUM FULL a través de las tablas del catálogo del sistema.
Variables de sesión
Suponga que el procesamiento de datos del caso anterior es bastante complicado para una sola consulta SQL, pero desea hacerlo con la frecuencia suficiente. Es decir, queremos usar el procesamiento de procedimientos en el
bloque DO , pero usar la transferencia de datos a través de tablas temporales será demasiado costoso.
Tampoco podremos usar los parámetros $ n para transferir al bloque anónimo. Las variables de sesión y la función
current_setting nos ayudarán a salir de esta situación.
Antes de la versión 9.2, era necesario preconfigurar un
espacio de
nombres custom_variable_classes para "sus" variables de sesión. En las versiones actuales, puede escribir algo como esto:
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;
Otros lenguajes de procedimiento admitidos pueden encontrar otras soluciones.
¿Conoces más formas? ¡Comparte en los comentarios!