Hlavní navigace

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

Pavel Stěhule

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).

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.

Našli jste v článku chybu?

8. 7. 2005 21:19

Myslim dokonce, ze to je lepsi preklad, nez kesovani. Preklad prikazu (a smyslu prikazu) exec_prepare_plan je priprava provadeciho planu spise nez kesovani provadeciho planu (take vzhledem k tomu, ze kesovani neni cesky).

8. 7. 2005 16:26

priprava ... to je prepare, ne ? Bohuzel v kontextu SQL pomerne pouzivany vyraz ...
Vitalia.cz: Jmenuje se Janina a žije bez cukru

Jmenuje se Janina a žije bez cukru

Lupa.cz: Kdo pochopí vtip, může jít do ČT vyvíjet weby

Kdo pochopí vtip, může jít do ČT vyvíjet weby

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá

Podnikatel.cz: Změny v cestovních náhradách 2017

Změny v cestovních náhradách 2017

DigiZone.cz: TV Philips a Android verze 6.0

TV Philips a Android verze 6.0

Lupa.cz: Proč firmy málo chrání data? Chovají se logicky

Proč firmy málo chrání data? Chovají se logicky

Podnikatel.cz: Podnikatelům dorazí varování od BSA

Podnikatelům dorazí varování od BSA

Podnikatel.cz: Snížení DPH na 15 % se netýká všech

Snížení DPH na 15 % se netýká všech

Root.cz: Certifikáty zadarmo jsou horší než za peníze?

Certifikáty zadarmo jsou horší než za peníze?

Vitalia.cz: Potvrzeno: Pobyt v lese je skvělý na imunitu

Potvrzeno: Pobyt v lese je skvělý na imunitu

Vitalia.cz: Proč vás každý zubař posílá na dentální hygienu

Proč vás každý zubař posílá na dentální hygienu

Vitalia.cz: Pamlsková vyhláška bude platit jen na základkách

Pamlsková vyhláška bude platit jen na základkách

Měšec.cz: Air Bank zruší TOP3 garanci a zdražuje kurzy

Air Bank zruší TOP3 garanci a zdražuje kurzy

Měšec.cz: U levneELEKTRO.cz už reklamaci nevyřídíte

U levneELEKTRO.cz už reklamaci nevyřídíte

120na80.cz: Na ucho teplý, nebo studený obklad?

Na ucho teplý, nebo studený obklad?

Lupa.cz: Propustili je z Avastu, už po nich sahá ESET

Propustili je z Avastu, už po nich sahá ESET

120na80.cz: Horní cesty dýchací. Zkuste fytofarmaka

Horní cesty dýchací. Zkuste fytofarmaka

Podnikatel.cz: Udávání a účtenková loterie, hloupá komedie

Udávání a účtenková loterie, hloupá komedie

Vitalia.cz: Spor o mortadelu: podle Lidlu falšovaná nebyla

Spor o mortadelu: podle Lidlu falšovaná nebyla

Podnikatel.cz: Chaos u EET pokračuje. Jsou tu další návrhy

Chaos u EET pokračuje. Jsou tu další návrhy