PostgreSQL Antipatterns: Passing Sets and Selects to SQL

Nthawi ndi nthawi, wopanga amafunikira perekani magawo angapo kapena kusankha kwathunthu pazopempha "pakhomo". Nthawi zina pali njira zachilendo kwambiri zothetsera vutoli.

Tiyeni tipite "kuchokera kuseri" ndikuwona momwe osachitira, chifukwa chake, ndi momwe mungachitire bwinoko.

"Kuyika" kwachindunji kwazinthu zomwe zimafunsidwa

Nthawi zambiri zimawonekera motere:

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

... kapena monga izi:

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

Za njira imeneyi akuti, olembedwa ndi ngakhale kukokedwa zokwanira:

Pafupifupi nthawi zonse zimakhala choncho njira yolunjika ku jakisoni wa SQL ndi katundu wowonjezera pamalingaliro abizinesi, omwe amakakamizika "kumata" chingwe chanu chafunso.

Njirayi ingakhale yolungamitsidwa pang'ono pokhapokha ngati kuli kofunikira. gwiritsani ntchito partitioning m'matembenuzidwe a PostgreSQL 10 ndi pansipa kuti mupeze dongosolo labwino kwambiri. M'matembenuzidwe awa, mndandanda wa zigawo zojambulidwa zimatsimikiziridwa popanda kuganizira magawo opatsirana, pokhapokha pamaziko a bungwe lopempha.

$n-makangano

Gwiritsani ntchito zosungira malo magawo ndi abwino, amakulolani kugwiritsa ntchito ZINTHU ZOKONZEKERA, kuchepetsa katundu pamalingaliro abizinesi (chingwe chofunsira chimapangidwa ndikuperekedwa kamodzi kokha) komanso pa seva ya database (kubwerezanso ndikukonzekera sikofunikira pazochitika zilizonse za pempho).

Chiwerengero cha zifukwa zosiyanasiyana

Mavuto adzatidikirira tikafuna kupititsa mikangano yosadziwika pasadakhale:

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

Mukasiya pempholi mu fomu iyi, ndiye kuti ngakhale itipulumutsa ku jakisoni yemwe angatithandizire, zipangitsa kuti pakhale kufunika komatira / kuyankha pempholo. pa njira iliyonse kuchokera ku chiwerengero cha mikangano. Kale bwino kuposa kuchita nthawi zonse, koma mukhoza kuchita popanda izo.

Ndikokwanira kudutsa gawo limodzi lokha lomwe lili ndi serialized chiwonetsero cha array:

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

Kusiyana kokha ndiko kufunikira kosinthiratu mkanganowo kukhala mtundu womwe mukufuna. Koma izi sizimayambitsa mavuto, chifukwa timadziwiratu kumene tikulankhula.

Kusintha kwachitsanzo (matrix)

Nthawi zambiri izi ndi mitundu yonse ya zosankha zosamutsa ma data kuti alowe mu nkhokwe "pa pempho limodzi":

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

Kuphatikiza pa mavuto omwe tawafotokozera pamwambapa ndi "kubwezeretsanso" pempho, izi zikhoza kutitsogoleranso osakumbukira ndi kuwonongeka kwa seva. Chifukwa chake ndi chosavuta - PG imasunga kukumbukira kowonjezera pazokanganazo, ndipo kuchuluka kwa zolembedwa zomwe zili pagawoli ndizochepa chabe ndi Wishlist logic application application. Makamaka milandu yachipatala inali yofunikira kuwona mikangano "yowerengeka" yoposa $9000 - osachita izi.

Tiyeni tilembenso funsolo, kugwiritsa ntchito kale "awiri-level" serialization:

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;

Inde, pankhani ya "zovuta" zomwe zili mkati mwa gulu, ziyenera kupangidwa ndi mawu.
Zikuwonekeratu kuti mwa njira iyi mukhoza "kukulitsa" kusankha ndi chiwerengero cha minda.

wakuda, wakuda, ...

Nthawi ndi nthawi pamakhala zosankha zodutsa m'malo mwa "magulu osiyanasiyana" angapo "mizere" yomwe ndidatchula. m’nkhani yapitayi:

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

