01 Procedurálne jazyky

Procedúry a triggery v PostgreSQL

22-04-2017

teoria

Obsah článku:

1. Procedurálne jazyky

Procedurálne jazyky

Pre prácu s procedurálnymi jazykmi je potrebne poznať aspoň základné operácie v SQL. Ide o špeciálne jazyky – napríklad PL/pgSQL - pre programovanie na strane databázového servera. PL/pgSQL je v PostgreSQL to, čo je PL/SQL pre Oracle alebo Transact/SQL v Microsoft SQL server. PL jazyky môžeme použiť na tvorbu funkcií, čo sú obalené sekvencie príkazov zapísaných vo vnútri SQL výrazu. Týmito funkciami sa rozširuje funkcionalita databázového servera. Tieto serverové rozšírenia sú taktiež známe pod menom vložené procedúry. Možnosti vytvárania vložených procedúr sa s každou novou verziou PostgreSQL zväčšujú. Aj v konkurenčných databázových serveroch je použitie procedurálnych jazykov pomerne rozšírené. FirebirdSQL ich používa od začiatku svojej existencie, MySQL umožňuje použitie vložených procedúr. Od 5. verzie a SQLite má jednoduchý nástroj v podobe uložených dotazov, ale je nutné nezabudnúť na zameranie SQLite. Vo svete komerčných DB serverov snáď nie je možné nájsť žiadny, ktorý by nepodporoval vložené procedúry. Obvyklým jazykom pre písanie týchto procedúr je PL/pgSQL, ktorý predstavuje nejakú nad množinu SQL. PostgreSQL je na tom ale ešte lepšie. Pre vložené procedúry je možné použiť jazyky PL/Perl, PL/Python, PL/Tcl, PL/Ruby, PL/v8(JavaScript), PL/sh, PL/PHP, PL/R alebo PL/Java. PostgreSQL používa syntaktický analyzátor (BISON) pre proces prekladu, čim umožňuje pridanie podpory pre ďalšie OpenSource jazyky. O PL by sa dalo povedať následujúce: za prvé – je to extrémne zjednodušený programovací jazyk určený k písaniu funkcií rozširujúcich prostredie databázy, t. j. obsluha triggerov, vlastných funkcií a agregačných funkcií, za druhé – je veľmi dobre previazaný s prostredím databázy. Aspoň do doby než bude k dispozícii prekladač do C bude použitie PL vždy znižovať efektivitu databázového systému, na druhú stranu vďaka PL a jeho prepojeniu s PostgreSQL máme k dispozícii prostriedok, v ktorom jednoducho a prehľadne pracujeme s logickým modelom databázy aplikácie a ktorý sa naviac, vďaka svojej jednoduchosti dá jednoducho zvládnuť.

Kedy používať PL jazyky

SQL príkazy sú na serveri spustené individuálne, každý dopyt je preto poslaný jeden po druhom na server, kde sa analyzuje, spracuje a vráti ako výsledok. Samozrejme sa nejedná o chybu, ale pokiaľ chceme spustiť tisíce príkazov denne, je rozumnejšie použiť PL funkciu. Presnejšie povedané, jedná sa o vloženú procedúru, ktorá môže vykonávať všetky SQL príkazy v kontrolnom cykle. Štruktúry cyklu ako WHILE a FOR, sa môžu vykonať na základe rôznych podmienok spolu s premennými, ktoré deklarujeme na uloženie výsledkov pre neskoršie spracovanie. Navyše je možné spracovávať chyby a výnimky. PL jazyky dokážu zapuzdriť SQL a implementovať rôzne programovacie techniky rôznych jazykov. Funkcie je možné napísať v rôznych programovacích jazykoch, ako je Perl, Python, Java, a podobne. Tieto funkcie môžu spúšťať ďalšie funkcie, môžu napríklad obsahovať čiastkové bloky vo vnútri hlavného bloku. Vďaka čomu dokážeme výrazne ovplyvniť výkonnosť databázy.

Pokiaľ máme časť kódu, funkčnosť, ktorá je v klientoch, je prepisovateľná. Ak má databázový server niekoľko rôznych klientov, ktorí pristupujú k jedinej databáze a často využívajú niektoré funkcie. Je výhodné tuto funkčnosť prepísať do vloženej procedúry, ktorú máme uloženú na serveri. Zmena v tejto procedúre znamená zmenu pre všetkých klientov/užívateľov, nie je kvôli tomu nutné upravovať a distribuovať užívateľom nové verzie klientských aplikácií. Pokiaľ robíme náročné operácie s dátami. Nakoľko, v binárnej aplikácií sú operácie rýchlejšie, než nascriptované na strane servera. Pokiaľ je funkčnosť na serveri, odpadne neustále analyzovanie dotazov poslaných na server, konvertácia premenných medzi serverom a klientskou aplikáciou a mimo iné sa výrazne zmenší zaťaženie medzi aplikáciou a serverom. Vložené procedúry sú uložené v databáze, pracujú s dátami presne tak, ako sú na serveri. Nielen náročné operácie, ale aj zložité dotazy, prípadne dynamicky generované obmedzenia výberu, za použitia rozumných pravidiel, sú výbornou témou k použitiu vložených procedúr.

