Von Zeit zu Zeit muss der Entwickler
eine Reihe von Parametern an die Anforderung oder sogar eine ganze Auswahl von "Eingaben" übergeben. Es gibt manchmal sehr seltsame Lösungen für dieses Problem.
Gehen wir "vom Gegenteil" aus und sehen, wie es sich nicht lohnt, warum und wie Sie es besser machen können.
Direkte Einfügung von Werten in den Anfragekörper
Normalerweise sieht es so aus:
query = "SELECT * FROM tbl WHERE id = " + value
... oder so:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)
Über diese Methode wird reichlich gesagt, geschrieben und
sogar gezeichnet :
Fast immer ist dies ein
direkter Pfad zur SQL-Injection und eine zusätzliche Belastung der Geschäftslogik, die gezwungen ist, Ihre Abfragezeichenfolge zu „kleben“.
Ein solcher Ansatz kann nur teilweise gerechtfertigt sein, wenn die
Verwendung von Abschnitten in Versionen von PostgreSQL 10 und niedriger erforderlich ist, um einen effizienteren Plan zu erhalten. In diesen Versionen wird die Liste der gescannten Abschnitte auch ohne Berücksichtigung der übermittelten Parameter nur auf Basis des Anforderungskörpers ermittelt.
$ n Argumente
Die Verwendung von Parameterplatzhaltern ist gut. Sie können
PREPARED STATEMENTS verwenden , um die Last sowohl für die Geschäftslogik (eine
Abfragezeichenfolge wird nur einmal generiert und übertragen) als auch für den Datenbankserver zu verringern (erneutes Parsen und Planen für jede Instanz der Anforderung ist nicht erforderlich).
Variable Anzahl von Argumenten
Probleme werden auf uns warten, wenn wir im Voraus eine unbekannte Anzahl von Argumenten übergeben möchten:
... id IN ($1, $2, $3, ...)
Wenn Sie die Anfrage in diesem Formular belassen, werden wir zwar vor potenziellen Injektionen geschützt, es ist jedoch erforderlich, die Anfrage
für jede Option anhand der Anzahl der Argumente zu analysieren. Schon besser als jedes Mal, aber darauf kann man verzichten.
Es reicht aus, nur einen Parameter zu übergeben, der die
serialisierte Darstellung des Arrays enthält :
... id = ANY($1::integer[])
Der einzige Unterschied besteht darin, dass das Argument explizit in den gewünschten Array-Typ konvertiert werden muss. Dies bereitet aber keine Probleme, da wir bereits im Vorfeld wissen, wo wir ansprechen.
Probentransfer (Matrizen)
Normalerweise sind dies alle möglichen Optionen zum Übertragen von Datensätzen zum Einfügen in die Datenbank „in einer Anforderung“:
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...
Zusätzlich zu den oben beschriebenen Problemen beim erneuten Festhalten der Anforderung kann dies auch zu nicht
genügend Arbeitsspeicher und einem Serverabsturz führen. Der Grund ist einfach: PG reserviert zusätzlichen Speicher für die Argumente, und die Anzahl der Datensätze in der Gruppe ist nur durch die angewendete Geschäftslogik von Wishlist begrenzt. In besonders klinischen Fällen musste man
"nummerierte" Argumente sehen, die größer als 9.000 US-Dollar waren - das war nicht nötig.
Wir schreiben die Anfrage um und wenden dabei die
zweistufige Serialisierung an :
INSERT INTO tbl SELECT unnest[1]::text k , unnest[2]::integer v FROM ( SELECT unnest($1::text[])::text[]
Ja, bei "komplexen" Werten innerhalb des Arrays müssen diese in Anführungszeichen gesetzt werden.
Es ist klar, dass Sie auf diese Weise die Auswahl mit einer beliebigen Anzahl von Feldern "erweitern" können.
unnest, unnest, ...
In regelmäßigen Abständen gibt es Übertragungsoptionen anstelle eines "Arrays von Arrays" mehrerer "Spaltenarrays", die ich
in einem früheren Artikel erwähnt habe :
SELECT unnest($1::text[]) k , unnest($2::integer[]) v;
Mit dieser Methode, die beim Generieren von Wertelisten für verschiedene Spalten einen Fehler macht, ist es sehr einfach, völlig
unerwartete Ergebnisse zu erzielen , die auch von der Serverversion abhängen:
Json
Ab Version 9.3 hat PostgreSQL umfassende Funktionen für die Arbeit mit dem Typ json eingeführt. Wenn die Definition der Eingabeparameter in Ihrem Browser erfolgt, können Sie daher direkt dort ein
json-Objekt für die SQL-Abfrage erstellen:
SELECT key k , value v FROM json_each($1::json);
Für frühere Versionen kann für
jeden (hstore) dieselbe Methode verwendet werden, aber die korrekte "Faltung" mit dem Entkommen komplexer Objekte in hstore kann Probleme verursachen.
json_populate_recordset
Wenn Sie im Voraus wissen, dass die Daten aus dem "Eingabe" -Json-Array eine Art Tabelle füllen, können Sie mit der Funktion "json_populate_recordset" viel beim "Dereferenzieren" der Felder und beim Umwandeln in die erforderlichen Typen sparen:
SELECT * FROM json_populate_recordset( NULL::pg_class , $1::json
json_to_recordset
Und diese Funktion erweitert einfach das übertragene Array von Objekten in die Auswahl, ohne sich auf das Tabellenformat zu verlassen:
SELECT * FROM json_to_recordset($1::json) T(k text, v integer);
TEMPORÄRE TABELLE
Wenn die Datenmenge in der übertragenen Stichprobe jedoch sehr groß ist, ist es schwierig und manchmal unmöglich, sie in einen serialisierten Parameter umzuwandeln, da eine einmalige
Zuweisung einer großen Speichermenge erforderlich ist. Beispielsweise müssen Sie lange, lange Zeit ein großes Datenpaket zu Ereignissen von einem externen System erfassen und anschließend einmal auf der Datenbankseite verarbeiten.
In diesem Fall wäre die beste Lösung,
temporäre Tabellen zu verwenden :
CREATE TEMPORARY TABLE tbl(k text, v integer); ... INSERT INTO tbl(k, v) VALUES($1, $2);
Die Methode eignet sich
für die seltene Übertragung großer Datenmengen .
Unter dem Gesichtspunkt der Beschreibung der Struktur seiner Daten unterscheidet sich die temporäre Tabelle von der "normalen" Tabelle nur um ein Merkmal
in der Systemtabelle pg_class und in
pg_type, pg_depend, pg_attribute, pg_attrdef, ... - überhaupt nichts.
In Websystemen mit einer großen Anzahl kurzlebiger Verbindungen generiert eine solche Tabelle daher jedes Mal neue Systemdatensätze, die bei geschlossener Verbindung zur Datenbank gelöscht werden. Infolgedessen führt die
unkontrollierte Verwendung von TEMP TABLE zum "Aufblähen" von Tabellen in pg_catalog und verlangsamt viele Operationen, die sie verwenden.
Dies kann natürlich mit Hilfe des
periodischen Durchlaufs VACUUM FULL durch die Systemkatalogtabellen bekämpft werden.
Sitzungsvariablen
Angenommen, die Verarbeitung von Daten aus dem vorherigen Fall ist für eine einzelne SQL-Abfrage recht kompliziert, aber Sie möchten dies häufig genug tun. Das heißt, wir möchten die prozedurale Verarbeitung im
DO-Block verwenden , aber die Datenübertragung durch temporäre Tabellen ist zu teuer.
Wir werden auch keine $ n-Parameter für die Übertragung in den anonymen Block verwenden können. Die Sitzungsvariablen und die Funktion
current_setting helfen uns, aus dieser Situation herauszukommen.
Vor Version 9.2 musste ein
custom_variable_classes- Namespace für "Ihre" Sitzungsvariablen vorkonfiguriert werden. In den aktuellen Versionen können Sie so etwas schreiben:
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;
Andere unterstützte Verfahrenssprachen können andere Lösungen finden.
Kennst du mehr Möglichkeiten? Teilen Sie in den Kommentaren!