ProHoster > Ibhulogi > Ulawulo > I-PostgreSQL Antipatterns: iiseti ezidlulayo kwaye zikhethe kwi-SQL

I-PostgreSQL Antipatterns: iiseti ezidlulayo kwaye zikhethe kwi-SQL

Amaxesha ngamaxesha kufuneka umphuhlisi dlulisa uluhlu lweparameters okanye ukhetho olupheleleyo kwisicelo "ekungeneni". Ngamanye amaxesha udibana nezisombululo ezingaqhelekanga kule ngxaki.
I-PostgreSQL Antipatterns: iiseti ezidlulayo kwaye zikhethe kwi-SQL
Masibuyele umva kwaye sibone ukuba yintoni esingamele siyenze, ngoba, kwaye singayenza njani ngcono.

Ukufakwa ngokuthe ngqo kwamaxabiso kwiqumrhu lesicelo

Ngokuqhelekileyo ikhangeleka ngolu hlobo:

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

... okanye ngolu hlobo:

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

Le ndlela iye yathethwa, yabhalwa kwaye nokuba izotyiwe kakhulu:

I-PostgreSQL Antipatterns: iiseti ezidlulayo kwaye zikhethe kwi-SQL

Phantse njalo oku indlela ethe ngqo kwiinaliti zeSQL kunye nomthwalo ongeyomfuneko kwingqiqo yoshishino, enyanzelekileyo ukuba "unamathele" kumgca wakho wombuzo.

Le ndlela inokuthetheleleka ngokuyinxenye kuphela ukuba kuyimfuneko usebenzisa ukwahlulahlula kwiinguqulelo ze-PostgreSQL ezili-10 nangaphantsi ukuze ufumane isicwangciso esisebenzayo. Kwezi nguqulelo, uluhlu lwamacandelo eskeniweyo luchongwa ngaphandle kokuthathela ingqalelo iiparamitha ezidlulisiweyo, kuphela ngesiseko somzimba wesicelo.

$ n-iingxabano

Sebenzisa izibambi-ndawo iparameters ilungile, ikuvumela ukuba usebenzise IINGXELO EZILUNGISELELWEYO, ukunciphisa umthwalo kokubili kwingqiqo yoshishino (umtya wombuzo wenziwa kwaye udluliselwe kanye kuphela) kunye nomncedisi wedatha (ukuhlaziya kwakhona kunye nokucwangcisa umzekelo wombuzo ngamnye awufuni).

Inani eliguquguqukayo leengxoxo

Iingxaki ziya kusilinda xa sifuna ukuphumelela inani elingaziwayo leengxoxo:

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

Ukuba siyasishiya isicelo kule fomu, nangona iya kusikhusela kwiinaliti ezinokubakho, iya kukhokelela kwisidingo sokudibanisa / ukucazulula isicelo. kukhetho ngalunye kuxhomekeke kwinani leempikiswano. Kungcono kunokukwenza ngalo lonke ixesha, kodwa unokwenza ngaphandle kwayo.

Kwanele ukudlula iparameter enye equlathe umelo olulandelelanayo:

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

Umahluko kuphela sisidingo sokuguqula ngokucacileyo ingxabano kuhlobo olufunekayo loluhlu. Kodwa oku akuzibangeli iingxaki, kuba sele sisazi kwangaphambili apho siya khona.

Ukuhanjiswa kwesampulu (matrix)

Ngesiqhelo ezi zizo zonke iintlobo zokhetho lokudlulisela iiseti zedatha ukuze zifakwe kuvimba wedatha "kwisicelo esinye":

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

Ukongeza kwiingxaki ezichazwe ngasentla "ngokuphinda-gluing" isicelo, oku kunokukhokelela kuthi ngapha kokungagcinwanga kunye nokuwa kweseva. Isizathu silula - i-PG igcina imemori eyongezelelweyo yeengxabano, kwaye inani leerekhodi kwiseti lilinganiselwe kuphela kwiimfuno zesicelo sengqiqo yezoshishino. Kwiimeko zeklinikhi ngakumbi kuye kwafuneka ndibone Iingxoxo "zenani" zingaphezulu kwe-9000 yeedola - musa ukwenza ngale ndlela.

Masibhale kwakhona isicelo sisebenzisa kakade "amanqanaba amabini" ulandelelwano:

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;

Ewe, kwimeko yexabiso "eliyinkimbinkimbi" ngaphakathi koluhlu, kufuneka lijikelezwe ziingcaphuno.
Kucacile ukuba ngale ndlela unako "ukwandisa" ukhetho kunye nenani elingaqhelekanga lemimandla.

engalunganga, engalunganga, ...

Ngamaxesha ngamaxesha kukho iinketho zokudlula endaweni "yoluhlu lwee-arrays" ezininzi "zintlu zeekholamu" endizikhankanyileyo. kwinqaku lokugqibela:

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

