PostgreSQL-teenpatrone: Gee stelle en keuses oor na SQL

Van tyd tot tyd het die ontwikkelaar nodig gee 'n stel parameters of selfs 'n hele seleksie aan die versoek "by die ingang". Soms is daar baie vreemde oplossings vir hierdie probleem.

Kom ons gaan "van die teenoorgestelde" en kyk hoe om dit nie te doen nie, hoekom, en hoe jy dit beter kan doen.

Direkte "invoeging" van waardes in die versoekliggaam

Dit lyk gewoonlik so iets:

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

... of soos volg:

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

Oor hierdie metode word gesê, geskryf en selfs geteken genoeg:

Byna altyd is dit direkte pad na SQL-inspuiting en 'n ekstra las op die besigheidslogika, wat gedwing word om jou navraagstring te "gom".

Hierdie benadering kan slegs gedeeltelik geregverdig word indien nodig. gebruik partisionering in PostgreSQL weergawes 10 en onder vir 'n meer doeltreffende plan. In hierdie weergawes word die lys van geskandeerde afdelings bepaal sonder om die oorgedra parameters in ag te neem, slegs op grond van die versoekliggaam.

$n argumente

Gebruik plekhouers parameters is goed, dit laat jou toe om te gebruik VOORBEREIDE VERKLARING, wat die las op beide die besigheidslogika verminder (die navraagstring word slegs een keer gevorm en versend) en op die databasisbediener (herontleding en beplanning is nie nodig vir elke geval van die versoek nie).

Veranderlike aantal argumente

Probleme sal op ons wag wanneer ons vooraf 'n onbekende aantal argumente wil deurgee:

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

As u die versoek in hierdie vorm laat, dan sal dit ons van potensiële inspuitings red, maar dit sal steeds lei tot die behoefte om die versoek vas te plak / te ontleed vir elke opsie uit die aantal argumente. Reeds beter as om dit elke keer te doen, maar jy kan daarsonder klaarkom.

Dit is genoeg om slegs een parameter wat bevat deur te gee geserialiseerde voorstelling van 'n skikking:

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

Die enigste verskil is die behoefte om die argument eksplisiet om te skakel na die verlangde skikking tipe. Maar dit veroorsaak nie probleme nie, aangesien ons reeds vooraf weet waar ons aanspreek.

Monsteroordrag (matriks)

Gewoonlik is dit allerhande opsies vir die oordrag van datastelle vir invoeging in die databasis "in een versoek":

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

Benewens die probleme wat hierbo beskryf is met die "her-lym" van die versoek, kan dit ons ook daartoe lei uit geheue en bediener crash. Die rede is eenvoudig - PG behou addisionele geheue vir die argumente, en die aantal rekords in die stel word slegs beperk deur die besigheidslogika-toepassing Wenslys. In veral kliniese gevalle was dit nodig om te sien "genommerde" argumente groter as $9000 - moenie dit so doen nie.

Kom ons herskryf die navraag en pas reeds toe "twee-vlak" 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, in die geval van "komplekse" waardes binne 'n skikking, moet hulle omraam word met aanhalingstekens.
Dit is duidelik dat u op hierdie manier die seleksie kan "uitbrei" met 'n arbitrêre aantal velde.

onrus, onrus, …

Van tyd tot tyd is daar opsies om in plaas van 'n "skikking skikkings" verskeie "skikkings kolomme" wat ek genoem het, deur te gee in die laaste artikel:

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

Met hierdie metode, as u 'n fout maak wanneer u waardelyste vir verskillende kolomme genereer, is dit baie maklik om heeltemal te kry onverwagte resultate, wat ook afhang van die bedienerweergawe:

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

Into

Vanaf weergawe 9.3 het PostgreSQL volwaardige funksies om met die json-tipe te werk. Daarom, as u invoerparameters in die blaaier gedefinieer is, kan u daar en vorm json-voorwerp vir SQL-navraag:

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

Vir vorige weergawes kan dieselfde metode gebruik word vir elke (hstore), maar korrekte "vou" met ontsnapping van komplekse voorwerpe in hstore kan probleme veroorsaak.

json_populate_rekordstel

As jy vooraf weet dat die data van die "input" json-skikking sal gaan om een ​​of ander tabel in te vul, kan jy baie spaar in "dereferencing"-velde en uitsaai na die verlangde tipes deur die json_populate_recordset-funksie te gebruik:

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

json_to_recordset

En hierdie funksie sal eenvoudig die geslaagde reeks voorwerpe "uitbrei" in 'n seleksie, sonder om op die tabelformaat staat te maak:

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

TYDELIKE TABEL

Maar as die hoeveelheid data in die oorgedrade monster baie groot is, is dit moeilik en soms onmoontlik om dit in een geserialiseerde parameter te gooi, aangesien dit 'n eenmalige groot geheue toekenning. Byvoorbeeld, jy moet 'n groot bondel gebeurtenisdata vir 'n lang, lang tyd van 'n eksterne stelsel versamel, en dan wil jy dit eenmalig aan die databasiskant verwerk.

In hierdie geval sal die beste oplossing wees om te gebruik tydelike tafels:

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

Die metode is goed vir ongereelde oordrag van groot volumes data.
Uit die oogpunt van die beskrywing van die struktuur van sy data, verskil 'n tydelike tabel van 'n "gewone" tabel in slegs een kenmerk. in pg_class stelsel tabelen in pg_type, pg_depend, pg_attribute, pg_attrdef, ... — en glad niks nie.

In webstelsels met 'n groot aantal kortstondige verbindings vir elk van hulle sal so 'n tabel dus elke keer nuwe stelselrekords genereer, wat uitgevee word wanneer die verbinding met die databasis gesluit word. Uiteindelik, onbeheerde gebruik van TEMP TABLE lei tot "swelling" van tabelle in pg_catalog en vertraag baie bedrywighede wat hulle gebruik.
Dit kan natuurlik bekamp word periodieke slaag VAKUUM VOL volgens die stelselkatalogustabelle.

Sessie veranderlikes

Gestel die verwerking van die data van die vorige geval is redelik kompleks vir 'n enkele SQL-navraag, maar jy wil dit nogal gereeld doen. Dit wil sê, ons wil prosedurele verwerking in DOEN blok, maar die gebruik van data-oordrag deur tydelike tabelle sal te duur wees.

Ons kan ook nie $n-parameters gebruik om na 'n anonieme blok oor te gaan nie. Die sessieveranderlikes en die funksie sal ons help om uit die situasie te kom. huidige_instelling.

Voor weergawe 9.2 moes jy vooraf konfigureer spesiale naamruimte pasgemaakte_veranderlike_klasse vir "hul" sessie veranderlikes. Op huidige weergawes kan jy iets soos volg skryf:

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

Daar is ander oplossings beskikbaar in ander ondersteunde proseduretale.

Ken jy meer maniere? Deel in die kommentaar!

Bron: will.com