PostgreSQL Antipatterns: paraméterek és kiválasztások átadása SQL-ben

Időnként a fejlesztőnek szüksége van arra, hogy a kérésben paraméterek halmazát vagy akár egy teljes mintát adjon át «bemenetként». Néha nagyon furcsa megoldásokkal találkozunk erre a feladatra.

Kezdjük „a végéről”, és nézzük meg, hogyan ne csináljuk, miért, és hogyan lehetne jobban.

Közvetlen „beillesztés” értékek a kérés törzsébe

Általában így néz ki:

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

… vagy így:

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

Erről a módszerről elegendő írás, beszélgetés és még ábrázolás is készült eléggé.

Szinte mindig ez — közvetlen út a SQL-injekciókhoz és a felesleges terheléshez a vállalati logikában, amely kénytelen “összeragasztani” a kérésed szövegét.

Részben ez a megközelítés csak abban az esetben indokolt, ha szekcionálást kell alkalmaznunk a PostgreSQL 10 és régebbi verziókban a hatékonyabb tervek érdekében. Ezekben a verziókban a beolvasandó szekciók listája még a továbbított paraméterek figyelembevételé nélkül készült, csak a kérés törzsén alapulva.

$n-érvek

Használat helyettesítők paraméterek — ez jó, lehetővé teszi a ELŐKÉSZÍTETT NYILATKOZATOK, csökkentve a terhelést mind a vállalati logikán (a kérés szövege egyszer készül el és kerül továbbításra), mind az adatbázis szerveren (nem szükséges a kérés minden példányához újraértelmezni és tervezni).

Változó számú érv

Problémák várnak ránk, amikor előre ismeretlen számú érvet szeretnénk átadni:

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

Ha a lekérdezést ilyen formában hagyjuk, az bár megvéd minket a potenciális injekcióktól, de mégis késztetni fog minket a lekérdezés összeállítására / elemzésére minden egyes érvsorozat esetén.Ez már jobb, mint ezt minden alkalommal megtenni, de ezt is elkerülhetjük.

Elég, ha csak egy paramétert adjunk át, amely tartalmazza a tömb sorosított reprezentációját:

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

Az egyetlen különbség, hogy szükség van az érv kifejezett átalakítására a kívánt tömbtípusra. De ez nem okoz problémát, mivel előre tudjuk, hova tartozunk.

Átvitel mintavétel (mátrix)

Általában ez különböző adathalmazonatok átadásának módjai, amelyek az adatbázisba „egy kérésben” illeszkednek:

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

A fent leírt „összeragasztási” problémák mellett ez még memória túllépéshez vezethet és a szerver leállításához. Az ok egyszerű — a PG a paramétereknél további memóriát foglal le, és a rekordok száma a vállalati logika igényei alapján van korlátozva. Különösen drámai esetekben láttam a "számok" argumentumok több mint 9000 dollár — ne csináld így.

Írjuk át a lekérdezést, alkalmazva már a "két szintű" sorozatolást:

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;

Igen, a "bonyolult" értékek esetén a tömbön belül őket idézőjelekbe kell zárni.
Nyilvánvaló, hogy ezen a módon bármilyen számú mezővel "kiterjeszthetjük" a lekérdezést.

unnest, unnest, …

Időnként előfordul, hogy a "tömbtömb" átadása helyett több "oszloptömböt" alkalmaznak, amiről már beszéltem. az előző cikkben:

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

Ebben a módon, ha hibázik az értéklisták generálásakor a különböző oszlopok számára, nagyon könnyű egészen meglepő eredményeket kapni, amik a szerver verziójától is függenek:

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

Már a 9.3-as verziótól kezdve a PostgreSQL teljes funkciókkal rendelkezik a json típus használatára. Tehát, ha a bemeneti paraméterek definiálása a böngészőben történik, ott közvetlenül létrehozhatja a json-objektumot az SQL lekérdezéshez:

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

A korábbi verziók esetében ugyanúgy használhatja ezt a módot each(hstore), de a bonyolult objektumok helyes "összevonása" az hstore-ban problémákat okozhat.

json_populate_recordset

Ha előre tudja, hogy az "bemeneti" json-tömbből származó adatok egy adott táblázat kitöltésére szolgálnak, jelentősen spórolhat az "fields" kiértékelésén és a megfelelő típusokra alakításon a json_populate_recordset függvény használatával:

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

json_to_recordset

Ez a függvény egyszerűen "kiterjeszti" a megadott objektumtömböt a lekérdezésben, anélkül, hogy a táblázat formátumát figyelembe venné:

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

IDEIGLENES TÁBLA

De ha az átadott adat mennyisége nagyon nagy, akkor egyetlen sorozatban elküldeni ezt — nehéz, néha lehetetlen is, mivel egy nagy mennyiségű memória egyszeri foglalását igényli. Például hosszú időn keresztül nagy adatcsomagokat kell gyűjtenie külső rendszerből, majd szeretné azokat egyszerre feldolgozni az adatbázis oldalán.

Ebben az esetben a legjobb megoldás ideiglenes táblák használata:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ismételje meg sok-sok alkalommal
...
-- itt valami hasznosat csinálunk az egész táblával

Ez a módszer kifejezetten nagy mennyiségű adat ritka továbbítására jó. adatok.
Adatszerkezet leírásának szempontjából az ideiglenes tábla annyiban különbözik a «normál» táblától, hogy van egy megkülönböztető tulajdonsága a rendszer tábla pg_class, míg a pg_type, pg_depend, pg_attribute, pg_attrdef, … — akkor pedig semmiben.

Ezért a webes rendszerekben, ahol sok rövid életű kapcsolat létezik, minden egyes kapcsolat esetén új rendszerbejegyzéseket fog generálni, amelyek az adatbázissal való kapcsolat megszakításakor törlődnek. Ennek következményeként a TEMP TABLE ellenőrizetlen használata a pg_catalog táblák «megnövekedéséhez» vezet és számos művelet lassításához, amelyek ezeket használják.
Persze, ez ellen harcolhatunk szisztematikus VACUUM FULL átjárással a rendszerkatalógus táblákon.

Session változók

Tegyük fel, hogy az előző esetben a adatok feldolgozása elég bonyolult egy SQL-lekérdezéshez, de azt gyakran szeretnénk elvégezni. Vagyis szeretnénk procedurális feldolgozást alkalmazni a DO-blokkon belül, de az ideiglenes táblákon keresztül történő adatátadás túl költséges lenne.

A $n-paraméterek használata az anonim blokkba való átvitelhez sem lenne lehetséges. Az idézőjelem segíthetnek a session változók és a current_setting.

Használat előtt a 9.2-es verzióban elő kellett konfigurálni egy külön névtér custom_variable_classes a «saját» session változók számára. Az aktuális verziókban azonban körülbelül így lehet írni:

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

Más támogatott procedurális nyelvekben is találhatunk egyéb megoldásokat.

Ismersz még más módszereket? Oszd meg őket a megjegyzésekben!

Forrás: habr.com