PostgreSQL Antipatterns: trasmissione di set e selezioni in SQL

Periodicamente, lo sviluppatore ha la necessità di trasmettere un insieme di parametri o addirittura un'intera selezione « in ingresso ». A volte si incontrano soluzioni molto stravaganti per questo problema.

Andiamo «al contrario» e vediamo come non bisogna fare, perché e come si può fare meglio.

L'inserimento diretto di valori nel corpo della richiesta

Di solito appare più o meno così:

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

… oppure così:

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

Su questo metodo è stato detto, scritto e anche disegnato abbondantemente:

Quasi sempre questo è un modo diretto per le iniezioni SQL e per un carico eccessivo sulla logica di business, costretta a "incollare" la stringa della vostra richiesta.

Un approccio è parzialmente giustificato solo nel caso di necessità di utilizzare la partizione nelle versioni di PostgreSQL 10 e precedenti per ottenere un piano più efficiente. In queste versioni, l'elenco delle sezioni scansionate è determinato senza considerare i parametri trasmessi, solo sulla base del corpo della richiesta.

$n-argomenti

Utilizzo segnaposto parametri — è una buona cosa, poiché consente di utilizzare PREPARED STATEMENTS, riducendo il carico sia sulla logica di business (la stringa di richiesta viene formata e trasmessa solo una volta), sia sul server DB (non è necessario analizzare nuovamente e pianificare per ogni istanza della richiesta).

Numero variabile di argomenti

I problemi ci attenderanno quando vorremo trasmettere un numero sconosciuto di argomenti:

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

Se lasciamo la query in questo modo, anche se ci proteggerà da potenziali iniezioni, porterà comunque alla necessità di unioni / analisi della richiesta per ogni variante del numero di argomenti. Già meglio che farlo ogni volta, ma si può fare anche senza.

Basta trasmettere un solo 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é sappiamo già in anticipo dove ci stiamo indirizzando.

Trasmissione di un campione (matrice)

Di solito si tratta di varie opzioni per trasmettere set di dati da inserire nel database "in un'unica richiesta":

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

Oltre ai problemi descritti in precedenza con l'«incollaggio» della richiesta, questo può portare anche a out of memory e la caduta del server. La ragione è semplice: gli argomenti PG riservano memoria aggiuntiva, mentre il numero di record nel set è limitato solo dalle richieste dell'applicazione della logica aziendale. In casi clinici particolarmente gravi, è capitato di vedere argomenti "numerici" maggiori di $9000 — non bisogna fare così.

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 si può "espandere" la selezione con un numero arbitrario di campi.

unnest, unnest, …

Occasionalmente si incontrano varianti in cui al posto di "array di array" vengono passati diversi "array di colonne", di cui ho già parlato nell'articolo precedente:

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

Con questo approccio, si sbaglia facilmente durante la generazione delle liste di valori per diverse colonne e si ottiene molto risultati 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 |

JSON

A partire dalla versione 9.3, PostgreSQL ha introdotto funzioni complete per lavorare con il tipo json. Pertanto, se la definizione dei parametri di input avviene nel browser, puoi facilmente formare 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, è possibile utilizzare lo stesso metodo per each(hstore), ma una corretta "applicazione" con l'escaping di oggetti complessi in hstore può causare problemi.

json_populate_recordset

Se sai già in anticipo che i dati dell'array json "in ingresso" verranno utilizzati per riempire una certa tabella, puoi risparmiare notevolmente nell'"indirezione" dei campi e nel casting ai tipi desiderati, 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 "espanderà" l'array di oggetti passato in una selezione, senza concentrarsi 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

TABELLA TEMPORANEA

Ma se la quantità di dati nella selezione trasmessa è molto grande, allora inserirla in un solo parametro serializzato è difficile, e a volte impossibile, poiché richiede un"allocazione масив grande di memoria per un singolo evento. Ad esempio, è necessario raccogliere a lungo un grande pacchetto di dati sugli eventi da un sistema esterno e poi si desidera elaborarlo una sola volta sul lato del database.

In questo caso, la soluzione migliore sarà 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 questa tabella intera

Il metodo è particolarmente valido per il trasferimento raro di grandi volumi fine.
Dal punto di vista della descrizione della struttura dei propri dati, la tabella temporanea differisce da una «normale» solo per un'unica caratteristica nella tabella di sistema pg_class, e in pg_type, pg_depend, pg_attribute, pg_attrdef, … — e alla fine non differisce affatto.

Pertanto, nei sistemi web con un alto numero di connessioni a breve termine, per ciascuna di esse una tale tabella genererà nuove registrazioni di sistema ogni volta, che vengono eliminate alla chiusura della connessione con il database. Di conseguenza, l'uso incontrollato di TEMP TABLE porta all'«ingrossamento» delle tabelle in pg_catalog e al rallentamento di molte operazioni che le utilizzano.
Certo, è possibile affrontare questo problema tramite un passaggio periodico di VACUUM FULL sulle tabelle del catalogo di sistema.

Variabili di sessione

Supponiamo che l'elaborazione dei dati del caso precedente sia abbastanza complessa per una singola query SQL, ma vogliamo farla abbastanza spesso. Cioè, vogliamo utilizzare l'elaborazione procedurale nel blocco DO, ma utilizzare il passaggio di dati tramite tabelle temporanee sarebbe troppo gravoso.

Non possiamo nemmeno usare i parametri $n per passare a un blocco anonimo. 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 variabili di sessione «proprie». Nelle versioni attuali, invece, è possibile scrivere più o meno così:

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

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

Conoscete altri metodi? Condivideteli nei commenti!

Fonte: habr.com