Ndi njira iyi, ngati mulakwitsa popanga mindandanda yazabwino zamagawo osiyanasiyana, ndizosavuta kupeza kwathunthu. zotsatira zosayembekezereka, zomwe zimadaliranso mtundu wa seva:

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

Kuyambira pa mtundu wa 9.3, PostgreSQL ili ndi ntchito zonse zogwirira ntchito ndi mtundu wa json. Chifukwa chake, ngati magawo anu olowera akufotokozedwa mu msakatuli, mutha pomwepo ndikupanga json chinthu cha SQL funso:

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

Kwa Mabaibulo akale, njira yomweyo angagwiritsidwe ntchito chilichonse (hstore), koma "kupinda" kolondola ndikuthawa zinthu zovuta mu hstore kungayambitse mavuto.

json_populate_recordset

Ngati mukudziwa pasadakhale kuti deta yochokera ku "input" json array idzadzaza tebulo lina, mutha kusunga zambiri mu "dereferencing" minda ndikuponyera mitundu yomwe mukufuna kugwiritsa ntchito 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

Ndipo ntchitoyi ingowonjezera "kukulitsa" mndandanda wazinthu zomwe zidasankhidwa, osadalira mtundu wa tebulo:

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

TEMPORARY TABLE

Koma ngati kuchuluka kwa deta mu chitsanzo chofalitsidwa ndi chachikulu kwambiri, ndiye kuti kuponyera mu gawo limodzi la serialized kumakhala kovuta, ndipo nthawi zina sikutheka, chifukwa kumafuna nthawi imodzi. kugawa kwakukulu kukumbukira. Mwachitsanzo, muyenera kusonkhanitsa deta yaikulu ya zochitika kuchokera ku dongosolo lakunja kwa nthawi yaitali, yaitali, ndiyeno mukufuna kuikonza nthawi imodzi kumbali yachinsinsi.

Pankhaniyi, njira yabwino kwambiri ndiyo kugwiritsa ntchito magome osakhalitsa:

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

Njirayo ndi yabwino kufalitsa ma volumes ambiri pafupipafupi deta.
Kuchokera pamalingaliro akufotokozera dongosolo la deta yake, tebulo lakanthawi kochepa limasiyana ndi tebulo la "nthawi zonse" mu gawo limodzi lokha. mu pg_class system tablendi pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ndipo palibe konse.

Choncho, mu machitidwe a intaneti omwe ali ndi chiwerengero chachikulu cha maulendo afupikitsa kwa aliyense wa iwo, tebulo loterolo lidzatulutsa zolemba zatsopano nthawi iliyonse, zomwe zimachotsedwa pamene kugwirizana kwa database kutsekedwa. Pomaliza, Kugwiritsa ntchito kosalamulirika kwa TEMP TABLE kumabweretsa "kutupa" kwamatebulo mu pg_catalog ndi kuchepetsa ntchito zambiri zomwe zimagwiritsa ntchito.
Inde, izi zikhoza kulimbana ndi nthawi ndi nthawi VACUUM FULL malinga ndi matebulo a kalozera wadongosolo.

Zosintha za Gawo

Tiyerekeze kuti kukonza kwazomwe zachitika kale ndizovuta kwambiri pafunso limodzi la SQL, koma mukufuna kuchita nthawi zambiri. Ndiye kuti, tikufuna kugwiritsa ntchito process process in DO block, koma kugwiritsa ntchito kusamutsa deta kudzera pa matebulo osakhalitsa kumakhala kokwera mtengo kwambiri.

Sitingagwiritsenso ntchito $n-parameters kupita kumalo osadziwika. Zosintha za gawoli ndi ntchito zitithandiza kuti tichoke muzochitikazo. current_setting.

Asanatuluke 9.2, mumayenera kusinthiratu malo apadera a mayina custom_variable_classes kwa "zawo" magawo osiyanasiyana. Pamitundu yamakono, mutha kulemba motere:

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

Palinso njira zina zopezeka m'zilankhulo zina zothandizidwa.

Mukudziwa njira zambiri? Gawani mu ndemanga!

Source: www.habr.com