ProHoster > Blog > administracja > Antywzorce PostgreSQL: przekazywanie zestawów i selekcji do SQL

Antywzorce PostgreSQL: przekazywanie zestawów i selekcji do SQL

Od czasu do czasu deweloper potrzebuje przekazać zestaw parametrów lub nawet cały wybór do żądania "przy wejściu". Czasami istnieją bardzo dziwne rozwiązania tego problemu.
Antywzorce PostgreSQL: przekazywanie zestawów i selekcji do SQL
Przejdźmy „od przeciwnej strony” i zobaczmy, jak tego nie robić, dlaczego i jak można to robić lepiej.

Bezpośrednie „wstawianie” wartości w treści żądania

Zwykle wygląda to mniej więcej tak:

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

... lub tak:

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

O tej metodzie mówi się, pisze i nawet narysowany wystarczająco:

Antywzorce PostgreSQL: przekazywanie zestawów i selekcji do SQL

Prawie zawsze tak jest bezpośrednia ścieżka do iniekcji SQL i dodatkowe obciążenie logiki biznesowej, która jest zmuszona „skleić” ciąg zapytania.

Takie podejście może być częściowo uzasadnione tylko w razie potrzeby. użyj partycjonowania w PostgreSQL w wersji 10 i niższych, aby uzyskać bardziej wydajny plan. W tych wersjach lista przeskanowanych odcinków ustalana jest bez uwzględnienia przesyłanych parametrów, tylko na podstawie treści zapytania.

$n argumentów

Używać symbole zastępcze parametry jest dobre, pozwala na użytkowanie PRZYGOTOWANE OŚWIADCZENIA, zmniejszając obciążenie zarówno logiki biznesowej (ciąg zapytania jest tworzony i przesyłany tylko raz), jak i serwera bazy danych (nie jest wymagana ponowna analiza i planowanie dla każdej instancji żądania).

Zmienna liczba argumentów

Problemy będą nas czekać, gdy będziemy chcieli z góry przekazać nieznaną liczbę argumentów:

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

Jeśli zostawisz wniosek w tej formie, to chociaż uchroni nas to przed potencjalnymi zastrzykami, to i tak będzie prowadzić do konieczności sklejenia/przeanalizowania wniosku dla każdej opcji z liczby argumentów. Już lepiej niż robienie tego za każdym razem, ale możesz 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 żądany typ tablicy. Ale to nie powoduje problemów, ponieważ już z góry wiemy, dokąd się zwracamy.

Transfer próbki (matryca)

Zwykle są to różnego rodzaju opcje przekazywania zestawów danych do wstawienia do bazy danych „w jednym żądaniu”:

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

Oprócz opisanych powyżej problemów z „ponownym sklejeniem” wniosku, może to również doprowadzić nas do tego brak pamięci i awaria serwera. Powód jest prosty - PG rezerwuje dodatkową pamięć na argumenty, a ilość rekordów w zestawie jest ograniczona jedynie przez aplikację logiki biznesowej Lista życzeń. W szczególnie klinicznych przypadkach konieczne było widzenie „numerowane” argumenty większe niż 9000 USD - nie rób tego w ten sposób.

Przepiszmy zapytanie, stosując już 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 „złożonych” wartości wewnątrz tablicy należy je ująć w cudzysłowy.
Oczywiste jest, że w ten sposób można „rozszerzyć” wybór o dowolną liczbę pól.

nie zagnieździć, nie zagnieździć,…

Od czasu do czasu pojawiają się opcje przekazywania zamiast „tablicy tablic” kilku „tablic kolumn”, o których wspomniałem w ostatnim artykule:

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 uzyskać całkowicie nieoczekiwane wyniki, które również zależą 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

Począwszy od wersji 9.3, PostgreSQL posiada pełnoprawne funkcje do pracy z typem json. Dlatego, jeśli twoje parametry wejściowe są zdefiniowane w przeglądarce, możesz od razu tam i formularz 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 „zwijanie” z ucieczką złożonych obiektów w hstore może powodować problemy.

json_populate_recordset

Jeśli wiesz z góry, że dane z tablicy „input” json trafią do wypełnienia jakiejś tabeli, możesz sporo zaoszczędzić na „dereferencjach” pól i rzutowaniu na pożądane typy za pomocą 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

A ta funkcja 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 | 2

TABELA TYMCZASOWA

Jeśli jednak ilość danych w przesłanej próbce jest bardzo duża, to wrzucenie jej do jednego serializowanego parametru jest trudne, a czasem wręcz niemożliwe, gdyż wymaga jednorazowego duża alokacja pamięci. Na przykład musisz zbierać dużą partię danych o zdarzeniach z systemu zewnętrznego przez długi, długi czas, a następnie chcesz je przetworzyć jednorazowo po stronie bazy danych.

W takim przypadku najlepszym rozwiązaniem byłoby użycie tabele tymczasowe:

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

Metoda jest dobra do rzadkiej transmisji dużych ilości dane.
Z punktu widzenia opisu struktury danych tabela tymczasowa różni się od „zwykłej” tabeli tylko jedną cechą. w tabeli systemowej pg_classa w pg_type, pg_depend, pg_attribute, pg_attrdef, ... — i zupełnie nic.

Dlatego w systemach webowych z dużą liczbą krótkotrwałych połączeń dla każdego z nich taka tabela będzie generować każdorazowo nowe rekordy systemowe, które są usuwane po zamknięciu połączenia z bazą danych. W końcu, niekontrolowane użycie TEMP TABLE prowadzi do "puchnięcia" tabel w pg_catalog i spowalniają wiele operacji, które ich używają.
Oczywiście można temu przeciwdziałać przepustka okresowa VACUUM FULL zgodnie z tabelami katalogowymi systemu.

Zmienne sesyjne

Załóżmy, że przetwarzanie danych z poprzedniego przypadku jest dość skomplikowane dla pojedynczego zapytania SQL, ale chcesz to robić dość często. Oznacza to, że chcemy użyć przetwarzania proceduralnego w ZABLOKUJ, ale przesyłanie danych przez tabele tymczasowe będzie zbyt kosztowne.

Nie możemy również użyć $n-parameters do przekazania do anonimowego bloku. Zmienne sesyjne i funkcja pomogą nam wyjść z sytuacji. obecne ustawienie.

Przed wersją 9.2 trzeba było wstępnie skonfigurować specjalna przestrzeń nazw niestandardowe_klasy_zmiennych dla „ich” zmiennych sesyjnych. W aktualnych wersjach możesz 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 : 3

Istnieją inne rozwiązania dostępne w innych obsługiwanych językach proceduralnych.

Znasz więcej sposobów? Podziel się w komentarzach!

Źródło: www.habr.com

Dodaj komentarz