ProHoster > Π‘Π»ΠΎΠ³ > Pagdumala > Mga Antipattern sa PostgreSQL: pagpasa sa mga set ug gipili sa SQL

Mga Antipattern sa PostgreSQL: pagpasa sa mga set ug gipili sa SQL

Matag karon ug unya gikinahanglan sa developer ipasa ang usa ka set sa mga parameter o bisan usa ka tibuuk nga pagpili sa hangyo "sa entrada". Usahay makit-an nimo ang katingad-an nga mga solusyon sa kini nga problema.
Mga Antipattern sa PostgreSQL: pagpasa sa mga set ug gipili sa SQL
Atong balikan ug tan-awon kung unsa ang dili angay buhaton, ngano, ug kung giunsa naton kini mahimo nga mas maayo.

Direkta nga pagsulud sa mga kantidad sa lawas sa hangyo

Kasagaran kini tan-awon sama niini:

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

...o ingon niini:

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

Kini nga pamaagi giingon, gisulat ug bisan gi-drawing daghan:

Mga Antipattern sa PostgreSQL: pagpasa sa mga set ug gipili sa SQL

Halos kanunay kini direkta nga agianan sa SQL injection ug wala kinahanglana nga pagkarga sa lohika sa negosyo, nga napugos sa "pagpilit" sa imong linya sa pangutana.

Kini nga pamaagi mahimong partially justified kon gikinahanglan gamit ang partitioning sa PostgreSQL nga mga bersyon 10 ug sa ubos aron makakuha og mas episyente nga plano. Sa kini nga mga bersyon, ang lista sa mga na-scan nga mga seksyon gitino nga wala gikonsiderar ang gipasa nga mga parameter, base lamang sa gihangyo nga lawas.

$n-mga argumento

Paggamit mga placeholder Ang mga parameter maayo, kini nagtugot kanimo sa paggamit GIANDAM NGA MGA PAHAYAG, pagkunhod sa load sa duha sa negosyo logic (ang query string namugna ug transmitted lamang sa makausa) ug sa database server (re-parsing ug scheduling alang sa matag pangutana nga pananglitan dili kinahanglan).

Variable nga gidaghanon sa mga argumento

Ang mga problema maghulat kanato kung gusto naton nga ipasa ang wala mailhi nga gidaghanon sa mga argumento:

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

Kung ibilin namon ang hangyo sa kini nga porma, bisan kung kini manalipod kanamo gikan sa mga potensyal nga pag-injection, magdala gihapon kini sa panginahanglan sa paghiusa / pag-parse sa hangyo alang sa matag kapilian depende sa gidaghanon sa mga argumento. Mas maayo kini kaysa buhaton kini matag higayon, apan mahimo nimo kung wala kini.

Igo na nga ipasa ang usa lang ka parameter nga adunay sulud serialized array representation:

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

Ang bugtong kalainan mao ang panginahanglan sa dayag nga pag-convert sa argumento ngadto sa gitinguha nga matang sa array. Apan dili kini hinungdan sa mga problema, tungod kay nahibal-an na naton daan kung diin kita moadto.

Pagbalhin sa sample (matrix)

Kasagaran kini ang tanan nga mga matang sa mga kapilian alang sa pagbalhin sa mga set sa datos alang sa pagsulud sa database "sa usa ka hangyo":

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

Dugang pa sa mga problema nga gihulagway sa ibabaw sa "pag-gluing pag-usab" sa hangyo, kini mahimo usab nga modala kanato sa walay memorya ug server crash. Ang rason mao ang yano - ang PG nagreserba og dugang nga panumduman alang sa mga argumento, ug ang gidaghanon sa mga rekord sa set limitado lamang sa mga panginahanglan sa aplikasyon sa lohika sa negosyo. Sa partikular nga mga klinikal nga mga kaso kinahanglan nako nga makita Ang "numero" nga mga argumento labaw pa sa $9000 - ayaw pagbuhat niini nga paagi.

Atong isulat pag-usab ang hangyo gamit ang na "duha ka lebel" nga 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;

Oo, sa kaso sa "komplikado" nga mga kantidad sa sulod sa usa ka laray, sila kinahanglan nga mapalibutan sa mga kinutlo.
Kini mao ang tin-aw nga sa niini nga paagi nga imong mahimo "pagpalapad" sa usa ka pagpili uban sa usa ka arbitraryong gidaghanon sa mga uma.

walay kapuslanan, walay kapuslanan,…

