Procedury składowane
Jak to działa? W skrócie mówiąc chodzi o to by niektóre operację na bazie dokonywać poprzez wywołanie funkcji, a nie listę kolejnych zapytań SQL w naszej aplikacji.
Jakie są plusy tego rozwiązania?
- implementacja logiki biznesowej - nie trzeba tej samej funkcjonalności implementować/poprawiać gdy mamy kilka aplikacji klienckich, podobna sprawa ma się przy zmianie środowiska programistycznego, mamy mniej do zaimplementowania od nowa
- wydajność - wszystko odbywa się po stronie serwera, już na samym czasie wymiany danych między bazą, a aplikacją zaoszczędzamy
- przejrzystość - wywołanie jednej procedury w aplikacji zamiast kilku zapytań SQL
W PostgreSQL mamy możliwość skorzystania z kilku języków proceduralnych: PL/pgSQL, PL/TCL, PL/Perl, PL/Python, ale na naszych hostingach administracja udostępnia nam zazwyczaj PL/pgSQL.
Na początku utwórzmy bardzo prostą funkcję, która co tydzień będzie miała za zadanie wskazywać określoną ilość najbardziej aktywnych użytkowników na forum. Poniższe przykłady działają dla wersji >= 8.0. Dla wersji 7.4 trzeba je lekko zmodyfikować, np. zamiast $$ użyć apostrofu '.
Niestety powyższy przykład jest niewydajny (dla każdego rekordu jest wykonywane zapytanie UPDATE), sam kilka razy przekombinowałem z procedurami składowanymi i były tego odwrotne skutki niż zamierzone. Poniżej poprawiona wersja.
Kolejny przykład pokazuje wykorzystanie SETOF. W ten sposób do utworzonej funkcji będziemy się odwoływać jak do tabeli.
Przy usuwaniu funkcji podajmy poza nazwą także argumenty.
Procedura składowana nieodzownie łączy się z tematem wyzwalaczy (triggers) i w sumie głównie tam je wykorzystujemy, ale o tym już w następnym wpisie.
Słowa kluczowe: PostgreSQL, Techblog, bazy danych, funkcje, plpgsql, procedury, sql, artykuł, blog
Komentarze i opinie
Słodziutko napisane, przyda sie troche przykłądowego kodu w zabawie z potgre
Wszystko pięknie, ale:
"implementacja logiki biznesowej - nie trzeba tej samej funkcjonalności implementować/poprawiać gdy mamy kilka aplikacji klienckich, podobna sprawa ma się przy zmianie środowiska programistycznego, mamy mniej do zaimplementowania od nowa"
TO JEST AKURAT WADA.
Logika biznesowa zostaje rozparcelowana pomiędzy aplikacje i DBMS. Nie daje się tego porządnie wersjonować, ba implementacja złożonej logiki biznesowej w czymś takim jak PL/pgSQL czy C jest nonsensem.
"wydajność - wszystko odbywa się po stronie serwera, już na samym czasie wymiany danych między bazą, a aplikacją zaoszczędzamy"
Na czasie wymiany danych między serwerem aplikacyjnym a bazą oszczędzamy ułamki milisekund, a marnujemy sekundy/minuty/godziny na nieoptymalne plany wykonania zapytań
dobrane w pierwszej kompilacji procedury. Nie mówiąc już o dużo gorszej skalowalności tego podejścia.
"przejrzystość - wywołanie jednej procedury w aplikacji zamiast kilku zapytań SQL" - jak chcę miec przejrzystość, to piszę aplikację przejrzyście. I wszystkie SQLe też mam w jednym miejscu. Nic nie stoi na przeszkodzie zrobienia warstwy abstrakcji dostępu do danych po stronie aplikacji.
hackit - niestety ale to Ty się mylisz:
1. wprowadzenie dodatkowej warstwy to ZAWSZE zaleta a nie wada.
2. Jakie "ułamki milisekund" ??!! Spróbuj sobie wyciągać dane z bazy po sieci kilkoma Twoimi ulubionymi zapytaniami SQL by potem coś z nimi zrobić a wynik zapisać do bazy - gwarantuje Ci, że NA PEWNO nie będą to ułamki milisekund.
3. Dlaczego uważasz ze procedury są nieskalowalne ? Wg mnie są tak skalowalne jak skalowalna jest baza. Można napisać nieoptymalną procedurę składowaną dokładnie tak samo jak i zapytanie SQL.
Generalnie: łatwo się rzuca zarzuty bez pokrycia - a ty robisz to notorycznie: piszesz np. "Nie daje się tego porządnie wersjonować, ba implementacja złożonej logiki biznesowej w czymś takim jak PL/pgSQL czy C jest nonsensem" - oczywiście "zapominasz" poparcia tych tez faktami. Akurat pracuje w firmie, gdzie przyjęliśmy implementacje złożonej logiki biznesowej w języku proceduralnym - i po paru latach uważam/uważamy to za bardzo dobre rozwiązanie, które się SPRAWDZIŁO W PRAKTYCE.
Warto znać się na tym, o o czym się pisze, mieć jakieś doświadczenie - nie wprowadzaj innych w błąd.
Witam.
koval - dzięki za post, uważam, że najważniejszą z przedstawionych przez Ciebie zalet jest szybkość wykonania zapytania. Jeśli hackit zarzucił Ci oszczędzanie ułamków milisekund, to powinien sprawdzić czas zapytania korzystając z modemu GSM - wtedy na pewno zauważy różnicę. Jeśli chodzi o implementację logiki biznesowej, to w/g mojej skromnej wiedzy - na dwoje babka wróżyła - wszystko zależy od złożoności logiki biznesowej.
Pozdrawiam i życzę większej częstotliwości pisania postów (ostatnie 3posty w 3 lata). To jak rozmowa z cywilizacją umiejscowioną daleko spoza Układu Słonecznego ;-)
Nowy komentarz