Aeg-ajalt vajab arendaja edastada päringule parameetrite komplekt või isegi terve valik "sissepääsu juures". Mõnikord leiate sellele probleemile väga kummalisi lahendusi.
Läheme tagasi ja vaatame, mida mitte teha, miks ja kuidas saaksime seda paremini teha.
Väärtuste otsene sisestamine päringu kehasse
Tavaliselt näeb see välja umbes selline:
query = "SELECT * FROM tbl WHERE id = " + value...või nii:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Seda meetodit on öeldud, kirjutatud ja palju:
Peaaegu alati on see nii otsetee SQL-i süstideni ja tarbetu koormus äriloogikale, mis on sunnitud teie päringurea "liimima".
Seda lähenemisviisi saab osaliselt õigustada ainult vajaduse korral kasutades partitsiooni PostgreSQL-i versioonides 10 ja vanemates, et saada tõhusam plaan. Nendes versioonides määratakse skannitud jaotiste loend edastatud parameetreid arvesse võtmata, ainult päringu keha põhjal.
$n-argumendid
Kasutama parameetrid on head, see võimaldab kasutada , vähendades nii äriloogika (päringutring genereeritakse ja edastatakse ainult üks kord) kui ka andmebaasiserveri koormust (iga päringu eksemplari jaoks pole vaja uuesti sõeluda ja ajastada).
Muutuv argumentide arv
Probleemid ootavad meid siis, kui tahame edastada teadmata arvu argumente:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Kui jätame taotluse sellele vormile, ehkki see kaitseb meid võimalike süstide eest, toob see siiski kaasa vajaduse taotlus liita/parsida iga valiku jaoks sõltuvalt argumentide arvust. See on parem kui seda iga kord teha, kuid saate ka ilma selleta hakkama.
Piisab ainult ühe parameetri edastamisest serialiseeritud massiivi esitus:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Ainus erinevus on vajadus argumendi selgesõnaliseks teisendamiseks soovitud massiivitüübiks. Kuid see ei tekita probleeme, kuna teame juba ette, kuhu läheme.
Proovi (maatriksi) ülekandmine
Tavaliselt on need kõikvõimalikud võimalused andmekogumite edastamiseks andmebaasi sisestamiseks "ühe päringuga":
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Lisaks ülalkirjeldatud probleemidele taotluse "uuesti liimimisel" võib see meid viia ka selleni mälu täis ja serveri krahh. Põhjus on lihtne – PG reserveerib argumentide jaoks lisamälu ning kirjete arvu komplektis piiravad vaid äriloogika rakendusvajadused. Eriti kliinilistel juhtudel pidin nägema Argumendid on rohkem kui 9000 dollarit - ära tee seda nii.
Kirjutame taotluse ümber, kasutades juba "kahetasandiline" serialiseerimine:
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, massiivi sees olevate "keeruliste" väärtuste korral peavad need olema ümbritsetud jutumärkidega.
On selge, et sel viisil saate valikut "laiendada" suvalise arvu väljadega.
unnest, unnest,…
Aeg-ajalt on võimalusi, et "massiivide massiivi" asemel edastada mitu "veergude massiivi", mida mainisin :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Kui teete selle meetodi abil erinevate veergude väärtuste loendite loomisel vea, on seda väga lihtne hankida ootamatuid 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-il olnud täisväärtuslikud funktsioonid json-tüübiga töötamiseks. Seega, kui sisendparameetrite definitsioon leiab aset teie brauseris, saate selle seal vormistada json-objekt SQL-päringu jaoks:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Varasemate versioonide puhul saab kasutada sama meetodit iga(hstore), kuid õige "konvolutsioon" hstore'i keerukate objektide põgenemisega võib probleeme tekitada.
json_populate_recordset
Kui teate ette, et json-massiivi "sisend" andmeid kasutatakse mõne tabeli täitmiseks, saate palju säästa "viite eemaldamise" väljadel ja nende ülekandmisel vajalikesse tüüpidesse, 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
Ja see funktsioon lihtsalt "laiendab" edastatud objektide massiivi valikuks, ilma tabelivormingule tuginemata:
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 | 2AJUTINE LAUD
Kui aga edastatud valimi andmemaht on väga suur, on selle ühte järjestatud parameetrisse paigutamine keeruline ja mõnikord võimatu, kuna see nõuab ühekordset eraldada suur hulk mälu. Näiteks peate koguma välissüsteemist pikka-pika aja jooksul koguma suure paketi sündmuste kohta andmeid ja seejärel soovite seda andmebaasi poolel ühekordselt töödelda.
Sel juhul oleks parim lahendus kasutada :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Meetod on hea suurte mahtude aeg-ajalt ülekandmiseks andmeid.
Oma andmete struktuuri kirjeldamise seisukohalt erineb ajutine tabel “tavalisest” ainult ühel viisil pg_class süsteemitabelisja sisse pg_type, pg_depend, pg_attribute, pg_attrdef, ... - mitte midagi.
Seetõttu genereerib selline tabel veebisüsteemides, kus igaühe jaoks on suur hulk lühiajalisi ühendusi, iga kord uued süsteemikirjed, mis andmebaasiga ühenduse sulgemisel kustutatakse. Lõpuks TEMP TABLE'i kontrollimatu kasutamine põhjustab pg_catalog tabelite "paisumist" ja aeglustab paljusid neid kasutavaid toiminguid.
Muidugi saab seda kasutada kasutades perioodiline läbimine VACUUM FULL süsteemi kataloogi tabelite järgi.
Seansi muutujad
Oletame, et eelmise juhtumi andmete töötlemine on ühe SQL-päringu jaoks üsna keeruline, kuid soovite seda teha üsna sageli. See tähendab, et me tahame kasutada protseduurilist töötlemist , kuid andmeedastus ajutiste tabelite kaudu läheb liiga kulukaks.
Samuti ei saa me kasutada $n-parameetrit anonüümsesse plokki liikumiseks. Seansi muutujad ja funktsioon aitavad meil sellest olukorrast välja tulla praegune_seade.
Enne versiooni 9.2 oli vaja eelkonfigureerida kohandatud_muutujate_klassid "teie" seansi muutujate jaoks. Praegustes versioonides saate kirjutada midagi sellist:
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 : 3Teisi lahendusi võib leida teistest toetatud protseduurikeeltest.
Kas teate muid viise? Jaga kommentaarides!
Allikas: www.habr.com