De tempos em tempos, o desenvolvedor precisa
passar um conjunto de parâmetros para a solicitação ou até mesmo uma seleção inteira de "entradas". Às vezes, soluções muito estranhas para esse problema são encontradas.
Vamos "do contrário" e ver como não vale a pena fazer, por que e como você pode fazer melhor.
Inserção direta de valores no corpo da solicitação
Geralmente é algo parecido com isto:
query = "SELECT * FROM tbl WHERE id = " + value
... mais ou menos:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)
Sobre este método, é dito, escrito e
até desenhado em abundância:
Quase sempre, esse é um
caminho direto para a injeção de SQL e uma carga extra na lógica de negócios, que é forçada a "colar" sua string de consulta.
Essa abordagem pode ser parcialmente justificada apenas se for necessário
usar o seccionamento nas versões do PostgreSQL 10 e inferior para obter um plano mais eficiente. Nessas versões, a lista de seções digitalizadas é determinada mesmo sem levar em consideração os parâmetros transmitidos, apenas com base no corpo da solicitação.
Argumentos $ n
O uso de
espaços reservados de parâmetros é bom, pois permite que você use
DECLARAÇÃO PREPARADA , reduzindo a carga na lógica de negócios (uma sequência de consultas é gerada e transmitida apenas uma vez) e no servidor de banco de dados (não é necessária nova análise e agendamento para cada instância da solicitação).
Número variável de argumentos
Os problemas nos aguardam quando queremos passar com antecedência um número desconhecido de argumentos:
... id IN ($1, $2, $3, ...)
Se você deixar a solicitação neste formulário, ainda que ela 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 sempre, mas você pode ficar sem ele.
É suficiente passar apenas um parâmetro que contém a
representação serializada da matriz :
... id = ANY($1::integer[])
A única diferença é a necessidade de converter explicitamente o argumento para o tipo de matriz desejado. Mas isso não causa problemas, pois já sabemos com antecedência para onde estamos nos dirigindo.
Transferência de amostra (matrizes)
Geralmente, existem 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 "aderência" da solicitação, isso também pode levar à
falta de memória e travamento 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 lógica comercial de Wishlist aplicada. Em casos especialmente clínicos, era preciso ver
argumentos "numerados" superiores a US $ 9.000 - sem necessidade.
Reescrevemos a solicitação, aplicando a
serialização de "dois níveis" :
INSERT INTO tbl SELECT unnest[1]::text k , unnest[2]::integer v FROM ( SELECT unnest($1::text[])::text[]
Sim, no caso de valores "complexos" dentro da matriz, eles precisam estar entre aspas.
É claro que dessa maneira você pode "expandir" a seleção com um número arbitrário de campos.
ninho, ninho, ...
Periodicamente, existem opções de transmissão em vez de uma "matriz de matrizes" de várias "matrizes de colunas", que mencionei
em um artigo anterior :
SELECT unnest($1::text[]) k , unnest($2::integer[]) v;
Com esse método, cometendo um erro ao gerar listas de valores para diferentes colunas, é muito simples obter
resultados completamente
inesperados , que também dependem da versão do servidor:
Json
A partir da versão 9.3, o PostgreSQL introduziu funções completas para trabalhar com o tipo json. Portanto, se a definição dos parâmetros de entrada no seu navegador ocorrer, você poderá criar um
objeto json para a consulta SQL :
SELECT key k , value v FROM json_each($1::json);
Para versões anteriores, o mesmo método pode ser usado para
cada (hstore) , mas a "convolução" correta com escape de objetos complexos no hstore pode causar problemas.
json_populate_recordset
Se você sabe com antecedência que os dados da matriz json "input" irão preencher algum tipo de tabela, você poderá economizar muito em "desreferenciar" os campos e transmitir os tipos necessários usando a função json_populate_recordset:
SELECT * FROM json_populate_recordset( NULL::pg_class , $1::json
json_to_recordset
E essa função simplesmente "expande" a matriz de objetos transferidos para a seleção, sem depender do formato da tabela:
SELECT * FROM json_to_recordset($1::json) T(k text, v integer);
TABELA TEMPORÁRIA
Mas se a quantidade de dados na amostra transmitida for muito grande, é difícil jogá-los em um parâmetro serializado e, às vezes, impossível, porque exige uma
alocação única
de uma grande quantidade de memória . Por exemplo, você precisa coletar um grande pacote de dados sobre eventos de um sistema externo por muito, muito tempo e, em seguida, deseja processá-lo 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 a transmissão rara de grandes quantidades de dados.
Do ponto de vista da descrição da estrutura de seus dados, a tabela temporária difere da "normal" apenas por um recurso
na tabela do sistema pg_class e em
pg_type, pg_depend, pg_attribute, pg_attrdef, ... - nada.
Portanto, em sistemas Web com um grande número de conexões de curta duração para cada um deles, essa tabela gera novos registros do sistema a cada vez, que são excluídos com a conexão com o banco de dados fechada. Como resultado, o
uso não controlado do TEMP TABLE leva ao "aumento" das tabelas no pg_catalog e diminui a velocidade de muitas operações que as utilizam.
Obviamente, isso pode ser combatido com a ajuda da
passagem periódica VACUUM FULL pelas tabelas do catálogo do sistema.
Variáveis de sessão
Suponha que o processamento de dados do caso anterior seja bastante complicado para uma única consulta SQL, mas você deseja fazê-lo com bastante frequência. Ou seja, queremos usar o processamento processual no
bloco DO , mas usar a transferência de dados através de tabelas temporárias será muito caro.
Também não poderemos usar os parâmetros n $ n para transferir para o bloco anônimo. As variáveis de sessão e a função
current_setting nos ajudarão a sair dessa situação.
Antes da versão 9.2, era necessário pré-configurar um
espaço para
nome custom_variable_classes para as variáveis de sessão “your”. Nas versões atuais, você pode escrever algo como isto:
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;
Outras linguagens processuais suportadas podem encontrar outras soluções.
Você conhece mais maneiras? Compartilhe nos comentários!