Antipatterns PostgreSQL: trecerea de seturi și selecții la SQL

Din când în când, dezvoltatorul are nevoie treceți la cerere un set de parametri sau chiar o selecție întreagă "la intrare". Uneori există soluții foarte ciudate la această problemă.

Să mergem „de la opus” și să vedem cum să nu o facem, de ce și cum o poți face mai bine.

„Inserarea” directă a valorilor în corpul cererii

De obicei arată cam așa:

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

... sau cam asa:

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

Despre această metodă se spune, se scrie și chiar desenat suficient:

Aproape întotdeauna este calea directă către injecția SQL și o încărcare suplimentară asupra logicii de afaceri, care este forțată să „lipească” șirul de interogare.

Această abordare poate fi parțial justificată numai dacă este necesar. utilizați partiționarea în PostgreSQL versiunile 10 și mai jos pentru un plan mai eficient. În aceste versiuni, lista secțiunilor scanate este determinată fără a ține cont de parametrii transmisi, doar pe baza corpului cererii.

$n argumente

Folosi substituenți parametrii este bun, vă permite să utilizați DECLARAȚII PREGĂTITE, reducând încărcarea atât pe logica de business (șirul de interogare este format și transmis o singură dată), cât și pe serverul bazei de date (reparsarea și planificarea nu sunt necesare pentru fiecare instanță a cererii).

Număr variabil de argumente

Ne vor aștepta probleme atunci când dorim să transmitem un număr necunoscut de argumente în avans:

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

Dacă lăsați cererea în acest formular, atunci, deși ne va salva de potențiale injecții, va duce totuși la necesitatea de a lipi/analiza cererea pentru fiecare opțiune din numărul de argumente. Deja mai bine decât să o faci de fiecare dată, dar te poți descurca fără ea.

Este suficient să treceți un singur parametru care conține reprezentare serializată a unui tablou:

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

Singura diferență este necesitatea de a converti explicit argumentul în tipul de matrice dorit. Dar acest lucru nu creează probleme, deoarece știm deja dinainte unde ne adresăm.

Transfer de eșantion (matrice)

De obicei, acestea sunt tot felul de opțiuni pentru transferul de seturi de date pentru inserarea în baza de date „într-o singură cerere”:

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

Pe lângă problemele descrise mai sus cu „re-lipirea” cererii, aceasta ne poate duce și la fara memorie și blocarea serverului. Motivul este simplu - PG rezervă memorie suplimentară pentru argumente, iar numărul de înregistrări din set este limitat doar de aplicația de logica de afaceri Wishlist. În cazuri mai ales clinice a fost necesar să se vadă argumente „numerotate” mai mari de 9000 USD - nu face acest lucru.

Să rescriem interogarea, aplicând deja serializare „pe două niveluri”.:

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;

Da, în cazul valorilor „complexe” într-o matrice, acestea trebuie să fie încadrate cu ghilimele.
Este clar că în acest fel puteți „extinde” selecția cu un număr arbitrar de câmpuri.

dezlănțuit, dezgustă,...

Din când în când există opțiuni pentru a trece în loc de „matrice de matrice” mai multe „matrice de coloane” pe care le-am menționat in ultimul articol:

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

Cu această metodă, dacă faceți o greșeală când generați liste de valori pentru diferite coloane, este foarte ușor să obțineți complet rezultate neașteptate, care depind și de versiunea serverului:

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

Începând cu versiunea 9.3, PostgreSQL are funcții complete pentru lucrul cu tipul json. Prin urmare, dacă parametrii dvs. de intrare sunt definiți în browser, vă puteți forma chiar acolo obiect json pentru interogarea SQL:

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

Pentru versiunile anterioare, aceeași metodă poate fi folosită pentru fiecare(hmagazin), dar „plierea” corectă cu evadarea obiectelor complexe din hstore poate cauza probleme.

json_populate_recordset

Dacă știți dinainte că datele din matricea json „input” vor merge pentru a completa un tabel, puteți economisi multe în câmpurile „dereferențiare” și turnarea la tipurile dorite folosind funcția 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

Și această funcție va „extinde” pur și simplu matricea de obiecte transmise într-o selecție, fără a se baza pe formatul tabelului:

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

TABEL TEMPORAR

Dar dacă cantitatea de date din eșantionul transmis este foarte mare, atunci aruncarea acesteia într-un parametru serializat este dificilă și uneori imposibilă, deoarece necesită o singură dată. alocare mare de memorie. De exemplu, trebuie să colectați un lot mare de date despre evenimente de la un sistem extern pentru o perioadă lungă de timp, apoi doriți să le procesați o singură dată pe partea bazei de date.

În acest caz, cea mai bună soluție ar fi utilizarea mese temporare:

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

Metoda este buna pentru transmiterea nefrecventă a volumelor mari date.
Din punctul de vedere al descrierii structurii datelor sale, un tabel temporar diferă de un tabel „obișnuit” într-o singură caracteristică. în tabelul de sistem pg_class, și în pg_type, pg_depend, pg_attribute, pg_attrdef, ... — și nimic.

Prin urmare, în sistemele web cu un număr mare de conexiuni de scurtă durată pentru fiecare dintre ele, un astfel de tabel va genera noi înregistrări de sistem de fiecare dată, care sunt șterse atunci când conexiunea la baza de date este închisă. În cele din urmă, utilizarea necontrolată a TEMP TABLE duce la „umflarea” tabelelor din pg_catalog și încetinirea multor operațiuni care le folosesc.
Desigur, acest lucru poate fi combatet trecere periodică VACUUM FULL conform tabelelor de catalog de sistem.

Variabile de sesiune

Să presupunem că procesarea datelor din cazul anterior este destul de complexă pentru o singură interogare SQL, dar doriți să o faceți destul de des. Adică dorim să folosim procesarea procedurală în DO blocați, dar folosirea transferului de date prin tabele temporare va fi prea costisitoare.

De asemenea, nu putem folosi $n-parametri pentru a trece la un bloc anonim. Variabilele de sesiune și funcția ne vor ajuta să ieșim din situație. Setari curente.

Înainte de versiunea 9.2, trebuia să preconfigurați spațiu de nume special clase_variabile_personalizate pentru variabilele de sesiune „lor”. Pe versiunile curente, puteți scrie ceva de genul acesta:

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

Există și alte soluții disponibile în alte limbi procedurale acceptate.

Știi mai multe moduri? Distribuie in comentarii!

Sursa: www.habr.com