Zobrazení zdroje stránky Automatické kešování prováděcích plánů v PL/pgSQL

[[category:Články]]
Poznámka: <b>Ve verzi 9.2 došlo k zásadním změnám v implementaci cache prováděcích plánů - a některé zde popisované problémy byly odstraněny.</b> Tento článek byl napsán vůči verzi 8.3.

Když jsem před několika lety psal první článek o PL/pgSQL, narazil jsem v dokumentaci na doporučení nepoužívat řetězec 'now' v tomto jazyce. Doporučení jsem akceptoval, vyzkoušel si, že to skutečně nefunguje, ale po pravdě řečeno moc jsem nechápal proč. Nejasně jsem si představoval, že se chybně přeloží do mezikódu (byte-code) i tato aktuální hodnota této pseudofunkce, což způsobí problém, na který upozorňovala dokumentace (čas vrácený řetězcem 'now' odpovídal překladu funkce).
<pre>
INSERT INTO (..) VALUES('now') -- nepouzivat v plpgsql!
</pre>
Zde poprvé odbočím. Lexikální a syntaktická analýza PL/pgSQL funkcí se provádí pouze jednou, při prvním zavolání funkce v rámci přihlášení. Jejím výsledkem je syntaktický strom uložený v session cache. Něco jako překlad do mezikódu v PL/pgSQL neexistuje, syntaktický strom je vstupem pro interpret, tj. PL/pgSQL je klasický interpret postavený na lex, yacc generátoru. Síla PL/pgSQL není v rychlosti, ale v jeho vazbě s SQL. Díky této vazbě může být implementace PL/pgSQL docela jednoduchá, až tak, že neobsahuje ani jednoduché vyhodnocení výrazů. Vše, co lze, se předhodí interpretu SQL. Interpret PL/pgSQL řeší jen proměnné a řídící konstrukce.

Pro uložení SQL výrazu v PL/pgSQL se používá typ PLpgSQL_expr (plpgsql.h). Pro další popis jsou významná pouze pole char *query (obsahuje text sql příkazu) a void *plan (ukazatel na kešovaný prováděcí plán SQL příkazu). Prováděcí plán se generuje pouze jednou, při prvním požadavku na vyhodnocení SQL výrazu.
<pre>
if (expr->plan == NULL) 
  exec_prepare_plan(estate, expr) 
