Udvikleren kan fra tid til anden have behov for at overføre et sæt parametre eller endda en hel sampling som input. Nogle gange støder man på meget mærkelige løsninger på denne opgave.
Lad os gå 'den modsatte vej' og se på, hvordan man ikke bør gøre det, hvorfor, og hvordan man kan gøre det bedre.
Direkte 'indsættelse' af værdier i forespørgslen
ser normalt omtrent sådan ud:
query = "SELECT * FROM tbl WHERE id = " + value... eller sådan her:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)Der er sagt, skrevet og ret mange om denne metode:
Næsten altid er det — en direkte vej til SQL-injektioner og unødvendig belastning af forretningslogikken, som er tvunget til at 'lime' din forespørgselsstreng.
Delvist kan denne tilgang være begrundet kun i tilfælde af behovet for at anvende partitionering i versioner af PostgreSQL 10 og tidligere for at opnå en mere effektiv plan. I disse versioner bestemmes listen over scannede sektioner endnu uden hensyntagen til de overførte parametre, kun baseret på forespørgselens krop.
$n-argumenter
Brugen af parametre — det er godt, det tillader brug af , hvilket reducerer belastningen både på forretningslogikken (forespørgselsstrengen dannes og overføres kun én gang) og på databaserver (der kræves ikke gentagen parsing og planlægning for hver instans af forespørgslen).
Variabelt antal argumenter
Problemer vil vente på os, når vi ønsker at overføre et på forhånd ukendt antal argumenter:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Hvis man lader forespørgslen stå i denne form, vil det, selvom det beskytter os mod potentielle injektioner, stadig føre til behovet for limning/parsing af forespørgslen for hver variant fra antallet af argumenter.Det er allerede bedre end at gøre dette hver gang, men man kan klare sig uden det.
Det er tilstrækkeligt at overføre bare ét parameter, der indeholder en serialiseret repræsentation af en array:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Den eneste forskel er nødvendigheden af at eksplicit konvertere argumentet til den ønskede array-type. Men det giver ikke problemer, da vi allerede ved, hvor vi skal hen.
Overførsel af oplysninger (matrix)
Normalt handler det om forskellige måder at overføre datasæt til indsættelse i databasen 'i én forespørgsel':
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Udover de tidligere nævnte problemer med 'limning' af forespørgslen, kan dette også føre til out of memory og servernedbrud. Årsagen er simpel — under argumenterne reserverer PG ekstra hukommelse, og antallet af poster i sættet er kun begrænset af de funktionelle ønsker i forretningslogikken. I særligt kliniske tilfælde har man set «nummererede» argumenter over $9000 — det behøver man ikke.
Lad os omskrive forespørgslen ved at anvende «to-niveau» 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 tilfælde af «komplekse» værdier inden for arrayet skal de indrammes med citationstegn.
Det er tydeligt, at man ved denne metode kan «udvikle» et udtræk med et vilkårligt antal felter.
unnest, unnest, …
Der opstår lejlighedsvis varianter, hvor man i stedet for «array af arrays» sender flere «arrays af kolonner», som jeg har nævnt :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Ved denne metode er det meget nemt at få helt uventede resultater, afhængigt af serverversionen:
-- $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
Siden version 9.3 har PostgreSQL haft fuld funktionalitet til at arbejde med json-typen. Derfor kan du, hvis definitionen af indgangsparametrene sker i browseren, direkte der danne json-objektet til SQL-forespørgslen:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'Til tidligere versioner kan den samme metode bruges til each(hstore), men korrekt «svømning» med escape af komplekse objekter i hstore kan forårsage problemer.
json_populate_recordset
Hvis du på forhånd ved, at data fra det «indgående» json-array skal bruges til at udfylde en bestemt tabel, kan du spare meget på «udredning» af felter og konvertering til de nødvendige typer ved at bruge funktionen 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
Denne funktion vil blot «udvikle» det overførte array af objekter i et udtræk uden at basere sig på tabelformatet:
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 | 2TEMPORARY TABLE
Men hvis mængden af data i den overførte forespørgsel er meget stor, kan det være svært at placere den i et enkelt serialiseret parameter — nogle gange er det endda umuligt, da det kræver en gang tildeling af en stor mængde hukommelseFor example, you may need to collect a large batch of event data from an external system for a long time, and then want to process it in bulk on the database side.
In this case, the best solution will be the use of :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- repeat many many times
...
-- here we do something useful with this entire table
This method is particularly good for occasionally transferring large volumes data.
From the perspective of describing the structure of your data, a temporary table differs from a "regular" table only by one characteristic in the system table pg_class, og der sendes noget til den, vil flytning af denne task til sektionen pg_type, pg_depend, pg_attribute, pg_attrdef, … — nothing at all.
Therefore, in web systems with a large number of short-lived connections, a table like this will generate new system records each time, which are deleted when the connection to the database is closed. As a result, uncontrolled use of TEMP TABLE leads to "bloating" of the tables in pg_catalog and slows down many operations that use them.
Of course, this can be combated with the help of a periodic VACUUM FULL pass through the system catalog tables.
Session variables
Let's assume that processing data from the previous case is complex enough for a single SQL query, but we want to do it quite frequently. That is, we want to use procedural processing in , but using temporary tables for data transfer would be too cumbersome.
We also cannot use $n-parameters for transmission into an anonymous block. Session variables and the function current_setting.
Up to version 9.2, it was necessary to pre-configure a custom_variable_classes for "your" session variables. In current versions, you can write something like this:
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 : 3In other supported procedural languages, you can find other solutions.
Do you know any other ways? Share in the comments!
Kilde: habr.com
