Hlavní navigace

PostgreSQL v roce 2005

Pavel Stěhule 9. 12. 2005

Rok 2005 se zapíše do historie, i do té open source databázové. Firebird2, MySQL5, PostgreSQL8 jsou systémy, které představují významný pokrok vůči předchozím verzím a s trochou nadsázky mohu upřímně napsat, že open source databáze dohonily komerční databázové systémy, které se ovšem zase dostaly někam jinam (kdo ale nepotřebuje OLAP, tak tomu by měly stávající open source systémy vyhovovat).

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_ex­clusion 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.

Našli jste v článku chybu?

15. 12. 2005 20:09

Petr Kubanek (neregistrovaný)
Souhlasim. A navic system komunikace Oraclu s uzivateli. Nasel jsem chybu v dokumentaci k JDBC - ma se pouzivat setPrivateTrace, ne setTrace, jelikoz setTrace je private - tu si zvenci nezavolate). Po cca 2 hodinach hledani na koho tuhle chybu poslat (budto zavolejte nasemu konzultantu nebo nam poslete fax..ale musite byt predplatitely, jinak se s vami nehodlame bavit) jsem to vzdal, takze chyba je tam zrejme dodnes.

V Postgresu jsem zjistil, ze dokumentace u ECPG k tomu, jak se predavaji typy…

14. 12. 2005 9:26

Jo, tomu rozumim. Jenomze diky tomu, ze se dela neco ne tak uplne obvykleho, co generuje trochu zvlastni pozadavky se to muze pohnout dopredu. A je to open source. Jestli natolik rozumis .NETu, tak to oprav , dopis. 100% budou nadseny
Měšec.cz: Kdy vám stát dá na stěhování 50 000 Kč?

Kdy vám stát dá na stěhování 50 000 Kč?

Podnikatel.cz: K EET. Štamgast už peníze na stole nenechá

K EET. Štamgast už peníze na stole nenechá

Podnikatel.cz: Přehledná titulka, průvodci, responzivita

Přehledná titulka, průvodci, responzivita

DigiZone.cz: ČRo rozšiřuje DAB do Berouna

ČRo rozšiřuje DAB do Berouna

120na80.cz: Pánové, pečujte o svoje přirození a prostatu

Pánové, pečujte o svoje přirození a prostatu

Vitalia.cz: Baletky propagují zdravotní superpostel

Baletky propagují zdravotní superpostel

Podnikatel.cz: Na poslední chvíli šokuje vyjímkami v EET

Na poslední chvíli šokuje vyjímkami v EET

Měšec.cz: Finančním poradcům hrozí vracení provizí

Finančním poradcům hrozí vracení provizí

Vitalia.cz: „Připluly“ z Německa a možná obsahují jed

„Připluly“ z Německa a možná obsahují jed

Měšec.cz: Zdravotní a sociální pojištění 2017: Připlatíte

Zdravotní a sociální pojištění 2017: Připlatíte

120na80.cz: Rakovina oka. Jak ji poznáte?

Rakovina oka. Jak ji poznáte?

DigiZone.cz: ČRa DVB-T2 ověřeno: Hisense a Sencor

ČRa DVB-T2 ověřeno: Hisense a Sencor

Podnikatel.cz: Babiše přesvědčila 89letá podnikatelka?!

Babiše přesvědčila 89letá podnikatelka?!

Lupa.cz: Google měl výpadek, nejel Gmail ani YouTube

Google měl výpadek, nejel Gmail ani YouTube

DigiZone.cz: Recenze Westworld: zavraždit a...

Recenze Westworld: zavraždit a...

DigiZone.cz: ČT má dalšího zástupce v EBU

ČT má dalšího zástupce v EBU

Podnikatel.cz: 1. den EET? Problémy s pokladnami

1. den EET? Problémy s pokladnami

Lupa.cz: Avast po spojení s AVG propustí 700 lidí

Avast po spojení s AVG propustí 700 lidí

Vitalia.cz: To není kašel! Správná diagnóza zachrání život

To není kašel! Správná diagnóza zachrání život

Vitalia.cz: Jsou čajové sáčky toxické?

Jsou čajové sáčky toxické?