ProHoster > Blog > Administración > Antipatróns de PostgreSQL: pasar conxuntos e seleccións a SQL

Antipatróns de PostgreSQL: pasar conxuntos e seleccións a SQL

De cando en vez un desenvolvedor precisa pasar un conxunto de parámetros ou incluso unha selección completa á solicitude "na entrada". Ás veces atopas solucións moi estrañas para este problema.
Antipatróns de PostgreSQL: pasar conxuntos e seleccións a SQL
Imos cara atrás e vexamos que non facer, por que e como podemos facelo mellor.

Inserción directa de valores no corpo da solicitude

Normalmente parece algo así:

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

... ou así:

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

Este método foi dito, escrito e mesmo debuxado abundancia:

Antipatróns de PostgreSQL: pasar conxuntos e seleccións a SQL

Case sempre isto é camiño directo ás inxeccións SQL e carga innecesaria na lóxica empresarial, que se ve obrigada a "pegar" a súa liña de consulta.

Este enfoque só pode xustificarse parcialmente se é necesario usando partición nas versións 10 e posteriores de PostgreSQL para obter un plan máis eficiente. Nestas versións, a lista de seccións dixitalizadas determínase sen ter en conta os parámetros transmitidos, só en función do corpo da solicitude.

$n-argumentos

Usar marcadores de posición parámetros é bo, permíteche usar DECLARACIONS PREPARADAS, reducindo a carga tanto na lóxica de negocio (a cadea de consulta xérase e transmítese só unha vez) como no servidor de base de datos (non é necesario re-analizar e programar cada instancia de consulta).

Número variable de argumentos

Os problemas agardaránnos cando queremos pasar un número descoñecido de argumentos:

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

Se deixamos a solicitude neste formulario, aínda que nos protexerá de posibles inxeccións, seguirá provocando a necesidade de fusionar/analizar a solicitude para cada opción dependendo do número de argumentos. É mellor que facelo cada vez, pero podes prescindir del.

É suficiente pasar só un parámetro que contén representación de matriz serializada:

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

A única diferenza é a necesidade de converter explícitamente o argumento ao tipo de matriz desexado. Pero isto non causa problemas, xa que xa sabemos de antemán cara onde imos.

Transferencia dunha mostra (matriz)

Normalmente estes son todo tipo de opcións para transferir conxuntos de datos para a súa inserción na base de datos "nunha solicitude":

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

Ademais dos problemas descritos anteriormente con "volver pegar" a solicitude, isto tamén nos pode levar sen memoria e fallo do servidor. O motivo é sinxelo: PG reserva memoria adicional para argumentos e o número de rexistros do conxunto está limitado só polas necesidades da aplicación da lóxica empresarial. En casos especialmente clínicos tiven que ver Os argumentos "números" son máis de $ 9000 - non o fagas deste xeito.

Reescribamos a solicitude usando xa serialización de "dous niveis".:

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;

Si, no caso de valores "complexos" dentro dunha matriz, deben estar rodeados de comiñas.
Está claro que deste xeito pode "ampliar" unha selección cun número arbitrario de campos.

desconcerto, desconcerto,...

De cando en vez hai opcións para pasar en lugar dunha "matriz de matrices" varias "matriz de columnas" que mencionei no artigo anterior:

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

Con este método, se cometes un erro ao xerar listas de valores para diferentes columnas, é moi sinxelo obter resultados inesperados, que tamén dependen da versión do servidor:

-- $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

Desde a versión 9.3, PostgreSQL ten funcións completas para traballar co tipo json. Polo tanto, se a definición dos parámetros de entrada ocorre no teu navegador, podes formala alí mesmo objeto json para consulta SQL:

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

Para versións anteriores, pódese usar o mesmo método cada (hstore), pero a "convolución" correcta con escape de obxectos complexos en hstore pode causar problemas.

json_populate_recordset

Se sabes de antemán que os datos da matriz json de "entrada" se usarán para encher algunha táboa, podes aforrar moito nos campos de "desreferenciación" e envialos aos tipos necesarios usando a función 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

E esta función simplemente "ampliará" a matriz de obxectos pasados ​​nunha selección, sen depender do formato da táboa:

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

MESA TEMPORAL

Pero se a cantidade de datos na mostra transferida é moi grande, entón botalos nun parámetro serializado é difícil e ás veces imposible, xa que require unha soa vez asignar unha gran cantidade de memoria. Por exemplo, cómpre recoller un gran paquete de datos sobre eventos dun sistema externo durante moito, moito tempo e, a continuación, quere procesalo unha soa vez no lado da base de datos.

Neste caso, a mellor solución sería usar táboas temporais:

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

O método é bo para transferencias ocasionais de grandes volumes datos.
Desde o punto de vista de describir a estrutura dos seus datos, unha táboa temporal difire dunha "regular" só nunha forma. na táboa do sistema pg_class, e dentro pg_type, pg_depend, pg_attribute, pg_attrdef, ... - non é nada.

Polo tanto, nos sistemas web cun gran número de conexións de curta duración para cada un deles, tal táboa xerará cada vez novos rexistros do sistema, que se eliminan cando se pecha a conexión á base de datos. Finalmente, o uso incontrolado de TEMP TABLE leva a un "inchazo" das táboas en pg_catalog e ralentizando moitas operacións que as usan.
Por suposto, isto pódese tratar usando paso periódico VACUUM FULL segundo as táboas do catálogo do sistema.

Variables de sesión

Supoñamos que procesar os datos do caso anterior é bastante complexo para unha consulta SQL, pero quere facelo con bastante frecuencia. É dicir, queremos utilizar o procesamento procesual en Bloque DO, pero usar a transferencia de datos a través de táboas temporais será demasiado caro.

Tampouco poderemos usar $n-parameters para pasar a un bloque anónimo. As variables de sesión e a función axudaranos a saír desta situación configuración_actual.

Antes da versión 9.2 era necesario preconfigurar espazo de nomes especial clases_variables_personalizadas para as "súas" variables de sesión. Nas versións actuais podes escribir algo así:

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

Pódense atopar outras solucións noutras linguaxes de procedemento admitidas.

Coñeces outras formas? Comparte nos comentarios!

Fonte: www.habr.com

Engadir un comentario