ProHoster > blog > Amministrazzjoni > PostgreSQL Antipatterns: Tgħaddi Settijiet u Agħżel għal SQL

PostgreSQL Antipatterns: Tgħaddi Settijiet u Agħżel għal SQL

Minn żmien għal żmien, l-iżviluppatur jeħtieġ jgħaddi sett ta 'parametri jew saħansitra għażla sħiħa għat-talba "fid-daħla". Kultant hemm soluzzjonijiet strambi ħafna għal din il-problema.
PostgreSQL Antipatterns: Tgħaddi Settijiet u Agħżel għal SQL
Ejja mmorru "mill-oppost" u naraw kif ma tagħmilx dan, għaliex, u kif tista 'tagħmel dan aħjar.

"Introduzzjoni" diretta tal-valuri fil-korp tat-talba

Normalment tidher xi ħaġa bħal din:

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

... jew bħal dan:

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

Dwar dan il-metodu jingħad, miktub u anke miġbuda biżżejjed:

PostgreSQL Antipatterns: Tgħaddi Settijiet u Agħżel għal SQL

Kważi dejjem hekk mogħdija diretta għall-injezzjoni SQL u tagħbija żejda fuq il-loġika tan-negozju, li hija sfurzata "kolla" is-sekwenza tal-mistoqsijiet tiegħek.

Dan l-approċċ jista' jiġi ġġustifikat parzjalment biss jekk ikun meħtieġ. uża qsim fil-verżjonijiet PostgreSQL 10 u hawn taħt għal pjan aktar effiċjenti. F'dawn il-verżjonijiet, il-lista ta 'sezzjonijiet skennjati hija determinata mingħajr ma jitqiesu l-parametri trażmessi, biss fuq il-bażi tal-korp tat-talba.

$n argumenti

Użu placeholders parametri hija tajba, tippermetti li tuża DIKJARAZZJONIJIET IPPREPARATI, tnaqqas it-tagħbija kemm fuq il-loġika tan-negozju (is-sekwenza tal-mistoqsijiet hija ffurmata u trażmessa darba biss) u fuq is-server tad-database (l-analiżi mill-ġdid u l-ippjanar mhumiex meħtieġa għal kull istanza tat-talba).

Numru varjabbli ta' argumenti

Il-problemi se jistennewna meta rridu ngħaddu minn qabel numru mhux magħruf ta’ argumenti:

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

Jekk tħalli t-talba f'din il-formola, allura għalkemm issalvana minn injezzjonijiet potenzjali, xorta se twassal għall-ħtieġa li t-talba tiġi mwaħħla / parse. għal kull għażla min-numru ta’ argumenti. Diġà aħjar milli tagħmel dan kull darba, iżda tista 'tagħmel mingħajrha.

Huwa biżżejjed li jgħaddi parametru wieħed biss li fih rappreżentazzjoni serjali ta' firxa:

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

L-unika differenza hija l-ħtieġa li l-argument jiġi kkonvertit b'mod espliċitu għat-tip ta' firxa mixtieqa. Iżda dan ma jikkawżax problemi, peress li diġà nafu minn qabel fejn qed nindirizzaw.

Trasferiment tal-kampjun (matriċi)

Normalment dawn huma kull xorta ta 'għażliet għat-trasferiment ta' settijiet ta 'dejta biex jiddaħħlu fid-database "f'talba waħda":

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

Minbarra l-problemi deskritti hawn fuq bil-"inkullar mill-ġdid" tat-talba, dan jista 'jwassalna wkoll għal barra mill-memorja u crash server. Ir-raġuni hija sempliċi - PG tirriżerva memorja addizzjonali għall-argumenti, u n-numru ta 'rekords fis-sett huwa limitat biss mill-applikazzjoni tal-loġika tan-negozju Wishlist. F'każijiet speċjalment kliniċi kien meħtieġ li tara argumenti "numerati" akbar minn $9000 - tagħmilx dan il-mod.

Ejja nikteb mill-ġdid il-mistoqsija, billi napplikaw diġà serialization "żewġ livelli".:

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;

Iva, fil-każ ta 'valuri "kumplessi" ġewwa firxa, jeħtieġ li jiġu inkwadrati bi kwotazzjonijiet.
Huwa ċar li b'dan il-mod tista '"tespandi" l-għażla b'numru arbitrarju ta' oqsma.

unnest, unnest, ...

