PostgreSQL հակասություններ: պարամետրերի և ընտրանքների փոխանցում SQL-ում

Ժամանակ առ ժամանակ ծրագրավորողի մոտ առաջանում է անհրաժեշտություն ինչքանաքչի համար փոխանցել հարց՝ պարամետրերի հավաքածու կամ նույնիսկ ամբողջ ընտրություն «մտնում». Иногда попадаются очень странные решения этой задачи.

Եկեք «համաձայն սահմանի» գնանք և տեսնենք, թե ինչպես անել չի կարելի, ինչու, և ինչպես կարելի է լավ անել.

Վշտելու «մարտկոց» արժեքներ հարցի մարմնի մեջ

Էկրան վրա, որպես правило, կազմվում է մոտավորապես այսպես.

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

… կամ այսպես:

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

Այս մեթոդի մասին նշված, գրված և բավականին նկարագրված է լավ խոտերի ազգեր.

Դա քիչ առաջին միշտ է՝ ուղղակի ճանապարհ դեպի SQL-ինքնացվածներ. և ավելորդ բեռի բիզնես-լոգիկա, որը ստիպված է "առաջնորդ" քո հարցի տողը.

Փոխանակ այս համակարգը մասամբ արդարացված է միայն պետք առաջանալ պատասխանների ստացման PostgreSQL 10 և ներքևում ավելի վառ արդյունքի առաջալու համար. Այդ տարբերակներ մասնավորացնում են սրանք, հարցի մարմնի կողմից:

$n-արգումներ

Օգտագործումը պլейсհոլդերներ պարամետրեր — դա լավ է, դա թույլ է տալիս օգտագործել PREPARED STATEMENTS, նվազեցնել բեռն, ինչպես և բիզնես-լոգիկայի վրա (հարցի տողը ձևավորվում և փոխանցվում է միայն մեկ անգամ), այնպես էլ տվյալների բազայի սերվերի վրա (հնարավոր չէ կրկնակի վերամշակում և պլանավորման համար յուրաքանչյուր օրինակ հարցի).

Փոփոխական քանակի արգումներ

Նախկին խնդրեր, կլինեն երբ մենք կցանկանանք փոխանցել նախապես չյայտնված քանակի արգումներ:

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

Եթե թողնել հարցը այս ձևով, ապա դա գեթ թե մեզ պահերին կմիանա հնարավոր ինքննակալիք, սակայն դա դեռ կհանգեցնի անհրաժեշտությանը տեսնել / Զեկույց հարցը ոչ բոլոր տարբերակների քանակից. Դա արդեն ավելի լավ է, քան դա անել ամեն անգամ, բայց կարելի է բարելավել առանց դրա.

Կորան է պարզապես փոխանցել մեկ պարամետր, պարունակվող դասավորված տեսլականը:

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

Միակ տարբերակն է՝ անհրաժեշտությունը մեկ անգամ հստակ ձևափոխել պարամետրը վերածելով ցանկալի տեսակ։ Բայց դա չի դիմում խնդիր, որովհետև մենք նույնիսկ գիտենք, ուր ենք շանսավոր:

Զինվորության փոխանցումն (մատրից)

Նորմալները դա տարատեսակներ են պարունակող տվյալների առաջարկ պատմորոշելով՝ «մի հարցով»:

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

Առավելագույն այլ խնդիրներով որպես վերագրող հարցը մեզ ևս կնպաստի out of memory և սերվերի ընկնելը։ Պատճառը պարզ է՝ PG-ն արգումանի համար նախապատրաստում է լրացուցիչ հիշողություն, իսկ հավաքման մեջ գրառումների քանակը սահմանափակում է միայն ծրագրավորող մղումներ։ Հատուկ դեպքերում էդպես մենք տեսնում ենք «համարը» արգումներ 9000-ից ավել — դա պետք չէ:

Կրկնակի կոդ օրենսդրություն մշակելու ենք՝ ճիշտ «երկհարկյա» սերիալիզացիա:

Տեղադրեք tbl հետևյալը
Ընտրեք
  unnest[1]::text k
