PostgreSQL Antipatterns: passaggio di insiemi e selezioni in SQL

Periodicamente, un sviluppatore ha la necessità di inviare a una richiesta un insieme di parametri o persino un'intera selezione in ingresso. A volte si presentano soluzioni molto strane per questo problema.

Partiamo "dall'opposto" e vediamo come non si dovrebbe fare, perché, e come si può fare meglio.

Inserimento diretto di valori nel corpo della richiesta

Di solito appare circa così:

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

… o così:

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

Di questo metodo è stato detto, scritto e persino illustrato in abbondanza:

Quasi sempre questo — è una strada diretta verso le SQL injection e un carico eccessivo sulla logica aziendale, costretta a "incollare" la stringa della tua query.

Un approccio del genere potrebbe essere parzialmente giustificato solo nel caso in cui sia necessario utilizzare il partizionamento nelle versioni di PostgreSQL 10 e precedenti per ottenere un piano più efficiente. In queste versioni, l'elenco delle sezioni da esaminare è determinato ancora senza considerare i parametri trasmessi, solo sulla base del corpo della query.

$n-argomenti

L'utilizzo di caratteri segnaposto parametri — questo è positivo, poiché consente di utilizzare PREPARED STATEMENTS, riducendo il carico sia sulla logica aziendale (la stringa della query viene formata e inviata solo una volta), sia sul server DB (non è necessario ripetere l'analisi e la pianificazione per ogni istanza della query).

Numero variabile di argomenti

I problemi ci aspettano quando vogliamo inviare un numero di argomenti sconosciuto in anticipo:

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

Se lasciamo la query in questo modo, essa ci proteggerà dalle potenziali injection, ma porterà comunque alla necessità di incollare/analizzare la query per ogni variante in base al numero di argomenti.È già migliore rispetto a farlo ogni volta, ma si può fare anche senza di questo.

È sufficiente inviare solo un parametro contenente una rappresentazione serializzata di un array:

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

L'unica differenza è la necessità di convertire esplicitamente l'argomento nel tipo di array richiesto. Ma questo non causa problemi, poiché già sappiamo dove stiamo indirizzando.

Trasmissione di una selezione (matrice)

Di solito si tratta di varianti per inviare set di dati da inserire nel database "con una sola richiesta":

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

Oltre ai problemi di "incollaggio" della query descritti sopra, questo può anche portarci a out of memory e al crash del server. La ragione è semplice: per gli argomenti PG riserva memoria aggiuntiva, e il numero di record nel set è limitato solo dalle esigenze logiche aziendali. In casi estremi, è stato necessario vedere "argomenti numerati" superiori a $9000 — non è necessario farlo.

Riscriviamo la query, applicando già una "serializzazione a due livelli":

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;

Sì, nel caso di "valori complessi" all'interno dell'array, è necessario racchiuderli tra virgolette.
È chiaro che in questo modo possiamo "sviluppare" una selezione con un numero arbitrario di campi.

unnest, unnest, …

A volte si incontrano varianti di invio invece di "array di array" di diversi "array di colonne", di cui ho parlato nell'articolo precedente:

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

Con questo metodo, sbagliando nella generazione di elenchi di valori per diverse colonne, è molto facile ottenere risultati completamente inaspettati, che dipendono anche dalla versione del server:-- $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 |

A partire dalla versione 9.3, PostgreSQL ha introdotto funzioni complete per lavorare con il tipo json. Pertanto, se la definizione dei parametri di ingresso avviene nel browser, puoi formare direttamente lì

JSON

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

Per le versioni precedenti, si può utilizzare lo stesso metodo per

each(hstore) , ma la corretta "compressione" con l'escape di oggetti complessi in hstore può causare problemi.json_populate_recordset

Se sai in anticipo che i dati da un array json "in ingresso" andranno a riempire una qualche tabella, puoi risparmiare molto nella "derivazione" dei campi e nella conversione ai tipi necessari, utilizzando la funzione 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

Questa funzione semplicemente "sviluppa" l'array di oggetti inviato in una selezione, senza basarsi sul formato della tabella:

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

TEMPORARY TABLE

Ma se la quantità di dati nella selezione inviata è molto grande, allora inserirla in un solo parametro serializzato è pesante, e talvolta anche impossibile, poiché richiede

l'allocazione di una grande quantità di memoria. assegnazioni di grande capacità di memoria. Ad esempio, potrebbe essere necessario raccogliere a lungo un grande pacchetto di dati sugli eventi da un sistema esterno e poi elaborarli in un'unica volta sul lato del database.

In questo caso, la soluzione migliore è l'uso di tabelle temporanee:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ripetere molte, molte volte
...
-- qui facciamo qualcosa di utile con l'intera tabella

Questo approccio è particolarmente indicato per la trasmissione non frequente di grandi volumi dei dati.
Dal punto di vista della struttura dei dati, una tabella temporanea differisce da una "normale" solo per un singolo attributo nella tabella di sistema pg_class, e nella pg_type, pg_depend, pg_attribute, pg_attrdef, … — e per nulla.

Pertanto, nei sistemi web con un alto numero di connessioni a breve termine, per ognuna di esse questa tabella genererà nuove voci di sistema ogni volta, che vengono eliminate alla chiusura della connessione al database. Di conseguenza, un uso incontrollato delle TEMP TABLE porta a un "indurimento" delle tabelle in pg_catalog e rallenta molte operazioni che le utilizzano.
Naturalmente, è possibile affrontare questo problema con un passaggio periodico di VACUUM FULL sulle tabelle del catalogo di sistema.

Variabili di sessione

Supponiamo che l'elaborazione dei dati dal caso precedente sia sufficientemente complessa per una singola query SQL, ma vogliamo eseguirla piuttosto frequentemente. Vogliamo quindi utilizzare l'elaborazione procedurale all'interno di un blocco DO, ma usare il passaggio dei dati tramite tabelle temporanee sarà troppo oneroso.

Non possiamo nemmeno usare i parametri $n per il passaggio all'interno del blocco anonimo. Possiamo risolvere la questione con le variabili di sessione e la funzione current_setting.

Fino alla versione 9.2 era necessario configurare in anticipo uno spazio dei nomi speciale custom_variable_classes per le "nostre" variabili di sessione. Nelle versioni attuali, si può scrivere in questo modo:

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

In altri linguaggi procedurali supportati si possono trovare anche altre soluzioni.

Conoscete altri metodi? Condividete nei commenti!

Fonte: habr.com