Příručka SQL/PSM

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

Úvod do SQL/PSM (PL/pgPSM)

Začátkem devadesátých let minulého století začalo být jasné, že standard ANSI SQL postrádá prostředky pro tvorbu uložených procedur (zejména možnost deklarovat proměnné a dále s nimi pracovat, řízení toku - cykly a podmínky). Na tuto skutečnost reagovaly komerční firmy implementací vlastních proprietárních prostředí. Zřejmě nejpopulárnějšími v té době byly jazyky PL/SQL (Oracle, 1992), T-SQL (Sybase a Microsoft, 1995) a SPL (Informix, 1996). Od roku 1990 se v standardizační komisi ANSI SQl této problematice začala věnovat skupina vývojářů kolem Jima Meltona. Nejstarší zmínka o SQL/PSM je ze srpna roku 1994. (Prvním výstupem bylo PSM-96 (PSM - Persistent Stored Modules) postavené nad SQL92. Ve svém článku Budoucnost programování v SQL z ledna 1997 zmiňuje Joe Celko SQL/PSM jako jazyk vycházející z Algolu rozšířený o prvky Ady (řešení výjimek). V roce 1998 (draft) standard byl již součástí SQL3 a označen současným názvem SQL/PSM (ANSI/ISO/IEC 9075-4:1999). Bohužel v té době většina významných firem již měla své vlastní prostředí (nekompatibilní se standardem), které, jak se ukázalo později, neopustily. Standard byl implementován pouze v těch RDBMS, kde do roku 1998 nebyla možnost tvorby SQL uložených procedur. Kromě DB2 (SQL PL, IBM, 2001) se většinou jedná o minoritní RDNMS: Mimer, Solid, 602SQL server. Do popředí se SQL/PSM opět dostává po roce 2005, kdy je implementován v Advantage Database Serveru (Sybase iAnywhere, 2005), v MySQL (2005) a PostgreSQL (2007). Jen zřídka je SQL/PSM implementován v plném rozsahu. Za zdařilou implementaci se pokládá SQL PL v DB2. Implementace SQL/PSM v PostgreSQL se nazývá (jak je v PostgreSQL zvykem) PL/pgPSM.

Stručně lze programovací jazyk definovaný v SQL/PSM charakterizovat jako jednoúčelový, zcela nový, moderní, jednoduchý procedurální programovací jazyk s integrovaným SQL a úzkou vazbou na prostředí SQL serverů. Jedná se o jazyk s bohatým repertoárem řídících konstrukcí a komfortním modelem zachycení a zpracování chyb. Datové typy, funkce přebírá z hostitelského SQL serveru. I/O rutiny pak úplně chybí (přesně v duchu architektury uložených procedur). Standard SQL/PSM se nevěnuje pouze popisu SQL procedur. Obsahuje i popis uspořádání procedur do modulů a popis tzv. externích procedur (implementace procedur v dalších prg. jazycích, např. C, Cobol, atd). Přestože se jedná o zcela nový programovací jazyk, na celé řadě konstrukcí je zřejmá inspirace jazyky PL/1, jazyky ADA, Modula a dalšími jazyky.

Následující ukázka je modifikací klasického příkladu Hello world v prostředí SQL/PSM. Parametrem funkce je jedinečný identifikátor uživatele. Uvnitř funkce se na základě tohoto identifikátoru získá skutečné jméno a příjmení uživatele z tabulky Users. Všimněte si deklarace proměnné, plnění proměnné hodnotou z tabulky (integrace SQL příkazu SELECT), použití SQL datového typu (varchar) a SQL oparátoru (operátor ||), komentáře.

CREATE OR REPLACE FUNCTION hello(uid integer)
RETURNS varchar AS
$$
  BEGIN
    DECLARE real_name varchar;
    -- Get real name
    SET real_name = (SELECT name || ' ' || surname 
                        FROM Users 
                       WHERE Users.uid = hello.uid);
    RETURN 'Hello, ' || real_name;
  END;
$$ LANGUAGE plpgpsm;

SELECT hello(123);

Vložení zdrojového kódu funkce mezi dvojici symbolů $$ je specifické pro PostgreSQL. PL/pgPSM je z pohledu PostgreSQL externí programovací jazyk, jako ostatní podporované PL jazyky (PL/pgSQL, PL/Perl, PL/Python), a tudíž PostgreSQL k tomuto jazyku přistupuje stejně a vynucuje si stejná pravidla zápisu. Implementace SQL/PSM má ještě další dvě podstatné odlišnosti od standardu. Za prvé, PostgreSQL umožňuje definovat pouze funkce (nikoliv procedury), což se projevuje určitými omezeními v řízení transakcí. Za druhé, PostgreSQL implicitně spouští funkce v režimu SECURITY CALLER, kdežto standard předpokládá režim SECURITY DEFINER. Mezi standardem a implementací SQL/PSM v PostgreSQL je ještě několik dalších rozdílů daných určitou volností standardu a implementačními závislostmi, nicméně nejedná se o nijak zvlášť markantní rozdíly. Asi je ještě předčasné počítat s plnou přenositelností kódu mezi RDBMS podporujícími SQL/PSM, nicméně v celé řadě případů sou jsou nutné úpravy minimální. Problémy mohou nastat u normou neošetřených konstrukcí (unbound selects, multirecordsets, atd).

Jazyk SQL/PSM je v RDBMS PostgreSQL (PL/pgPSM) běží na modifikovaném run-time PL/pgSQL. Tudíž výkonnostně i funkčně je na tom velice podobně jako jazyk PL/pgSQL. PL/pgSQL je v tuto chvíli vyzrálejší a časem prověřené prostředí vhodné pro jakkoliv kritické aplikace. PL/pgPSM naopak přináší shodu se standardem a novější, a bohatší programovací jazyk.

Rozlišujeme mezi tzv. externími uloženými procedurami a SQL uloženými procedurami. Rozdíl je v použitém jazyce a v přístupu. Pokud uložené procedury jsou realizovány v klasických prg. jazycích, a běží mimo prostředí SQL serveru (vlastní funkce, vlastní datové typy), jedná se o externí procedury. V případě, že se použije specializovaný jazyk a procedura běží v prostředí SQL serveru (sdílí datové typy a funkce), pak takovou proceduru označujeme jako SQL proceduru. Příklady SQL procedur jsou prostředí PL/SQL, T-SQL, nebo PSQL či PL/pgSQL, PL/pgPSM. Externí procedury jsou standardizované pouze pro jazyk Java (SQL/J). Existuje ovšem celá řada dalších implementací např. CLR pro SQLServer 2005 nebo PL/Perl, PL/Python pro PostgreSQL. Obě třídy procedur mají své pro a proti, doporučuje se ale upřednostnění SQL procedur vyjma těch případů, kdy jsou neefektivní (např. iterační výpočet integrálu) nebo chybí dostatečná funkcionalita (např. I/O operace). Velkou výhodou SQL procedur je integrace s prostředím. Zpravidla nedochází k zbytečným konverzím, a obvykle se používají předzpracované SQL příkazy (prepared statements). Kromě toho všechny statické SQL příkazy jsou verifikované (což ještě neznamená, že jsou 100% správné, nicméně to znamená, že jsou syntakticky správné).

Praktické tipy návrhu uložených procedur v prostředí PL/pgPSM

V podstatě platí veškerá doporučení pro návrh jakéhokoliv software, tj. požadavky na čitelnost, srozumitelnost kódu:

  • dbejte na dodržování jmenné konvence,
  • používejte komentáře a názorné názvy proměnných,
  • používejte jednotnou konvenci pro odsazování bloků,
  • dodržujte rozumnou délku procedury (kolem 50 řádků),
  • používejte "upovídaný" styl .. využívejte volitelná návěstí smyček.

K tomu ještě specifická doporučení platná pouze pro SQL uložené procedury (bez ohledu na konkrétní prostředí):

  • pomocí prefixů a kvalifikovaných jmen atributů se vyhněte možným kolizím názvů sloupců a jmen proměnných,
  • jednotným způsobem řešte zachycení a ošetření chyb,
  • vyhněte se ISAM programování - co lze vyřešit pomocí SQL řešte pomocí SQL (pozor na cykly napříč celými tabulkami),
  • omezte kurzory a dočasné tabulky na nezbytné minimum,
  • v triggerech neopravujte data,
  • procedury by měli realizovat určitou činnost, nikoliv jen zapouzdřit selecty.

Na následujících dvou příkladech si všimněte špatného (ISAM) přístupu a dobrého přístupu k návrhu uložených procedur. Obě procedury volají pro určitou podmnožinu zaměstnanců proceduru print_info.

-- spatne (ISAM pristup)
CREATE OR REPLACE FUNCTION report_a()
RETURNS void AS 
$$
  main: FOR outer AS 
            SELECT id FROM Users 
        DO
          BEGIN
            DECLARE name, surname varchar;
            DECLARE age int;
            SET (name, surname, age) = (SELECT e.name, e.surname, e.age
                                           FROM Employers e
                                          WHERE e.id = outer.id);
            IF age >= 20 AND age <= 29 THEN
              CALL print_info(name, surname, age, 'y');
            ELSE IF age >=30 AND age <= 50 THEN
              CALL print_info(name, surname, age, 'o');
            END IF;                     
          END;
        END FOR main;
$$ LANGUAGE plpgpsm;

-- dobre
CREATE OR REPLACE FUNCTION report_b()
RETURNS void AS
$$
  main: FOR fc AS
            -- veskere mozne podminky a transformace resim v prikazu SELECT
            SELECT e.*, CASE WHEN e.age BETWEEN 20 AND 29 THEN 'y'
                             WHEN e.age BETWEEN 30 AND 50 THEN 'o' END AS tp
               FROM Employers e
              WHERE e.age BETWEEN 20 AND 50
        DO
          CALL print_info(fc.name, fc.surname, fc.age, fc.tp);
        END FOR main;
$$ LANGUAGE plpgpsm;

Příručka jazyka PL/pgPSM

Každá funkce obsahuje jeden PL/pgPSM příkaz. Následující jedno příkazové funkce jsou korektní. Pozn. Pro uživatele PL/SQL jazyků to může být mírný šok. Na rozíl od PL/SQL, kde základním příkazem funkce nebo procedury je složený příkaz, je v SQL/PSM základním příkazem libovolný příkaz. Pokud funkce neobsahuje žádný SQL příkaz, používejte atribut IMMUTABLE. Kromě jiného i zajistíte efektivnější provádění funkce. Speciálním PL/pgPSM příkazem je složený příkaz, který umožňuje zadat libovolně dlouhou syntakticky správnou posloupnost PL/pgPSM příkazů.

CREATE OR REPLACE FUNCTION sum2params(IN a integer, IN b integer, OUT c integer) AS 
$$
  SET c = a + b;
$$ LANGUAGE plpgpsm IMMUTABLE;

CREATE OR REPLACE FUNCTION insert_val(a integer)
RETURNS void AS
$$
  INSERT INTO Foo VALUES(a);
$$ LANGUAGE plpgpsm;

CREATE OR REPLACE FUNCTION get_sum(IN a integer, OUT b integer) AS
$$
  SET b = (SELECT sum(f.a) 
              FROM Foo f
             WHERE f.a > get_sum.a);
$$ LANGUAGE plpgpsm;

CREATE OR REPLACE FUNCTION dummy() 
RETURNS void AS
$$
  BEGIN
  END;
$$ LANGUAGE plpgpsm IMMUTABLE;

K dispozici jsou následující příkazy:

Ještě před samotným popisem PL/pgPSM příkazů si projděte následující jednoduché ukázky:

-- case statement
CREATE OR REPLACE FUNCTION foo1(a integer)
RETURNS void AS $$
  CASE a
    WHEN 1, 3, 5, 7, 9 THEN
      PRINT a, 'is odd number';
    WHEN 2, 4, 6, 8, 10 THEN
      PRINT a. 'is odd number';
    ELSE 
      PRINT a, 'isn't from range 1..10';
  END CASE;
$$ LANGUAGE plpgpsm;

-- while statement
CREATE OR REPLACE FUNCTION foo2(a integer)
RETURNS void AS 
$$
  BEGIN
    DECLARE i integer DEFAULT 1;
    WHILE i <= a 
    DO
      PRINT i;
      SET i = i + 1;
    END WHILE;
  END
$$ LANGUAGE plpgpsm;

-- for statement
CREATE OR REPLACE FUNCTION foo3(a integer)
RETURNS void AS 
$$
  FOR fc AS
      SELECT i 
         FROM generate_series(1,a) AS g(i)
  DO
    PRINT fc.i;
  END FOR;
$$ LANGUAGE plpgpsm;

Použití jazyka PL/pgPSM

Parametry ovlivňující provádění funkcí

V PL/pgPSM jsou k dispozici dva přepínače: DUMP a RECOMPILE. Použití prvního způsobí výpis přeloženého kódu funkce do systémového logu. Druhý zajistí vynulování nakešovaných prováděcích plánů při každém startu funkce. Parametry se zapisují ještě před vlastní kód za klíčové slovo #OPTION.

#OPTION DUMP

Účelem tohoto přepínače je zobrazení přeloženého kódu do systémováho logu. Rozhodně se nejedná o typickou činnost - většina uživatelů PL/pgSQL o této možnosti v životě neslyšela. Dokáže být velice užitečný při hledání kolize názvů proměnných a SQL atributů. V případě této chyby nám systém hlásí (v lepším případě), že dotaz nelze přeložit nebo (v horším případě) je výsledek dotazu jiný než očekáváme.

Tento přepínač demonstruje skutečnost, že PL/pgSQL je v podstatě preprocesor jazyka SQL. Vlastní interpret je natolik minimalistický, že pro provádění veškerých operací (logických, aritmetických) se spoléhá na SQL. Díky tomu je zajištěna kompatibilita s SQL. Na druhou stranu PL/pgSQL se nehodí pro výpočetně náročné úlohy (s provedením každého SQL příkazu je spojená nezanedbatelná režie). K těmto účelům v PostgreSQL slouží PL/Perl, PL/Python nebo klasické C.

-- tabulka foo obsahuje sloupec a
CREATE OR REPLACE FUNCTION kolize(a integer)
RETURNS SETOF Foo AS
$$
#option dump
  SELECT * 
    FROM Foo 
   WHERE a = a;
$$ LANGUAGE plpgpsm;

Jedná se o skutečně záludnout chybu (a poměrně častou). Přeložený kód výpadá následovně:

Execution tree of successfully compiled PL/pgSQL function kolize(integer):                            
                                                                                                      
Function's data area:                                                                                 
    entry 0: VAR $1               type int4 (typoid 23) atttypmod -1                                  
    entry 1: VAR found            type bool (typoid 16) atttypmod -1                                  
                                                                                                      
Function's statements:                                                                                
  0: *unnamed*:                                                                                       
     BLOCK                                                                                            
  2:   SQL 'SELECT * FROM Foo WHERE  $1  =  $1  {$1=0}'                                               
  0:   RETURN NULL                                                                                    
     END *unnamed*                                                                                    
                                                                                                      
End of execution tree of function kolize(integer)   

Chyba je v podmínce WHERE. PL/pgSQL nechápe zápis a = a jako podmínku typu: SQL atribut je roven proměnné. Zápis znamená, že proměnná a je rovna proměnné a, což je splněno pro všechny řádky tabulky, a také výsledkem je celá tabulka Foo.

#OPTION RECOMPILE

Tento přepínač snižuje pravděpodobnost nekonzistence prováděcích plánů tím, že před každým startem procesury se procedura znovu přeloží a tím dojde k vyčištění cache prováděcích plánů. Zároveň se tím ale prodlouží doba spuštění procedury (dochází k překladu) a doba běhu (dochází k průbežnému vytváření prováděcích plánů). Tudíž tento přepínač používejte jen v nutných případech, nebo ještě lépe, nepoužívejte jej vůbec. Jak se vyhnout použití přepínače RECOMPILE je popsáno v sekci věnované dočasným tabulkám.Účelem tohoto přepínače je usnadnit portování uložených procedur z jiných RDBMS, kde jinak funguje ukládání prováděcích plánů.

CREATE OR REPLACE FUNCTION testr()
RETURNS SETOF Foo AS
$$
#option dump recompile
  BEGIN
    DROP TABLE IF EXISTS FooG;
    INSERT INTO FooG
       SELECT * 
          FROM Foo;
    RETURN TABLE(SELECT *
                    FROM FooG);
  END;
$$ LANGUAGE plpgpsm;

Tento přepínač je analogií přepínači WITH RECOMPILE v T-SQL Microsoft SQL Serveru.

Portování uložených procedur z MySQL 5.x

Originální zdrojový kód procedury generující fraktály je přebrán z archivu MySQL dev. PostgreSQL řeší jinak:

  • změnu aktuálního schématu,
  • zápis zdrojového kódu procedury (nepoužívá mechanismus separátoru),
  • pro spojení řetězců používá operátor || nikoliv funkci concat,
  • neumožňuje volné SQL dotazy, jelikož nepodporuje procedury, ale pouze funkce.

Všimněte si minimálních rozdílů v upraveném kódu pro PostgreSQL.

Instalace PL/pgPSM

Vzhledem k nezralosti implementace PL/pgPSM ještě tento interpret není zařazen do distribuce. Zatím je ke stažení v jeslích postgresql projektů http://pgfoundry. Přesun do distribuce bude možný až po plné implementaci standardu a po důkladném otestování. Zatím zbývá dopsat podporu příkazů RESIGNAL a GET STACKET DIAGNOSTIC. Pokud se obejdete bez těchto příkazů, můžete používat PL/pgPSM bez obav. Interpret PL/pgPSM je modifikací interpretu PL/pgSQL, který je lety i tisíci projekty důkladně prověřen.

Pro překlad PL/pgPSM je potřeba instalovat PostgreSQL ze zdrojových kódů. Potřebujete alespoň verzi 8.2. Poslední verzi PL/pgPSM naleznete na http://pgfoundry.org/frs/?group_id=1000238&release_id=767.

Postup

  • Stáhněte si nejnovější zdrojové soubory PL/pgPSM z adresáře http://pgfoundry.org/frs/?group_id=1000238
  • pokud máte PostgreSQL instalovaný ze zdrojových kódů, rozbalte archív do adresáře contrib
  • proveďte příkazy make a make install
  • jako superuser spusťe psql postgres < plpgpsm.sql
  • od tohoto okamžiku můžete používat plpgpsm stejně jako ostatní jazyky
  • test instalace make installcheck

Pokud nemáte PostgreSQL instalovaný ze zdrojových kódů, musíte mít alespoň develop knihovny PostgreSQL

  • kdekoliv rozbalte archiv plpgpsm
  • spusťte příkazy make USE_PGXS=1 a make USE_PGXS=1 install
  • další postup je stejný jako v předchozím případě

Uvítáme jakoukoliv formu spolupráce. A to ať doplněním této dokumentace, její korekturou, překladem, rozšířením testovacích scénářů, doplněním funkcionality nebo samotným použitím.