04 PL/Python

teoria

PL/Python

Ďalším z procedurálnych jazykov je PL/Python. Je to klasický objektový Python, ktorý je možné použiť k vytváraniu uložených procedúr v PostgreSQL. PL/Python je však trochu iný ako PL/pgSQL. Ide o procedurálny jazyk s nálepkou Untrasted. Jazyk plpythonu je teda označený ako nedôveryhodný, takže ho bežný užívateľ nemôže používať. Buď musí mať super užívateľské práva, alebo sa zmení dôveryhodnosť jazyka na TRUE. Do tejto množiny Untrasted jazykov patrí okrem PL/Python aj PL/Pearl, či PL/TCL a mnoho iných. Programovanie v týchto jazykoch sa riadi pravidlami a zákonitosťami daného jazyka. Z pohľadu začlenenia vytvorených funkcií do DBS PgSQL je pri spustení týchto funkcií volaný vnútorný interpreter, z tohto pohľadu teda pomalšie riešenie než u PL/pgSQL. Na druhej strane je však možné riešiť mnoho úloh v týchto jazykoch rýchlejšie než priamo v DBS alebo v klientskej aplikácií. Argumentom pre rozšírenie funkčnosti o funkcie napísané v týchto jazykoch je možnosť I/O operácií. Tieto jazyky majú oproti Trusted variantám, väčšie možnosti využitia a väčšie právomoci. Dokážu k príkladu vytvárať nové súbory a pristupovať k ním, či napr. otvárať sockety. To samozrejme so sebou prináša aj zvýšenú opatrnosť pri ich vytváraní. Ako nedôveryhodný je PL/Pythonu označený preto, pretože s ním môže nekalý užívateľ narušiť beh serveru (napríklad jeho zaťažením). Možnosti takéhoto jazyka sú ozaj rozsiahle a je v ňom možné pravdepodobne všetko čo v bežnom jazyku Python. Obtiažnejšie je však ladenie takýchto funkcií.1

PL/Python dokonca zvláda aj komunikáciu s okolitým svetom internetu:

1
2
3
4
5
6
CREATE FUNCTION ziskaj_ip_adresu(adresa text)
RETURNS inet
AS $$
	import socket
	return socket.gethostbyname(adresa)
$$ LANGUAGE plpythonu SECURITY DEFINER;

Funkcia vyššie dokáže zistiť IP adresu zvoleného servera, otestujeme ju príkazom:

1
2
3
4
5
6
teoria_tst=# SELECT ziskaj_ip_adresu('www.postgresql.org');
ziskaj_ip_adresu
----------------
217.196.149.50
(1 row)

Pričom sa ukázala sila tohto jazyka. Podobná funkcia by v jazyku C, obsahovala desiatky riadkov kódu.
Od verzie 9.3 sa pomerne zásadných úprav dočkala podpora uložených procedúr v PL/Python. Tento jazyk je rozšírený zväčša medzi užívateľmi PostGISu (Podpora pre geografické objekty). Zásadným krokom vpred je podpora Pythonu 3. Dvojková rada už podporuje Unicode. Parametre funkcie typu pole sú teraz mapované priamo na pole Pythonu.

Pokiaľ sú všetci užívatelia databázového servera dôveryhodní, je možné zmeniť plpythonu na dôveryhodný:

1
2
3
4
5
6
7
8
9
10
11
12
-- ako užívateľ postgres
teoria_tst=# SELECT lanpltrusted FROM pg_language
WHERE lanname= 'plpythonu';
lanpltrusted
--------------
 f
(1 row)
teoria_tst=# UPDATE pg_language
SET lanpltrusted = true WHERE lanname = 'plpythonu';
UPDATE 1
-- teraz môže používať PL/Python každý

Inou možnosťou by bolo nastaviť konkrétnemu užívateľovi super užívateľské práva:

1
2
3
4
-- ako užívatel postgres
teoria_tst=# ALTER USER peter WITH superuser;
ALTER ROLE
-- teraz je peter superuser a môže pracovať s PL/Python

Inštalácia PL/Python

Často treba nainštalovať samotný PL jazyk na server. Čo v linuxovom systéme OpenSuse, vykonáme príkazom:

1
root# sudo zypper install postgresql-plpython

Pre použitie jazyka PL/Python, je potrebné si ho najskôr pre danú databázu „aktivovať“ príkazom CREATE EXTENSION. (Je potrebné na to mať taktiež príslušné práva).

1
teoria_tst=# CREATE EXTENSION plpythonu;

Jazyk však môžeme aktivovať aj príkazom CREATE LANGUAGE.

