Co nového v PostgreSQL 7.3

Tato verze patří k těm zlomovějším a přináší hned několik naprostých novinek. Minulý díl tohoto občasného seriálu jsem začínal větou o radosti, u PostgreSQL 7.3 vím naprosto přesně, jaký typ uživatelů bude mít radost z novinky zvané „SCHEMA“. Budou to uživatelé provozující databázi ve víceuživatelském režimu, s tím, že jednotlivé uživatele je vhodné od sebe důkladně separovat – typicky u DB hostingu. Jak známo, uživatel je v PostgreSQL definován globálně a může se tedy přihlásit k jakékoliv databázi, záleží na nastavení práv uvnitř databáze, co uživateli umožní. Globálnímu přihlášení uživatelů lze zabránit udržováním separátních souborů s hesly pro každou databázi. Vše záleží na konfiguraci serveru.

Nová implementace schémat umožňuje uvnitř databáze vytvářet jmenné prostory a objekty (například tabulky) do nich ukládat. V praxi to znamená, že mohou existovat dvě tabulky stejného jména v téže DB za předpokladu, že každá je v jiném schématu. To by asi samo o sobě nebylo nijak zajímavé, pokud by schémata nešlo asociovat s uživatelem a vnutit mu tak po přihlášení k DB přesně dané schéma. Defaultně je v DB vytvořeno a používáno schéma „public“. Ve vztahu uživatel a schéma pak lze i definovat, může-li uživatel k danému schématu a může-li uvnitř tohoto schématu vytvářet nějaké DB objekty. Pokud potřebujete přistoupit k objektu nějakého jiného schématu, dělá se to použitím prefixu se jménem požadovaného schematu, např: SELECT * FROM myschema.mytable; V jakých schématech má server vyhledávat objekty bez prefixu, lze ovlivnit příkazem „SET search_path“, defaultní nastavení je $user,public. Ten, kdo rád něco ničí a činí tak rád snadno a rychle, bude mít pravděpodobně radost z možnosti smazat jedním příkazem vše, co je ve schématu: DROP SCHEMA schema CASCADE;. Protože tento článek není jen o schématech, doporučuji pro podrobnosti nahlédnout do dokumentace.

Dlouho jsem říkával, že příkaz ALTER table je záměrně v PostgreSQL nedodělán k dokonalosti právě proto, aby kritici PostgreSQL měli nějaký solidní argument. Bohužel tato modla padla a verze 7.3 s sebou přináší rozšíření o DROP COLUMN. V ALTER TABLE je novinek více, například možnost definovat, jak budou data do tabulek ukládána (ALTER COLUMN colname SET STORAGE).

Další novinka je nádhernou ukázkou, o co v PostgreSQL vlastně jde. PostgreSQL je řadou lidí ceněn hlavě pro jeho možnost snadné rozšiřitelnosti o uživatelem definované funkce. Nyní tyto funkce mohou vracet data uspořádaná do tabulky a funkci pak lze používat v SELECT FROM namísto tabulky. Například:

SELECT * FROM pg_show_all_settings() AS (jmeno text, nastaveni text)
      WHERE jmeno='search_path';

    jmeno    |  nastaveni
-------------+--------------
 search_path | $user,public

Definice sloupců a jejich datových typů je v tomto případě nutná, protože funkce data vytváří interně sama a při přípravě dotazu PostgreSQL nic o těchto sloupcích neví. Toto je nutné jen u funkcí, které vracejí tzv. RECORD (v definici funkce je to RETURNS SETOF RECORD). Pokud funkce vrací data z nějaké tabulky, lze o této skutečnosti PostgreSQL informovat pomocí RETURNS SETOF jmeno_tabulky a sloupce již není nutné uvádět, nebo je nutné si definovat datový typ (CREATE TYPE typ AS (jmeno text, nastaveni text);), jehož název se použije namísto jména tabulky v RETURNS SETOF.

CREATE FUNCTION tab(text)
       RETURNS SETOF tab AS
           'SELECT * FROM tab WHERE data LIKE $1'
       LANGUAGE SQL;

SELECT * FROM tab('bbb');
 id | data
----+------
  2 | bbb

Myslím, že i ten, kdo nemá rád PostgreSQL, musí uznat, že je to prostě nádhera:-) Pochopitelně funkce nemusí být psána v SQL nebo PL/pgSQL, ale obecně v jakémkoliv PL jazyce, který podporuje k tomu používané API (nativně psané v C). V PL/pgSQL je pro tvorbu řádky nový příkaz „RETURN NEXT“, který vrátí svému interpretu řádku, ale funkci samotnou neukončí. Více viz ukázku na konci článku.

Funkcí se ve verzi 7.3 dotklo více věcí, a to hlavně z oblasti práv. Funkce nyní podobně jako RULE může běžet, je-li to v její definici řečeno, s právy toho, kdo ji vytvořil – například super-user. Dalších změn doznala detailnější definice toho, co a za jakých okolností funkce vrací (např. práce s NULL pointerem). Pokud píšete vlastní funkce, určitě si danou čast dokumentace prostudujte.

