Antipattern PostgreSQL: passaggio di insiemi e selezioni a SQL

Di tanto in tanto, lo sviluppatore ha bisogno passare alla richiesta un set di parametri o anche un'intera selezione "all'entrata". A volte ci sono soluzioni molto strane a questo problema.

Andiamo "dall'opposto" e vediamo come non farlo, perché e come puoi farlo meglio.

"Inserimento" diretto dei valori nel corpo della richiesta

Di solito assomiglia a questo:

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

... o così:

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

Su questo metodo è detto, scritto e anche disegnato Abbastanza:

Quasi sempre lo è percorso diretto a SQL injection e un carico aggiuntivo sulla logica aziendale, che è costretta a "incollare" la stringa di query.

Questo approccio può essere parzialmente giustificato solo se necessario. usa il partizionamento in PostgreSQL versioni 10 e precedenti per un piano più efficiente. In queste versioni, l'elenco delle sezioni scansionate viene determinato senza tener conto dei parametri trasmessi, solo sulla base del corpo della richiesta.

$n argomenti

l'uso di segnaposto parametri è buono, ti permette di usare DICHIARAZIONI PREPARATE, riducendo il carico sia sulla logica di business (la stringa di query viene formata e trasmessa una sola volta) sia sul server del database (non è richiesta la rianalisi e la pianificazione per ogni istanza della richiesta).

Numero variabile di argomenti

I problemi ci aspetteranno quando vorremo passare in anticipo un numero imprecisato di argomenti:

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

Se lasci la richiesta in questo modulo, anche se ci salverà da potenziali iniezioni, porterà comunque alla necessità di incollare / analizzare la richiesta per ogni opzione dal numero di argomenti. Già meglio che farlo ogni volta, ma puoi farne a meno.

È sufficiente passare un solo parametro contenente 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 desiderato. Ma questo non crea problemi, poiché sappiamo già in anticipo dove ci rivolgiamo.

Trasferimento del campione (matrice)

Di solito si tratta di tutti i tipi di opzioni per il trasferimento di set di dati da inserire nel database "in una richiesta":

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

Oltre ai problemi sopra descritti con il "reincollaggio" della richiesta, anche questo può portarci fuori dalla memoria e arresto anomalo del server. Il motivo è semplice: PG riserva memoria aggiuntiva per gli argomenti e il numero di record nel set è limitato solo dall'applicazione di logica aziendale Wishlist. In casi particolarmente clinici è stato necessario vedere argomenti "numerati" maggiori di $9000 - non farlo in questo modo.

Riscriviamo la query, applicando già 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 di un array, devono essere racchiusi tra virgolette.
È chiaro che in questo modo si può "espandere" la selezione con un numero arbitrario di campi.

unnest, unnest, …

Di tanto in tanto ci sono opzioni per passare invece di un "array di array" diversi "array di colonne" che ho menzionato nell'ultimo articolo:

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

Con questo metodo, se commetti un errore durante la generazione di elenchi di valori per colonne diverse, è molto facile ottenerli completamente 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 funzioni complete per lavorare con il tipo json. Pertanto, se i tuoi parametri di input sono definiti nel browser, puoi direttamente lì e formare oggetto json per 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 ciascuno (hstore), ma il "folding" corretto con l'escape di oggetti complessi in hstore può causare problemi.

json_populate_recordset

Se sai in anticipo che i dati dall'array json "input" andranno a riempire qualche tabella, puoi risparmiare molto in campi "dereferenziati" e trasmettere 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

E questa funzione semplicemente "espanderà" l'array passato di oggetti in una selezione, senza fare affidamento 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

TAVOLO PROVVISORIO

Ma se la quantità di dati nel campione trasmesso è molto grande, inserirli in un parametro serializzato è difficile e talvolta impossibile, poiché richiede un'unica grande allocazione di memoria. Ad esempio, è necessario raccogliere un grande batch di dati di eventi da un sistema esterno per molto, molto tempo, quindi si desidera elaborarli una volta sul lato del database.

In questo caso, la soluzione migliore sarebbe usare tavoli temporanei:

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

Il metodo è buono per la trasmissione poco frequente di grandi volumi dati.
Dal punto di vista della descrizione della struttura dei suoi dati, una tabella temporanea differisce da una tabella "normale" in una sola caratteristica. nella tabella di sistema pg_class, mentre in pg_type, pg_depend, pg_attribute, pg_attrdef, ... - e niente di niente.

Pertanto, nei sistemi web con un numero elevato di connessioni di breve durata per ciascuna di esse, tale tabella genererà ogni volta nuovi record di sistema, che vengono eliminati quando viene chiusa la connessione al database. Infine, l'uso incontrollato di TEMP TABLE porta al "gonfiamento" delle tabelle in pg_catalog e rallentando molte operazioni che li utilizzano.
Naturalmente, questo può essere combattuto con passaggio periodico VUOTO PIENO secondo le tabelle del catalogo di sistema.

Variabili di sessione

Si supponga che l'elaborazione dei dati del caso precedente sia piuttosto complessa per una singola query SQL, ma si desideri eseguirla abbastanza spesso. Cioè, vogliamo utilizzare l'elaborazione procedurale in DO blocco, ma l'utilizzo del trasferimento dati tramite tabelle temporanee sarà troppo costoso.

Inoltre, non possiamo utilizzare i parametri $n per passare a un blocco anonimo. Le variabili di sessione e la funzione ci aiuteranno a uscire dalla situazione. impostazioni correnti.

Prima della versione 9.2, dovevi preconfigurare spazio dei nomi speciale classi_variabili_personalizzate per le "loro" variabili di sessione. Nelle versioni attuali, puoi scrivere qualcosa del genere:

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

Sono disponibili altre soluzioni in altri linguaggi procedurali supportati.

Conosci più modi? Condividi nei commenti!

Fonte: habr.com