ProHoster > Bloc > Administració > Antipatterns de PostgreSQL: passar conjunts i seleccions a SQL

Antipatterns de PostgreSQL: passar conjunts i seleccions a SQL

De tant en tant, el desenvolupador necessita passar un conjunt de paràmetres o fins i tot una selecció sencera a la sol·licitud "a l'entrada". De vegades hi ha solucions molt estranyes a aquest problema.
Antipatterns de PostgreSQL: passar conjunts i seleccions a SQL
Anem “del contrari” a veure com no fer-ho, per què i com ho pots fer millor.

"Inserció" directa de valors al cos de la sol·licitud

Normalment s'assembla a això:

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

... o així:

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

Sobre aquest mètode es diu, escriu i fins i tot dibuixat suficient:

Antipatterns de PostgreSQL: passar conjunts i seleccions a SQL

Gairebé sempre ho és camí directe a la injecció SQL i una càrrega addicional a la lògica empresarial, que es veu obligada a "enganxar" la vostra cadena de consulta.

Aquest enfocament només es pot justificar parcialment si és necessari. utilitzar particions a les versions 10 i posteriors de PostgreSQL per a un pla més eficient. En aquestes versions, la llista de seccions escanejades es determina sense tenir en compte els paràmetres transmesos, només en funció del cos de la sol·licitud.

$n arguments

Utilitzar marcadors de posició els paràmetres és bo, us permet utilitzar DECLARACIONS PREPARADES, reduint la càrrega tant a la lògica empresarial (la cadena de consulta només es forma i es transmet una vegada) com al servidor de la base de dades (no cal re-anàlisi i planificació per a cada instància de la sol·licitud).

Nombre variable d'arguments

Els problemes ens esperaran quan volem passar un nombre desconegut d'arguments per endavant:

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

Si deixeu la sol·licitud en aquest formulari, tot i que ens estalviarà de possibles injeccions, encara caldrà enganxar/analitzar la sol·licitud. per a cada opció a partir del nombre d'arguments. Ja és millor que fer-ho cada cop, però pots prescindir-ne.

N'hi ha prou amb passar només un paràmetre que contingui representació serialitzada d'una matriu:

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

L'única diferència és la necessitat de convertir explícitament l'argument al tipus de matriu desitjat. Però això no genera problemes, ja que ja sabem per endavant on ens dirigim.

Transferència de mostra (matriu)

En general, aquestes són tot tipus d'opcions per transferir conjunts de dades per inserir-los a la base de dades "en una sol·licitud":

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

A més dels problemes descrits anteriorment amb el "reenganxat" de la sol·licitud, això també ens pot portar sense memòria i fallada del servidor. El motiu és senzill: PG reserva memòria addicional per als arguments i el nombre de registres del conjunt només està limitat per l'aplicació de lògica empresarial Wishlist. En casos especialment clínics calia veure arguments "numerats" superiors a 9000 dòlars - No ho facis d'aquesta manera.

Reescriurem la consulta, aplicant-la ja serialització de "dos nivells".:

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;

Sí, en el cas de valors "complexos" dins d'una matriu, s'han d'emmarcar amb cometes.
És evident que d'aquesta manera es pot "ampliar" la selecció amb un nombre arbitrari de camps.

desarreglar, desencallar,...

De tant en tant hi ha opcions per passar en lloc d'una "matriu de matrius" diverses "matrius de columnes" que he esmentat en el darrer article:

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

Amb aquest mètode, si cometeu un error en generar llistes de valors per a diferents columnes, és molt fàcil obtenir-ne completament resultats inesperats, que també depenen de la versió del servidor:

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

A partir de la versió 9.3, PostgreSQL té funcions completes per treballar amb el tipus json. Per tant, si els vostres paràmetres d'entrada es defineixen al navegador, podeu fer-ho allà mateix objecte json per a la consulta SQL:

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

Per a les versions anteriors, es pot utilitzar el mateix mètode cadascun (hstore), però el "plegament" correcte amb objectes complexos que escapen a hstore pot causar problemes.

json_populate_recordset

Si sabeu per endavant que les dades de la matriu json "d'entrada" aniran a omplir alguna taula, podeu estalviar molt en els camps de "desreferenciar" i emetre als tipus desitjats mitjançant la funció 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

I aquesta funció simplement "ampliarà" la matriu d'objectes passats a una selecció, sense dependre del format de la taula:

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

TAULA TEMPORAL

Però si la quantitat de dades a la mostra transmesa és molt gran, llavors llançar-les a un paràmetre serialitzat és difícil, i de vegades impossible, ja que requereix un sol cop. gran assignació de memòria. Per exemple, heu de recopilar un gran lot de dades d'esdeveniments d'un sistema extern durant molt de temps i, a continuació, voleu processar-lo una vegada a la base de dades.

En aquest cas, la millor solució seria utilitzar taules temporals:

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

El mètode és bo per a la transmissió poc freqüent de grans volums dades.
Des del punt de vista de la descripció de l'estructura de les seves dades, una taula temporal es diferencia d'una taula "normal" només en una característica. a la taula del sistema pg_classi en pg_type, pg_depend, pg_attribute, pg_attrdef, ... —i res de res.

Per tant, en sistemes web amb un gran nombre de connexions de curta durada per a cadascuna d'elles, aquesta taula generarà cada cop nous registres del sistema, que s'eliminen quan es tanca la connexió a la base de dades. Finalment, l'ús incontrolat de TEMP TABLE condueix a la "inflació" de les taules a pg_catalog i alentint moltes operacions que els utilitzen.
Per descomptat, això es pot combatre passa periòdica BUIT COMPLET segons les taules del catàleg del sistema.

Variables de sessió

Suposem que el processament de les dades del cas anterior és força complex per a una sola consulta SQL, però voleu fer-ho amb força freqüència. És a dir, volem utilitzar el processament processal Bloc DO, però utilitzar la transferència de dades a través de taules temporals serà massa car.

Tampoc podem utilitzar $n-parametres per passar a un bloc anònim. Les variables de sessió i la funció ens ajudaran a sortir de la situació. configuració_actual.

Abans de la versió 9.2, calia preconfigurar espai de noms especial classes_variables_personalitzades per a les "seves" variables de sessió. A les versions actuals, podeu escriure alguna cosa com això:

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

Hi ha altres solucions disponibles en altres idiomes de procediment compatibles.

Coneixes més maneres? Comparteix als comentaris!

Font: www.habr.com

Afegeix comentari