Jak nepoužívat PL/pgSQL, případně PL/SQL, a další fatální chyby

Autor: Pavel Stěhule, 2018

Je zajímavé, že chyby, které se často dělají při psaní funkcí v jazyku PL/pgSQL (PostgreSQL) jsou dost podobné, ne-li totožné s chybami při používání PL/SQL (Oracle). Je to dáno i znalostmi a zkušenostmi samotných vývojářů, kteří se snaží přenést svůj styl práce z klasických programovacích jazyků do prostředí uložených procedur. V určitých případech je to ale fatální chyba, která může vést k brutální degradaci výkonu.

Tento článek vychází z mé prezentace pro druhý postgresový meetup

Popis zpracování SQL a PL/pgSQL příkazů

PL/pgSQL je úzce integrovaný s SQL enginem v PostgreSQL. Proto začnu popisem zpracování SQL. Každý dotaz, který se v PostgreSQL provádí (nikoliv každý příkaz), se parsuje a počítá se optimální exekuční plán. Exekuční plán je posloupnost operací, jejichž vykonáním získáme výsledek dotazu. Pro jeden konkrétní dotaz může existovat více variant. Použije se ta varianta, u které se předpokládá celkově nižší využití zdrojů (IO, paměť, CPU). U běžných dotazů je optimalizace relativně nenáročnou úlohou (na dnešních CPU). Výjimkou jsou extrémně rychlé dotazy, kde i krátký čas pro optimalizaci může být v celkovém času dotazu významný, případně komplikované dotazy s velkým množstvím tabulek, kde optimalizace může trvat déle než vlastní vykonání dotazu. U opakovaně vykonávaných dotazů můžeme režii na optimalizaci redukovat použitím před připravených dotazů na straně serveru (server side prepared statements). Nejedná se o nic jiného než o uložení exekučního (prováděcího) plánu do cache, z které se může opakovaně použít.

Vlastní vykonání dotazu má dvě složky - inicializaci plánu, kdy se z cache načítají informace nezbytné pro druhou složku, kterou je vlastní vykonání dotazu. Vykonávání dotazu je fakticky interpretace prováděcího plánu. Inicializace plánu je většinou zanedbatelná - vyjma situací, kdy z vnějšího plánu je opakovaně volán vnořený plán např. pro každý zpracovávaný řádek. Interpret prováděcího plánu se obvykle nazývá executor. Téměř vždy tento interpret spouští jiný interpret - interpret výrazů.

Např. pro dotaz

SELECT jmeno || prijmeni
  FROM data
 WHERE upper(prijmeni) = 'Stehule'

se pro každý řádek tabulky data aktivuje interpret výrazů pro výrazy

• upper(prijmeni) = 'Stehule'
• jmeno || prijmeni

Pokud nebudeme mít k dispozici funkcionální index, pak prováděcí plán tohoto dotazu bude jednoduchý - sekvenční čtení s filtrem. Aktuálně se ve všech případech jedná o jednoduché interprety (počínaje 9.6 je možné některé plány interpretovat paralelně) a ve verzi 11 bude možnost (v případě integrace s LLVM) aplikovat na některé interpretované části výpočtu JIT překlad. Trochu oklikou se Postgres vrací k designu z konce 70 let, kdy se prováděcí plány generovali do C, následně překládaly a vykonávaly se už jako klasický program, který mohl použít maximum paměti. Jakmile bylo k dispozici trochu více paměti, tak se od toho upustilo - kompilace trvala dlouho, a pokud bylo úzké hrdlo v IO operacích, což tehdy typicky bylo, tak kompilovaný kód neběžel extra rychleji než vykonání plánu v interpretu.

Zrekapituluji - pro vykonání dotazu jsou důležité tři časy - t1 (plánování - redukováno cache plánů), t2 (inicializace plánu - nelze redukovat, ale většinou je zanedbatelná), a t3 (vlastní vykonání dotazu).

