Pro nezasvěcené, PostgreSQL je jedním z trojice (čtveřice) dominantních open source SQL databázových systémů: MySQL, Firebird, PostgreSQL (SQLite). Od konkurence se odlišuje důrazem na respektování ANSI SQL, rozšiřitelností, výkonem v prostředí s déle trvajícími transakcemi, spolehlivostí (PostgreSQL obsahuje všechny standardní mechanismy pro zajištění referenční a doménové integrity) a jistou duchovní spřízněností s Oracle (dřív se z něj často opisovalo, dnes už je obtížné protlačit cokoliv, co není v ANSI). Původně (ještě v minulém tisíciletí) byl PostgreSQL experimentální databází vyvíjenou v akademickém prostředí. Z těch časů má PostgreSQL pár OOP vlastností, například umožňuje dědičnost tabulek. Už to vypadalo, že tyto relikty budou definitivně odstraněny, když se letos při pracech na podpoře partitioningu ukázalo, že dědičnost se opět hodí. Výkonově drží PostgreSQL krok s ostatními rdbms, nicméně není to a nebude databáze pro jednoduché LAMP aplikace (SELECT, INSERT, UPDATE nad jednou tabulkou zůstává a zůstane doménou MySQL), viz listopadové články posuzující výkonost MySQL, Firebirdu a PostgreSQL.
Letos vývojáři PostgreSQL stihli uvolnit dvě velké verze 8.0 a 8.1. Přelomová je 8.0 nativní podporou pro velké Microsoft Windows systémy (minimum WinXP), Savepoints, Tablespaces, Point-In-Time-Recovery, pro vývojáře třeba $$ notace zápisu kódu uložených procedur. Více najdete v článku Co nového v PostgreSQL 8.0?. Nejviditelnější změnou v 8.1 je nahrazení systému uživatelů a skupin systémem rolí a dvoufázového potvrzování. Kromě toho spousta důležitých maličkostí (ještě tak jednu, dvě verze, a budu úplně spokojený člověk). 8.1 dotahuje, to co bylo začato 8.0. Savepoints (vnořené transakce) jsou použité v PL/pgSQL při obsluze chyb, v psql (konzole) pro obalení jednotlivých SQL příkazů atd.
2PC, dvoufázové potvrzování
Super věc, pokud chcete klidně spát a přenášíte data napříč několika RDBMS. 2PC podporuje dlouho Firebird, nově teď PostgreSQL a MySQL. Normální transakce má čtyři stavy: otevřená, blokovaná, potvrzená nebo zrušená. 2PC přidává dva nový stav: připravená. Převedením transakce do tohoto stavu transakci uzavřeme, držíme si ale zadní vrátka transakcí potvrdit nebo odvolat. Postup je následující: nejdříve se snažíme všechny transakce dostat do stavu připravená. Pokud se to povede, tak je potvrdíme, v opačném případě je odvoláme. Pokud potvrdíme první, musíme potvrdit ostatní. Co se ale stane, když v průběhu potvrzování dojde k výpadku systému? Nestane se nic. 2PC transakce jsou ve stavu potvrzená perzistentní. 2PC zavádí tři nové příkazy:
PREPARE TRANSACTION label
COMMIT PREPARED label
ROLLBACK PREPARED label Systém rolí
Předchozí systém práv založený na skupinách uživatelů byl nyní nahrazen systémem rolí (role může, z určitého pohledu, představovat jak skupinu, tak uživatele). Oproti předchozímu systému, kde uživatel automaticky získal práva všech skupin, jejichž byl členem, nyní máme na výběr mezi dvěma variantami: první funkčně ekvivalentní předchozímu systému (INHERIT), druhou, kdy role pouze garantuje možnost explicitně se přepnout do určité role a získat tak její oprávnění (NOINHERIT).
Dále má role možnost, pokud má k tomu oprávnění, delegovat svá práva jiným rolím, jinými slovy nabírat členy. Je potřeba dát pozor na fakt, že CURRENT_USER vrací aktuální roli a nikoliv roli, pod kterou se uživatel přihlašoval. Tuto roli (login username) vrací funkce SESSION_USER.
Ostatní
- zvýšení výkonu na více procesorových systémech
- automatické použití indexů pro funkce MIN() a MAX(). V předchozích verzích byla nutná ruční optimalizace SELECT col FROM tab ORDER BY col LIMIT 1
- indexy lze kombinovat – předchozí verze použily pouze jediný index. Pokud má dotaz podmínku tab.col1 = c1 AND tab.col2 = c2, použijí se i jednoduché indexy nad col1 a col2, oproti předchozí verzi, kdy bylo nutné vytvořit vícesloupcový index nad col1 a col2.
- podpora sdílených zámků redukuje požadavky na zamykání řádků. V předchozích verzích docházelo k problémům se zamykáním při realizaci referenční integrity.
- GIST indexy podporují souběžný přístup – ocení uživatelé PostGISu a TSearch2.
- partitioning – založený na dědičnosti a omezení tabulek. Při zapnutém parametru constraint_exclusion se při plánování dotazu vyloučí tabulky, jejichž některé omezení je v rozporu s podmínkou dotazu.
CREATE TABLE data(inserted date, ...);
CREATE TABLE data2004(CHECK (EXTRACT(year FROM inserted) = 2004)) INHERITS (data);
SELECT * from data WHERE EXTRACT(year FROM inserted) = 2005; - vymazání více tabulek jedním příkazem TRUNCATE – tak je možné bezpečně (z hlediska referenční integrity) vymazat obsah tabulek, aniž by se musela provádět kontrola ref. integr.
- příkaz EXPLAIN ANALYZE zobrazí seznam a prováděcí časy všech triggerů aktivovaných příkazem.
- příkaz initdb vytváří systémovou databázi postgres. Na ni se nyní odkazují příkazy typu createuser, createdb. V předchozích verzích nebylo možné vytvářet a mazat databáze, pokud někdo používal databázi template1.
- funkce lastval() – vrací naposled zvyšovanou hodnotu sekvence v rámci přihlášení (session). Jistým způsobem se tak nechá modelovat chování typu Identity.
- funkce GREATEST() a LEAST() – maximum a minimum ze seznamu hodnot
- SQL, PL/Perl a PL/pgSQL podporují OUT a INOUT parametry
- parametry PL/pgSQL příkazu RAISE mohou být výrazy
- PL/Perl podporuje return_next()
CREATE OR REPLACE FUNCTION oracle(text, text) RETURNS SETOF RECORD AS $$
use DBI;
$dbh= DBI->connect('dbi:Oracle:',$_[0],'',{});
$sth = $dbh->prepare($_[1]);
$sth->execute();
while ($dt = $sth->fetchrow_hashref)
{ return_next $dt; }
$sth->finish(); $dbh->disconnect();
return undef;
$$ LANGUAGE plperlu;
SELECT * FROM oracle('anonymous/tiger@triss',
'SELECT CURRENT_DATE AS c1 FROM DUAL') AS ("C1" date); - PL/Perl funkce může vracet hodnoty typu pole – pole v Perlu se automaticky překóduje na pole v PostgreSQL
CREATE OR REPLACE FUNCTION rsplit(text, text) RETURNS text[] AS $$
my @result = ($_[0] =~ /$_[1]/g);
return \@result;
$$ LANGUAGE plperl;
SELECT rsplit('1223456 424','([0-9]+)');
rsplit
--------------
{1223456,424} - \set ON_ERROR_ROLLBACK každý zpracovávaný příkaz se zapouzdřuje do vnořené transakce, takže případná chyba neomezuje uživatele (vynucení explicitního rollbacku) při interaktivní práci s konzolou psql.
- integrace autovacuum do systému – systém může, pokud je nastaven parametr autovacuum, samočinně aktivovat proces vacuum. Ke své činnosti potřebuje aktivní kolektor statistiky a statistiku na úrovni řádku. K zahájení dojde po dosažení přednastaveného počtu delete a update operací. Pro každou databázi se spouští jeden subproces.
- nově je možnost dočasně zablokovat triggery nad tabulkou, a to všechny (smí provést pouze uživatel s oprávněním Superuser) nebo vlastní ALTER TABLE .. DISABLE TRIGGER ..
- příkaz RAISE v PL/pgSQL může obsahovat výrazy
- PL/pgSQL podporuje příkaz CONTINUE
- optimalizace COPY, agregační funkce, …
Změn je hodně a nemá smysl je všechny jmenovat (najdete je v [Release Notes]).
Bohužel dva týdny po vydání 8.1 se přišlo na způsob, jak celkem zřetelně zrychlit win verzi a jak urychlit dotazy SELECT COUNT(*). Pro 8.2 zůstává lepší národní podpora, podpora XML, implementace příkazu MERGE, uvažuje se o debuggeru pro PL/pgSQL, autonomních transakcích, podpoře rekurzivních dotazů. Prostě vývoj pokračuje dál. Připravuje se verze 8.1.1.
ČLÁNKY DO MAILU