Ściśle mówiąc, termin „procedury przechowywane” wskazuje na procedury SQL w Postgres, wprowadzone w Postgres 11. Powiązane:
Są też funkcje , które działają prawie, ale nie do końca tak samo, i były tam od samego początku.
Funkcje z LANGUAGE sqlsą w zasadzie tylko plikami wsadowymi z prostymi poleceniami SQL w opakowaniu funkcji (a zatem atomowym, zawsze uruchamianym w ramach pojedynczej transakcji) akceptującym parametry. Wszystkie instrukcje w funkcji SQL są planowane jednocześnie , co nieznacznie różni się od wykonywania jednej instrukcji po drugiej i może wpływać na kolejność wykonywania blokad.
Co więcej, najbardziej dojrzałym językiem jest PL / pgSQL ( LANGUAGE plpgsql). Działa dobrze i był ulepszany z każdym wydaniem w ciągu ostatniej dekady, ale najlepiej służy jako klej do poleceń SQL. Nie jest przeznaczony do ciężkich obliczeń (innych niż polecenia SQL).
Funkcje PL / pgSQL wykonują zapytania takie jak przygotowane instrukcje . Ponowne użycie buforowanych planów zapytań zmniejsza część kosztów związanych z planowaniem i czyni je nieco szybszymi niż równoważne instrukcje SQL, co może być zauważalnym efektem w zależności od okoliczności. Może mieć również skutki uboczne, jak w tym powiązanym pytaniu:
Niesie to zalety i wady przygotowanych wypowiedzi - jak omówiono w instrukcji . W przypadku zapytań o tabele z nieregularnym rozkładem danych i zmiennymi parametrami dynamiczny SQL z EXECUTEmoże działać lepiej, gdy zysk ze zoptymalizowanego planu wykonania dla danego parametru (parametrów) przewyższa koszt ponownego planowania.
Ponieważ ogólne plany wykonania Postgres 9.2 są nadal buforowane dla sesji, ale cytując instrukcję :
Odbywa się to natychmiast dla przygotowanych instrukcji bez parametrów; w przeciwnym razie ma to miejsce dopiero po wyprodukowaniu pięciu lub więcej wykonań planów, których szacunkowy średni koszt (w tym koszty ogólne planowania) jest droższy niż ogólny kosztorys planu.
Przez większość czasu uzyskujemy to, co najlepsze z obu światów (pomniejszone o dodatkowe koszty ogólne) bez użycia (ab) EXECUTE. Szczegóły w Co nowego w PostgreSQL 9.2 na PostgreSQL Wiki .
Postgres 12 wprowadza dodatkową zmienną serwera,plan_cache_mode aby wymusić plany ogólne lub niestandardowe. W szczególnych przypadkach należy zachować ostrożność.
Możesz wygrać duże dzięki funkcjom po stronie serwera, które zapobiegają dodatkowym objazdom do serwera bazy danych z Twojej aplikacji. Niech serwer wykona jak najwięcej zadań jednocześnie i zwróci tylko dobrze określony wynik.
Unikaj zagnieżdżania złożonych funkcji, zwłaszcza funkcji tabel ( RETURNING SETOF recordlub TABLE (...)). Funkcje to czarne skrzynki, które stanowią bariery optymalizacyjne w narzędziu do planowania zapytań. Są one optymalizowane osobno, nie w kontekście zewnętrznego zapytania, co ułatwia planowanie, ale może skutkować mniej niż doskonałymi planami. Ponadto kosztów i wielkości wyników funkcji nie można wiarygodnie przewidzieć.
Wyjątkiem od tej reguły są proste funkcje SQL ( LANGUAGE sql), które mogą być „inline” - jeśli zostaną spełnione pewne warunki . Przeczytaj więcej o tym, jak działa narzędzie do planowania zapytań w tej prezentacji autorstwa Neila Conwaya (zaawansowane rzeczy).
W PostgreSQL funkcja zawsze działa automatycznie w ramach jednej transakcji . Wszystko się udaje lub nic. Jeśli wystąpi wyjątek, wszystko jest wycofywane. Ale jest obsługa błędów ...
Dlatego też funkcje nie są dokładnie „procedurami składowanymi” (nawet jeśli termin ten jest czasem wprowadzany w błąd). Niektóre polecenia podoba VACUUM, CREATE INDEX CONCURRENTLYczy CREATE DATABASEnie można uruchomić wewnątrz bloku transakcji, więc nie są one dozwolone w funkcji. (Ani w procedurach SQL, jak na Postgres 11. To może zostać dodane później.)
Przez lata napisałem tysiące funkcji plpgsql.