.... 
rc = SPI_execute_plan(expr->plan, ...
</pre>
Kešování je nutnost. V případě jednodušších výrazů může generování plánu trvat několikanásobně déle než samotné vyhodnocení výrazu. Změřte si rozdíl mezi prvním a druhým spuštěním PL/pgSQL funkce. Tento čas můžete připsat na vrub právě generování prováděcích plánů. Kešování vyřešilo problém s efektivitou provádění PL/pgSQL funkcí a přineslo dva problémy: nakešované prováděcí plány nejsou sdílené a ani persistentní (zpomaluje se start aplikace, roste spotřeba paměti (řešením je např. pgpool)), nakešované prováděcí plány mohou být občas neadekvátní a jejich provedení způsobí run-time error.

Jakmile je plán jednou sestaven a je uložen v keši, existuje bez možnosti změny až do odhlášení nebo rekompilace funkce. V PostgreSQL není žadný atribut analogický atributu WITH RECOMPILE MSSQL. Naštěstí k chybám z důvodu chybného prováděcího plánu dochází výjimečně a pouze ze dvou možných důvodů.

První příčinou je změna změna struktury databáze - jinak pokud po prvním volání funkce zrušíte některý z datových objektů (tabulka, sekvence), který byl ve funkci použit, následující volání funkce skončí chybou. Řešením není vytvoření objektu stejného typu a jména, protože nový objekt dostane nový (a tedy jiný) oid (object identifikátor). Rušit za provozu datové tabulky nikoho nenapadne, dočasnou tabulku asi každého - proto je tento problém spojen v ToDo s dočasnými tabulkami. Řešením je všechny dočasné tabulky vytvářet před prvním zavoláním PL/pgSQL funkcí a pak je nerušit, maximálně mazat. (Silně to svádí, zvlášť pokud jste na PostgresSQL přešli z MSSQL, kde je ale mechanismus předávání výsledných recordsetů z procedur úplně jiný.)

Jelikož se prováděcí plán ukládá do keše až v okamžiku prvního použití objektu a nikoliv v čase překladu, můžeme dočasné tabulky vytvářet i v těle funkce.
<pre>
CREATE OR REPLACE FUNCTION ... 
  BEGIN 
      PERFORM 1 
       FROM pg_catalog.pg_class c 
            JOIN 
            pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
      WHERE c.relkind IN ('r','') AND c.relname = 'tmptab' 
        AND pg_catalog.pg_table_is_visible(c.oid) AND n.nspname LIKE 'pg_temp%'; 
    IF FOUND THEN 
      TRUNCATE tmptab; 
    ELSE 
      CREATE TEMP TABLE tmptab(... 
    END IF;
</pre>
Druhou možnou příčinou jsou dynamické dotazy. Jejich prováděcí plán se sice nekešuje, ale jejich výsledek může způsobit chybu jinde.
<pre>
CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$ 
  DECLARE _t varchar[] = '{integer, varchar}'; 
    _v varchar; _r record; 
  BEGIN 
    FOR _i IN 1 .. 2 LOOP 
      FOR _r IN EXECUTE 'SELECT 1::'||_t[_i]||' AS _x' LOOP 
        _v := _r._x; 
      END LOOP; 
    END LOOP; 
END; $$ LANGUAGE plpgsql; 
select foo();
</pre>

Spuštění funkce skončí s chybou (_v := _r._x;) při druhém průchodu cyklu FOR _i IN ..
ERROR: type of "_r._x" does not match that when preparing the plan

Proč? Příkaz přiřazení obsahuje SQL výraz (ještě jednou, každý výraz v PL/pgSQL je SQL výrazem). Při první iteraci se vytvořil prováděcí plán, který předpokládal, že hodnota _r._x je integer, při druhé iteraci je ale typu varchar, a tudíž je plán v keši neadekvátní. Řešením je mít tolik přiřazovacích příkazů, kolik je možných kombinací prováděcích plánů. Na první pohled nesmyslný kód funkce je správný:
<pre>
FOR _i IN 1 .. 2 LOOP 
  FOR _r IN EXECUTE 'SELECT 1::'||_t[_i]||' AS _x' LOOP 
    IF _i = 1 THEN _v := _r._x; 
    ELSIF _i = 2 THEN _v := _r._x; 
    END IF; 
  END LOOP; 
END LOOP;
</pre>
Přiznám se, že mi toto chování PL/pgSQL žíly netrhá. Znám příčinu problému a umím se podle toho zařídit. Nicméně konečnému řešení (regeneraci plánu v případě detekce chyby) nic nestojí v cestě. Zkusmo jsem použil makra PG_TRY(), PG_CATCH() a PG_END_TRY() pro zachycení chyby a regeneroval chybný plán bez měřitelné ztráty rychlosti. Je jen otázkou času, kdy se někdo ujme řešení a vytvoří patch. Pravděpodobně se tak ale nestane ve verzi 8.1.

Odbočení druhé: abych dokázal vysvětlit, kde je zakopaný pes v případě 'now', musím se ještě zmínit o procesu generování prováděcího plánu SQL příkazů.

Jednou z etap přípravy plánu je redukce konstant a zjednodušení funkcí (např. 2+2=4, True Or cokoliv = True, náhrada immutable funkcí s konstantními parametry výsledkem funkce - což je právě případ 'now' (backend/optimizer/util/clause.c - evaluate_function()), náhrada hodnotou NULL STRICT funkcí, pokud některý z jejich parametrů je NULL, atd.

V našem případě bylo 'now' nahrazeno konstantou, která zůstala uložena v prováděcím plánu a samozřejmě byla opakovaně vyhodnocována stejně. Za normálních okolností to nezpůsobovalo žádné problémy. Kromě PL/pgSQL PostgreSQL neobsahovala žádné nástroje schopné kešovat prováděcí plány. Výjimkou bylo PL/pgSQL, kde byl poprvé tento problém detekován (a stále je třeba si na něj dávat pozor).

Nepamatuji se, že bych kdy použil 'now'. Automaticky používám magické proměnné CURENT_DATE a CURRENT_TIMESTAMP, které na kešování netrpí. A pokud bych, snad z nostalgie, 'now' chtěl použít, pak jedině v kombinaci s proměnnou:
<pre>
DECLARE d date; BEGIN d := 'now'; INSERT INTO (..) VALUES(d); ...
</pre>
Proč? Na funkci obsahující proměnnou je optimalizátor krátký (v tomto případě funkce datein()). PL/pgSQL sám něco na způsob optimalizace neprovádí, tudíž není schopen detekovat, že d je vlastně konstanta, takže řetězec 'now' se bude přetypovávat na odpovídající typ skutečně až v době vyhodnocení výrazu a výsledkem bude odpovídající čas vyhodnocení výrazu.