Nyní se dostávám k PL/pgSQL. Opět se jedná o jednoduchý interpret založený na vykonávání abstrktního syntaktického stromu kódu. Interpretují se vysokoúrovňové příkazy - IF, FOR, ASSIGN, RETURN, .. a v tomto případě není absolutně žádný problém s rychlostí. Alternativou jsou interprety založené na p-code (na místě písmeně p už jsem viděl hromadu dalších písmen). p-code je podobný assambleru - a bez použití JIT může být interpretace p-code docela pomalá (vždy záleží na kontextu - dost často "pomalý kód" je pořád dost rychlý, a rychlejší exekuce by měla zanedbatelný efekt). Způsob interpretace je jeden ze zásadních rozdílů mezi PL/pgSQL (AST), a PL/SQL (který používá tzv m-code .. což variace na p-code). AST funkce zůstává v persistentní paměti připojení k databází (session). V Postgresu jsou zdrojáky funkce uložené v textové podobě, a při prvním spuštění se vytvoří parsováním (v tomto kontextu se mi příčí použít slovo kompilace) AST, který do konce spojení nebo do změny funkce zůstává v paměti.

Dalším rozdílem mezi PL/pgSQL a PL/SQL je chápání výrazů. V PL/pgSQL je každý výraz SQL výrazem - integrace s SQL enginem je absolutní. V PL/SQL je výraz výrazem PL/SQL bez vazby na SQL engine. PL/SQL může existovat bez SQL (v Oracle tehdy integrovali interpret jazyka ADA do Oracle), kdežto PL/pgSQL bez SQL engine existovat nemůže. Nebylo by jak spočítat i tak banální výrazy jako je 1+1 nebo 1<2. Každé řešení má své pro a proti. V PL/pgSQL (Postgres) je každý výraz příkazem SELECT. Vše je krásně konzistentní - vše co můžeme dělat v SELECTech, můžeme dělat i ve výrazech PL/pgSQL. To v Oracle nelze. Na druhou stranu SQL příkazy, i když jsou triviální jsou náročnější na inicializaci než od databáze izolované výrazy v Oracle. Pokud použiji PLpgSQL jako lepidla pro SQL příkazy, tak nepoznám rozdíl. Jakmile budu v PL/pgSQL počítat integrály, tak narazím. PL/SQL by mělo být několikrát rychlejší.

Je nutné si zapamatovat, že každý výraz, který napíšete v PL/pgSQL se transformuje na příkaz SELECT a o něm platí výše uvedené. Interpret PL/pgSQL zajistí opakované použití plánu, nicméně časy t2 a t3 nelze nijak redukovat (zatím, a v brzké budoucnosti). Není to tak zlé, jak to vypadá. Tzv jednoduché SELECTy (ne úplně přesně - SELECTy bez klauzule FROM) se vykonávají ve speciálním režimu, kde inicializace dotazu je zredukována na minimum (bohužel k nule to má stále dost daleko).

-- rychlost vykonání simple dotazu
do $$
declare s int default 0;
begin
  for i in 1..1000000 loop
    s := s + 1;
  end loop;
end;
$$;
DO
Time: 319,820 ms

-- castecne optimalizovany
do $$
declare s int default 0;
begin
  for i in 1..1000000 loop
    select s + 1 into s;
  end loop;
end;
$$;
DO
Time: 3724,333 ms (00:03,724)

-- full 
do $$
declare s int default 0;
begin
  for i in 1..1000000 loop
    s := (select s + 1);
  end loop;
end;
$$;
DO
Time: 5785,919 ms (00:05,786)

Chyba, která se dnes už naštěstí vidí zřídka, je používání SELECT INTO místo přiřazovacího příkazu. Při použití přiřazovacího příkazu umí interpret PL/SQL vykonávat výrazy o řád rychleji než při použítí SELECT INTO.

První fatální chyba - ISAM antipattern

Jedná se o relativně starou chybu, která se vyskytovala od prvních aplikací portovaných z COBOLu do SQL. Základem je nepochopení SQL a snaha se vyhnout za každou cenu netriviálním SQL příkazům. Místo jednoho SQL příkazu se použije cyklus a v těle cyklu triviální SQL příkaz s filtrem na primární klíč. ISAM přístup efektivně vyblokuje optimalizátor - na triviálním SQL příkazu není co optimalizovat, a co hůře - násobí čas t2 (inicializaci plánu).

create table foo(id integer unique, v integer);
insert into foo select i, 1 from generate_series(1,1000000) g(i);

