PostgreSQL - Příspěvky [cs]

Automatic execution plan caching in PL/pgSQL

2007-12-30T20:03:24Z

194.255.108.253:

[[category:Articles]]
Translate by Hana Kabilková

Few years ago, when I wrote article about PL/pgSQL I found in documentation recommendation to not use string 'now' in this language. I accepted this, I tried it and it didn't work, but I realy didn't know why. I thought that it was because of bad transtation to byte-code of actually pseudofunction value, which made this problem (time returned of string 'now' agreed with function translation).
<pre>
INSERT INTO (..) VALUES('now') -- don't use with plpgsql!
</pre>
Here I first deviate. Lexical and syntactic analysis PL/pgSQL of functions is making only once, in first call function within the scope of login. The result is syntactic tree which is saves in session cache. Something like translation to byte-code in PL/pgSQL doesn't exist, syntactic tree is input for interpreter, it means that PL/pgSQL is classic interpreter which stands on lex, yacc generator. Power PL/pgSQL isn't in speed but in his accouplement with SQL. Because of this accouplement implementation PL/pgSQL can be as easy as it doesn't contain even simple evaluation of expressions. Everithing possible is send to interpreter SQL. Interpreter PL/pgSQL resolves only variables and operative construction.

In saving SQL term in PL/pgSQL is using type PLpqSQL_expr(plpgsql.h). For another description are important only array char *query (contains text sql statement) and void *plan (indicator on cach execute plan SQL statement). Execute plan is generating only once, in first requirement evaluation SQL expression.
<pre>
if (expr->plan == NULL)
exec_prepare_plan(estate, expr)
....
rc = SPI_execute_plan(expr->plan, ...
</pre>
Caching is important. In easier expression generating of plan can be longer than expression evaluation. Measure difference between first and second start of PL/pgSQL function. You can write this time on score now generating execute plans. Caching solved problem with effective executing PL/pgSQL function and gave us two problems: Caching execute plans aren't shared and persistanced too (star application are slower, increasing of memory usage (resolution is f.e. pgool), caching execute plans can be sometimes not adequate and their executing makes run-time error.

When the plan is ready, it's save in cache and there it can be changed until log off or compilation on function. In PostgreSQL there is no attribute of analogy attribute WITH RECOMPILE MSSQL. Fortunately wrong executing plans errors are exception and only because of two reasons.

The first reason is change of database structure - when we canceled some of data objects (table, sequence) which was used in function, after the first called, the following called of function ends with error. The resolution isn't new object with the same name and type, because new object get new (different) oid (object identificator). Nobody think about canceled tabel during the operation, but every body want canceled temporary table - and because of this, is this problem bonded in ToDo with temporary tables. The resolution is all of temporary tables makes befor first called PL/pgSQL function and then not canceled, only clean. (Sometimes it trepan, especially when you convered from MSSQL to PostgresSQL, where the mechanism of transfering recordsets of procedurs is very different).

We can make temporary tables in the body of function because executing plan saves into the cache in the first moment of using the objet, not in the time of translation.
<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>

The second reason is dynamic questions. Their executing plan isn't caching, but their result can make error on difrent place.
<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>

Start of function ends with error (_v := _r._x;) in second passing of cycle FOR _i IN .. ERROR: type of "_r._x" does not match that when preparing the plan.

Why? Command of assigning contains SQL expression (every expression in PL/pgSQL is SQL expression). In first interace it makes executing plan, which supposed that value_r._x is integer, in second interace is type varchar, so the plan in cach is not adequate. The resolution is have as many assigning expressions as many possible executing plans combinations. At the firs look absurd code of function is right.

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

I tell you that this behavior of PL/pgSQL is free for me. I know reason of the problem and I can set up for that. But definitive resolution (regeneration of plan when the error was detected) can be make. I tried macras PG_TRY(), PG_CATCH() and PG_END_TRY() for fixation of errorand regenerated wrong plan without loss of speed. It's only the question of time, when someone catch this resolution and make patch. Probablty not in version 8.1.

Second divagation : to show where is the problem with 'now' I have to mention proces of generation executing plan SQL expression.

One of a period of preparing the plan is reduction constants and simplification functions (f.e. 2+2=4, True Or whatever=True, alternative immutable functions with constant argument of function consequence - and this is 'now' case (backend/optimizer/util/clause.c - evaluate_function()), alternative value NULL STRICT function, if some of their arguments is NULL, etc.

In our case was 'now' replaced by argument which was saved in executing plan and it was still same repeated evaluated. Normaly it don't have to make some problems. Expect PL/pgSQL PostgreSQL doesn't contain any tools which can cache executing plans. Exception was PL/pgSQL, where this problem was first detected (and we still have to be carefull about this).

I can't remember if I ever used 'now'. I automatically use magic parameters CURENT_DATE a CURRENT_TIMESTAMP which haven't got problem with cache. And if I only for nostalgia want to use 'now' , i have to do this only with parameter.

<pre>
DECLARE d date; BEGIN d := 'now'; INSERT INTO (..) VALUES(d); ...
</pre>
Why? For function with parameter is optimizer so short ( here function datein()). PL/pgSQL doesn't do something like optimalization, so it can't detected that it he is the parameter, and string 'now' will have to retype on corresponding type only in time of evaluating expression and consequence will be corresponding time of evaluating expression.

Diskuse:Santiago

2007-12-28T08:10:55Z

194.255.108.253: /* pozdrav */

Ahoj Pavle pelegrinos!

do Santiaga jdu za týden, vyrážím ze severozápadního Slezska, z Jesenicka.

Díky za postřehy na Tvé stránce. Budou se mi možná hodit. Pokud bych měl na tebe e-mail, napsal bych Ti to osobně.

S pozdravem
Tom Hradil
thradil@jeseniky.org
tel: 603 231 624

:: Zdarec, kontakt na mne je na [[Pavel Stěhule]]

== pozdrav ==

Pavle,
díky za poznámky!
Vyrazila jsem letos ze Saint Jean a až za tři roky dokončím svá študýrování, chtěla bych jít od nás z Moravy.Máš moc pěkné fotky!
Bylo to ... krásné,že? Spoustu věcí jsem zapomněla a postupně se v určitých okamžicích vynořují, asi abych zůstala vděčná.
Jinak nemůžu zapomenout na oči poutníků.
Radost při putování, které začalo po návratu a všem potenciálním poutníkům odvahu vydat se na cestu!
L.
fangaia@seznam.cz
:: Díky, Jo, Camino je druhej svět a vůbec putování. Teď chodím po republice, už jsem přešel Čechy i Moravu z Jablůnkova do Aše, a stojí to taky za to. Jen by to chtělo nějaký refídže. Měj se hezky a dobrý rok přeji.

Postgres Informix

2007-12-15T09:17:42Z

194.255.108.253:

Na otázku, zda PostgreSQL může nahradit Informix odpověděl Jeff Larsen. Dovolil jsem si jeho mail přeložit a citovat. Originál je k dohledání v archivu konference.
<pre>
* From: "Jeff Larsen" <jlar310 ( at ) gmail ( dot ) com>
* To: Chad ( dot ) Hendren ( at ) sun ( dot ) com
* Subject: Re: PostgresSQL vs. Informix
* Date: Wed, 28 Nov 2007 17:11:00 -0600

Používáme Informix a aktuálně jsem pracoval na studii proveditelnosti přechodu
na PostgreSQL. Ověřil jsem si kvalitu dokumentace Postgresu a úroveň podpory
mailing listech.

Obě databáze mají své pro a proti. Jelikož mám PostgreSQL na jiném hardware,
nelze jednoduše porovnat výkon, ale myslím si, že Informix je rychlejší a má
robustnější plánovač. To jest, dělá to co má, aniž by potřeboval pomoc v takových
věcech jako je přetypování. Řekl bych, že Postgres je na 75 až 80% rychlosti
Informixu. Nechci začínat flame. Je to můj odhad, a jsem si jistý, že se stejným
hardwarem a s trochou ladění by byl výkon Postresql lepší.

Co je na Informixu vynikající je jeho plně synchronní replikace, kde potvrzení
transakce na primárním serveru garantuje potvrzení transakce na sekundárním
serveru. Vysoká dostupnost je pro nás kritická, a co je mi známo, toto je slabinou
PostgreSQL. Jistě, Postgres podporuje replikaci, ale tato podpora ještě není vyzrálá
pro enterprise prostředí.

Informix má podstatně pokročilejší online zálohování, ne nepodobné PITR zálohování
Postgresu, které ovšem není tak závislé na nízkoúrovňových systémových nástrojích.
Dále podporuje inkrementální backup, takže nemusíme provádět pokaždé dump celé
databáze. Informix nabízí ještě další zálohovací utilitu onbar, ale s tou nemám
žádné zkušenosti, takže ji nebudu komentovat.

Co hovoří pro Postgres. Cena, jasně. Miluji, když mohu ušetřit sto tisíc dolarů.
PostgreSQL více respektuje SQL standardy, takže je snažší portovat aplikace. Postgres
má také některé lepší vestavěné funkce a větší možnosti indexování. PostgreSQL má
širší nabídku jazyků pro uložené procedury (python, perl), a konečně PostgresSQL
umožňuje dědičnost tabulek, což Informix neumožňuje.

Určitě nejpůsobivější věcí na Postgresu jsou jeho konference (mailing lists). Podpora
Informixu je v pohodě, nicméně trubci na zákaznické podpoře nejsou vývojáři, kteří
vědí o co jde. Zatraceně, vývojáři Postgresu odpověděli na mé otázky přímo a to ještě
o víkendu. Nevím kolik by stála podpora za přímý přístup k těm nejvyšším guru.

Rád bych, kdyby moje odpověď mohla být důkladnější. Jsem si jistý, že jsem zapomněl
zmínit řadu suprových věcí na Postgresu, ale jak už jsem řekl, moje hodnocení
je pouze orientační. Právě řešíme, zda-li přejít, jako firma, na Postgres. Používáme
open source a uvítal bych, kdybychom používali i PostgreSQL.

Jeff
</pre>

Diskuse:Aktuality

2007-12-15T07:45:15Z

194.255.108.253: nova verze, nova diskuze

Features83

2007-12-12T21:07:16Z

194.255.108.253: /* Výkon ve Windows */

=PostgreSQL 8.3 Feature List=
Tento seznam obsahuje popis většiny, nikoliv všech, nových funkcí verze 8.3. Pro přehlednost je tento popis rozčleněn do několika skupin podle účelu. Podrobnější informace jsou v dokumentaci PostgreSQL a v poznámkách k verzi (Release Notes). Skutečně kompaktní informace najdete v tabulce podporovaných funkcí (feature matrix), která je ovšem pouze v anglickém jazyce.

==Výkon==
===Konzistentní výkon===
Tyto funkce zvyšují schopnost PostgreSQL dodržet konzistentní časy odezvy bez ohledu na zatížení serveru.
;HOT
:Heap Only Tuple (HOT) dramaticky snižuje problémy s údržbou databáze spojené s častými změnami dat, snižuje nutnost spouštění VACUUM a poskytuje znatelně vyšší výkon některých aplikací.
;Asynchronní potvrzování
:V tomto režimu COMMIT nečeká na potvrzení fyzického zápisu na disk. Za cenu rizika potenciální ztráty posledních transakcí, v případě výpadku systému, získáme lepší dobu odezvy. Přesto je jištěna integrita dat. Tento režim je určen pro operace, které lze bezpečně opakovat .. např. import databáze, import dat a je lokální (vztahuje se pouze na ty uživatele, kteří se do tohoto režimu přepnou).
;Vyrovnání zátěže způsobené checkpointy
:V případě, že je server silně vytížen, dojde k pozdržení checkpointů a k utlumení jejich frekvence. Tím se snižuje negativní vliv checkpointů na dobu odezvy.
;Strategie Just-in-time zápisu na disk
:Na základě statistik a aktuální aktivity je dynamicky odhadnuto, kolik čistých paměťových stránek bude potřeba a kolik jich bude nutné vyčistit (zapsat na disk).

===Zrychlení===
Díky řadě úprav se dosáhlo citelného zrychlení některých operací.
;Zkrácení času obnovy
:Nutný čas pro obnovu databáze z Write Ahead logu se zkrátil redukcí počtu nutných I/O operací.
;Cyklický buffer v meziúložišti řádků
:Dramaticky urychluje menší spojení tabulek typu merge join tím, že lépe využívá paměť a nedochází tak k zápisům na disk.
;Rychlejší porovnávání v LIKE/ILIKE
:Zrychlila se operace částečného porovnání (partial match) zejména v případech, kdy se používá vícebajtové kódování.
;Top-N řazení
:Dramaticky zrychluje řazení v případě, že výsledek dotazu je omezen klauzulí LIMIT.
;Opožděné přidělování XID (Lazy XID Assignment)
:Znatelně zvyšuje výkon těch databází, kde výrazně převažuje čtení nad zápisem (případně vůbec nedochází k zápisu) a to tak, že identifikační číslo transakce přiřadí pouze v případě potřeby (dojde k změně dat).
;Možnost určení ceny funkce
:Nám dovolí připojit ke každé funkci odhad náročnosti funkce (cena) a počtu vrácených řádků, což v důsledku znamená reálnější (lepší) prováděcí plán.

===Rozsáhlé databáze===
Nasledující funkce dovolí uživatelům provozovat i poměrně velké datové sklady v PostgreSQL.
;Synchronizované čtení
:V originále tzv. piggybacking table scan, je synchronizované souběžné čtení jedné tabulky více uživateli (jeden přečtený blok se pošle více uživatelům). Tato technika významně redukuje celkovou potřebu IO operací (pozn. překladatele - piggyback se používá spolu se slovem jízda ve smyslu jízdy na nárazníku, rámu (přeneseně jízdy načerno nebo zadarmo). V tomto případě se data načítají z disku pro jednoho uživatele a okamžitě distribuují všem dalším uživatelům, kteří je v tu chvíli také potřebují.
;Ochrana L2 Cache
:Nově optimalizovaný kód snižuje četnost zahození obsahu cache CPU, které způsobuje zpomalení současně zpracovávaných dotazů.
;Zkrácení délky hlavičky typu Varlena
:Úpravou formátu datového typu Varlena, který se v PostgreSQL používá pro uložení všech hodnot s proměnnou velikostí, dochází ke snížení velikosti databáze zhruba o 20%.

===Výkon ve Windows===
Nemůžeme zapomenout na naše uživatele, kteří používají Windows. PostgreSQL 8.3 posouvá Windows do první ligy podporovaných platforem.
;Podpora MS Visual C++
:PostgreSQL lze přeložit nejen v MinGW (vývojové prostředí umožňující snažší portování aplikací z OS typu Unix), ale i v Microsoft Visual C++. Použití překladače fy. Microsoft by mělo zvýšit výkon a stabilitu na platformách této firmy.
;Přepracování startovního kódu serveru
:Drasticky snižuje spotřebu paměti procesu postmaster, což umožňuje paralelní běh více obslužných procesů (kteří obslouží více klientů).

==Administrace==
Administrace PostgreSQL je jednodušší než u proprietárních databází, nicméně vždy je další prostor pro zlepšení. PostgreSQL obsahuje řadu nových funkcí zjednodušujících správu a poskytujících administrátorovi databáze podrobnější a obsáhlejší diagnostiku.
;CSV Log Output
Volitelně lze zapisovat do logu v CSV formátu, což zjednodušuje tvorbu nástrojů na analýzu výkonu a ad-hoc auditů.
;Podpora SSPI GSSAPI
:Podpora autentifikačního systému Kerberos v PostgreSQL byla rozšířena o možnost použít bezpečnostní API: SSPI je průmyslový standard na platformě Windows a GSSAPI je průmyslový standard v prostředí Unix a Linux. Podpora těchto API by měla zjednodušit integraci uvnitř velkých vnitropodnikových sítí.
;Lokální nastavení systémových proměnných
:Pro každou uživatelskou funkci lze určit specifické nastavení systémových proměnných. Kromě jiného se tím řeší bezpečnostní problém s přenastavením systémové proměnné search_path.
;Více procesové autovacuum
:Umožňuje paralelní běh servisního procesu, díky čemuž je autovacuum použitelné i v aplikacích s tisíci tabulkami.
;pgStandby
:Administrativní rutina zjednodušující provoz serveru ve Warm Standby režimu.
;ORDER BY Nulls First/Last
:Umožňuje vytvářet indexy, kde jsou řádky obsahující NULL umístěny na začátek nebo na konec indexu.

==Vývoj==
===Vývoj aplikací===
Díky celé řady úprav se PostgreSQL 8.3 může měřit s nejlepšími proprietárními databázemi v podpoře komplexních více vrstvých databázových aplikací.
;Fulltextové vyhledávání
:TSearch2 byl plně integrován do kódu jádra. Také došlo k pročištění API. Díky tomu se fulltext jednodušeji používá a snáze rozšiřuje o podporu nových jazyků, slovníků a systémů určujících relevanci.
;Odstranění neplatných plánů
:Uložené prováděcí plány mohou být odstraněny jak samotnou aplikací, tak automaticky, když jsou tabulky aktualizovány.
;Editovatelné kurzory (Updatable Cursors)
:Kurzory nyní podporují WHERE CURRENT OF, což umožňuje flexibilnější návrh aplikací postavených na použití kurzorů.

<h3>Nové datové typy</h3>
;Typ XML
:Typ XML plně podporuje standard SQL/XML, který je součástí ANSI SQL:2003 včetně kontroly zápisu, typově bezpečných operací, funkcí generujících XML a XPath dotazů. Verze 8.3 obsahuje ještě další funkce umožňující export v XML.
;Typ UUID
:Typ UUID nese 128 bitovou unifikovaný (globální) jednoznačný identifikátor, který se uplatní zejména v distribuovaných aplikacích.
;Pole hodnot kompozitního typu
:Pole nyní může být vytvořeno pro kompozitní (složené typy), které obsahují více sloupců v jedné hodnotě, jako je typ tabulka nebo zákaznický typ.
;Výčtový typ
:Výčtový typ je určen seřazeným seznamem alternativních hodnot. Díky tomuto typu bude snažší migrace z MySQL do PostgreSQL.

<h3>Uložené procedury</h3>
Tyto dvě nové funkce zvyšují použitelnost PL/pgSQL, což je náš nejpopulárnější jazyk pro tvorbu uložených procedur v PostgreSQL.
;RETURN QUERY
:Nyní lze v PL/pgSQL ještě jednodušeji vrátit výsledek dotazu v SRF funkci.
; Posuvné kurzory (Scrollable Cursors)
:PL/pgSQL nyní také podporuje posuvné kurzory, umožňující v PL/pgSQL procedurách dynamický pohyb kurzoru (vpřed i vzad).

==Příslušenství==
Řada důležitých funkcí není podporována v základním kódu. Důvodem je snaha o udržení co nejmenšího jádra databáze, které se snáze udržuje. Existuje několik stovek volitelných doplňků, s kterými PostgreSQL umožňuje replikace, podporuje vysokou dostupnost, použití dalších programovacích jazyků, integraci aplikací a získává i některé další experimentální možnosti. Většina těchto doplňků je volně ke stažení z archivu [http://www.pgfoundry.org pgFoundry]. Část aplikací(modulů) z následujícího seznamu je již přepravena pro 8.3. Část modulů s 8.3 spolupracuje, ale ještě nevyužívá všechny možnosti, které jsou v 8.3. Tyto moduly se budou v následujícím období aktualizovat.

;[https://developer.skype.com/SkypeGarage/DbProjects/pgbouncer pgBouncer]
:Tento více vláknový connection pooler umožňuje jedné PostgreSQL databázi udržovat až 100,000 spojení mezi aplikacemi a serverem.
;[https://developer.skype.com/SkypeGarage/DbProjects/PlProxy PL/Proxy]
:Interface pro distribuované horizontálně separované tabulky.
;[http://pgsnmpd.projects.postgresql.org/ pgSNMP]
:Standardní SNMP ovladač pro PostgreSQL zjednodušující monitorování serverů v síti.
;[http://code.google.com/p/sepgsql/downloads/list SEpgsql]
:Bezpečnostní doplněk postavený nad modelem a metodikou SELinuxu, který dovoluje aplikovat unifikovaný postup SELinuxu jak na OS tak na DBMS.
;[http://pgfoundry.org/projects/edb-debugger/ PL/pgSQL Debugger]
:Nový grafický nástroj podporující interaktivní ladění a krokování PL/pgSQL procedur.
;[http://pgfoundry.org/projects/pgpool/ pgPoolII]
:PgPoolII staví na úspěchu předchozí verze. Pomocí inteligentní replikace dotazů umožňuje datový partitioning.
;[http://bucardo.org/ Bucardo]
:V případě PostgreSQL první dostupný systém podporující multi-master asynchronní replikaci.
;[http://www.postgresql.at/english/pr_cybercluster_e.html CyberCluster]
:Nově otevřený open-source projekt integrující a rozšiřující několik stávajících nástrojů pro clustering, jako je pgCluster nebo pgPool.
;[http://www.slony.info/ Slony-I]
:Druhá verze Slony-I, našeho nejpopulárnějšího replikačního systému, nyní používá nové replikační API v PostgreSQL 8.3.

Automatická kontrola rodného čísla

2007-12-09T17:41:51Z

194.255.108.253: doplneni vyjimky

Profesionální programování je více-méně o tvorbě knihoven. Programátor, ktrerý
staví stále na zelené louce nebude těžko bude produktivní. Klasické jazyky podporují
vytváření knihoven a modulů. K dispozici je dostatek kvalitních knihoven, kterými
se můžeme inspirovat při tvorbě vlastních knihoven.

To bohužel neplatí u programování v prostředí uložených procedur. Zatím se nijak zvlášť
oproti ostatním neprosadil žádný koncept. Jednou z možností, jak organizovat vlastní
knihovny je vytváření vlastních "inteligentních" datových typů. Jelikož PostgreSQL
vzniklo v akademickém prostředí, které je co se týče potřeby ukládání specializovaných
dat podstatně pestřejší a náročnější než ekonomicko-finanční sféra (na kterou bylo
původně SQL cílené), PostgreSQL od samotného svého počátku disponovalo možností vytváření
vlastních datových formátů. V klasickém SQL je příliš úzký rejstřík datových typů, v
čehož důsledku musíme používat obecné typy TEXT nebo BLOB. Díky jejich obecnosti dokážeme
do nich uložit libovolnou textovou nebo binární hodnotu, ale nic víc. Tyto typy neposkytují
žádné další (specializované) operace.

Vlastní datový typ v PostgreSQL může být implementován pouze v jazycích C nebo Java.
Kromě skutečných datových typů ještě existují složené datové typy (obdoba typu RECORD v
Pascalu). Kromě vlastních binárních datových typů můžeme definovat vlastní doménové
typy. Následující příklad obsahuje podporu rodného čísla právě pomocí domén.

Rodné číslo je hodnota s celkem dost vysokou redundanci, zvlášť když máme k dispozici
datum narozeni a pohlaví osoby. Ve formuláři nedokáže zabránit záměrně falšované hodnotě,
ale s celkem s vysokou pravděpodobností dokaze zachytit překlepy. Vzhledem
k tomu, že obsahuje citlivé udaje (věk, pohlaví) předpokládá se, že bude v dohledné době
nahrazen neutrálním jedinečným identifikátorem osob (uvažuje se o číslu sociálního
zabezpečení).
<pre>
CREATE OR REPLACE FUNCTION check_form_rodne_cislo(varchar, boolean)
RETURNS boolean AS $$
DECLARE
str_parts varchar[];
num_parts integer[];
after_1953 boolean;
birthday_str varchar;
BEGIN
SELECT INTO str_parts regexp_matches
FROM regexp_matches($1, E'^(\\d{2})(\\d{2})(\\d{2})(/)?(\\d{3,4})$');
IF FOUND THEN
-- Test existence lomitka v rodnem cisle rizeny druhym parametrem,
-- ve vyznamu NULL (nema vyznam), true - povinne, false - nepovinne.
IF $2 <> (str_parts[4] IS NOT NULL) THEN
RETURN false;
END IF;
-- Test modulo 11 se provadi pouze tehdy, pokud existuje
-- kontrolni cislice, tj. pocinaje rokem 1954.
after_1953 := char_length(str_parts[5]) = 4;
IF after_1953
AND to_number($1, CASE WHEN str_parts[4] IS NULL
THEN '9999999999'
ELSE '999999/9999' END) % 11 <> 0 THEN
-- Nicmene z tohoto pravidla existuje vyjimka cca 1000
-- rodnych cisel, kdy modulo 9 mistneho cisla je rovno 10
-- a kontrolni cislice je 0
IF NOT (to_number($1, CASE WHEN str_parts[4] IS NULL
THEN '999999999'
ELSE '999999/999' END) % 11 = 10
AND to_number($1, CASE WHEN str_parts[4] IS NULL
THEN '?????????9'
ELSE '??????/???9' END) = 0) THEN
RETURN false;
END IF;
END IF;
-- Kontrola validniho datumu, po roce 2004 se mohou
-- k mesici pricitat hodnoty 20 (pro muze) a 70 pro zeny.
num_parts := ARRAY[to_number(str_parts[1],'99'),
to_number(str_parts[2],'99'),
to_number(str_parts[3],'99')];
num_parts := ARRAY[CASE WHEN NOT after_1953
THEN 1900 + num_parts[1]
WHEN after_1953 AND num_parts[1] >= 54
THEN 1900 + num_parts[1]
ELSE 2000 + num_parts[1] END,
CASE WHEN num_parts[2] > 70
THEN num_parts[2] - 70
WHEN num_parts[2] > 50
THEN num_parts[2] - 50
WHEN num_parts[2] > 20
THEN num_parts[2] - 20
ELSE num_parts[2] END,
num_parts[3]];
birthday_str := replace(to_char(num_parts[1],'9999')
|| to_char(num_parts[2],'09')
|| to_char(num_parts[3],'09'),
' ','');
RETURN birthday_str = to_char(to_date(birthday_str, 'YYYYMMDD'),
'YYYYMMDD');
END IF;
RETURN false;
END;
$$ LANGUAGE plpgsql;
</pre>

V případě binárního vlastního typu potřebujeme ještě konverzní funkce z
typu rc, rc1 a rc2 do typů date a boolean, které použijeme v definici
přetypování (CREATE CAST). Nic takového pro domény neexistuje. Nicméně mohu
napsat funkce to_date a to_boolean a ty používat. Aby nedocházelo k opakování
kódu, existuje implicitní přetypování rc1->rc a rc2->rc, což je korektní
(rc je obecnější než rc1 nebo rc2). Funkce to_bool a to_date musím volat
explicitně.
<pre>
CREATE DOMAIN rc VARCHAR CHECK (check_form_rodne_cislo(value, null::boolean));

-- Predpokladam validni vstup (overeny funkci check_form_rodne_cislo).
-- Typ parametru varchar je zvolen proto, protoze je predkem domen rc,
-- rc1 a rc2. Domeny se lisi vztahem vuci lomitku uvnitr rodneho cisla.
CREATE OR REPLACE FUNCTION to_date(rc)
RETURNS date AS $$
DECLARE
str_parts varchar[];
num_parts integer[];
after_1953 boolean;
birthday_str varchar;
BEGIN
SELECT INTO str_parts regexp_matches
FROM regexp_matches($1, E'^(\\d{2})(\\d{2})(\\d{2})(/)?(\\d{3,4})$');
IF FOUND THEN
after_1953 := char_length(str_parts[5]) = 4;
-- Po roce 2004 se mohou k mesici pricitat
-- hodnoty 20 (pro muze) a 70 pro zeny. Pred timto rokem
-- se vzdy pricita 50 pro zeny
num_parts := ARRAY[to_number(str_parts[1],'99'),
to_number(str_parts[2],'99'),
to_number(str_parts[3],'99')];
num_parts := ARRAY[CASE WHEN NOT after_1953
THEN 1900 + num_parts[1]
WHEN after_1953 AND num_parts[1] >= 54
THEN 1900 + num_parts[1]
ELSE 2000 + num_parts[1] END,
CASE WHEN num_parts[2] > 70
THEN num_parts[2] - 70
WHEN num_parts[2] > 50
THEN num_parts[2] - 50
WHEN num_parts[2] > 20
THEN num_parts[2] - 20
ELSE num_parts[2] END,
num_parts[3]];
RETURN to_date(
replace(to_char(num_parts[1],'9999')
|| to_char(num_parts[2],'09')
|| to_char(num_parts[3],'09'),
' ',''),
'YYYYMMDD');
END IF;
RAISE NOTICE 'Incorrect format of type rodne cislo (value: %)', $1;
END;
$$ LANGUAGE plpgsql;

-- Predpokladam validni vstup (overeny funkci check_form_rodne_cislo).
-- Typ parametru varchar je zvolen proto, protoze je predkem domen rc,
-- rc1 a rc2. Domeny se lisi vztahem vuci lomitku uvnitr rodneho cisla.
CREATE OR REPLACE FUNCTION to_bool(rc)
RETURNS boolean AS $$
DECLARE
str_part varchar[];
birthday_mm integer;
BEGIN
SELECT INTO str_part regexp_matches
FROM regexp_matches($1, E'^\\d{2}(\\d{2})\\d{2}/?\\d{3,4}$');
IF FOUND THEN
birthday_mm := to_number(str_part[1],'09');
RETURN CASE WHEN birthday_mm > 50
THEN false
ELSE true END;
END IF;
RAISE NOTICE 'Incorrect format of type rodne cislo (value: %)', $1;
END;
$$ LANGUAGE plpgsql;

-- Registrace domen
CREATE DOMAIN rc1 VARCHAR CHECK (check_form_rodne_cislo(value, false));
CREATE DOMAIN rc2 VARCHAR CHECK (check_form_rodne_cislo(value, true));

-- vytvoreni konverznich pravidel, rc je zevseobecnenim rc1 a rc2
CREATE CAST (rc1 AS rc) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (rc2 AS rc) WITHOUT FUNCTION AS IMPLICIT;
</pre>

Použití
<pre>
CREATE TABLE foo (r rc1);
INSERT INTO foo('7307--/----');
-- ziskani narozeni z udaju v db
SELECT to_date(r)
FROM foo;
</pre>

Příručka SQL/PSM

2007-12-09T09:47:58Z

194.255.108.253: /* Úvod do SQL/PSM (PL/pgPSM) */

==Ú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 [http://www.dbmsmag.com/9701d06.html 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.
<pre>
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);
</pre>
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.
<pre>
-- 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;
</pre>

== 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|složený příkaz]], který umožňuje zadat libovolně dlouhou syntakticky správnou posloupnost PL/pgPSM příkazů.
<pre>
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;
</pre>

K dispozici jsou následující příkazy:
* [[SQL příkaz#SQL příkaz|SQL příkaz]],
* [[Složený příkaz|složený příkaz]] - mezi dvojicí BEGIN a END je libovolný počet PL/pgPSM příkazů oddělených středníkem,
* [[Příkazy CALL a RETURN|Volání a ukončení procedury]] - příkazy CALL a RETURN,
* [[Přiřazovací příkaz|přiřazovací příkaz]] - SET proměnná = hodnota,
* [[Podmíněné provádění příkazů]]:
** [[Podmíněné provádění příkazů#Příkaz IF|IF ELSEIF ELSE END IF]],
** [[Podmíněné provádění příkazů#Příkaz CASE|CASE WHEN THEN END CASE]].
* [[Příkazy cyklu|Příkazy cyklu]]
** [[Příkazy cyklu#Příkaz LOOP|LOOP co END LOOP]],
** [[Příkazy cyklu#Příkaz WHILE|WHILE podmínka DO co END WHILE]],
** [[Příkazy cyklu#Příkaz REPEAT|REPEAT co UNTIL podmínka END REPEAT]],
** [[Příkazy cyklu#Příkaz FOR|FOR select DO co END FOR]] (iterace napříč výsledkem dotazu).
* [[signalizace chyb]] - SIGNAL a RESIGNAL
* [[Příkaz PRINT|zobrazení výsledku na konzoli]] - PRINT (nestandardní konstrukce),
* [[Příkazy cyklu#Příkazy LEAVE a ITERATE|opuštění a nová iterace cyklu]] - příkazy LEAVE a ITERATE,
* [[Dynamické SQL|dynamické SQL]] - EXECUTE a EXECUTE IMMEDIATE,
* [[Příkaz GET DIAGNOSTICS]] - získání diagnostických údajů.

Ještě před samotným popisem PL/pgPSM příkazů si projděte následující jednoduché ukázky:
<pre>
-- 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;
</pre>
=== Použití jazyka PL/pgPSM ===
* [[SQL příkaz#Použití kurzorů|Použití kurzorů]]
* [[SQL příkaz#Funkce vracející tabulky|Funkce vracející tabulky]]
* [[Složený příkaz#Ošetření chyb|Ošetření chyb, signalizace chyby (výjimky) a zachycení signálu]]
* [[Použití dočasných tabulek v 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.
<pre>
-- 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;
</pre>
Jedná se o skutečně záludnout chybu (a poměrně častou). Přeložený kód výpadá následovně:
<pre>
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)
</pre>
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é [[Použití dočasných tabulek v PL/pgPSM|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ů.
<pre>
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;
</pre>
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 [[zdrojový kód procedury generující fraktál pro PostgreSQL|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.

Kešování výsledku funkcí v PL/Perl

2007-10-30T21:46:27Z

194.255.108.253:

Žádné jiné aplikace nedokáží vygenerovat tak ohromnou zátěž jako www aplikace. Je to dáno jednak vlastnostmi protokolu HTTP, jednak dostupností www aplikací. Neoptimálně napsané aplikace dokáží položit libovolnou databázi na libovolném hardware. Naopak dobře navržené aplikace si vystačí i s levným a na slušném vybavení poskytují dostatečnou rezervu výkonu jako ochrany před špičkovým zatížením. V prvé řadě jde o minimalizaci počtu dotazů do databáze. Zejména těch pomalých (to jsou už dotazy nad 50ms). Řešením je intenzivní využívání cache. Všechno co lze, je třeba umístit do cache. Příklad z praxe. Prodejní web zaměřený na literaturu obsahoval na každé stránce žebříček nejprodávanějších knih. Bez použití cache se při každm vykreslení stránky pustil dotaz obsahující sekvenční čtení tabulek prodeje a knih doplnění požadavkem na třídění. Tento relativně jednoduchý dotaz (trvající cca 20ms) v reálném provozu dokonale vyřadil celý server z provozu. V jednu chvíli jej spouštělo 500-1000 uživatelů. Proto se používají techniky, které uživatele do jisté míry šidí. Jen ve výjimečných případech musí všichni uživatelé vidět aktuální data. Zrovna u žebříčku prodejnosti bohatě stačí aktualizovat data několikrát denně. Pětková verze PHP obsahují nástroje, kterými lze vybudovat a udržovat datovou cache. Ty se použily také v případě elektronického knihkupectví s celkem očekávaným efektem. Místo nejnovějšího hw se mohl bezproblémově použít několik let starý server.

Datové cache můžeme používat i na databázové úrovni. První obvyklé řešení jsou materializované pohledy aktualizované triggery. Jedná se o relativně jednoduché řešení, které udržuje agregovaná data 100% aktuální. To relativně je ovšem na místě. Napsat opravdu robustní řešení není až tak snadné, a k tomu ještě v řadě případů potřebujeme explicitní zamykání, které se negativně projeví na průchodnosti (výkonu) databáze. Pokud nepotřebujeme vždy aktuální data, tak nejjednodušším řešením jsou materializované pohledy aktualizované cronem. Nevýhodou tohoto řešení je závislost na externí službě (cron). Při jejím selhání budou materializované pohledy neaktuální (nad rámec předpokládané neaktuálnosti). Tuto nevýhodu nemá použití cache na výstup z uložené procedury.

V PostgreSQL lze použít cache v prostředí PL/Perl, kde je k dispozici pole $_SHARED, kam můžeme ukládat libovolné hodnoty (i tabulky). Uložit tabulku do této cache mne napadlo až poté, co jsem viděl [http://www.oracle.com/technology/oramag/oracle/07-sep/o57plsql.html cache v Oracle 11g], která je ovšem mnohem sofistikovanější, bezpečnější a efektivnější (znevalidní obsah cache při změně dat, lze nastavit limity pro cache, cache je sdílena všemi uživateli). Cache PL/Perlu je omezena na session - což by nemělo vadit, pokud používáte nějakou techniku poolování. Data v PL/Perlu nejsou uložena příliš efektivně, takže do cache není vhodné ukládat velké tabulky.

Následující příklady počítají s následujícím datovým modelem:
<pre>
CREATE TABLE Books(
id serial PRIMARY KEY,
name VARCHAR(20));

CREATE TABLE Sale(
book_id integer REFERENCES Books(id),
inserted timestamp DEFAULT(CURRENT_TIMESTAMP)
);

INSERT INTO Books VALUES(1,'Dracula');
INSERT INTO Books VALUES(2,'Nosferatu');
INSERT INTO Books VALUES(3,'Bacula');

INSERT INTO Sale VALUES(1, '2007-10-11');
INSERT INTO Sale VALUES(2, '2007-10-12');
INSERT INTO Sale VALUES(2, '2007-10-13');
INSERT INTO Sale VALUES(3, '2007-10-10');
</pre>

Funkce Top10Books vrátí tabulku 10 nejprodávanějších knih v daném měsíci. Řádky jsou očíslované. Je to triviální funkce v PL/pgSQL. Na malé testovací množině trvá cca 2ms. Pokud ale testovací množina obsahovala dvacet tisíc prodejů během jednoho měsíce, to je docela realistické, tak její provedení trvá cca 120ms. Což už je příliš, pokud by se měla volat, při vykreslení každé stránky. Výsledkem je malá tabulka (obsahuje 2 sloupce, max 10 řádek), tudíž je ideální kandidát pro kešování.
<pre>
-- Top10 is on every page
CREATE OR REPLACE FUNCTION Top10Books(IN date, OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
BEGIN
ordr := 0;
FOR name IN SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc('month', $1)
AND date_trunc('month', $1) + interval '1month' - interval '1day'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10
LOOP
ordr := ordr + 1;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
</pre>

Přepsáním do perlu nic nezískáme. Funkce trvá stejně dlouho.
<pre>
CREATE OR REPLACE FUNCTION Top10BooksPerl(IN date, OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
if (not defined($_SHARED{plan_for_top10books}))
{
$_SHARED{plan_for_top10books} = spi_prepare(
'SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc(\'month\', $1)
AND date_trunc(\'month\', $1) + interval \'1month\' - interval \'1day\'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
return_next({
ordr => ++$i,
name => $row->{name}
});
}
return;
$$ LANGUAGE plperlu;
</pre>

Teprve s využitím cache získáme zásadní urychlení - očekávané. Funkce má druhý IN parametr, který určuje, zda-li cache má být aktualizována, nebo zda-i se má použít obsah cache. Díky použití cache není žádny rozdíl v rychlosti mezi voláním funkce s malou testovací množinou nebo s velkou testovací množinou.
<pre>
CREATE OR REPLACE FUNCTION Top10BooksCached(IN date, IN bool, OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
return $_SHARED{tableof_top10book}
if (defined ($_SHARED{tableof_top10book}) and not (defined($_[1]) and $_[1] eq "t"));
if (not defined($_SHARED{plan_for_top10books}))
{
$_SHARED{plan_for_top10books} = spi_prepare(
'SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc(\'month\', $1)
AND date_trunc(\'month\', $1) + interval \'1month\' - interval \'1day\'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $heap;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
push @$heap, {ordr => ++$i, name => $row->{name}}
}
$_SHARED{tableof_top10book} = $heap ;
return $_SHARED{tableof_top10book};
$$ LANGUAGE plperlu;
</pre>

Modifikací předešlého, je funkce s cache omezenou časem. Např. budu chtít, aby se cache aktualizovala vždy po pěti minutách (300sec).
<pre>
CREATE OR REPLACE FUNCTION Top10BooksCached(IN date, IN integer, OUT ordr integer, OUT name varchar(20))
RETURNS SETOF RECORD
AS $$
return $_SHARED{tableof_top10book}
if (defined ($_SHARED{tableof_top10book})
and defined($_SHARED{actualised_top10book})
and ($_SHARED{actualised_top10book} + $_[1] > time));
if (not defined($_SHARED{plan_for_top10books}))
{
$_SHARED{plan_for_top10books} = spi_prepare(
'SELECT b.name
FROM Books b
JOIN
Sale s
ON b.id = s.book_id
WHERE s.inserted BETWEEN date_trunc(\'month\', $1)
AND date_trunc(\'month\', $1) + interval \'1month\' - interval \'1day\'
GROUP BY b.name
ORDER BY count(*) DESC
LIMIT 10' , 'DATE');
}
my $row;
my $i = 0;
my $heap;
my $sth = spi_query_prepared($_SHARED{plan_for_top10books}, $_[0]);
while (defined ($row = spi_fetchrow($sth))) {
push @$heap, {ordr => ++$i, name => $row->{name}}
}
$_SHARED{tableof_top10book} = $heap ;
$_SHARED{actualised_top10book} = time;
return $_SHARED{tableof_top10book};
$$ LANGUAGE plperlu;
</pre>

Použití:
<pre>
postgres=# select * from Top10BooksCached(current_date, 300);
ordr | name
------+-----------
1 | Nosferatu
2 | Bacula
3 | Dracula
(3 rows)

Time: 128,965 ms
postgres=# select * from Top10BooksCached(current_date, 300);
ordr | name
------+-----------
1 | Nosferatu
2 | Bacula
3 | Dracula
(3 rows)

Time: 11,911 ms
</pre>