Ngale ndlela, ukuba wenza impazamo xa usenza uluhlu lwamaxabiso kwimiqolo eyahlukeneyo, kulula kakhulu ukuyifumana. iziphumo ebezingalindelekanga, ekwaxhomekeke kuguqulelo lomncedisi:

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

Ukusukela kwinguqulo ye-9.3, i-PostgreSQL inemisebenzi epheleleyo yokusebenza nohlobo lwe-json. Ke ngoko, ukuba inkcazo yeeparamitha zegalelo yenzeka kwisikhangeli sakho, ungayakha kwalapho json into yombuzo weSQL:

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

Kwiinguqulelo zangaphambili, indlela efanayo ingasetyenziselwa nganye(hstore), kodwa echanekileyo "convolution" ebaleka izinto ezintsonkothileyo kwi-hstore inokubangela iingxaki.

json_populate_recordset

Ukuba uyazi kwangaphambili ukuba idatha evela kwi "input" uluhlu lwejson luya kusetyenziselwa ukugcwalisa itafile, ungagcina kakhulu kwi "dereferencing" imihlaba kwaye uyilahle kwiintlobo ezifunekayo ngokusebenzisa i-json_populate_recordset umsebenzi:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Kwaye lo msebenzi uya "kwandisa" uluhlu olugqithisiweyo lwezinto kukhetho, ngaphandle kokuxhomekeka kwifomathi yetafile:

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

ITHEYIBHILE YETHUTYANA

Kodwa ukuba inani ledatha kwisampulu egqithiselweyo likhulu kakhulu, ukuyiphosa kwiparameter enye kunzima kwaye ngamanye amaxesha akunakwenzeka, kuba ifuna ixesha elinye. yabela inani elikhulu lememori. Ngokomzekelo, kufuneka uqokelele ipakethe enkulu yedatha kwiziganeko ezivela kwinkqubo yangaphandle ixesha elide, ixesha elide, kwaye emva koko ufuna ukuyicubungula ngexesha elinye kwicala ledatha.

Kule meko, isisombululo esona silungileyo siya kuba kukusebenzisa iitafile zethutyana:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ΠΏΠΎΠ²Ρ‚ΠΎΡ€ΠΈΡ‚ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ-ΠΌΠ½ΠΎΠ³ΠΎ Ρ€Π°Π·
...
-- Ρ‚ΡƒΡ‚ Π΄Π΅Π»Π°Π΅ΠΌ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ со всСй этой Ρ‚Π°Π±Π»ΠΈΡ†Π΅ΠΉ Ρ†Π΅Π»ΠΈΠΊΠΎΠΌ

Indlela ilungile ngokugqithiselwa ngamaxa athile imiqulu emikhulu idatha.
Ukusuka kwindawo yokujonga inkcazo yesakhiwo sedatha yayo, itafile yesikhashana iyahluka kwi "rhoqo" ngendlela enye kuphela. kwi pg_class system table, kunye pg_type, pg_depend, pg_attribute, pg_attrdef, ... - akukho kwanto.

Ngoko ke, kwiinkqubo zewebhu kunye nenani elikhulu lokuxhamla kwexesha elifutshane ngalinye kubo, itafile enjalo iya kuvelisa iirekhodi zenkqubo entsha ngexesha ngalinye, ezicinywayo xa uxhumano lwedatha luvaliwe. Ekugqibeleni, ukusetyenziswa okungalawulwayo kwe-TEMP TABLE kukhokelela "ekudumbeni" kweetafile kwi-pg_catalog kunye nokucothisa imisebenzi emininzi abayisebenzisayo.
Kakade ke, oku kunokujongana nokusetyenziswa isicatshulwa samaxesha athile IVACUUM EPHELELE ngokwenkqubo yeetafile zekhathalogu.

Iiguquguquko zeseshoni

Masicinge ukuba ukusetyenzwa kwedatha ukusuka kwimeko yangaphambili kunzima kakhulu kumbuzo omnye weSQL, kodwa ufuna ukuyenza rhoqo. Oko kukuthi, sifuna ukusebenzisa inkqubo yokusebenza kwi YENZA ibhlokhi, kodwa ukusebenzisa ugqithiso lwedatha ngeetafile zethutyana kuya kuba yindleko kakhulu.

Kananjalo asiyi kukwazi ukusebenzisa i-$n-iparamitha zokudlulela kwibhloko engaziwa. Iinguqu zeseshoni kunye nomsebenzi uya kusinceda ukuba siphume kule meko ulungiselelo_ lwangoku.

Phambi kwenguqulelo 9.2 bekuyimfuneko ukuqwalasela kwangaphambili isithuba samagama esikhethekileyo custom_variable_classes "yeyakho" iinguqu zeseshoni. Kwiinguqulelo zangoku ungabhala into efana nale:

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

Ezinye izisombululo zinokufumaneka kwezinye iilwimi zenkqubo ezixhaswayo.

Ngaba zikhona ezinye iindlela ozaziyo? Yabelana kwizimvo!

umthombo: www.habr.com

Yongeza izimvo