ProHoster > Blogs > Administrācija > PostgreSQL antipatterns: kopu un atlases nodošana SQL

PostgreSQL antipatterns: kopu un atlases nodošana SQL

Laiku pa laikam izstrādātājam ir nepieciešams nodod pieprasījumam parametru kopu vai pat visu atlasi "pie ieejas". Dažreiz šai problēmai ir ļoti dīvaini risinājumi.
PostgreSQL antipatterns: kopu un atlases nodošana SQL
Ejam "no pretējās puses" un redzēsim, kā to nedarīt, kāpēc un kā to var izdarīt labāk.

Tieša vērtību "ievietošana" pieprasījuma pamattekstā

Parasti tas izskatās apmēram šādi:

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

... vai šādi:

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

Par šo metodi ir teikts, rakstīts un pat uzzīmēts pietiekami:

PostgreSQL antipatterns: kopu un atlases nodošana SQL

Gandrīz vienmēr tā ir tiešais ceļš uz SQL injekciju un papildu slodze biznesa loģikai, kas ir spiesta “pielīmēt” jūsu vaicājuma virkni.

Šo pieeju var daļēji attaisnot tikai nepieciešamības gadījumā. izmantot sadalīšanu PostgreSQL versijās 10 un jaunākās versijās, lai iegūtu efektīvāku plānu. Šajās versijās skenēto sadaļu saraksts tiek noteikts, neņemot vērā pārsūtītos parametrus, tikai pamatojoties uz pieprasījuma pamattekstu.

$n argumenti

Izmantot vietturi parametri ir labi, tas ļauj izmantot SAGATAVOTI PAZIŅOJUMI, samazinot slodzi gan biznesa loģikai (vaicājuma virkne tiek veidota un pārsūtīta tikai vienu reizi), gan datu bāzes serverim (katram pieprasījuma gadījumam nav nepieciešama atkārtota parsēšana un plānošana).

Mainīgs argumentu skaits

Problēmas mūs gaidīs, kad vēlēsimies iepriekš nodot nezināmu skaitu argumentu:

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

Ja atstājat pieprasījumu šajā veidlapā, lai gan tas mūs pasargās no iespējamām injekcijām, tas joprojām radīs nepieciešamību pielīmēt / parsēt pieprasījumu katrai opcijai no argumentu skaita. Jau labāk, nekā to darīt katru reizi, bet var iztikt arī bez tā.

Pietiek nodot tikai vienu parametru, kas satur masīva serializēts attēlojums:

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

Vienīgā atšķirība ir nepieciešamība nepārprotami pārvērst argumentu vēlamajā masīva veidā. Bet tas nerada problēmas, jo mēs jau iepriekš zinām, kur mēs risinām.

Parauga pārsūtīšana (matrica)

Parasti šīs ir visu veidu iespējas datu kopu pārsūtīšanai ievietošanai datu bāzē “vienā pieprasījumā”:

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

Papildus iepriekš aprakstītajām problēmām saistībā ar pieprasījuma "pārlīmēšanu", tas var arī mūs novest pie pietrūkst atmiņas un servera avārija. Iemesls ir vienkāršs - PG argumentiem rezervē papildu atmiņu, un ierakstu skaitu komplektā ierobežo tikai biznesa loģikas lietojumprogramma Wishlist. Īpaši klīniskos gadījumos bija nepieciešams redzēt "numurētie" argumenti, kas lielāki par 9000 USD - nedari to šādā veidā.

Pārrakstīsim vaicājumu, jau piesakoties "divu līmeņu" serializācija:

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;

Jā, ja masīvā ir "sarežģītas" vērtības, tās jāierāmē ar pēdiņām.
Ir skaidrs, ka šādā veidā jūs varat "paplašināt" atlasi ar patvaļīgu skaitu lauku.

unnest, unnest,…

Ik pa laikam ir iespējas "masīvu masīva" vietā nodot vairākus manis pieminētos "kolonnu masīvus" pēdējā rakstā:

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

Izmantojot šo metodi, ja pieļaujat kļūdu, ģenerējot dažādu kolonnu vērtību sarakstus, to ir ļoti viegli iegūt pilnībā negaidīti rezultāti, kas ir atkarīgi arī no servera versijas:

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

Sākot ar versiju 9.3, PostgreSQL ir pilnvērtīgas funkcijas darbam ar json tipu. Tāpēc, ja jūsu ievades parametri ir definēti pārlūkprogrammā, varat tur izveidot formu json objekts SQL vaicājumam:

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

Iepriekšējām versijām var izmantot to pašu metodi katrs(hveikals), taču pareiza "locīšana" ar sarežģītiem objektiem hstore var radīt problēmas.

json_populate_recordset

Ja jau iepriekš zināt, ka dati no “input” json masīva tiks aizpildīti, lai aizpildītu kādu tabulu, varat daudz ietaupīt “atsauces” laukos un apraidē uz vajadzīgajiem veidiem, izmantojot funkciju 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

Un šī funkcija vienkārši “paplašinās” nodoto objektu masīvu atlasē, nepaļaujoties uz tabulas formātu:

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

PAGAIDU GALDS

Bet, ja datu apjoms pārraidītajā izlasē ir ļoti liels, tad to iemest vienā serializētā parametrā ir grūti un dažreiz neiespējami, jo tas prasa vienreizēju liels atmiņas sadalījums. Piemēram, jums ir jāsavāc liela notikumu datu pakete no ārējās sistēmas ilgu, ilgu laiku un pēc tam vēlaties tos apstrādāt vienreiz datu bāzes pusē.

Šajā gadījumā labākais risinājums būtu izmantot pagaidu galdi:

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

Metode ir laba par retu lielu apjomu pārraidi datus.
No datu struktūras apraksta viedokļa pagaidu tabula no “parastās” atšķiras tikai ar vienu pazīmi. pg_class sistēmas tabulāun pg_type, pg_depend, pg_attribute, pg_attrdef, ... - un vispār nekā.

Tāpēc tīmekļa sistēmās ar lielu skaitu īslaicīgu savienojumu katram no tiem šāda tabula katru reizi ģenerēs jaunus sistēmas ierakstus, kas tiek dzēsti, kad savienojums ar datu bāzi tiek slēgts. Galu galā, nekontrolēta TEMP TABLE izmantošana izraisa pg_catalog tabulu "pietūkumu" un palēninot daudzas darbības, kurās tās tiek izmantotas.
Protams, ar to var cīnīties periodiskā caurlaide VAKUUMS PILNS saskaņā ar sistēmas kataloga tabulām.

Sesijas mainīgie

Pieņemsim, ka iepriekšējā gadījuma datu apstrāde vienam SQL vaicājumam ir diezgan sarežģīta, bet jūs vēlaties to darīt diezgan bieži. Tas ir, mēs vēlamies izmantot procesuālo apstrādi DO bloks, bet datu pārsūtīšana caur pagaidu tabulām būs pārāk dārga.

Mēs arī nevaram izmantot $n-parametrus, lai pārietu uz anonīmu bloku. Sesijas mainīgie un funkcija palīdzēs mums izkļūt no situācijas. pašreizējais_iestatījums.

Pirms versijas 9.2 jums bija jāveic iepriekšēja konfigurācija īpaša nosaukumvieta custom_mainable_classes "viņu" sesijas mainīgajiem. Pašreizējās versijās varat rakstīt kaut ko līdzīgu:

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

Citās atbalstītajās procesuālajās valodās ir pieejami arī citi risinājumi.

Vai zināt vairāk veidu? Dalies komentāros!

Avots: www.habr.com

Pievieno komentāru