ProHoster > Log > administrasjon > PostgreSQL-antimønstre: Sende sett og valg til SQL

PostgreSQL-antimønstre: Sende sett og valg til SQL

Fra tid til annen trenger utvikleren sende et sett med parametere eller til og med et helt utvalg til forespørselen "ved inngangen". Noen ganger er det veldig merkelige løsninger på dette problemet.
PostgreSQL-antimønstre: Sende sett og valg til SQL
La oss gå "fra det motsatte" og se hvordan du ikke gjør det, hvorfor og hvordan du kan gjøre det bedre.

Direkte "innsetting" av verdier i forespørselsteksten

Det ser vanligvis omtrent slik ut:

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

... eller slik:

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

Om denne metoden er det sagt, skrevet og til og med tegnet nok:

PostgreSQL-antimønstre: Sende sett og valg til SQL

Nesten alltid er det det direkte vei til SQL-injeksjon og en ekstra belastning på forretningslogikken, som er tvunget til å "lime" søkestrengen din.

Denne tilnærmingen kan kun delvis begrunnes hvis nødvendig. bruk partisjonering i PostgreSQL versjoner 10 og nedenfor for en mer effektiv plan. I disse versjonene bestemmes listen over skannede seksjoner uten å ta hensyn til de overførte parametrene, bare på grunnlag av forespørselsorganet.

$n argumenter

Bruk plassholdere parametere er bra, det lar deg bruke UTARBEIDEDE UTTALELSER, reduserer belastningen både på forretningslogikken (spørringsstrengen dannes og overføres bare én gang) og på databaseserveren (reparsing og planlegging er ikke nødvendig for hver forekomst av forespørselen).

Variabelt antall argumenter

Problemer vil vente på oss når vi ønsker å sende et ukjent antall argumenter på forhånd:

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

Hvis du legger igjen forespørselen i dette skjemaet, vil det, selv om det vil redde oss fra potensielle injeksjoner, fortsatt føre til behovet for å lime / analysere forespørselen for hvert alternativ fra antall argumenter. Allerede bedre enn å gjøre det hver gang, men du kan klare deg uten det.

Det er nok å sende bare én parameter som inneholder serialisert representasjon av en matrise:

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

Den eneste forskjellen er behovet for å eksplisitt konvertere argumentet til ønsket matrisetype. Men dette skaper ikke problemer, siden vi allerede på forhånd vet hvor vi henvender oss.

Prøveoverføring (matrise)

Vanligvis er dette alle slags alternativer for å overføre datasett for innsetting i databasen "i én forespørsel":

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

I tillegg til problemene beskrevet ovenfor med «re-liming» av forespørselen, kan dette også føre oss til tomt for minne og serverkrasj. Årsaken er enkel - PG reserverer ekstra minne for argumentene, og antall poster i settet er bare begrenset av forretningslogikkapplikasjonens ønskeliste. I spesielt kliniske tilfeller var det nødvendig å se "nummererte" argumenter større enn $9000 - ikke gjør det på denne måten.

La oss skrive om spørringen, og søke allerede "to-nivå" serialisering:

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;

Ja, i tilfelle av "komplekse" verdier inne i en matrise, må de være innrammet med anførselstegn.
Det er klart at man på denne måten kan "utvide" utvalget med et vilkårlig antall felt.

urolighet, uro, …

Fra tid til annen er det alternativer for å passere i stedet for en "array of arrays" flere "arrays of columns" som jeg nevnte i den siste artikkelen:

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

Med denne metoden, hvis du gjør en feil når du genererer lister med verdier for forskjellige kolonner, er det veldig enkelt å få helt uventede resultater, som også avhenger av serverversjonen:

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

Fra og med versjon 9.3 har PostgreSQL introdusert fullverdige funksjoner for å jobbe med json-typen. Derfor, hvis inngangsparametrene dine er definert i nettleseren, kan du rett der og danne json-objekt for SQL-spørring:

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

For tidligere versjoner kan samme metode brukes for hver(hstore), men riktig "folding" med unnslippende komplekse objekter i hstore kan forårsake problemer.

json_populate_recordset

Hvis du på forhånd vet at dataene fra «input» json-matrisen vil gå til å fylle ut en eller annen tabell, kan du spare mye i «dereferencing»-felt og casting til de ønskede typene ved å bruke json_populate_recordset-funksjonen:

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

json_to_recordset

Og denne funksjonen vil ganske enkelt "utvide" det beståtte utvalget av objekter til et utvalg, uten å stole på tabellformatet:

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

MIDLERTIDIG BORD

Men hvis datamengden i den overførte prøven er veldig stor, er det vanskelig å kaste det inn i en serialisert parameter, og noen ganger umulig, siden det krever en engangs stor minneallokering. For eksempel må du samle inn en stor batch med hendelsesdata fra et eksternt system i lang, lang tid, og så vil du behandle det en gang på databasesiden.

I dette tilfellet vil den beste løsningen være å bruke midlertidige bord:

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

Metoden er god for sjelden overføring av store volumer data.
Fra synspunktet om å beskrive strukturen til dataene, skiller en midlertidig tabell seg fra en "vanlig" tabell i bare én funksjon. i systemtabellen pg_classog i pg_type, pg_depend, pg_attribute, pg_attrdef, ... – og ingenting i det hele tatt.

Derfor vil en slik tabell i websystemer med et stort antall kortvarige tilkoblinger for hver av dem generere nye systemposter hver gang, som slettes når tilkoblingen til databasen lukkes. Etter hvert, ukontrollert bruk av TEMP TABLE fører til "svelling" av tabeller i pg_catalog og bremse ned mange operasjoner som bruker dem.
Dette kan selvsagt bekjempes periodisk bestått VAKUUM FULL i henhold til systemkatalogtabellene.

Sesjonsvariabler

Anta at behandlingen av dataene fra forrige sak er ganske komplisert for en enkelt SQL-spørring, men du vil gjøre det ganske ofte. Det vil si at vi ønsker å bruke prosessuell behandling i GJØR blokker, men å bruke dataoverføring gjennom midlertidige tabeller vil bli for dyrt.

Vi kan heller ikke bruke $n-parametere for å sende til en anonym blokk. Sesjonsvariablene og funksjonen vil hjelpe oss å komme oss ut av situasjonen. nåværende innstilling.

Før versjon 9.2 måtte du forhåndskonfigurere spesielt navneområde tilpassede_variable_klasser for "deres" øktvariabler. På gjeldende versjoner kan du skrive noe slikt:

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

Det finnes andre løsninger tilgjengelig på andre støttede prosedyrespråk.

Vet du flere måter? Del i kommentarene!

Kilde: www.habr.com

Legg til en kommentar