ProHoster > Блог > Administracija > PostgreSQL Antipatterns: prosljeđivanje skupova i odabira u SQL

PostgreSQL Antipatterns: prosljeđivanje skupova i odabira u SQL

S vremena na vrijeme programer treba proslijediti skup parametara ili čak cijeli odabir zahtjevu "na ulazu". Ponekad naiđete na vrlo čudna rješenja za ovaj problem.
PostgreSQL Antipatterns: prosljeđivanje skupova i odabira u SQL
Vratimo se unazad i vidimo šta ne treba raditi, zašto i kako to možemo učiniti bolje.

Direktno umetanje vrijednosti u tijelo zahtjeva

Obično izgleda otprilike ovako:

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

...ili ovako:

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

Ovaj metod je rečeno, napisano i čak i nacrtana dosta:

PostgreSQL Antipatterns: prosljeđivanje skupova i odabira u SQL

To je skoro uvek direktan put do SQL injekcija i nepotrebno opterećenje poslovne logike, koja je prinuđena da "zalijepi" liniju vašeg upita.

Ovaj pristup može se djelimično opravdati samo ako je potrebno korištenjem particioniranja u PostgreSQL verzijama 10 i starijim da biste dobili efikasniji plan. U ovim verzijama, lista skeniranih sekcija se utvrđuje bez uzimanja u obzir prenošenih parametara, samo na osnovu tijela zahtjeva.

$n-argumenata

Koristite placeholders parametri su dobri, omogućava vam korištenje PRIPREMLJENE IZJAVE, smanjujući opterećenje i na poslovnoj logici (niz upita se generira i prenosi samo jednom) i na poslužitelju baze podataka (ponovno raščlanjivanje i zakazivanje za svaku instancu upita nije potrebno).

Varijabilni broj argumenata

Problemi će nas čekati kada želimo da prenesemo nepoznat broj argumenata:

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

Ako ostavimo zahtjev u ovom obliku, iako će nas zaštititi od potencijalnih injekcija, to će i dalje dovesti do potrebe za spajanjem/raščlanjivanjem zahtjeva za svaku opciju u zavisnosti od broja argumenata. Bolje je nego raditi svaki put, ali možete i bez toga.

Dovoljno je prenijeti samo jedan parametar koji sadrži serijalizovana reprezentacija niza:

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

Jedina razlika je potreba da se argument eksplicitno pretvori u željeni tip niza. Ali to ne stvara probleme, jer već unaprijed znamo kuda idemo.

Prijenos uzorka (matrice)

Obično su to sve vrste opcija za prijenos skupova podataka za umetanje u bazu podataka "u jednom zahtjevu":

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

Pored gore opisanih problema sa „ponovnim lijepljenjem“ zahtjeva, to nas može dovesti i do ponestalo memorije i pad servera. Razlog je jednostavan - PG rezerviše dodatnu memoriju za argumente, a broj zapisa u setu ograničen je samo potrebama aplikacije poslovne logike. U posebno kliničkim slučajevima koje sam morao vidjeti „brojni“ argumenti su više od 9000 dolara - ne radi to ovako.

Prepišimo zahtjev koristeći već "dvostepena" serijalizacija:

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, u slučaju „složenih“ vrijednosti unutar niza, one moraju biti okružene navodnicima.
Jasno je da na ovaj način možete „proširiti“ izbor sa proizvoljnim brojem polja.

uznemiren, uznemiren,…

S vremena na vrijeme postoje opcije za prosljeđivanje umjesto "niza nizova" nekoliko "nizova kolona" koje sam spomenuo u prošlom članku:

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

Ovom metodom, ako pogriješite prilikom generiranja liste vrijednosti za različite stupce, vrlo je lako dobiti neočekivani rezultati, što takođe zavisi od verzije servera:

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

Od verzije 9.3, PostgreSQL je imao punopravne funkcije za rad sa json tipom. Stoga, ako se definicija ulaznih parametara pojavi u vašem pretraživaču, možete je formirati upravo tamo json objekt za SQL upit:

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

Za prethodne verzije može se koristiti ista metoda svaki (hstore), ali ispravna "konvolucija" s izbjegavanjem složenih objekata u hstore može uzrokovati probleme.

json_populate_recordset

Ako unaprijed znate da će se podaci iz “input” json niza koristiti za popunjavanje neke tablice, možete puno uštedjeti u “dereferenciranju” polja i prebaciti ih na tražene tipove pomoću funkcije 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

A ova funkcija će jednostavno "proširiti" proslijeđeni niz objekata u selekciju, bez oslanjanja na format tablice:

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

PRIVREMENI STOL

Ali ako je količina podataka u prenesenom uzorku vrlo velika, onda je ubacivanje u jedan serijalizirani parametar teško, a ponekad i nemoguće, jer zahtijeva jednokratnu dodijeliti veliku količinu memorije. Na primjer, potrebno je da sakupite veliki paket podataka o događajima iz vanjskog sistema dugo, dugo vremena, a zatim želite da ga obradite jednokratno na strani baze podataka.

U ovom slučaju, najbolje rješenje bi bilo korištenje privremeni stolovi:

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

Metoda je dobra za povremene transfere velikih količina podaci.
Sa stanovišta opisivanja strukture svojih podataka, privremena tabela se razlikuje od „obične“ samo na jedan način u sistemskoj tabeli pg_classand in pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ništa.

Dakle, u web sistemima sa velikim brojem kratkotrajnih veza za svaku od njih, takva tabela će svaki put generisati nove sistemske zapise, koji se brišu kada se veza sa bazom podataka zatvori. na kraju, nekontrolisana upotreba TEMP TABLE dovodi do "nabujanja" tabela u pg_catalogue i usporavanje mnogih operacija koje ih koriste.
Naravno, ovo se može riješiti korištenjem periodični prolaz VACUUM FULL prema tablicama sistemskog kataloga.

Varijable sesije

Pretpostavimo da je obrada podataka iz prethodnog slučaja prilično složena za jedan SQL upit, ali želite da to radite prilično često. Odnosno, želimo koristiti proceduralne obrade u DO blok, ali korištenje prijenosa podataka kroz privremene tablice će biti preskupo.

Također nećemo moći koristiti $n-parametre za prolazak u anonimni blok. Varijable sesije i funkcija će nam pomoći da se izvučemo iz ove situacije current_setting.

Prije verzije 9.2 bilo je potrebno unaprijed konfigurirati poseban imenski prostor prilagođene_promenljive_klase za "vaše" varijable sesije. Na trenutnim verzijama možete napisati nešto ovako:

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

Druga rješenja mogu se naći u drugim podržanim proceduralnim jezicima.

Znate li još neke načine? Podijelite u komentarima!

izvor: www.habr.com

Dodajte komentar