-- 1M netriviálních SQL příkazů
do $$
declare s integer default 0;
begin
  for i in 1..1000000 loop -- zde může být SQL generující seznam id
    s := s +
     (select v from foo where id = i);
    end loop;
end;
$$;
DO
Time: 18017,332 ms (00:18,017)

-- 1M simple SQL + 1 netriviální
do $$
declare
s integer default 0;
_v integer;
begin
  for _v in
    select v from foo
  loop
    s := s + _v;
  end loop;
end;
$$;
DO
Time: 577,308 ms -- kod je 36x rychlejší

-- nicméně
select sum(v) from foo;
Time: 49,415 ms

Sami vidíte, co může způsobit ISAM přístup. Ukázky jsou zde zvoleny, tak aby to tlouklo do očí. Realita ovšem může být ještě horší. Inicializace plánu pro dotaz select v from foo where id = i bude trvat minimální čas. Pokud by se jednalo o komplexnější dotaz vracející desítky sloupců, spojující desítky tabulek, pak inicializace dotazu může trvat 10-100ms. Pro desítky tisíc řádků (což ale v db světě vůbec nic normálně neznamená) můžeme být na desítkách, stovkách vteřin.

S názvem této chyby jsem se setkal v literatuře k MS SQL Serveru. Tuto chybu jsem viděl v PL/SQL. Tato chyba je příslovečná pro aplikace používající ORM knihovny.

Problémem není jen čas t2 - přečíst 1M řádků sekvenčně je výrazně rychlejší než přečíst milión krát 1 řádek indexem. A to se mi veškerá data luxusně vešla do cache Postgresu (shared buffers). V okamžiku, kdy by se muselo sahat na disk a neměl bych SSD, tak by příklad s ISAM chybou mohl být o řád pomalejší. Pamatujte si, tabulka (i oindexovaná) nemá stejné přístupové charakteristiky jako pole (kde nemusí být až tak velký rozdíl (ale také může) mezi sekvenčním čtením a náhodným přístupem).

Pamatujte si, co můžete udělat jedním čitelným SQL příkazem, byste měli udělat tímto příkazem. Z každého pravidla existují výjimky. SQL příkaz nad 100 řádků nepovažuji za čitelný. Můžete se setkat s omezením optimalizátoru, špatnými odhady, a pak je téměř jediným řešením dotaz rozbít a možná použít nějakou variantu ISAM přístupu (nebo dočasnou tabulku pro mezivýsledky - pak nezapomeňte na ANALYZE).

Druhá fatální chyba - funkce ve smyslu obálek dotazu

Toto je častá chyba, na kterou můžeme narazit pouze v prostředí uložených procedur. Programátory k této chybě vede jejich přirozenost - touha i tréning vytvářet malé bloky (funkce) a z bloků pak větší celky (aplikace). V tomto případě jde ale o nepochopení, co by mělo být tou základní entitou. Tou nemůže být funkce - poněvadž funkce představují téměř neproniknutelnou bariéru pro optimalizátor (funkce v PL/pgSQL jsou absolutně neproniknutelnou bariérou). V SQL databázi je základní entitou dotaz - a pokud chceme stavět komplexnější celky, pak musíme použít pohledy. Pohled (View) může schovat složitost (navenek můžeme pracovat s komplexním pohledem jako jednoduchou tabulkou), a zároveň je optimalizovatelný. Také se zpracuje jako jeden SQL příkaz (tudíž čas t2 je tam pouze 1x - samozřejmě, že pro komlexní dotaz čas t2 bude větší než pro jednoduchý SQL příkaz - není ale násobený počtem řádků).

-- pokud možno, takové funkce nevytvářejte!!!
CREATE OR REPLACE FUNCTION public.nazev_okresu(id text)
RETURNS text
LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN (SELECT nazev
            FROM okresy
           WHERE okresy.id = nazev_okresu.id);
END;
$function$;

Tato funkce je obecně pro vyhledávání nevhodná - snažím se id převést na text, a pak dál pracovat s textem, což jsou náročnější operace - nicméně transformační funkce ve tvaru PK⇒hodnota jsou určitě velmi časté.