Úspešnosť aktivácie jazyka je najlepšie otestovať funkciou pre zistenie používanej verzie Pythonu:

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION info_verzia()
RETURNS TEXT
AS $$
    	import sys
    	string = sys.version
 	return string
$$ LANGUAGE plpythonu;

Výpis verzie používaného Python interpretra:

1
2
3
4
5
teoria_tst=# SELECT info_verzia();
info_verzia
---------------
2.7.12 (default, Jun 28 2016, 06:57:42) [GCC]
(1 row)

Základná funkcia v PL/Python

Rovnako ako v PL/pgSQL aj v PL/Python využívame príkaz CREATE LANGUAGE, pre tvorbu funkcií. Syntax takejto funkcie sa nijako nelíši, samozrejme až na zvolený jazyk funkcie a telo funkcie, v ktorom už nevyužívame syntax SQL, ale syntax programovacieho jazyka Python. Mierne odlišné sú aj údajové typy premenných. Riadime sa syntaxou:

1
2
3
4
5
CREATE FUNCTION meno_funkcie(argumenty)
RETURNS navratova_premenna
AS $$
	  # PL/Python - telo fukcie
$$ LANGUAGE plpythonu;

Do tela takejto funkcie je dokonca možné importovať Python moduly - externé knižnice, ktoré však musia byť nainštalované v DB serveri (teoreticky je tu možnosť používať virtualenv). PL/Python ponúka modul plpy, ktorý je vždy automaticky importovaný. Obsahuje užitočné metódy ako je notice a info, ktorá zobrazí INFO hlásenie (klasické pythonovské metódy pre výpis, ako je print, nemožno použiť).
Modul plpy a ukážka vo funkcii:

1
2
3
4
5
CREATE OR REPLACE FUNCTION hello_world()
RETURNS TEXT
AS $$
	plpy.notice('Funkcia Hello World, sa aktivovala!')
$$ LANGUAGE plpythonu;

Použitie funkcie hello_wrold():

1
2
3
4
5
teoria_tst=# SELECT hello_world();
hello_world
---------------
Funkcia Hello World, sa aktivovala!
(1 row)

Konverzie dátových typov

Prvou a poslednou činnosťou, ktorá sa vykoná keď je PL funkcia zavolaná, je konverzia hodnôt argumentov medzi PL/python a PostgreSQL. PostgreSQL typy musia byť konvertované na typy, ktoré podporuje PL/python a návratová hodnota musí byť naopak konvertovaná na typ pre PostgreSQL. Niečo také v PL/pgSQL nie je potrebné nakoľko používa rovnaké typy ako PostgreSQL. PL/Python však používa svoje vlastné typy premenných (Integer, string, date…), a tak je potrebné ich konvertovať.

PostgreSQL Python 2 Python 3 Komentár
int2, int4 int int
Int8 long int
real, double, numeric float float V tomto prípade môže dôjsť k strate presnosti
bytea str bytes Žiaden prevod kódovania
text, char(), varchar() str Str Vo verzií Python 2 má reťazec kódovanie servera Vo verzií Python 3 je každý reťazec v kódovaní UNICODE.
Všetky ostatné typy str str

Je potrebné aby kódovanie reťazcov bolo rovnaké ako kódovanie v PostgreSQL. Reťazce, ktoré nie sú validné vedú k chybe. Nie všetky chyby kódovania dokáže PostgreSQL zachytiť, a tak sa takéto „chybné“ dáta môžu dostať do výsledkov. Reťazce kódované v UNICODE su konvertované na správne kódovanie automaticky. V Pythone 3 su všetky reťazce UNICODE. Inak povedané, všetko pri 0, False, prázdnom reťazci alebo slovníku sa v PostgreSQL stane false. Jediná výnimka je pri None, kde sa kontrola vykoná pred ostatnými konverziami. None sa vždy prekonvertujte na NULL a nie na hodnotu typu boolean.

Prístup k databáze s PL/Python

Vďaka modulu plpy a funkcií plpy.execute() je v Pythone jednoduché získať prístup k databáze. Táto funkcia má na svojom vstupe textový dotaz a vracia zoznam slovníkov (list of dictionaries) ako výsledok. Syntax funkcie plpy.execute():

1
2
vysledok = plpy.execute
(<dotaz text>, [<nepovinny max. pocet riadkov>])

V nasledujúcom príklade bude premenná tst_id slúžiť ako argument vo vnútri funkcie dotaz_select(). Tento argument bude ďalej predaný podfunkcií plpy.execute().