Minn żmien għal żmien hemm għażliet biex minflok "array of arrays" jgħaddu diversi "arrays of columns" li semmejt fl-aħħar artiklu:

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

B'dan il-metodu, jekk tagħmel żball meta tiġġenera listi ta 'valuri għal kolonni differenti, huwa faċli ħafna li tikseb kompletament riżultati mhux mistennija, li jiddependu wkoll fuq il-verżjoni tas-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

Jibda mill-verżjoni 9.3, PostgreSQL għandu funzjonijiet sħaħ biex jaħdem mat-tip json. Għalhekk, jekk il-parametri tal-input tiegħek huma definiti fil-brawżer, tista' dritt hemm u tifforma oġġett json għal mistoqsija SQL:

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

Għall-verżjonijiet preċedenti, l-istess metodu jista 'jintuża għal kull (hstore), iżda korretta "tiwi" ma jaħarbu oġġetti kumplessi fil hstore jistgħu jikkawżaw problemi.

json_populate_recordset

Jekk taf minn qabel li d-dejta mill-array json "input" se tmur biex timla xi tabella, tista 'tiffranka ħafna f'oqsma ta' "dereferencing" u casting għat-tipi mixtieqa billi tuża l-funzjoni 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

U din il-funzjoni sempliċement "tespandi" l-firxa ta 'oġġetti mgħoddija f'għażla, mingħajr ma toqgħod fuq il-format tal-mejda:

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

TABELLA TEMPORANJA

Imma jekk l-ammont ta 'dejta fil-kampjun trażmess huwa kbir ħafna, allura li titfa' f'parametru serjali wieħed huwa diffiċli, u xi kultant impossibbli, peress li teħtieġ darba waħda. allokazzjoni kbira tal-memorja. Pereżempju, għandek bżonn tiġbor lott kbir ta 'dejta tal-avvenimenti minn sistema esterna għal żmien twil u twil, u mbagħad trid tipproċessaha ta' darba fuq in-naħa tad-database.

F'dan il-każ, l-aħjar soluzzjoni tkun li tuża imwejjed temporanji:

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

Il-metodu huwa tajjeb għal trasmissjoni mhux frekwenti ta 'volumi kbar data.
Mill-perspettiva tad-deskrizzjoni tal-istruttura tad-dejta tagħha, tabella temporanja tvarja minn tabella "regolari" f'karatteristika waħda biss. fit-tabella tas-sistema pg_class, u ġewwa pg_type, pg_depend, pg_attribute, pg_attrdef, ... — u xejn.

Għalhekk, f'sistemi tal-web b'numru kbir ta 'konnessjonijiet ta' ħajja qasira għal kull wieħed minnhom, tabella bħal din tiġġenera rekords ġodda tas-sistema kull darba, li jitħassru meta tingħalaq il-konnessjoni mad-database. Eventwalment, użu mhux ikkontrollat ​​ta' TEMP TABLE iwassal għal "nefħa" tat-tabelli f'pg_catalog u jonqos ħafna operazzjonijiet li jużawhom.
Naturalment, dan jista 'jiġi miġġieled jgħaddu perjodiċi VACUUM FULL skond it-tabelli tal-katalgu tas-sistema.

Varjabbli tas-Sessjoni

Ejja ngħidu li l-ipproċessar tad-dejta mill-każ preċedenti huwa pjuttost kumpless għal mistoqsija SQL waħda, iżda trid tagħmel dan spiss. Jiġifieri rridu nużaw l-ipproċessar proċedurali fi DO blokk, iżda l-użu tat-trasferiment tad-dejta permezz ta 'tabelli temporanji se jkun għali wisq.

Aħna wkoll ma nistgħux nużaw $n-parametri biex ngħaddu għal blokka anonima. Il-varjabbli tas-sessjoni u l-funzjoni se jgħinuna noħorġu mis-sitwazzjoni. current_setting.

Qabel il-verżjoni 9.2, kellek tikkonfigura minn qabel namespace speċjali klassijiet_varjabbli_personalizzati għall-varjabbli tas-sessjoni "tagħhom". Fuq verżjonijiet attwali, tista 'tikteb xi ħaġa bħal din:

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

Hemm soluzzjonijiet oħra disponibbli f'lingwi proċedurali appoġġjati oħra.

Taf aktar modi? Aqsam fil-kummenti!

Sors: www.habr.com

Żid kumment