postgres=# EXPLAIN ANALYZE SELECT * FROM obce
                            WHERE nazev_okresu(okres_id) = 'Benešov';
                               QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on obce (cost=0.00..1699.62 rows=31 width=41)
                  (actual time=0.289..153.959 rows=114 loops=1)
   Filter: (nazev_okresu((okres_id)::text) = 'Benešov'::text)
   Rows Removed by Filter: 6136
 Planning Time: 0.165 ms
 Execution Time: 154.046 ms
(5 rows)

Použitím takovéto funkce optimalizátor nemá žádný prostor pro optimalizaci, a výsledkem je sekvenční čtení s opakovaným voláním funkce název_okresu (pro každou obec). Uvnitř této funkce se provádí dotaz nad tabulkou okresy. Je to ta nejhorší varianta, která může být - fakticky je to nested loop + 6164 * t2.

Funkce v jazyku SQL je o něco rychlejší, ale pořád to není žádná sláva:

CREATE OR REPLACE FUNCTION public.nazev_okresu(id text)
RETURNS text
LANGUAGE sql
AS $function$
  SELECT nazev FROM okresy WHERE okresy.id = nazev_okresu.id
$function$;

postgres=> EXPLAIN ANALYZE SELECT * FROM obce
                            WHERE nazev_okresu(okres_id) = 'Benešov';
                           QUERY PLAN                                               
-------------------------------------------------------------------
 Seq Scan on obce  (cost=0.00..1698.62 rows=31 width=41)
                   (actual time=0.249..108.037 rows=114 loops=1)
   Filter: (nazev_okresu((okres_id)::text) = 'Benešov'::text)
   Rows Removed by Filter: 6136
 Planning time: 0.075 ms
 Execution time: 109.016 ms
(5 rows)

Dotaz s JOINem je výrazně rychlejší

postgres=> EXPLAIN ANALYZE SELECT ob.*, ok.nazev AS nazev_okresu
                             FROM obce ob
                             JOIN okresy ok ON ob.okres_id = ok.id
                            WHERE ok.nazev = 'Benešov';
                                        QUERY PLAN                                                              
---------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.28..15.11 rows=81 width=51)
              (actual time=0.196..6.661 rows=114 loops=1)
   ->  Seq Scan on okresy ok  (cost=0.00..1.96 rows=1 width=17)
                              (actual time=0.051..0.067 rows=1 loops=1)
         Filter: (nazev = 'Benešov'::text)
         Rows Removed by Filter: 76
   ->  Index Scan using obce_okres_id_idx on obce ob  (cost=0.28..12.34 rows=81 width=41)
                                                      (actual time=0.084..3.959 rows=114 loops=1)
         Index Cond: ((okres_id)::text = ok.id)
 Planning time: 1.181 ms
 Execution time: 7.763 ms
(8 rows)

Co je tedy správným řešením, když chci zakrýt složitost dotazů? Tím správným řešením jsou pohledy.

CREATE VIEW obce_okresy AS
   SELECT ob.*, ok.nazev AS nazev_okresu
     FROM obce ob
     JOIN okresy ok ON ob.okres_id = ok.id;

postgres=> EXPLAIN ANALYZE SELECT * FROM obce_okresy WHERE nazev_okresu = 'Benešov';
                                         QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.28..15.11 rows=81 width=51)
              (actual time=0.093..3.000 rows=114 loops=1)
   ->  Seq Scan on okresy ok  (cost=0.00..1.96 rows=1 width=17)
                              (actual time=0.025..0.050 rows=1 loops=1)
         Filter: (nazev = 'Benešov'::text)
         Rows Removed by Filter: 76
   ->  Index Scan using obce_okres_id_idx on obce ob  (cost=0.28..12.34 rows=81 width=41)
                                                      (actual time=0.038..1.189 rows=114 loops=1)
         Index Cond: ((okres_id)::text = ok.id)
 Planning time: 0.903 ms
 Execution time: 3.975 ms
(8 rows)

Jak vidíte, ze z cca 150ms jsem se dostal na cca 3ms -- tj s použitím správného nástroje jsem docílil cíle (pracuji s jednoduchým dotazem) a výsledek je cca 50x rychlejší. Co je důvodem tohoto zrychlení? Jednak má SQL optimalizátor větší prostor pro optimalizaci a může najít optimální řešení - v tomto případě index scan, druhak nedochází k opakovanému volání dotazu a tedy k výrazné redukci času t2.