, unnest[2]::integer v
Երեքից հետո (
  Ընտրեք
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

Այո, եթե մթերքում կան «բարդ» արժեքներ, դրանք պետք է տեղադրվեն մեջբերումների մեջ:
Պարզ է, որ այս կերպ հնարավոր է «հանել» ընտրությունը որևէ թվով դաշտերով:

unnest, unnest, …

Հաճախ հանդիպում են տարբերակները, որտեղ «մասշտաբների զանգվածի» փոխարեն փոխանցվում են մի շարք «սյուներով զանգվածներ», որոնց մասին ես հիշատակել եմ: նախորդ հոդվածում:

Ընտրեք
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Այս եղանակով, տարբեր սյուների համար արժեքների ցուցակները ստեղծելիս սխալվելը շատ հեշտ է հանգեցնել լրիվ անհրաժեշտ արդյունքների:, որը կախված է նաև սերվերի տարբերակից:

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

9.3 տարբերակից սկսած PostgreSQL-ում լրիվ ֆունկցիաներ պետք են json տիպի հետ աշխատանքի համար: Ուստի, եթե ձեր մուտքային պարամետրերի սահմանումը տեղի է ունենում مرورگرում, դուք կարող եք ուղղակի այնտեղ ձևավորել json-խումբ SQL հարցման համար::

Ընտրեք
  key k
, value v
Երեքից հետո
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Նախկին տարբերակների համար նման մի եղանակ կարելի է օգտագործել each(hstore), բայց ճիշտ «փաթեթավորումը» բարդ օբյեկտների մուտքային տվյալներում hstore-ում խնդրում կարող է առաջացնել խնդիրներ:

json_populate_recordset

Եթե դուք արդեն գիտեք, որ «մուտքային» json զանգվածից դուրս եկող տվյալները կգնան որևէ սեղանի համալրման համար, դուք կարող եք շատ խնայել «ամբողջ մամուլում» և ձևափոխելու անհրաժեշտույթերում՝ օգտագործելով json_populate_recordset ֆունկցիան:

Ընտրեք
  *
Երեքից հետո
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Այս ֆունկցիան պարզապես «փաթեթավորելու» փոխանցված օբյեկտների զանգվածը ընտրության մեջ, առանց սեղանի ձևաչափին հենվելու:

Ընտրեք
  *
Երեքից հետո
  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

ՎՐԱՇԵՆԱՐԱՅԻՆ ՍԵՂԱՆՔ

Բայց եթե փոխանցված ընտրության տվյալների ծավալը շատ մեծ է, ապա մեկ ներկայացված պարամետրով դեպի այն ցենցավորելը դժվար է, իսկ երբեմն էլ անհնար, քանի որ պահանջում է մեկ անգամ մեծ հիշողության ծավալը. Օրինակ, ձեզ պետք է երկար ժամանակ հավաքել մեծ տվյալների փաթեթ ցանկացած արտաքին համակարգից, հետո ցանկանում եք այդ բոլորը մեկ անգամ մշակել DB կողմից:

Այս դեպքում լավագույն լուծումը կլինի օգտագործումը ժամանակավոր սեղաններ::

ՍԵՂԱՆՔ TEMPORARY TABLE tbl(k text, v integer);
...
Տեղադրեք tbl(k, v) արժեքները($1, $2); -- կրկնել շատ-շատ անգամ
...
-- այստեղ մենք անում ենք ինչ-որ օգտակար ամբողջ սեղանի հետ:

Մեթոդը լավ է հենց մեծ ծավալների հազվագյուտ փոխանցման համար: միջոցով:
Համար ձեր տվյալների կառուցվածքը նկարագրելու տեսանկյունից ժամանակավոր սեղանը տարբերվում է «սովորականից» միայն մեկ նշանով: pg_class համակարգային սեղանում:, իսկ pg_type, pg_depend, pg_attribute, pg_attrdef, … — իրականում ոչնչով:

Այդ պատճառով, վեբ-համակարգերում, որտեղ շատ կարճ վերցվող կապեր կան, այդ մեզ համար նախորդում նշված աղյուսակը յուրաքանչյուր կապի համար նոր համակարգային գրառումներ է ստեղծում, որոնք հեռացվում են տվյալների բազայի հետ կապը փակելու ժամանակ: Վերջում, անհսկելի օգտագործումը TEMP TABLE հանգեցնում է pg_catalog աղյուսակների "շտկմանը"։ և մի քանի գործողությունների ձգձգմանը, որոնք դրանք օգտագործում են։
Թե իհարկե, դա կարելի է հաղթահարել VACUUM FULL ժամանակ առ ժամանակ անցնելու օգնությամբ համակարգային կատալոգի աղյուսակների համար։

Սեսիայի փոփոխականներ

Կարծես տվյալների մշակման նախորդ դեպքը բավականին դժվար է մեկ SQL հարցման համար, սակայն մենք ուզում ենք դա կատարել բավականին հաճախ: Դա նշանակում է, որ մենք ցանկանում ենք օգտագործել ընթացակարգային մշակումը DO-բլոկում,բայց տվյալների փոխանցումը ժամանակավոր աղյուսակների միջոցով կլինի շատ ծախսատար։

Մենք չենք կարող օգտագործել $n-պարամետրերը անանուն բլոկում փոխանցելու համար: Մենք կօգնվի սեսիայի փոփոխականներն ու գործառույթը current_setting.

9.2-ից մինչև վարկածներում պետք է նախապես կոնֆիգուրացվի especial որակի տարածք custom_variable_classes մենք պետք է մեզ "իրական" սեսիայի փոփոխականների համար: Այժմ ներկայիս վերապրոցեսներում կարելի է գրել մոտավորապես այնպես ՝

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

Այլ աջակցվող ընթացակարգային լեզուներում կարելի է գտնել նաև այլ լուծումներ։

Դուք знаете այլ եղանակներ? կիսվեք մեկնաբանություններում!

Ընտանիք: habr.com