1
2
3
4
5
6
7
8
9
10
11
12
teoria_tst=# CREATE OR REPLACE FUNCTION
dotaz_select(tst_id int)
RETURNS text
AS $$
	vysledok = plpy.execute("""
		SELECT * FROM tst_tabulka
		WHERE tst_id = '%s'""" % tst_id)
	if vysledok.nrows() > 0:
		return vysledok
	else:
		return 'Nenašli sa žiadne riadky s týmto ID'
$$ LANGUAGE plpythonu;

Spustenie funkcie v psql:

1
2
3
4
5
6
7
teoria_tst=# SELECT dotaz_select(1);
dotaz_select
-------------------------------------------------------
<PLyResult status=5 nrows=1
rows=[{'stat': 'SK', 'tst_meno': 'Rob', 'psc': '02901',
 'mesto': 	'Namestovo', 'adresa': 'Vavrecka 240', 	'rok_narodenia': 1990, 'tst_id': 1}]>
(1 row)

PL/Python funkcie s návratovou hodnotou RECORD

Pre hodnotu návratového typu záznam je možné v PL/Python použiť:

  • Sekvenciu alebo zoznam hodnôt v rovnakom poradí ako pri poliach v návratovom zázname.
  • Slovník (dictionary) s kľúčmi totožnými ako pri návratovom zázname.
  • Triedu alebo typovú inštanciu s atribútmi zhodujúcimi sa s poliami v návratovom zázname.

Nasledujúce príklady funkcií ukazujú všetky tri možné spôsoby:
Prvý za použitia inštancie:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE FUNCTION uzivatel_info(
	INOUT meno_uzivatela name,
	OUT id_uzivatela oid,
	OUT super_uzivatel boolean)
AS $$
   	class PGUzivatel:
       		def __init__(self, meno_uzivatela,
		 id_uzivatela, super_uzivatel):
           		self.meno_uzivatela =  meno_uzivatela
           		self.id_uzivatela =  id_uzivatela
           		self.super_uzivatel =  super_uzivatel
   	u = plpy.execute("""\
       		select usename,usesysid,usesuper
       		from pg_user
       		where usename = '%s'""" % meno_uzivatela)[0]
   			uzivatel = PGUzivatel(u['usename'], 					u['usesysid'], u['usesuper'])
   	return uzivatel
$$ LANGUAGE plpythonu;

Jednoduchší spôsob za použitia slovníka:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION uzivatel_info(
	INOUT meno_uzivatela NAME,
	OUT   id_uzivatela   OID,
	OUT   super_uzivatel BOOLEAN)
AS $$
u = plpy.execute("""\
	select usename,usesysid,usesuper
 	from pg_user
	where usename = '%s'""" % meno_uzivatela)[0]
return
	{'meno_uzivatela':u['usename'], 						'id_uzivatela':u['usesysid'], 						'super_uzivatel':u['usesuper']}
$$ LANGUAGE plpythonu;

A posledný za použitia n-tice (tuple):

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE FUNCTION uzivatel_info(
	INOUT meno_uzivatela NAME,
	OUT   id_uzivatela OID,
	OUT   super_uzivatel BOOLEAN)
AS $$
u = plpy.execute("""\
	select usename,usesysid,usesuper
    	from pg_user
    	where usename = '%s'""" % meno_uzivatela)[0]
return (u['usename'], u['usesysid'], u['usesuper'])
$$ LANGUAGE plpythonu;

Nula v hranatých zátvorkách na konci príkazu u = plpy.execute(…)[0] vo všetkých troch ukážkach, značí výber prvého riadku výsledku, nakoľko pl.execute aj pre jednoriadkové výsledky vráti zoznam výsledkov. V Pythone je nutné dávať pozor pri kopírovaní kódu. Jednotlivé bloky kódu by mali byť odsadené štyrmi medzerami (PEP-8). Je nutné skontrolovať správnosť odsadenia štandardne vždy, pokiaľ pri vytváraní funkcie dôjde k chybe menom „IndentationError“. Zvyčajne nemá zmysel deklarovať triedu vo vnútri funkcie iba kvôli návratovej hodnote záznam. V prvej funkcií sa nachádza hlavne pre ukážku možností jazyka Python.1

Všetky tieto definované funkcie vracajú rovnaký výsledok:

1
2
3
4
5
teoria_tst=# SELECT * FROM userinfo('postgres');
meno_uzivatela | id_uzivatela | super_uzivatel
----------+---------+--------------+--------------
postgres 	     |	       10 |  t
(1 row)

Zachytenie a spracovanie chyby

