imported>Pavel v 24. 7. 2013, 06:37

2013-07-24T06:37:44Z

Nová stránka

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

Automatické kešování prováděcích plánů v PL/pgSQL - Historie editací

imported>Pavel v 24. 7. 2013, 06:37