Od czasu do czasu deweloper ma potrzebę przekazać zestaw parametrów lub nawet cały wybór do żądania "przy wejściu". Czasami można znaleźć bardzo dziwne rozwiązania tego problemu.
Podejdźmy do tego „od drugiej strony” i zobaczmy, czego nie należy robić, dlaczego i jak można to zrobić lepiej.
Bezpośrednie wstawianie wartości do treści żądania
Zazwyczaj wygląda to mniej więcej tak:
query = "SELECT * FROM tbl WHERE id = " + value… albo tak:
query = "SELECT * FROM tbl WHERE id = :param".format(param=value)O tej metodzie już mówiono, pisano i wystarczająco:
Prawie zawsze tak jest - bezpośrednia ścieżka do wstrzyknięć SQL i dodatkowe obciążenie dla logiki biznesowej, która zmuszona jest „skleić” ciąg Twojego żądania.
Jeśli zajdzie taka konieczność, takie podejście można uzasadnić tylko częściowo. wykorzystanie partycjonowania w PostgreSQL w wersji 10 i starszych, aby uzyskać bardziej wydajny plan. W tych wersjach lista sekcji do przeskanowania jest ustalana bez uwzględniania przekazywanych parametrów, a jedynie na podstawie treści żądania.
$n-argumentów
Używać parametry - to jest dobre, pozwala na użycie , zmniejszając obciążenie zarówno logiki biznesowej (ciąg zapytania jest generowany i przesyłany tylko raz), jak i serwera bazy danych (nie jest wymagane ponowne analizowanie i planowanie dla każdego wystąpienia zapytania).
Zmienna liczba argumentów
Problemy mogą się pojawić, gdy będziemy chcieli przekazać nieznaną liczbę argumentów:
... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...Jeśli pozostawimy żądanie w tej formie, ochroni nas to przed potencjalnymi wstrzyknięciami, ale nadal będzie wymagało sklejenia/przeanalizowania żądania dla każdej opcji z liczby argumentów. To lepsze niż robienie tego za każdym razem, ale można się bez tego obejść.
Wystarczy przekazać tylko jeden parametr zawierający serializowana reprezentacja tablicy:
... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'Jedyną różnicą jest konieczność jawnej konwersji argumentu na pożądany typ tablicy. Ale to nie stanowi żadnego problemu, ponieważ z góry wiemy, dokąd zmierzamy.
Przeniesienie próbki (matrycy)
Zazwyczaj są to wszelkiego rodzaju opcje przesyłania zestawów danych w celu ich umieszczenia w bazie danych „w jednym żądaniu”:
INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...Oprócz opisanych powyżej problemów z „ponownym wklejaniem” żądania, może to również doprowadzić do brak pamięci i awaria serwera. Powód jest prosty: PG rezerwuje dodatkową pamięć na argumenty, a liczba rekordów w zestawie jest ograniczona jedynie przez wymagania logiki biznesowej. W szczególnie klinicznych przypadkach musiałem zobaczyć „ponumerowane” argumenty powyżej 9000 dolarów - Nie rób tego.
Przepiszmy zapytanie, używając już zastosowanego serializacja „dwupoziomowa”:
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;
Tak, w przypadku wartości „złożonych” wewnątrz tablicy, muszą one być ujęte w cudzysłów.
Oczywiste jest, że w ten sposób można „rozszerzyć” próbę o dowolną liczbę pól.
gniazdo, gniazdo, …
Czasami istnieją opcje umożliwiające przekazanie kilku „tablic kolumn” zamiast „tablicy tablic”, o której wspomniałem. :
SELECT
unnest($1::text[]) k
, unnest($2::integer[]) v;Dzięki tej metodzie, jeśli popełnisz błąd podczas generowania list wartości dla różnych kolumn, bardzo łatwo jest całkowicie stracić nieoczekiwane wyniki, które zależą również od wersji serwera:
-- $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
Od wersji 9.3 PostgreSQL posiada pełnoprawne funkcje do pracy z typem json. Dlatego jeśli zdefiniujesz parametry wejściowe w przeglądarce, możesz je od razu tam wygenerować. obiekt json dla zapytania sql:
SELECT
key k
, value v
FROM
json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'W przypadku poprzednich wersji można zastosować tę samą metodę każdy(hstore), ale prawidłowe „składanie” z uciekaniem złożonych obiektów w hstore może powodować problemy.
json_populate_zestaw_rekordów
Jeśli wiesz z góry, że dane z tablicy JSON „wejściowej” zostaną użyte do wypełnienia pewnej tabeli, możesz sporo zaoszczędzić na „dereferencjonowaniu” pól i rzutowaniu na wymagane typy, używając funkcji 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_do_zestawu_rekordów
Funkcja ta po prostu „rozwinie” przekazaną tablicę obiektów do zaznaczenia, bez polegania na formacie tabeli:
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 | 2TABELA TYMCZASOWA
Jeśli jednak objętość danych w przesłanej próbce jest bardzo duża, wówczas wrzucenie jej do jednego parametru serializowanego jest trudne, a czasami niemożliwe, ponieważ wymaga jednorazowego duże przydziały pamięci. Na przykład, musisz zebrać dużą ilość danych dotyczących zdarzeń z systemu zewnętrznego przez długi czas, a potem chcesz ją jednorazowo przetworzyć po stronie bazy danych.
W tym przypadku najlepszym rozwiązaniem będzie użycie :
CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком
Metoda jest dobra dokładnie do rzadkiego przesyłania dużych ilości dane.
Z punktu widzenia opisu struktury danych tabela tymczasowa różni się od tabeli „zwykłej” tylko jednym: w tabeli systemowej pg_classa w pg_type, pg_depend, pg_attribute, pg_attrdef, … - nic zupełnie.
Dlatego w systemach internetowych posiadających dużą liczbę krótkotrwałych połączeń, dla każdego z nich taka tabela będzie za każdym razem generować nowe rekordy systemowe, które zostaną usunięte w momencie zamknięcia połączenia z bazą danych. W rezultacie, niekontrolowane użycie TEMP TABLE prowadzi do „rozdęcia” tabel w pg_catalog i spowalniając wiele operacji z nich korzystających.
Oczywiście, można temu zaradzić za pomocą okresowy przejazd VACUUM FULL zgodnie z tabelami katalogu systemowego.
Zmienne sesji
Załóżmy, że przetwarzanie danych z poprzedniego przypadku jest na tyle złożone, że można je wykonać za pomocą jednego zapytania SQL, ale chcemy to robić dość często. Oznacza to, że chcemy wykorzystać przetwarzanie proceduralne w , ale korzystanie z transferu danych za pomocą tabel tymczasowych będzie zbyt kosztowne.
Nie będziemy mogli również użyć $n parametrów do przekazania do anonimowego bloku. Zmienne sesji i funkcja pomogą nam wyjść z tej sytuacji. bieżące_ustawienie.
Przed wersją 9.2 konieczne było przeprowadzenie wstępnej konfiguracji niestandardowe_klasy_zmiennych dla „Twoich” zmiennych sesji. W obecnych wersjach można napisać coś takiego:
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 : 3Inne rozwiązania można znaleźć w innych obsługiwanych językach proceduralnych.
Czy znasz jakieś inne sposoby? Podziel się swoją opinią w komentarzach!
Źródło: www.habr.com
