ProHoster > Blog > Administratioun > PostgreSQL Antipatterns: Passéiere Sets a Wielt op SQL

PostgreSQL Antipatterns: Passéiere Sets a Wielt op SQL

Vun Zäit zu Zäit brauch den Entwéckler Passe eng Rei vu Parameteren oder souguer eng ganz Auswiel un d'Ufro "an der Entrée". Heiansdo ginn et ganz komesch Léisunge fir dëse Problem.
PostgreSQL Antipatterns: Passéiere Sets a Wielt op SQL
Loosst eis zréckgoen a kucken wat net ze maachen, firwat, a wéi mir et besser kënne maachen.

Direkt "Insertion" vu Wäerter am Ufro Kierper

Et gesäit normalerweis sou eppes aus:

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

... oder esou:

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

Dës Method gouf gesot, geschriwen an souguer gezeechent vill:

PostgreSQL Antipatterns: Passéiere Sets a Wielt op SQL

Bal ëmmer ass dëst direkten Wee zu SQL Injektiounen an onnéideg Belaaschtung op Geschäftslogik, déi gezwongen ass Är Ufrolinn ze "klemmen".

Dës Approche kann nëmmen deelweis gerechtfäerdegt ginn wann néideg. benotzen partitioning an PostgreSQL Versiounen 10 an drënner fir e méi effiziente Plang. An dëse Versioune gëtt d'Lëscht vun de gescannte Sektiounen festgeluegt ouni déi iwwerdroe Parameteren ze berücksichtegen, nëmmen op der Basis vum Ufro Kierper.

$n-Argumenter

Benotzt Plazhalter Parameteren ass gutt, et erlaabt Iech ze benotzen BERÉIERT ERKLÄNNER, d'Belaaschtung souwuel op der Geschäftslogik ze reduzéieren (d'Ufro String gëtt nëmmen eemol generéiert an iwwerdroen) an op den Datebankserver (nei Parsing a Fuerplang fir all Ufroinstanz ass net erfuerderlech).

Variabel Zuel vun Argumenter

Probleemer waarden op eis wa mir eng onbekannt Unzuel vun Argumenter weiderginn wëllen:

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

Wa mir d'Ufro an dëser Form verloossen, obwuel et eis vu potenziellen Injektiounen schützt, wäert et nach ëmmer zu der Noutwendegkeet féieren fir d'Ufro ze fusionéieren / ze analyséieren fir all Optioun je d'Zuel vun Argumenter. Scho besser wéi et all Kéier ze maachen, awer Dir kënnt ouni et maachen.

Et ass genuch nëmmen ee Parameter ze passéieren deen enthält serialiséiert Representatioun vun engem Array:

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

Deen eenzegen Ënnerscheed ass de Besoin fir d'Argument explizit an de gewënschten Array-Typ ze konvertéieren. Dat bréngt awer keng Problemer, well mer schonn am Viraus wëssen, wou mir ugoen.

Probe Transfer (Matrix)

Normalerweis sinn dës all Zorte vu Méiglechkeeten fir Datensets ze transferéieren fir an d'Datebank "an enger Ufro" ze setzen:

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

Zousätzlech zu den uewe beschriwwene Probleemer mat der Ufro "nei-gluewen", kann dëst eis och féieren aus Erënnerung an Server Crash. De Grond ass einfach - PG reservéiert zousätzlech Erënnerung fir Argumenter, an d'Zuel vun de Rekorder am Set ass nëmme limitéiert duerch d'Applikatiounsbedürfnisser vun der Geschäftslogik. A besonnesch klineschen Fäll war et néideg ze gesinn "nummeréiert" Argumenter méi wéi $ 9000 - maach et net esou.

Loosst eis d'Ufro iwwerschreiwe, scho gëllen "zwee-Niveau" Serialiséierung:

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;

Jo, am Fall vu "komplexe" Wäerter an engem Array, musse se vun Zitater ëmgi sinn.
Et ass kloer datt Dir op dës Manéier d'Auswiel mat enger arbiträrer Zuel vu Felder "ausdehnen".

Onrou, Onrou, ...

