Antipadrões do PostgreSQL: passando conjuntos e seleções para SQL

De vez em quando, o desenvolvedor precisa passar um conjunto de parâmetros ou até mesmo uma seleção inteira para a solicitação "na entrada". Às vezes, existem soluções muito estranhas para esse problema.

Vamos "pelo contrário" e veremos como não fazer, por que e como você pode fazer melhor.

"Inserção" direta de valores no corpo da solicitação

Geralmente é mais ou menos assim:

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

... ou assim:

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

Sobre este método é dito, escrito e mesmo desenhado suficiente:

Quase sempre é caminho direto para injeção de SQL e uma carga extra na lógica de negócios, que é forçada a “colar” sua string de consulta.

Esta abordagem pode ser parcialmente justificada apenas se necessário. usar particionamento nas versões 10 e anteriores do PostgreSQL para um plano mais eficiente. Nessas versões, a lista de trechos verificados é determinada sem levar em consideração os parâmetros transmitidos, apenas com base no corpo da solicitação.

$n argumentos

Usar espaços reservados parâmetros é bom, permite que você use DECLARAÇÕES PREPARADAS, reduzindo a carga tanto na lógica de negócios (a string de consulta é formada e transmitida apenas uma vez) quanto no servidor de banco de dados (reparsing e planejamento não são necessários para cada instância da solicitação).

Número variável de argumentos

Problemas nos aguardam quando queremos passar um número desconhecido de argumentos antecipadamente:

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

Se você deixar a solicitação neste formulário, embora isso nos salve de possíveis injeções, ainda levará à necessidade de colar / analisar a solicitação para cada opção do número de argumentos. Já é melhor do que fazer isso todas as vezes, mas você pode ficar sem isso.

Basta passar apenas um parâmetro contendo representação serializada de um array:

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

A única diferença é a necessidade de converter explicitamente o argumento para o tipo de array desejado. Mas isso não causa problemas, pois já sabemos de antemão para onde estamos nos dirigindo.

Transferência de amostra (matriz)

Normalmente, esses são todos os tipos de opções para transferir conjuntos de dados para inserção no banco de dados “em uma solicitação”:

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

Além dos problemas descritos acima com a "recolagem" do pedido, isso também pode nos levar a fora da memória e falha do servidor. O motivo é simples - o PG reserva memória adicional para os argumentos e o número de registros no conjunto é limitado apenas pela lista de desejos do aplicativo de lógica de negócios. Em casos especialmente clínicos, era necessário ver argumentos "numerados" superiores a US$ 9000 - não faça assim.

Vamos reescrever a consulta, aplicando já serialização de "dois níveis":

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;

Sim, no caso de valores "complexos" dentro de um array, eles precisam ser enquadrados com aspas.
É claro que desta forma você pode "expandir" a seleção com um número arbitrário de campos.

desaninhar, desaninhar, …

De tempos em tempos existem opções de passar ao invés de um "array de arrays" vários "arrays de colunas" que mencionei no último artigo:

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

Com este método, se você cometer um erro ao gerar listas de valores para diferentes colunas, é muito fácil obter completamente resultados inesperados, que também dependem da versão 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

A partir da versão 9.3, o PostgreSQL possui funções completas para trabalhar com o tipo json. Portanto, se seus parâmetros de entrada são definidos no navegador, você pode ali mesmo e formar objeto json para consulta SQL:

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

Para versões anteriores, o mesmo método pode ser usado para cada(hstore), mas a "dobragem" correta com escape de objetos complexos no hstore pode causar problemas.

json_populate_recordset

Se você sabe de antemão que os dados do array json “input” irão preencher alguma tabela, você pode economizar muito em “desreferenciar” campos e converter para os tipos desejados usando a função 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 função simplesmente “expandirá” a matriz de objetos passada em uma seleção, sem depender do formato da tabela:

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

TABELA TEMPORÁRIA

Mas se a quantidade de dados na amostra transmitida for muito grande, jogá-los em um parâmetro serializado é difícil e às vezes impossível, pois requer um único grande alocação de memória. Por exemplo, você precisa coletar um grande lote de dados de eventos de um sistema externo por muito, muito tempo e depois deseja processá-los uma vez no lado do banco de dados.

Nesse caso, a melhor solução seria usar tabelas temporárias:

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

O método é bom para transmissão pouco frequente de grandes volumes dados.
Do ponto de vista da descrição da estrutura de seus dados, uma tabela temporária difere de uma tabela “normal” em apenas uma característica. na tabela do sistema pg_classe em pg_type, pg_depend, pg_attribute, pg_attrdef, ... - e nada.

Portanto, em sistemas web com um grande número de conexões de curta duração para cada uma delas, tal tabela irá gerar novos registros do sistema a cada vez, que são apagados quando a conexão com o banco de dados é encerrada. Eventualmente, o uso descontrolado de TEMP TABLE leva ao "inchaço" das tabelas em pg_catalog e retardando muitas operações que os utilizam.
Claro, isso pode ser combatido com passe periódico VACUUM FULL de acordo com as tabelas do catálogo do sistema.

Variáveis ​​de sessão

Suponha que o processamento dos dados do caso anterior seja bastante complexo para uma única consulta SQL, mas você deseja fazê-lo com bastante frequência. Ou seja, queremos usar o processamento processual em Faça bloco, mas usar a transferência de dados por meio de tabelas temporárias será muito caro.

Também não podemos usar $n-parameters para passar para um bloco anônimo. As variáveis ​​de sessão e a função nos ajudarão a sair da situação. Configuração atual.

Antes da versão 9.2, você tinha que pré-configurar espaço de nomes especial classes_variáveis_personalizadas para "suas" variáveis ​​de sessão. Nas versões atuais, você pode escrever algo assim:

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

Existem outras soluções disponíveis em outras linguagens processuais suportadas.

Conhece mais maneiras? Compartilhe nos comentários!

Fonte: habr.com