Matag karon ug unya adunay mga kapilian sa pagpasa imbes nga usa ka "array of arrays" daghang "arrays of columns" nga akong gihisgutan sa miaging artikulo:

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

Uban niini nga pamaagi, kung masayop ka sa paghimo og mga lista sa mga kantidad alang sa lainlaing mga kolum, dali ra makuha. wala damha nga mga resulta, nga nagdepende usab sa bersyon sa server:

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

Sukad sa bersyon 9.3, ang PostgreSQL adunay bug-os nga mga gimbuhaton alang sa pagtrabaho kauban ang tipo sa json. Busa, kung ang kahulugan sa mga parameter sa pag-input mahitabo sa imong browser, mahimo nimo kini maporma didto mismo json object alang sa SQL query:

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

Alang sa miaging mga bersyon, ang parehas nga pamaagi mahimong magamit sa matag(hstore), apan ang husto nga "convolution" sa pag-ikyas sa mga komplikadong butang sa hstore mahimong hinungdan sa mga problema.

json_populate_recordset

Kung nahibal-an nimo nga daan nga ang datos gikan sa "input" json array gamiton aron pun-on ang pipila ka lamesa, makatipig ka ug daghan sa "dereferencing" nga mga natad ug ihulog kini sa gikinahanglan nga mga tipo pinaagi sa paggamit sa json_populate_recordset function:

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

json_to_recordset

Ug kini nga function "mopalapad" sa gipasa nga han-ay sa mga butang sa usa ka pagpili, nga wala magsalig sa format sa lamesa:

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

TEMPORARYO NGA TABLE

Apan kung ang gidaghanon sa mga datos sa gibalhin nga sample dako kaayo, nan ang paglabay niini ngadto sa usa ka serialized parameter lisud ug usahay imposible, tungod kay nagkinahanglan kini og usa ka higayon. paggahin ug dakong kantidad sa memorya. Pananglitan, kinahanglan nimo nga mangolekta usa ka dako nga pakete sa datos sa mga panghitabo gikan sa usa ka eksternal nga sistema sa dugay, dugay nga panahon, ug dayon gusto nimo nga iproseso kini usa ka higayon sa kilid sa database.

Sa kini nga kaso, ang labing kaayo nga solusyon mao ang paggamit temporaryo nga mga lamesa:

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

Maayo ang pamaagi alang sa panagsa nga pagbalhin sa daghang mga volume datos.
Gikan sa punto sa panglantaw sa paghulagway sa istruktura sa mga datos niini, ang usa ka temporaryo nga lamesa lahi sa usa ka "regular" sa usa lamang ka paagi. sa pg_class system tableug sa pg_type, pg_depend, pg_attribute, pg_attrdef, ... - wala gyud.

Busa, sa mga sistema sa web nga adunay daghang mga mubu nga mga koneksyon alang sa matag usa niini, ang ingon nga lamesa makamugna og bag-ong mga rekord sa sistema matag higayon, nga mapapas kung ang koneksyon sa database sirado. Sa kadugayan, Ang dili makontrol nga paggamit sa TEMP TABLE mosangpot sa "paghubag" sa mga lamesa sa pg_catalog ug pagpahinay sa daghang mga operasyon nga naggamit niini.
Siyempre, kini mahimong atubangon sa paggamit periodic passage PUNO NGA VACUUM sumala sa mga lamesa sa katalogo sa sistema.

Mga Variable sa Sesyon

Ibutang ta nga ang pagproseso sa datos gikan sa miaging kaso medyo komplikado alang sa usa ka SQL nga pangutana, apan gusto nimo nga buhaton kini kanunay. Kana mao, gusto namon nga gamiton ang pagproseso sa pamaagi sa DO block, apan ang paggamit sa pagbalhin sa datos pinaagi sa temporaryo nga mga lamesa mahimong mahal kaayo.

Dili usab kami makagamit sa $n-parameter para sa pagpasa sa usa ka anonymous block. Ang mga variable sa session ug ang function makatabang kanato nga makagawas sa kini nga sitwasyon kasamtangan_setting.

Sa wala pa ang bersyon 9.2 gikinahanglan nga pre-configure espesyal nga namespace custom_variable_classes alang sa "imong" mga variable sa sesyon. Sa kasamtangan nga mga bersyon mahimo nimong isulat ang usa ka butang nga sama niini:

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

Ang ubang mga solusyon mahimong makit-an sa ubang gisuportahan nga pamaagi nga mga pinulongan.

May nahibal-an ka ba nga ubang mga paagi? Ipakigbahin sa mga komento!

Source: www.habr.com

Idugang sa usa ka comment