Kešování prováděcích plánů v PL/pgSQL

INSERT INTO (..) VALUES('now') -- nepouzivat v plpgsql! 

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.

  if (expr->plan == NULL)
     exec_prepare_plan(estate, expr)
  ....
  rc = SPI_execute_plan(expr->plan, ... 

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.

CREATE OR REPLACE FUNCTION ...
BEGIN
  PERFORM 1 FROM pg_catalog.pg_tables WHERE tablename = 'xx'
    AND schemaname LIKE 'pg_temp%';
  IF FOUND THEN
    TRUNCATE xx;
  ELSE
    CREATE TEMP TABLE xx(...
  END IF; 

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.

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(); 

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ý:

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; 

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/opti­mizer/util/clau­se.c – evaluate_functi­on()), 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:

DECLARE d date;
BEGIN d := 'now';
  INSERT INTO (..) VALUES(d);
  ... 

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.

Po letech vývoje PL/pgSQL představuje stabilní vývojové prostředí. Na výraznější změny si patrně budeme muset počkat do doby, kdy bude PostgreSQL podporovat sdílení prováděcích plánů. Ve verzi 8.1 přijdou jen chuťovky, jako je příkaz continue, pružnější příkaz raise – oproti minulé verzi, kdy parametry mohly být pouze proměnné, akceptuje i výrazy a snad i uživatelem definované výjimky.