Vun Zäit zu Zäit ginn et Méiglechkeete fir amplaz vun enger "Array vun Arrays" e puer "Arrays vu Sailen" ze passéieren, déi ech erwähnt hunn am leschten Artikel:

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

Mat dëser Method, wann Dir e Feeler maacht wann Dir Lëschte vu Wäerter fir verschidde Kolonnen generéiert, ass et ganz einfach ze kréien onerwaart Resultater, déi och vun der Serverversioun ofhänken:

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

Language

Zënter Versioun 9.3 huet PostgreSQL vollwäerteg Funktiounen fir mat dem json-Typ ze schaffen. Dofir, wann d'Definitioun vun Inputparameter an Ärem Browser geschitt, kënnt Dir et direkt do bilden json Objet fir SQL Ufro:

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

Fir virdrun Versiounen, kann déi selwecht Method benotzt ginn jeweils (hstore), mee korrekt "Klappen" mat entgoe komplex Objeten an hstore kann Problemer féieren.

json_populate_recordset

Wann Dir am Viraus wësst datt d'Donnéeën vum "Input" json Array benotzt gi fir eng Tabell auszefëllen, kënnt Dir vill an "Dereferencing" Felder spueren an se op déi erfuerderlech Aarte casten andeems Dir d'json_populate_recordset Funktioun benotzt:

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

json_to_recordset

An dës Funktioun wäert einfach déi passéiert Array vun Objeten an eng Selektioun "ausbauen", ouni op den Dëschformat ze vertrauen:

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

Temporär Dësch

Awer wann d'Quantitéit vun Donnéeën an der transferéierter Probe ganz grouss ass, dann ass et schwéier an heiansdo onméiglech fir se an e serialiséierte Parameter ze setzen, well et eng eenzeg Kéier erfuerdert. grouss Erënnerung Bewëllegung. Zum Beispill musst Dir e grousse Package vun Daten iwwer Eventer vun engem externe System fir eng laang, laang Zäit sammelen, an da wëllt Dir et eemol op der Datebank Säit veraarbechten.

An dësem Fall wier déi bescht Léisung ze benotzen temporär Dëscher:

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

D'Method ass gutt fir selten Iwwerdroung vu grousse Bänn daten.
Aus der Siicht fir d'Struktur vu sengen Donnéeën ze beschreiwen, ënnerscheet sech eng temporär Tabell vun engem "normalen" Dësch an nëmmen enger Feature. an der pg_class System Dëschan a pg_type, pg_depend, pg_attribute, pg_attrdef, ... - a guer näischt.

Dofir, a Websystemer mat enger grousser Zuel vu kuerzliewege Verbindunge fir jidderee vun hinnen, gëtt esou eng Tabell all Kéier nei Systemrecords generéiert, déi geläscht ginn wann d'Verbindung mat der Datebank zou ass. schlussendlech, onkontrolléiert Notzung vun TEMP TABLE féiert zu "Schwellung" vun Dëscher an pg_catalog a verlangsamt vill Operatiounen déi se benotzen.
Natierlech kann dëst bekämpft ginn periodesch Pass VAKUUM VOLLZÄIT no de System Katalog Dëscher.

Sessioun Variablen

Loosst eis unhuelen datt d'Veraarbechtung vun den Daten aus dem fréiere Fall zimmlech komplex ass fir eng SQL Ufro, awer Dir wëllt et zimmlech dacks maachen. Dat ass, mir wëllen prozedural Veraarbechtung an DO blockéieren, awer d'Benotzung vun Datenübertragung duerch temporäre Dëscher wäert ze deier sinn.

Mir kënnen och $n-Parameteren net benotze fir an en anonyme Block ze passéieren. D'Sessiounsvariablen an d'Funktioun hëllefen eis aus der Situatioun erauszekommen. aktuell_setting.

Virun der Versioun 9.2 musst Dir virkonfiguréieren spezielle Nummraum custom_variable_classes fir "hir" Sëtzung Verännerlechen. Op aktuell Versioune kënnt Dir esou eppes schreiwen:

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

Et ginn aner Léisunge verfügbar an aneren ënnerstëtzte prozedurale Sproochen.

Kennt Dir aner Weeër? Deelen an de Kommentaren!

Source: will.com

Setzt e Commentaire