Využitie uložených procedúr sa dá považovať za ďalší stupeň k viacvrstvovým aplikáciam. Zatiaľ čo niektoré aplikácie je možné napísať ako jednovrstvové (napr. väčšina hier), kde všetko rieši program, je manipulácia s databázami príklad dvojvrstvovej aplikácie, kde jedna vrstva je aplikácia ako taká a druhou vrstvou je databáza. Trojvrstvové aplikácie majú medzi klientskou časťou a databázovým serverom ešte aplikační server. Pochopiteľne existujú aj ďalšie vrstvy, ale to sa jedná, väčšinou, o rozvrstvenie aplikačného serveru. Použitie vložených procedúr sa dá považovať za dva a pol vrstvovú aplikáciu, niektoré časti kódu sú na serveri, ale nie je to plný aplikačný server.

Kedy nepoužívať PL jazyky

Nie vždy je možné použiť vložené procedúry. Podmienkou pre prenesenie kódu do vložených procedúr je absolútna neinteraktivita a bezstavovosť. Nakoľko väčšina aplikácií istý stupeň interakcie vyžaduje, je nepravdepodobné a ako skúsenosti užívateľov napovedajú aj neefektívne písať aplikácie čisto v uložených procedúrach. SQL server nemôže nahradiť aplikačný server, iba čiastočne, a je na úvahe užívateľa, aby zvážil všetky pre a proti.

Rovnako tak ako majú uložené procedúry svoje omedzenia ma svoje omedzenia jazyk PL/pgSQL. V určitých prípadoch nie je možné PL/pgSQL použiť buď z nedostatočnosti jazyka (PL je príliš statický) alebo z dôvodu neefektivity.

PL/pgSQL nie je vhodné používaťk kinicializácií veľkých polí — dôvodom je pomalý zápis do poľa. Je omnoho rýchlejšie prvky poľa vytvoriť pomocou konverzie tabuľky, ktorú vytvoríme SRF (set-returning function) funkciou na pole. Napríklad najrýchlejší spôsob vytvorenia poľa vzostupných hodnôt je SELECT ARRAY (SELECT * FROM generate_series(1,100));. Funkcia generate_series je SRF funkcia generujúca postupnosť hodnôt zo zadaného intervalu.1

Rýchlosťou nevyniká ani iteračné zostavenie reťazca. Pokiaľ je funkcia príliš pomalá, je potrebné skúsiť následujúci trik. Svoju funkciu prevedieme na SRF funkciu - časť, kde zlučujeme reťazce nahradíme príkazom RETURN NEXT časť_reťazca. Konečné zlúčenie spustime mimo telo funkcie pomocou funkcie array_to_string.

CREATE OR REPLACE FUNCTION generuj_retazec(integer)
	RETURNS SETOF varchar AS $$
BEGIN
	FOR _i IN 1 .. $1 LOOP
		RETURN NEXT '<item>'||_i||'</item>';
  	END LOOP;
  	RETURN;
END; $$ LANGUAGE plpgsql;
SELECT array_to_string(
	  ARRAY(SELECT * FROM generuj_retazec(1000)), '');

PL/pgSQL nepodporuje žiadne I/O operácie, a ani sa nepredpokladá, že by ich v blízkej budúcnosti podporoval - PL/pgSQL existuje iba v TRUSTED variante. Riešením je použitie niektorého z UNTRASTED jazykov ako je PL/Perl alebo PL/Python.

PL/pgSQL nie je vhodný k návrhu univerzálnych triggerov. V PL/pgSQL neexistuje možnosť ako previesť hodnotu typu RECORD na pole (Položka typu RECORD je prístupná iba prostredníctvom statického identifikátoru známeho v dobe kompilácie, pole je prístupné prostredníctvom dynamického indexu). Totiž neexistuje žiadna možnosť ako dynamicky iterovať cez všetky položky typu RECORD, alebo dynamicky pristupovať k typu RECORD. Riešením je opäť využiť iný programovací jazyk, tentokrát však postačia TRUSTED varianty.

Procedurálne jazyky a psql

Pre uľahčenie a sprehľadnenie práce s PL jazykmi v nástroji psql, môžeme použiť špeciálne psql príkazy.

Zoznam užívateľom vytvorených funkcií:
\df
Podrobnejšie informácie:
\df+
Otvorí danú funkciu v editore:
\ef meno_funkcie
Zoznam event triggerov:
\dy
Zoznam nainštalovaných procedurálnych jazykov:
\dL
Zoznam nainštalovaných rozšírení (extensions):
\dx
Zapnutie/vypnutie rozšíreného výpisu:
\x
Automatické zapnutie rozšíreného výpisu:
\x auto
Watcher spustí predtým zadaný príkaz SELECT implicitne každé 2 sec:
\watch
Vráti časovú dĺžku trvania danej operácie:
\timing on
Umožňuje zadávanie SQL dotazov v predvolenom editore
\e
Spustí a vykoná SQL príkazy v súbore:
\i meno_súboru.sql

Zdroje:

https://www.root.cz/clanky/plpgsql-prakticky/
http://postgresql.ok.cz/doc/plpgsql.html
http://www.linuxsoft.cz/article.php?id_article=1426