ProHoster > Blog > Uprava > Protivzorci PostgreSQL: posredovanje naborov in izbir v SQL

Protivzorci PostgreSQL: posredovanje naborov in izbir v SQL

Od časa do časa razvijalec potrebuje zahtevi posredovati nabor parametrov ali celo celoten izbor "na vhodu". Včasih obstajajo zelo čudne rešitve tega problema.
Protivzorci PostgreSQL: posredovanje naborov in izbir v SQL
Pojdimo "od nasprotne" in poglejmo, kako tega ne storiti, zakaj in kako lahko to storite bolje.

Neposredno "vstavljanje" vrednosti v telo zahteve

Ponavadi je videti nekako takole:

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

... ali takole:

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

O tej metodi je rečeno, napisano in celo narisano dovolj:

Protivzorci PostgreSQL: posredovanje naborov in izbir v SQL

Skoraj vedno je neposredna pot do vbrizgavanja SQL in dodatno obremenitev poslovne logike, ki je prisiljena »zlepiti« vaš poizvedbeni niz.

Ta pristop je mogoče delno upravičiti le, če je potrebno. uporabite particioniranje v PostgreSQL različice 10 in nižje za učinkovitejši načrt. V teh različicah se seznam skeniranih odsekov določi brez upoštevanja poslanih parametrov, samo na podlagi telesa zahteve.

$n argumentov

Uporaba nadomestne oznake parametrov je dober, omogoča uporabo PRIPRAVLJENE IZJAVE, kar zmanjša obremenitev poslovne logike (poizvedbeni niz se oblikuje in prenese samo enkrat) in strežnika baze podatkov (ponovno razčlenjevanje in načrtovanje ni potrebno za vsak primerek zahteve).

Spremenljivo število argumentov

Težave nas bodo čakale, ko bomo želeli posredovati vnaprej neznano število argumentov:

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

Če pustite zahtevo v tej obliki, bo kljub temu, da nas bo to rešilo pred morebitnimi injekcijami, še vedno povzročilo potrebo po lepljenju / razčlenjevanju zahteve za vsako možnost iz števila argumentov. Že tako bolje, kot da bi to počeli vsakič, a lahko tudi brez tega.

Dovolj je, da posredujete samo en parameter, ki vsebuje serializirana predstavitev matrike:

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

Edina razlika je potreba po eksplicitni pretvorbi argumenta v želeno vrsto matrike. A to ne povzroča težav, saj že vnaprej vemo, kam se obračamo.

Prenos vzorca (matrica)

Običajno so to vse vrste možnosti za prenos nizov podatkov za vnos v bazo podatkov "v eni zahtevi":

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

Poleg zgoraj opisanih težav s "prelepljenjem" povpraševanja nas to lahko pripelje tudi do zmanjkalo je spomina in zrušitev strežnika. Razlog je preprost - PG rezervira dodaten pomnilnik za argumente, število zapisov v nizu pa je omejeno le s poslovno logično aplikacijo Wishlist. V posebej kliničnih primerih je bilo treba videti "oštevilčeni" argumenti, večji od 9000 $ - ne delaj tega na ta način.

Prepišemo poizvedbo, že se prijavimo "dvonivojsko" serializacijo:

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, v primeru "kompleksnih" vrednosti v matriki jih je treba uokviriti z narekovaji.
Jasno je, da lahko na ta način izbor »razširite« s poljubnim številom polj.

negnezdo, negnezdo, …

Občasno obstajajo možnosti za posredovanje več "nizov stolpcev", ki sem jih omenil, namesto "matrike nizov". v zadnjem članku:

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

S to metodo, če naredite napako pri ustvarjanju seznamov vrednosti za različne stolpce, je zelo enostavno popolnoma nepričakovani rezultati, kar je odvisno tudi od različice strežnika:

-- $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 različice 9.3 ima PostgreSQL polne funkcije za delo s tipom json. Torej, če so vaši vnosni parametri definirani v brskalniku, lahko kar tam in oblikujete json objekt za poizvedbo SQL:

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

Za prejšnje različice je mogoče uporabiti isto metodo vsak (hstore), vendar lahko pravilno "zlaganje" z uhajanjem kompleksnih predmetov v hstore povzroči težave.

json_populate_recordset

Če vnaprej veste, da bodo podatki iz "vhodne" matrike json šli za zapolnitev neke tabele, lahko veliko prihranite pri "dereferenciranju" polj in prelivanju v želene vrste s funkcijo 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

In ta funkcija bo preprosto "razširila" posredovano matriko predmetov v izbor, ne da bi se zanašala na obliko tabele:

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

ZAČASNA MIZA

Če pa je količina podatkov v poslanem vzorcu zelo velika, je njihovo zbiranje v en serijski parameter težko in včasih nemogoče, saj zahteva enkratno velika dodelitev pomnilnika. Na primer, dolgo, dolgo časa morate zbirati veliko količino podatkov o dogodkih iz zunanjega sistema, nato pa jih želite enkrat obdelati na strani baze podatkov.

V tem primeru bi bila najboljša rešitev uporaba začasne mize:

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

Metoda je dobra za redko prenašanje velikih količin podatke.
Z vidika opisa strukture svojih podatkov se začasna tabela od »navadne« tabele razlikuje le po eni lastnosti. v sistemski tabeli pg_class, in v pg_type, pg_depend, pg_attribute, pg_attrdef, ... — in prav nič.

Zato bo v spletnih sistemih z velikim številom kratkotrajnih povezav za vsako od njih takšna tabela vsakič znova ustvarila nove sistemske zapise, ki se ob prekinitvi povezave z bazo izbrišejo. sčasoma, nenadzorovana uporaba TEMP TABLE vodi do "nabrekanja" tabel v pg_catalogue in upočasnitev številnih operacij, ki jih uporabljajo.
Seveda se je proti temu mogoče boriti periodični prehod VAKUUM POLN po tabelah sistemskega kataloga.

Spremenljivke seje

Recimo, da je obdelava podatkov iz prejšnjega primera precej zapletena za eno poizvedbo SQL, vendar jo želite izvajati precej pogosto. To pomeni, da želimo uporabiti postopkovno obdelavo v DO blok, vendar bo prenos podatkov prek začasnih tabel predrag.

Prav tako ne moremo uporabiti parametrov $n za prehod v anonimni blok. Spremenljivke seje in funkcija nam bodo pomagale rešiti se iz situacije. trenutna_nastavitev.

Pred različico 9.2 ste morali predhodno konfigurirati poseben imenski prostor razredi_spremenljivk_po meri za "njihove" spremenljivke seje. V trenutnih različicah lahko napišete nekaj takega:

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

Na voljo so tudi druge rešitve v drugih podprtih proceduralnih jezikih.

Ali poznate več načinov? Delite v komentarjih!

Vir: www.habr.com

Dodaj komentar