Použití dočasných tabulek v PL/pgPSM
Na dočasných tabulkách jsou zajímavé dvě skutečnosti. 1) že obsahují data pouze jedné session, 2) že po ukončení session zmizí. Kvůli bodu 1. dočasné tabulky používáme. Bod dva nám působí problémy, jelikož vždy musíme dočasnou tabulku vytvářet na začátku session. Celkem přirozeně vkládáme vytváření dočasných tabulek na začátek procedur. Musíme ale vyřešit opakované spouštění takové procedury. Ta zákonitě selže neboť dočasná tabulka již při druhém spuštění existuje a podruhé ji vytvořit nelze. Pozn. Na rozdíl od MSSQL, kde dočasné tabulky vytvořené v proceduře se po ukončení procedury automaticky ruší. Celkem logicky bychom umístili odstranění tabulky na konec procedury. Což je ale chyba.
V PL/pgPSM dochází k ukládání prováděcích plánů. V prováděcích plánech se na tabulky neodkazuje jménem, ale jejich jednoznačným identifikačním číslem, což je neustále rostoucí posloupnost čísel. Tudíž pokud se v někde dotazu odkážete na tabulku, a ta pak odstraníte, pak opakované provádění tohoto dotazu selže. Vyrovnávací paměť prováděcích plánů se inicializuje překladem funkce, tj. stačí funkci znovu přeložit a proběhne v pořádku.
Zjišťovat, jestli je nutné funkci znobu přeložit nebo ne by bylo náročné. Řešení jsou dvě. První typické pro PostgreSQL a v tomto případě optimální. Dočasnou tabulku nikdy neodstraňujeme. Pouze ji na začátku procedury vyčistíme. V případě, že rušení obsahu vyvolá výjimky, což může nastat pouze tehdy, když tabulka dosud neexistuje, tak ji vytvoříme:
CREATE OR REPLACE FUNCTION xx()
RETURNS SETOF Foo AS
$$
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42P01'
CREATE TABLE foog(
a integer,
b integer);
TRUNCATE TABLE fooG;
END;
INSERT INTO fooG
VALUES(10,56),(1,1);
RETURN SELECT * FROM foog;
END;
$$ LANGUAGE plpgpsm;
Tato metoda je efektivní. Vytvoření tabulky je zapouzdřeno do vyhrazeného složeného příkazu z důvodu vyšší efektivity. Continue handler si totiž vyžaduje nastavení safe pointu na začátek každého příkazu, což má nezanedbatelnou režii, a proto se snažím minimalizovat počet příkazů v takovém bloku. Pozn. Jinak se chová continue handler pro varování, který toto režii nemá.
Druhou metodou, která vychází spíše z tradice MSSQL nebo MySQL je příznak RECOMPILE, který zajistí opakovaný překlad při každém spuštění funkce. Což vyřeší problém s neplatným prováděcím plánem, na druhou stranu máme opakovanou režii s překladem PL/pgSQL kódu a pro každý SQL příkaz režii s přípravou prováděcího plánu. Nicměně je to stejně efektivní, jako když používáte SQL ze své aplikace a nepoužíváte prepared statements. V každém případě jste ušetřeni rizika SQL injections. Vzhledem k nižší efektivite byste přepínač RECOMPILE neměli nikdy použít u funkce, která se používá opakovaně (v cyklu). Učelem tohoto přepínače je usnadnit portování procedur z jiných RDBMS.
U PostgreSQL 8.3 tento přepínač ztrácí smysl, jelikož PostgreSQL dokáže nevalidní plány aktualizovat. Nicméně v případě často volaných funkcí se vyplatí používat první metodu pro práci s dočasnými tabulkami, jelikož nedochází ke ztrátám výkonu s opakovaného generování prováděcích plánů.
CREATE OR REPLACE FUNCTION xx1()
RETURNS SETOF Foo AS
$$
#option recompile
BEGIN
DROP TABLE IF EXISTS fooG;
CREATE TABLE foog(
a integer,
b integer);
INSERT INTO fooG
VALUES(10,56),(1,1);
RETURN SELECT * FROM foog;
END;
$$ LANGUAGE plpgpsm;