PostgreSQL antipatternid: andmehulkade ja valikute saatmine SQL-is

Arendaja peab perioodiliselt edastama päringus teatud parameetrid või isegi terve valiku «sisendisse». Mõnikord on selle ülesande lahendused väga kummalised. Alustame „vastupidiselt” ja vaatame, kuidas mitte teha, miks ja kuidas saab paremini.

Otsene „süstitud” väärtuste lisamine päringu kehasse

Tavaliselt näeb see välja umbes nii:

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

… või nii:

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

Sellest meetodist on räägitud, kirjutatud ja

isegi joonistatud piisavalt: Peaaegu alati viib see -

otse SQL süstimise teele ja liigse koormuseni äri loogikas, mis peab „liimima” teie päringu rea. Osaliselt on see lähenemine õigustatud ainult juhul, kui on vaja

kasutada jaotust PostgreSQL 10 ja vanemates versioonides efektiivse plaani saamiseks. Nendes versioonides määratakse skaneeritavate sektsioonide loetelu enne edastatavaid parameetreid, ainult päringu keha alusel. $n-argumendid

kohtade hoidjad

Kasutamine parameetrid - see on hea, sest see võimaldab kasutada PREPARED STATEMENTS , vähendades koormust nii äriloogikas (päringurea moodustamine ja edastamine toimub ainult üks kord) kui ka andmebaasi serveris (ei ole vaja korduvat käsitlemist ja planeerimist iga päringu eksemplari jaoks).Muutuva arvu argumente

Probleemid ootavad meid, kui soovime edastada ette teadmata arvu argumente:

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

Kui jätta päring selliseks, siis see hoiab meid küll potentsiaalsete süstide eest, kuid toob endaga kaasa vajaduse päringu liimimiseks/analüüsimiseks

iga variandi jaoks argumendi arvust. See on juba parem kui teha seda iga kord, kuid selleta on võimalik hakkama saada.Piisab, kui edastada vaid üks parameeter, mis sisaldab

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

Ainus erinevus on vajadus selgelt muundada argument soovitud massiivi tüübiks. Kuid see ei tekita probleeme, kuna me juba teame, kuhu suuname.

Valiku (maatriksi) edastamine

Tavaliselt on need erinevad variandid andmekogumite edastamiseks andmebaasi „ühe päringuga”:

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

Lisaks eespool kirjeldatud probleemidele „liimimisega” võib see meid veelgi viia

mälu ületamiseni out of memory ja serveri langemisele. Põhjus on lihtne — PG reservib täiendavat mälu, ning ridade arv komplektis on piiratud ainult rakenduse äriloogika soovidega. Erakordsetel juhtudel on nähtud «numbrilised» argumendid üle $9000 — ära nii tee.

Korrigeerime päringu, rakendades juba «kaheastmelist» serialiseerimist:

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;

Jah, juhul kui massiivi sees on «keerukamad» väärtused, tuleb need ümber ringi panna jutumärkidesse.
On selge, et sellisel viisil saab «lahti võetakse» valiku, millel on meelevaldne arv välju.

unnest, unnest, …

Perioodiliselt esinevad variandid, kus «massiväli» asemel edastatakse mitu «veergude massiivi», millest ma varem rääkisin eelmisel artiklis:

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

Sellise meetodi puhul, kui teete vigu erinevate veergude väärtuste loomiseks, on väga lihtne saada täiesti üllatavaid tulemusi, mis sõltuvad ka serveri versioonist:

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

Alates versioonist 9.3 on PostgreSQL-is ilmunud täisfunktsionaalsed funktsioonid JSON-tüübi käsitlemiseks. Seetõttu, kui teie sisendi parameetrite määramine toimub brauseris, saate seal otse luua JSON-objekt SQL päringu jaoks:

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

Eelnevate versioonide jaoks saab sama meetodit kasutada each(hstore), kuid korrektne «kokkuvõte» keerukate objektide majandamisega hstore'is võib tekitada probleeme.

json_populate_recordset

Kui teate eelnevalt, et andmed «sisenemise» JSON-massiivist lähevad mõne tabeli täitmiseks, saate oluliselt kokku hoida «de-referentsite» väljade määramisel ja sobivate tüüpide konverteerimisel, kasutades funktsiooni 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

See funktsioon lihtsalt «lahti võetakse» edastatud objekti massiiv valikuks, tuginedes tabeli formaadile:

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

AJUTINE TABEL

Aga kui edastatava valiku andmemaht on väga suur, siis selle laskmine ühte serialiseeritud parameetrisse — on keeruline, ja mõnikord isegi võimatu, kuna see nõuab ühekordset suurte mälu mahtude eraldamistNäiteks, kui teil on vaja kaua-kaua koguda suur andmepakett välisest süsteemist ja siis tahate selle korraga andmebaasis töödelda.

Sellisel juhul on parim lahendus ajutised tabelid:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- korrata palju-palju kordi
...
-- siin teeme midagi kasulikku terve selle tabeliga

Meetod on hea just harvade suurte andmemahtude edastamiseks andmeid.
Andmestruktuuri kirjeldamise seisukohalt erineb ajutine tabel „tavalisest” ainuselt ühe tunnuse poolest süsteemitabelis pg_classtoob kaasa selle, et "saadetamise" hetkel server ei ole veel käivitatud ja kõik läheb katki. pg_type, pg_depend, pg_attribute, pg_attrdef, … — muidu ei erinegi.

Seetõttu genereerib veebisüsteemides, kus on palju lühiajalisi ühendusi, iga kord nende jaoks selline tabel uusi süsteemi registreeringuid, mis kustutatakse andmebaasiga ühenduse lõpetamisel. Tulemuseks on, kontrollimatu TEMP TABLE kasutamine viib pg_catalog'i tabelite „paisumiseni” ja paljude nende kasutavate operatsioonide aeglustumiseni.
Loomulikult on selle vastu võimalik võidelda aeg-ajalt VACUUM FULL'i läbiviimisega süsteemi katalooge tabeleid.

Sessiooni muutujad

Oletame, et varasema juhtumi andmete töötlemine on piisavalt keeruline ühe SQL-käsku jaoks, kuid tahame seda teha piisavalt sageli. See tähendab, et soovime kasutada protseduurilist töötlemist DO-plokis, kuid andmete edastamine ajutiste tabelite kaudu on liiga kulukas.

$n-parameetrite kasutamine anonüümse ploki andmete edastamiseks ei ole samuti võimalik. Lahenduseks on meil sessiooni muutujad ja funktsioon current_setting.

Enne versiooni 9.2 oli vajalik eelnevalt konfigureerida eriline nimede ruum custom_variable_classes oma sessiooni muutujate jaoks. Käsitles ajakohastes versioonides saab kirjutada umbkaudu järgmiselt:

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

Teistes toetatud protseduurilistes keeltes võib leida ka muid lahendusi.

Kas teate veel viise? Jagage oma mõtteid kommentaarides!

Allikas: habr.com