Funkcie, ktoré pristupujú do databázy môžu naraziť na chybu, ktorá môže viesť k prerušeniu a vyvolaniu výnimky. Plpy.execute dokáže vyvolať inštanciu podtriedy plpy.SPIError, ktorá štandardne ukončí chod funkcie. Túto chybu je možné zachytiť rovnako ako všetky ostatné výnimky v Pythone za použitia konštrukcie try/except.

1
2
3
4
5
6
7
8
9
10
CREATE or REPLACE FUNCTION skuska_pridania_zaznamu()
RETURNS TEXT AS $$
try:
    plpy.execute("INSERT INTO tst_tabulka
VALUES(8, 'Boďka', 1995, 'Istebne 33', 'Istebne', 		'SK', '02113');")
except plpy.SPIError:
    return "Nastala chyba"
else:
    return "Riadok bol úspešne pridaný."
$$ LANGUAGE plpythonu;

Test funkcie:

1
2
3
4
SELECT skuska_pridania_zaznamu();
	skuska_pridania_zaznamu
-------------------------
	Nastala chyba

Modul plpy ponúka tieto funkcie:

  • plpy.debug( sprava, **kwargs )
  • plpy.log( sprava, **kwargs )
  • plpy.info( sprava, **kwargs )
  • plpy.notice( sprava, **kwargs )
  • plpy.warning( sprava, **kwargs )
  • plpy.error( sprava, **kwargs )
  • plpy.fatal( sprava, **kwargs )

Funkcie plpy.error a plpy.fatal slúžia na vyvolanie výnimky v Pythone. Obe funkcie sú takmer totožné a slúžia na upozornenie či informovanie užívateľa databázy. Funkcií zadáme správu ako jej argument. Týchto argumentov môže mať funkcia niekoľko a takýto argument môže obsahovať špeciálne kľúčové slová. Tieto slová iba obohacujú chybovú správu. Je možné použiť následujúce kľúčové slová:

  • detail
  • hint
  • sqlstate
  • schema_name
  • table_name
  • column_name
  • datatype_name
  • constraint_name

Funkcia plpy.error a jej použite vo funkcií:

1
2
3
4
5
CREATE FUNCTION vyvolaj_vynimku() RETURNS void AS $$
plpy.error("Správa o výnimke",
detail="bližšie informácie",
hint="nápoveda k ladeniu")
$$ LANGUAGE plpythonu;

1
2
3
4
5
6
7
8
9
teoria_tst=# SELECT vyvolaj_vynimku();
ERROR: plpy.Error:  Správa o výnimke
DETAIL:  bližšie informácie
HINT:  nápoveda k ladeniu
CONTEXT: Traceback (most recent call last):
PL/Python function "vyvolaj_vynimku",
 line 4, in <module>
hint="nápoveda k ladeniu")
PL/Python function "vyvolaj_vynimku"

Trigger funkcie V PL/Python

Ako aj ďalšie PL jazyky, tak aj PL/Python je možné použiť pre zápis trigger funkcií. Deklarácia trigger funkcie je odlišná od bežnej funkcie iba návratovým typom RETURNS TRIGGER. Jednoduchá trigger funkcia, ktorá informuje volajúceho o spustení triggera, môže vyzerať takto:

1
2
3
4
5
CREATE OR REPLACE FUNCTION oznam_po_zavolani()
RETURNS TRIGGER
AS $$
plpy.notice('Trigger bol spustený!')
$$ LANGUAGE plpythonu;

Po vytvorení funkcie, otestujeme trigger na tabuľke tst_tabulka:

1
2
3
4
5
6
7
8
9
10
11
teoria_tst=# CREATE TRIGGER tabulka_oznam
BEFORE INSERT ON tabulka
EXECUTE PROCEDURE oznam_po_zavolani();
CREATE TRIGGER
teoria_tst=# INSERT INTO tabulka_tst
		VALUES
		(5, 'Deniska', 1995, 'Vavrecka 150',
		 'Namestovo', 'SK', '02901');
NOTICE: Trigger bol spustený!
CONTEXT: PL/Python function "oznam_po_zavolani"
INSERT 0 1

