ProHoster > Blog > Administrácia > PostgreSQL Antipatterns: odovzdávanie množín a výberov do SQL

PostgreSQL Antipatterns: odovzdávanie množín a výberov do SQL

Z času na čas vývojár potrebuje odovzdať do požiadavky súbor parametrov alebo dokonca celý výber "Pri vchode". Niekedy narazíte na veľmi zvláštne riešenia tohto problému.
PostgreSQL Antipatterns: odovzdávanie množín a výberov do SQL
Poďme späť a uvidíme, čo nerobiť, prečo a ako to môžeme urobiť lepšie.

Priame vloženie hodnôt do tela požiadavky

Zvyčajne to vyzerá nejako takto:

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

...alebo takto:

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

Táto metóda bola povedané, napísaná a dokonca nakreslený veľa:

PostgreSQL Antipatterns: odovzdávanie množín a výberov do SQL

Takmer vždy to tak je priama cesta k SQL injekciám a zbytočné zaťaženie obchodnej logiky, ktorá je nútená „zlepiť“ váš riadok dopytu.

Tento prístup možno čiastočne odôvodniť iba v prípade potreby pomocou delenia v PostgreSQL verzie 10 a nižšej, aby ste získali efektívnejší plán. V týchto verziách sa zoznam naskenovaných úsekov určuje bez zohľadnenia prenášaných parametrov, len na základe tela požiadavky.

$n-argumentov

Použitie zástupné symboly parametre sú dobré, umožňuje vám to použiť PRIPRAVENÉ VYHLÁSENIA, čím sa znižuje zaťaženie obchodnej logiky (reťazec dotazu sa generuje a prenáša len raz) a databázového servera (nie je potrebná opätovná analýza a plánovanie pre každú inštanciu dotazu).

Variabilný počet argumentov

Problémy nás čakajú, keď budeme chcieť odovzdať neznámy počet argumentov:

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

Ak ponecháme žiadosť v tejto forme, aj keď nás to ochráni pred prípadnými injekciami, bude to stále viesť k potrebe zlúčiť/spracovať žiadosť pre každú možnosť v závislosti od počtu argumentov. Je to lepšie ako to robiť zakaždým, ale dá sa to zvládnuť aj bez toho.

Stačí odovzdať len jeden parameter obsahujúci serializovaná reprezentácia poľa:

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

Jediným rozdielom je potreba explicitne previesť argument na požadovaný typ poľa. To však nespôsobuje problémy, pretože už vopred vieme, kam ideme.

Prenos vzorky (matice)

Zvyčajne ide o najrôznejšie možnosti prenosu súborov údajov na vloženie do databázy „v jednej žiadosti“:

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

Okrem vyššie popísaných problémov s „prelepovaním“ požiadavky nás môže viesť aj k tomu nedostatok pamäte a pád servera. Dôvod je jednoduchý – PG rezervuje dodatočnú pamäť pre argumenty a počet záznamov v sade je obmedzený len aplikačnými potrebami obchodnej logiky. V obzvlášť klinických prípadoch som musel vidieť Argumenty „číslo“ sú viac ako 9000 XNUMX USD - nerobte to týmto spôsobom.

Prepíšme požiadavku pomocou už „dvojúrovňovej“ serializácie:

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;

Áno, v prípade „komplexných“ hodnôt v poli musia byť ohraničené úvodzovkami.
Je jasné, že týmto spôsobom môžete „rozšíriť“ výber o ľubovoľný počet polí.

nehniezdiť, nehniezdiť,…

Z času na čas existujú možnosti, ako namiesto „pola polí“ odovzdať niekoľko „polí stĺpcov“, ktoré som spomenul v poslednom článku:

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

S touto metódou, ak urobíte chybu pri generovaní zoznamov hodnôt pre rôzne stĺpce, je veľmi ľahké získať neočakávané výsledky, ktoré tiež závisia od verzie servera:

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

Od verzie 9.3 má PostgreSQL plnohodnotné funkcie pre prácu s typom json. Preto, ak sa definícia vstupných parametrov vyskytuje vo vašom prehliadači, môžete ju vytvoriť priamo tam json objekt pre SQL dotaz:

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

Pre predchádzajúce verzie je možné použiť rovnakú metódu každý(hstore), ale správna "konvolúcia" s únikom zložitých objektov v hstore môže spôsobiť problémy.

json_populate_recordset

Ak vopred viete, že údaje zo „vstupného“ poľa json sa použijú na vyplnenie nejakej tabuľky, môžete veľa ušetriť v poliach „dereferencovania“ a ich pretypovaní na požadované typy pomocou funkcie 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

A táto funkcia jednoducho „rozšíri“ odovzdané pole objektov do výberu bez toho, aby sa spoliehala na formát tabuľky:

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

DOČASNÁ TABUĽKA

Ak je však množstvo údajov v prenesenej vzorke veľmi veľké, potom je ich vloženie do jedného serializovaného parametra ťažké a niekedy nemožné, pretože to vyžaduje jednorazový alokovať veľké množstvo pamäte. Potrebujete napríklad zbierať veľký balík dát o udalostiach z externého systému dlho, dlho a potom ich chcete jednorazovo spracovať na strane databázy.

V tomto prípade by bolo najlepším riešením použiť dočasné stoly:

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

Metóda je dobrá na príležitostné presuny veľkých objemov údaje.
Z hľadiska popisu štruktúry svojich údajov sa dočasná tabuľka líši od „bežnej“ iba jedným spôsobom. v systémovej tabuľke pg_classA pg_type, pg_depend, pg_attribute, pg_attrdef, ... - vôbec nič.

Preto vo webových systémoch s veľkým počtom krátkodobých spojení pre každý z nich takáto tabuľka vygeneruje zakaždým nové systémové záznamy, ktoré sa po ukončení spojenia s databázou vymažú. nakoniec nekontrolované používanie TEMP TABLE vedie k „nafúknutiu“ tabuliek v pg_catalog a spomalenie mnohých operácií, ktoré ich využívajú.
Samozrejme, že sa to dá riešiť používaním periodický prechod VACUUM FULL podľa katalógových tabuliek systému.

Premenné relácie

Predpokladajme, že spracovanie údajov z predchádzajúceho prípadu je pomerne zložité na jeden SQL dotaz, ale chcete to robiť pomerne často. To znamená, že chceme použiť procesné spracovanie v DO blokovať, ale používanie prenosu dát cez dočasné tabuľky bude príliš drahé.

Taktiež nebudeme môcť použiť $n-parameters na prechod do anonymného bloku. Premenné relácie a funkcia nám pomôžu dostať sa z tejto situácie aktuálne_nastavenie.

Pred verziou 9.2 bolo potrebné predkonfigurovať špeciálny menný priestor custom_variable_classes pre „vaše“ premenné relácie. V aktuálnych verziách môžete napísať niečo takéto:

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

Iné riešenia možno nájsť v iných podporovaných procedurálnych jazykoch.

Poznáte nejaké iné spôsoby? Podeľte sa v komentároch!

Zdroj: hab.com

Pridať komentár