Další novinka je o zvýšení výkonosti. Na výše uvedeném příkladě je zřejmé, že na PostgreSQL jsou během parsování a přípravy dotazu kladeny poměrně značné nároky (téměř vše v dotazu od datových typů přes funkce, operátory apod. je plně dynamické). Pokud vašemu serveru v rámci jedné session posíláte opakovaně složitější a stále stejné dotazy, je pro vás určena dvojice funkcí PREPARE a EXECUTE. PREPARE dotaz předzpracuje a uloží v paměti serveru, EXECUTE ho následně provede. Pochopitelně, že v dotazu lze používat parametry:

PREPARE mujdotaz (text, int) AS
        SELECT * FROM tab WHERE data LIKE $1 AND id > $2;

EXECUTE mujdotaz ('b%a', 1);
 id | data
----+------
  4 | bca
  5 | bda

Z paměti před-připravený dotaz odstraníte příkazem DEALLOCATE. Protože pokud se člověk nepochválí, tak to za něj nikdo neudělá, vězte, že PREPARE/EXECUTE má prapůvod v ČR :-)

V 7.3 přibyla jako další důležitá věc první verze „Dependency Tracking“, což je systém sledující závislosti mezi objekty (tabulky, triggery, funkce apod.) uvnitř DB a znemožňující zásahy do designu DB, pokud na daném objektu závisí objekt jiný. Zároveň je u příkazů DROP možné pomocí parametru CASCADE odstranit všechny závislé objekty.

U tabulek ještě zůstaneme, protože je třeba zmínit se o změně týkající se datového typu SERIAL, který již automaticky nevytváří UNIQUE index. Je tedy nutné slůvko UNIQUE v definici sloupce uvádět. Příjemné změny doznala i referenční integrita a cizí klíč nyní může ukazovat i na sloupec s UNIQUE indexem, a ne pouze, jak tomu bylo dříve, na sloupec s PRIMARY KEY.

Dalším vylepšením při definování tabulek je možnost vytvořit si doménu (definice datového typu a jeho integritní omezení – například CHECK, NOT NULL apod.) jako samostatný objekt a následně ho používat v definicích libovolného množství tabulek stejně, jako se používá datový typ. Více najdete v dokumentaci u příkazu CREATE/DROP DOMAIN.

Další radostnou zprávou do našich luhů a hájů je, že podpora locales a multibyte kódování je defaultní. Což by mohlo pomoci hlavně uživatelům distribucí, kde PostgreSQL balí někdo, komu tak moc na nějakém tom háčku nebo čárce nezáleží.

K překódovávání mezi různými sadami kódování se váže další věc, a tou je příkaz CREATE CONVERSION, pomocí kterého můžete pro převod mezi dvěma kódováními vnutit PostgreSQL nějakou svoji funkci.

Libujete-li si v OpravduDlou­hýchNázvech, tak délka identifikátoru (jména objektu v DB) byla zvýšena z 32 na 63.

Verze 7.2 přišla s příkazem CREATE OR REPLACE FUNCTION, v 7.3 najdete podobné příkazy CREATE OR REPLACE VIEW a CREATE OR REPLACE RULE.

Příkaz COPY patří mezi mé oblíbené a je pěkné, že nově je možné u něho definovat jména požadovaných sloupců: COPY table (f1,f2) TO stdin;

Velkých změn doznal příkaz SET, kterým lze již nyní značně ovlivnit chování serveru. Novinkou je možnost udělat některá nastavení platná jen po dobu právě probíhající transakce, a to doplněním o slůvko local: SET LOCAL foo TO bar;

V oblasti datových typů doznal změn typ TIMESTAMP a TIME, který je nyní implicitně používán jako TIMESTAMP WITHOUT TIME ZONE (podobně i TIME).

Pokaždé musí být žádoucí způsob, jakým PostgreSQL přetypovává data (konverze z jednoho datového typu do jiného), proto byl přidán příkaz CREATE/DROP CAST, kterým lze pro dva zvolené datové typy definovat přetypování například pomocí v příkazu stanovené funkce.

Pokud k psaní raději než cokoliv jiného používáte štětec, tak právě pro vás byla do PostgreSQL přidána další převážně asijská kódování: Korean (JOHAB), Thai (WIN874), Vietnamese (TCVN), Arabic (WIN1256), Simplified Chinese (GBK) a Korean (UHC).

Změn je pochopitelně hodně (jejich seznam má cca 500 řádků) a nejlepší, bude pokud si novou verzi PostgreSQL 7.3 nainstalujete a sami vyzkoušíte.

A na závěr slibovaná PL/pgSQL funkce vracející SETOF, tedy více řádek (dík za inspiraci Pavlu Stěhulemu):

CREATE TYPE abc AS (f1 text);

CREATE OR REPLACE FUNCTION abeceda(integer) RETURNS SETOF abc AS '
  DECLARE f abc%ROWTYPE;
  BEGIN
    FOR r IN 1..$1 LOOP
        f.f1 := chr(r+64);
      FOR i IN r+65..r+63+$1 LOOP
        f.f1 := f.f1 || '' '' || chr(i);
      END LOOP;
      RETURN NEXT f;
    END LOOP;
    RETURN;
  END;
' LANGUAGE 'plpgsql';


SELECT * FROM abeceda(9);
        f1
-------------------
 A B C D E F G H I
 B C D E F G H I J
 C D E F G H I J K
 D E F G H I J K L
 E F G H I J K L M
 F G H I J K L M N
 G H I J K L M N O
 H I J K L M N O P
 I J K L M N O P Q
(9 řádek)