Když už takové funkce máte, pak workaroundem je funkcionální index. Je to ale další index jehož údržba něco stojí (na statických tabulkách bych to ale neřešil). Tady jsem ale v pasti - pro funkcionální indexy potřebuji immutable funkce - a funkce nazev_okresu rozhodně není immutable. Sice dokáži přetlačit Postgres, ale v ten moment tu mám riziko, že někdo změní obsah tabulky okresy, a pokud nereindexuji zmíněný funkční index, tak index bude nekonzistentní, načež mohu dostávat špatné výsledky. Takže na to pozor! V tomto případě funkční index nad znásilněnou immutable funkcí může být dobrá past.

Zkusme opačnou transformaci.

CREATE OR REPLACE FUNCTION id_okresu(nazev text)
RETURNS text AS $$
BEGIN
  RETURN (SELECT id FROM okresy WHERE okresy.nazev = id_okresu.nazev);
END;
$$ LANGUAGE plpgsql;

V tomto případě si ji mohu dovolit - název okresu je také unikátní. Ve výpisu prováděcího plánu nevidím žádnou změnu:

postgres=> EXPLAIN ANALYZE SELECT * FROM obce WHERE okres_id = id_okresu('Praha');
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Seq Scan on obce  (cost=0.00..1698.62 rows=81 width=41) (actual time=0.860..160.338 rows=1 loops=1)
   Filter: ((okres_id)::text = id_okresu('Praha'::text))
   Rows Removed by Filter: 6249
 Planning time: 0.227 ms
 Execution time: 160.606 ms
(5 rows)

Takže jsem si nepomohl. Nicméně vidím tady prostor pro zlepšení - funkce v PostgreSQL jsou defaultně tzv volatilní, tj optimalizátor by je měl volat pro každou hodnotu, a nesmí se snažit redukovat počet volání. V tomto případě, pokud nemodifikujeme data, tak tuto funkci můžeme označit jako STABLE, tj v rámci jednoho dotazu, pro jeden parametr bude vracet vždy stejný výsledek.

CREATE OR REPLACE FUNCTION id_okresu(nazev text)
RETURNS text AS $$
BEGIN
  RETURN (SELECT id FROM okresy WHERE okresy.nazev = id_okresu.nazev);
END;
$$ LANGUAGE plpgsql STABLE;

postgres=> EXPLAIN ANALYZE SELECT * FROM obce WHERE okres_id = id_okresu('Praha');
                                     QUERY PLAN                                                         
---------------------------------------------------------------------------------------
 Index Scan using obce_okres_id_idx on obce  (cost=0.53..10.61 rows=35 width=41)
                                             (actual time=0.228..0.235 rows=1 loops=1)
   Index Cond: ((okres_id)::text = id_okresu('Praha'::text))
 Planning time: 0.599 ms
 Execution time: 0.308 ms
(4 rows)

Vidím, že index se použil, a dotaz je velice rychlý. Příznakem STABLE jsem umožnil práci optimalizátoru, který dovnitř funkcí v PL/pgSQL nevidí a tudíž musí předpokládat tu nejhorší variantu, a vždy volat funkci, aby nedošlo k špatnému výsledku.

Zde si dovolím malou poznámku - v ostatních databázích se s funkcemi zachází jako kdyby byly defaultně stabilní. Při portaci do Postgresu a nepřeznačení funkcí musí samozřejmě docházet k problémům s výkonem - a to proto, že v Postgresu výchozí úroveň je VOLATILE. Ora2pg při transformaci funkcí v PL/SQL automaticky nastavuje STABLE.

Horší by bylo, kdyby transofmace text⇒PK nebyla jednoznačná, tam už mi označení funkce STABLE nepomůže. V ten moment musí být tato (transformační) funkce typu SETOF a optimalizátor nedokáže efektivně využít konstanty.

V Postgresu se používá následující trik - místo SETOF funkce (vrací více hodnot) použiji funkci, která vrátí pole:

CREATE OR REPLACE FUNCTION id_okresu3(nazev text)
RETURNS text[] AS $$
BEGIN
  RETURN (SELECT array_agg(id) FROM okresy WHERE okresy.nazev = id_okresu3.nazev);
END;
$$ LANGUAGE plpgsql STABLE;

postgres=> EXPLAIN ANALYZE SELECT * FROM obce WHERE okres_id = any (id_okresu3('Benešov'));
                                    QUERY PLAN                                                          
----------------------------------------------------------------------------------------------
 Index Scan using obce_okres_id_idx on obce  (cost=0.53..45.23 rows=114 width=41)
                                             (actual time=0.312..1.026 rows=114 loops=1)
   Index Cond: ((okres_id)::text = ANY (id_okresu3('Benešov'::text)))
 Planning time: 1.348 ms
 Execution time: 1.668 ms
(4 rows)

Dotaz s filtrem na pole je efektivní.

Nicméně takovéto znásilňování relačního přístupu bych nemusel dělat, kdybych použil rovnou pohled (a vyvaroval se používání funkcí, které zakrývají SQL). Když už něco takového musíte udělat, tak nezapomínejte na STABLE (samozřejmě pokud platí pravidla pro použití STABLE funkcí).

Pozor na typy proměnných používaných pro filtry

Špatně viditelnou chybou (s fatálním vlivem na výkon) je rozdílnost typů atributů tabulky, a proměnné použité ve filtru v PL/pgSQL. Parametr v dotazu přebírá typ použité proměnné. Pokud není shoda mezi typem atributu a typem parametru, tak se nepoužije index a dotaz jde do sekvenčního čtení. Výjimkou jsou konverze int, bigint a text, varchar.

postgres=> EXPLAIN SELECT * FROM film WHERE film_id = 10022::bigint;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Index Scan using film_pkey on film  (cost=0.28..8.29 rows=1 width=386)
   Index Cond: (film_id = '10022'::bigint)
(2 rows)

postgres=> EXPLAIN SELECT * FROM film WHERE film_id = 10022::numeric;
                      QUERY PLAN                       
-------------------------------------------------------
 Seq Scan on film  (cost=0.00..69.00 rows=5 width=386)
   Filter: ((film_id)::numeric = '10022'::numeric)
(2 rows)

Tuto chybu jsem viděl zanesenou při konverzi z Oracle. Datový typ number v Oraclu může být intem, bigintem, numericem v PostgreSQL. V definici tabulky vývojáři zvolili jako substituci bigint. V kódu funkce pak pro odpovídající proměnnou numeric. Tím efektivně zablokovali použití indexu. Řešením je použití tzv odvozených typů (řešení ve stylu Oracle) nebo použití doménových typů (ANSI SQL, Postgres).

CREATE TABLE foo(id bigint, ....)

DECLARE
  _id foo.id%type; -- typ zkopírovaný ze sloupce id tabulky foo

Další doporučení s ohledem na výkon

• Zbytečně nepoužívejte dočasné tabulky - dočasné tabulky jsou pro pár řádků v PostgreSQL skutečně drahé. Dočasná tabulka je, co se týče systémového katalogu, normální tabulkou - a vytvoření, zrušení tabulky jsou poměrně náročné operace - které navíc vedou k bobtnání systémových tabulek, a tím i k dalšímu zpomalení. Dočasnou tabulku má smysl použít pro tisíce řádků. Nad dočasnou tabulkou si můžeme vytvořit index. Dočasná tabulka by měla mít statistiky (nezapomínejte na příkaz ANALYZE, autovacuum ignoruje dočasné tabulky, navíc při krátké životnosti by je ani nestihlo analyzovat). Místo dočasných tabulek v Postgres používejte pole. Na pole není navázán žádný záznam v katalogu, a tudíž jsou vůči dočasným tabulkám fakticky bez další režie.
• Zbytečně nezachytávejte výjimky - pokud možno ne v cyklu. PL/pgSQL má téměř totožnou syntax jako PL/SQL pro zachytávání výjimek, což svádí k tomu je používat stejně. Implementace je ale úplně jiná. Výjimky v PL/SQL jsou záležitostí čistě interpretu PL/SQL. Výjimky v PostgreSQL jsou spojené s databázovým enginem - použitím EXCEPTION WHEN se na začátek bloku vloží SAVEPOINT, a po zachycení výjimky se automaticky provede ROLLBACK k tomuto savepointu. Jak SAVEPOINT, tak ošetření ROLLBACKEM jsou docela drahé operace na to aby se používaly uvnitř cyklu.
• Dnešní databáze zvládnou opravdu hodně - na silnějších strojích i desítky tisíc transakcí za vteřinu. Pokud máte problémy s výkonem, snažte se zjistit v kterém příkazu, v které operaci. Dost často se jedná o hlouposti, které lze relativně jednoduše opravit.

