ProHoster > Blog > Amministrazione > PostgreSQL Antipatterns: Passendu Sets è Selects à SQL

PostgreSQL Antipatterns: Passendu Sets è Selects à SQL

Da u tempu à u tempu un sviluppatore hà bisognu passà un settore di parametri o ancu una selezzione sana à a dumanda "à l'entrata". Calchì volta vi scontra suluzioni assai strana à stu prublema.
PostgreSQL Antipatterns: Passendu Sets è Selects à SQL
Andemu in daretu è vede ciò chì ùn deve micca fà, perchè, è cumu pudemu fà megliu.

Inserzione diretta di valori in u corpu di dumanda

Di solitu pare qualcosa cusì:

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

... o cusì:

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

Stu metudu hè statu dettu, scrittu è ancu disegnata assai:

PostgreSQL Antipatterns: Passendu Sets è Selects à SQL

Quasi sempre questu hè percorsu direttu à injections SQL è carica innecessaria nantu à a logica cummerciale, chì hè furzata à "colla" a vostra linea di dumanda.

Stu approcciu pò esse ghjustificatu parzialmente solu s'ellu hè necessariu utilizendu a particione in e versioni PostgreSQL 10 è sottu per ottene un pianu più efficau. In queste versioni, a lista di e rùbbriche scansate hè determinata senza piglià in contu i paràmetri trasmessi, solu nantu à a basa di u corpu di dumanda.

$n-argumenti

Usu placeholders paràmetri hè bonu, vi permette di aduprà DICLARAZIONI PREPARATE, riducendu a carica sia nantu à a logica di l'affari (a stringa di query hè generata è trasmessa una sola volta) è in u servitore di basa di dati (re-parsing è scheduling per ogni istanza di query ùn hè micca necessariu).

Variable numeru di argumenti

I prublemi ci aspittàranu quandu vulemu passà un numeru scunnisciutu di argumenti:

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

Se lascemu a dumanda in questa forma, ancu s'ellu ci prutegerà da iniezioni potenziali, hà sempre purtatu à a necessità di unisce / analizà a dumanda per ogni opzione secondu u numeru di argumenti. Hè megliu cà fà ogni volta, ma pudete fà senza.

Hè abbastanza à passà solu un paràmetru chì cuntene rapprisintazioni di matrice seriale:

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

L'unica diferenza hè a necessità di cunvertisce esplicitamente l'argumentu à u tipu di array desideratu. Ma questu ùn causa micca prublemi, postu chì sapemu digià in anticipu induve andemu.

Trasferimentu di una mostra (matrice)

Di solitu si tratta di ogni tipu d'opzioni per u trasferimentu di set di dati per inserisce in a basa di dati "in una dumanda":

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

In più di i prublemi descritti sopra cù "re-gluing" a dumanda, questu pò ancu guidà à noi fora di memoria è crash di u servitore. U mutivu hè simplice - PG riserva memoria supplementu per l'argumenti, è u numeru di registri in u settore hè limitatu solu da i bisogni di l'applicazione di a logica cummerciale. In casi particulari clinichi aghju avutu à vede L'argumenti "numeru" sò più di $ 9000 - ùn fate micca cusì.

Riscrivemu a dumanda usendu digià serializazione "dui 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;

Iè, in u casu di valori "cumplessi" in un array, devenu esse circundati da virgulette.
Hè chjaru chì in questu modu pudete "espansione" una selezzione cù un numeru arbitrariu di campi.

unnest, unnest, ...

Di tantu in tantu ci sò opzioni per passà invece di un "array of arrays" parechji "arrays of columns" chì aghju citatu in l'ultimu articulu:

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

Cù stu metudu, se fate un sbagliu quandu genera liste di valori per diverse colonne, hè assai faciule d'ottene. risultati inaspettati, chì dipende ancu da a versione di u servitore:

-- $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

Dapoi a versione 9.3, PostgreSQL hà avutu funzioni cumplete per travaglià cù u tipu json. Dunque, se a definizione di i paràmetri di input si trova in u vostru navigatore, pudete formà quì json object per a dumanda SQL:

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

Per versioni precedenti, u listessu metudu pò esse usatu ognunu (hstore), ma currettu "convolution" cù fughje uggetti cumplessi in hstore pò causari prublemi.

json_populate_recordset

Se sapete in anticipu chì i dati da l'array json "input" seranu utilizati per riempie qualchì tavula, pudete salvà assai in i campi "deferencing" è casting à i tipi richiesti usendu a 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 sta funzione solu "espansione" l'array di l'uggetti passati in una selezzione, senza s'appoghjanu à u formatu di a tavola:

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 a quantità di dati in a mostra trasferita hè assai grande, allora scaccià in un paràmetru serializatu hè difficiule è qualchì volta impussibile, postu chì hè bisognu di una sola volta. attribuisce una grande quantità di memoria. Per esempiu, avete bisognu di cullà un grande pacchettu di dati nantu à l'avvenimenti da un sistema esternu per un bellu pezzu, è poi vulete processà una volta nantu à a basa di dati.

In questu casu, a megliu suluzione seria di utilizà tavule tempuranee:

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

U metudu hè bonu per trasferimenti occasionali di grandi volumi dati.
Da u puntu di vista di a descrizzione di a struttura di e so dati, una tavula tempuranea difiere da una "regular" in una sola manera. in a tabella di sistema pg_classè in pg_type, pg_depend, pg_attribute, pg_attrdef, ... - nunda in tuttu.

Per quessa, in i sistemi web cù un gran numaru di cunnessione di corta durata per ognuna di elli, una tale tavula generarà ogni volta novi registri di u sistema, chì sò sguassati quandu a cunnessione à a basa di dati hè chjusa. Eventualmente, L'usu incontrollatu di TEMP TABLE porta à "inflazione" di e tavule in pg_catalog è rallentà parechje operazioni chì l'utilizanu.
Di sicuru, questu pò esse trattatu cù l'usu passage periodic VACUUM FULL secondu i tavule di u catalogu di u sistema.

Variabili di sessione

Assumimu chì u processu di e dati da u casu precedente hè abbastanza cumplessu per una dumanda SQL, ma vulete fà abbastanza spessu. Vale à dì, vulemu aduprà u prucessu procedurale in DO bloccu, ma l'usu di trasferimentu di dati à traversu tavule tempurane serà troppu caru.

Ùn pudemu ancu aduprà $n-parameters per passà à un bloccu anonimu. Variabili di sessione è a funzione ci aiutanu à esce da sta situazione paràmetru_corrente.

Prima di a versione 9.2 era necessariu di pre-configurazione spaziu di nomi speciale classi_variabili_personalizzate per "a vostra" variabili di sessione. Nant'à e versioni attuali pudete scrive qualcosa cum'è questu:

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

Altre soluzioni ponu esse truvate in altre lingue procedurali supportate.

Cunniscite altre manere ? Condividi in i cumenti!

Source: www.habr.com

Add a comment