Samozrejme, predchádzajúca trigger funkcia je trochu zbytočná, ako akýkoľvek trigger, ktorý nevie kedy, a na ktorých dátach bol spustený. Tomuto predchádzajú špeciálne premenné typu slovník (dictionary) nazývané TD. V ktorých sa môžu nachádzať následujúce premenné:
TD["event"]
Meno akcie, ktorá trigger spustila, premenná obsahuje jeden z následujúcich reťazcov: INSERT, UPDATE, DELETE alebo TRUNCATE.
TD["when"]
Čas kedy sa trigger spustil (AFTER/BEFORE/INSTEAD OF)
TD["level"]
ROW alebo STATEMENT
TD["old"]
Obsahuje starý riadok (pred modifikáciou / zmazaním).
TD["new"]
Obsahuje riadok s novou verziou hodnôt (iba pre INSERT a UPDATE pri akcií DELETE je None).
TD["name"]
Meno triggera z príkazu CREATE TRIGGER.
TD["table_name"]
Meno tabuľky, nad ktorou sa trigger spúšťa.
TD["relid"]
Identifikátor objektu OID tabuľky, nad ktorou sa trigger spustil.
TD["args"]
Pokiaľ príkaz CREATE TRIGGER obsahuje argumenty, sú dostupne cez túto premennú. Každý argument má svoj index v slovníku od TD[“args”][0] po TD[“args”][n-1].

Pokiaľ je premenná TD[“when”] (“BEFORE”, “INSTEAD OF”) a TD[“level”] == „ROW“, je možné vrátiť SKIP pre prerušenie akcie. Návratová hodnota None alebo OK ukazuje, že riadok je nemodifikovaný a je v poriadku pokračovať. Hodnota None je v Pythone nastavená implicitne. V prípade že je potrebné modifikovať hodnoty v TD[“new”] a chceme aby PostgreSQL pokračoval s týmito modifikovanými hodnotami, môžeme použiť návratovú premennú MODIFY, ktorá jazyku PL/python naznačí, že dáta sú pozmenené. Čo je možne iba v prípade, ak hodnota premennej TD[“event”] je INSERT alebo UPDATE, inak bude návratová hodnota ignorovaná.

Špeciálne premenné vo funkcií

Nasledujúca trigger funkcia je užitočná pri vytváraný triggerov, vďaka nej je možné jednoducho a prehľadne vypísať všetky tieto premenné. Čo uvítame hlavne vo fáze ladenia funkcií.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER
AS $$
import pprint
pretty _data = pprint.pformat(
	(
		('TD["table_schema"]'
		, TD["table_schema"] ),
		('TD["event"]'
		, TD["event"] ),
		('TD["when"]'
		, TD["when"] ),
		('TD["level"]'
		, TD["level"] ),
		('TD["old"]'
		, TD["old"] ),
		('TD["new"]'
		, TD["new"] ),
		('TD["name"]'
		, TD["name"] ),
		('TD["table_name"]'
		, TD["table_name"] ),
		('TD["relid"]'
		, TD["relid"] ),
		('TD["args"]'
		, TD["args"] ),
	)
)
plpy.notice('Obsah premenných:\n' + pretty _data)
$$ LANGUAGE plpythonu;

Trigger nastavíme aby spustil funkciu po DML operácií nad tabuľkou tst_tabulka :

1
2
3
4
CREATE TRIGGER test_explore_trigger
AFTER INSERT OR UPDATE OR DELETE ON tabulka_tst
FOR EACH ROW
EXECUTE PROCEDURE debug_trigger('jeden', 2, null);

Trigger sa aktivuje napríklad akciou INSERT:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
teoria_tst=# INSERT INTO tst_tabulka
		VALUES (6, 'Pišta', 1990, 'Vavrecka 240', '
				Vranov nad Topľou', 'SK', '02103');
NOTICE:  Obsah premenných:
(('TD["table_schema"]', 'public'),
('TD["event"]', 'INSERT'),
('TD["when"]', 'AFTER'),
('TD["level"]', 'ROW'),
('TD["old"]', None),
('TD["new"]',
{'adresa': 'Pod Topľou', 'mesto': 'Vranov nad Topľov', 	'psc': '02103',
'rok_narodenia': 1994, 'stat': 'SK', 'tst_id': 7, 	'tst_meno': 'Dano'}),
('TD["name"]', 'test_explore_trigger'),
('TD["table_name"]', 'tst_tabulka'),
('TD["relid"]', '16585'),
('TD["args"]', ['jeden', '2', 'null']))
CONTEXT: PL/Python function "debug_trigger"
INSERT 0 1

Za povšimnutie stojí fakt, že hodnoty premennej TD[“args”], ktorá obsahuje argumenty predané funkcií v príkaze CREATE TRIGGER, sú všetky prevedené na typ reťazec a to dokonca aj NULL. Vždy pri programovaní vlastných zložitejších trigger funkcií pridáme aj túto ladiacu (debug) funkciu.

Zdroje:

http://www.sallyx.org/sally/psql/triggery.php?showAdds=0;