Praktické aplikace pro vývoj v PL/pgSQL

• Extenze plpgsql_check dokáže detekovat překlepy (odkazy na neexistující SQL objekty a případně jejich atributy). Dokáže detekovat i skryté konverze datových typů, které zpomalují běh funkce.
• Extenze auto_explain umožňuje zalogovat prováděcí plány dotazů bežících déle než zadaný časový limit. S volbou log_nested_statements umí zalogovat i dotazy volané uvnitř funkce (což samotný Postgres, potažmo logování pomalých dotazů log_min_duration_statement neumí).
• PL/pgSQL profiler - plprofiler je velice komfortní aplikace zobrazující pro každý řádek funkce v PL/pgSQL počet průchodů, celkový čas, nejdelší čas, atd.
• Trasování PL funkcí (za PL funkce se označují zákaznické funkce v Postgresu) - poskytne informaci, kolikrát se daná funkce volala, a jak dlouho toto volání trvalo:

  postgres=# SET track_functions TO 'pl';
  SET
  postgres=# EXPLAIN ANALYZE SELECT * FROM obce WHERE nazev_okresu(okres_id) = 'Benešov';
                                 QUERY PLAN                        
  --------------------------------------------------------------------
   Seq Scan on obce  (cost=0.00..1698.62 rows=31 width=41)
                     (actual time=1.053..225.603 rows=114 loops=1)
     Filter: (nazev_okresu((okres_id)::text) = 'Benešov'::text)
     Rows Removed by Filter: 6136
   Planning time: 2.737 ms
   Execution time: 232.263 ms
  (5 rows)
  
  postgres=# select * from pg_stat_user_functions ;
   funcid | schemaname |   funcname   | calls | total_time | self_time 
  --------+------------+--------------+-------+------------+-----------
    16952 | public     | nazev_okresu |  6250 |    192.658 |   192.658
  (1 row)
  

Vývoj přímo nad databází - deployment založený na reverze engineeringu

Téměř všude vidím, že se uložené procedury píší v PgAdminu a podobných nástrojích, což vlastně není až tak důležité. Problém je, že zdrojové kódy funkcí jsou uložené přímo v databázi:

• databáze, PostgreSQL není výjimkou, neumí verzovat kód. Nejsou verze, nejsou větvě, není historie. Databáze obsahuje produkční, rozpracovaný i opuštěný kód. Vše se distribuuje uživatelům.
• V PostgreSQL ztrácíte vnější komentáře.

Silně doporučuji přistupovat ke kódu v PL/pgSQL jako k jakémukoliv jinému kódu a ukládat jej externě v souborech a použít libovolný systém pro správu verzí: GIT, ... Deployment by měl být pokud možno automatizovaný bez nutnosti ruční práce administrátora. Pokud máte automatizovanou instalaci, tak snadno vytvoříte a snad udržíte testovací prostředí, a těchto testovacích prostředí můžete mít, kolik chcete - nejlépe každý vývojář své vlastní, plus integrační, plus předprodukční, atd. Ruční deployment jakoukoliv automatizaci vylučuje.

Závěr

Pokud se budete držet výše zmíněných doporučení, tak si myslím, že s PL/pgSQL nebudete mít jediný problém. PL/pgSQL je jednoduchý nezáludný jazyk - ale vývojář si musí uvědomit a akceptovat, že pracuje s SQL databází, a že jazyk pro manipulaci s daty v db je SQL, který má svá pravidla, která je dobré znát a respektovat. To ale platí pro každý programovací jazyk a každé prostředí.