Novinky v připravovaném PostgreSQL 9.3

Commitem patche vývoj určité funkce nekončí, spíš začíná (složitější věci nelze zvládnout během jednoho roku). Například materializované pohledy se budou dokončovat další dva tři roky, u aktualizovatelných FDW (Foreign Data Wrapers) chybí podpora 2PC (dvoufázový commit), což bude vyžadovat ještě další dva roky práce. Po nemastném úvodu se commitování docela rozjelo. Přes veškerý prodiskutovaný čas, se povedlo připravit dost zajímavých funkcí, díky kterým může být 9.3 pro řadu uživatelů atraktivní.

SQL

Klauzule LATERAL (LATERAL spojení relací)

Jak vysvětlit LATERAL join? Jedná se o spojení dvou databázových relací X a Y tj podmnožinu kartézského součinu X x Y, přičemž v Y se lze odkazovat na X. V určitém smyslu je LATERAL join podobný korelovaným poddotazům, jelikož se pro každý prvek z X dohledává podmnožina pY, jejíž prvky vyhovují daným kritériím Při korelovaném poddotazu se zjišťuje, zda-li je pY neprázdná množina, a pokud ano, tak se do výsledku přidá prvek x. Při LATERAL spojení se do výsledné databázové relace přidá sloučení prvku x s podmnožinou pY. V Postgresu jsme se k LATERAL spojení dostali oklikou – díky SRF funkcím. SRF (Set Returnig Functions) funkce jsou funkce, které vrací tabulku. Pokud se volají v klauzuli FROM, tak mohou mít pouze konstantní parametry. To nám znemožňuje je použít např. pro každou hodnotu vybraného sloupce tabulky. V Postgresu existuje několik triků využívajících některé archaické funkce implementované ještě v čase PostQUELu (původní dotazovací jazyk v Postgresu). Má to ale svá negativa – definované, přesto neobvyklé chování a v určitých případech možné neefektivní provedení dotazu. Díky LATERAL spojení můžeme SRF funkci volat v dotazu opakovaně (tak jak chceme), aniž bychom byli nuceni používat obskurní triky.

CREATE TABLE deti(id_rodice int, jmeno varchar);
CREATE TABLE rodice(id int, jmeno varchar);
INSERT INTO rodice VALUES(1, 'Novak');
INSERT INTO rodice VALUES(2, 'Trachta');
INSERT INTO deti VALUES(1, 'Anicka');
INSERT INTO deti VALUES(1, 'Pepicka');
INSERT INTO deti VALUES(1, 'Liduska');
INSERT INTO deti VALUES(2, 'Honzik');
INSERT INTO deti VALUES(2, 'Pepik');

CREATE OR REPLACE FUNCTION deti_rodic(int)
RETURNS SETOF deti AS $$
SELECT *
   FROM deti
  WHERE id_rodice = $1
LANGUAGE sql;

postgres=# SELECT * FROM deti_rodic(1);
  id_rodice │  jmeno
 ───────────┼─────────
          1 │ Anicka
          1 │ Pepicka
          1 │ Liduska
(3 rows)

-- pouziti osklivych triku:
-- umisteni SRF do seznamu atributu
-- doplneni chybejicich hodnot, pokud SRF vraci vice radku
-- prevzeti pouze jednoho atributu vysledku SRF
-- navic neni mozny inlining
postgres=# SELECT r.jmeno, (deti_rodic(r.id)).jmeno FROM rodice r;
  jmeno  │  jmeno
─────────┼─────────
 Novak   │ Anicka
 Novak   │ Pepicka
 Novak   │ Liduska
 Trachta │ Honzik
 Trachta │ Pepik
(5 rows)

Pokud by se v jednom SQL dotazu kombinovalo více SRF funkcí, tak byste mohli být překvapeni výsledkem (v jednoduchých případech ale dostanete očekávané výsledky).

postgres=# SELECT r.jmeno, x.jmeno
              FROM rodice r,
                   LATERAL deti_rodic(r.id) x;
  jmeno  │  jmeno
─────────┼─────────
 Novak   │ Anicka
 Novak   │ Pepicka
 Novak   │ Liduska
 Trachta │ Honzik
 Trachta │ Pepik
(5 rows)

případně (aby byl vidět lehký vztah ke korelovaným poddotazům):

postgres=# SELECT r.jmeno, x.jmeno
              FROM rodice r,
                   LATERAL (SELECT *
                               FROM deti d
                              WHERE r.id =d.id_rodice) x;
  jmeno  │  jmeno
─────────┼─────────
 Novak   │ Anicka
 Novak   │ Pepicka
 Novak   │ Liduska
 Trachta │ Honzik
 Trachta │ Pepik
(5 rows)

Na svých školeních doporučuji nepoužívat flagy VOLATILE, STABLE, IMMUTABLE u jednoduchých SQL funkcí. SRF SQL funkce jsou trochu něco jiného. Flagem IMMUTABLE můžeme přinutit rewriter (modul, který předzpracovává SQL příkaz) k inlinigu SRF funkcí, který se defaultně pro SRF funkce neprovádí. Výsledek uvidíme na prováděcích plánech:

-- bez inliningu - NESTED LOOP!
postgres=# EXPLAIN SELECT r.jmeno, x.jmeno
                      FROM rodice r,
                           LATERAL deti_rodic(r.id) x;
                                 QUERY PLAN
────────────────────────────────────────────────────────────────────────────
 Nested Loop  (cost=0.25..41.27 rows=2000 width=39)
   ->  Seq Scan on rodice r  (cost=0.00..1.02 rows=2 width=11)
   ->  Function Scan on deti_rodic x  (cost=0.25..10.25 rows=1000 width=32)
(3 rows)

CREATE OR REPLACE FUNCTION deti_rodic(int)
RETURNS SETOF deti AS $$
SELECT *
   FROM deti
  WHERE id_rodice = $1
LANGUAGE sql IMMUTABLE; -- magické slovo IMMUTABLE

-- s inliningem - optimalizováno
postgres=# EXPLAIN SELECT r.jmeno, x.jmeno
                      FROM rodice r,
                           LATERAL deti_rodic(r.id) x;
                             QUERY PLAN
─────────────────────────────────────────────────────────────────────
 Hash Join  (cost=1.04..2.16 rows=5 width=14)
   Hash Cond: (deti.id_rodice = r.id)
   ->  Seq Scan on deti  (cost=0.00..1.05 rows=5 width=11)
   ->  Hash  (cost=1.02..1.02 rows=2 width=11)
         ->  Seq Scan on rodice r  (cost=0.00..1.02 rows=2 width=11)
(5 rows)

V tomto případě LATERAL join potřebujeme pouze, pokud chceme používat SRF funkce (např. z důvodu reuse kódu). Typický dotaz by mohl vypadat asi takto:

postgres=# EXPLAIN SELECT r.jmeno, d.jmeno
                      FROM rodice r
                           JOIN deti d
                           ON r.id = d.id_rodice;
                             QUERY PLAN
─────────────────────────────────────────────────────────────────────
 Hash Join  (cost=1.04..2.16 rows=5 width=14)
   Hash Cond: (d.id_rodice = r.id)
   ->  Seq Scan on deti d  (cost=0.00..1.05 rows=5 width=11)
   ->  Hash  (cost=1.02..1.02 rows=2 width=11)
         ->  Seq Scan on rodice r  (cost=0.00..1.02 rows=2 width=11)
(5 rows)

Všimněte si, že prováděcí plán je stejný jako u LATERAL spojení – Postgres dokáže dobře optimalizovat SRF SQL funkce (pozor: platí to pouze pro SQL funkce!).

Zápisem se tato varianta spojení podobá staršímu zápisu kartézského součinu SELECT FROM X, Y, tedy SELECT FROM X, LATERAL Y.

Bez LATERAL spojení by dalším řešením byla funkce v PL/pgSQL – tam jde ale vždy o simulaci nested loopu:

postgres=# \sf sim
CREATE OR REPLACE FUNCTION public.sim()
 RETURNS TABLE(rodic_jmeno character varying, dite_jmeno character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  FOR r IN SELECT *
              FROM rodice
  LOOP
    RETURN QUERY SELECT r.jmeno, x.jmeno
                    FROM deti_rodic(r.id) x;
  END LOOP;
  RETURN;
END;
$function$

postgres=# SELECT * FROM sim();
 rodic_jmeno │ dite_jmeno
─────────────┼────────────
 Novak       │ Anicka
 Novak       │ Pepicka
 Novak       │ Liduska
 Trachta     │ Honzik
 Trachta     │ Pepik
(5 rows)

Příklad pro procvičení. Mějme tabulku se sloupcem id jako primárním klíčem a celočíselným polem. Cílem je vytořit dotaz, kde sloupec id bude zachován a prvky v poli budou zvýšeny o hodnotu jedna. S LATERAL spojením je to hračka.

CREATE TABLE test(id integer, pole int[]);
INSERT INTO test VALUES(1, '{1,2,3,4}'),(2,'{5,6}');

postgres=# SELECT t.id, array_agg(u.v + 1)
              FROM test t,
                   unnest(t.pole) u(v)
             GROUP BY t.id;
 id │ array_agg
────┼───────────
  1 │ {2,3,4,5}
  2 │ {6,7}
(2 rows)

Modifikovatelné pohledy (updateable views)

I v předchozích verzích bylo možné, byť v několika krocích, docílit toho, že vybraný pohled byl aktualizovatelný. K dispozici jsou tzv rules nebo počínaje 9.1 INSTEAD OF triggery. V 9.3 je každý pohled modifikovatelný pokud platí, že klauzule FROM obsahuje pouze jednu tabulku nebo jiný modifikovatelný pohled, nedochází k žádné transformaci databázové relace, tj není použit DISTINCT, GROUP BY, HAVING, LIMIT nebo OFFSET, UNION, INTERSECT, EXCEPT, v seznamu atributů není použit žádný výraz, a žádný atribut není použit vícenásobně.

postgres=# CREATE TABLE foo(a int, b int);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(10,20),(30,40);
INSERT 0 2
postgres=# CREATE VIEW boo AS SELECT * FROM foo WHERE a = 10;
CREATE VIEW
postgres=# UPDATE boo SET b = 40;
UPDATE 1

Klauzule CHECK OPTION, která zajišťuje že po INSERTu nebo UPDATEu pohledu zůstanou všechny záznamy v tomto pohledu viditelné, není podporována (věřím, že dočasně).

Kdysi se tradovalo, a přiznávám, může to být fáma, že modifikovatelné pohledy byly nezbytnou podmínkou pro běh SAPu. A jelikož Postgres tuto funkcionalitu nenabízel, tak se SAP nikdy nesnažil o portaci na Postgres, ačkoliv to bylo požadováno některými zákazníky. Je ale skutečností, že v době, kdy MySQL ab spolupracovalo se SAPem, tak se v MySQL updateable views objevily. Nicméně stejně nikdy SAP na MySQL neběžel a docela pochybuji, že by se v SAPu o portaci na Postgres snažili – jelikož, kdo nemá na Oracle, tak nemá na SAP, a kdo si může dovolit SAP, tak si může dovolit i Oracle.

Materializované pohledy (materialized views)

PostgreSQL 9.3 přináší nejjednodušší možnou formu materializovaných pohledů (explicitně aktualizované).

postgres=# CREATE MATERIALIZED VIEW obce_stredocesky_kraj AS SELECT * FROM obce WHERE okres_id LIKE 'CZ02%';
SELECT 1145

Fakticky se vytvoří tabulka, kterou je možné na vyžádání aktualizovat voláním příkazu REFRESH. Pozor, tento příkaz vyžaduje exkluzivní zámek nad aktualizovaným pohledem.

postgres=# REFRESH MATERIALIZED VIEW obce_stredocesky_kraj ;
REFRESH MATERIALIZED VIEW

Jelikož je materializovaný pohled jen jiná forma tabulky, lze nad ním vytvářet indexy.

postgres=# CREATE INDEX ON obce_stredocesky_kraj (nazev );
CREATE INDEX

postgres=# EXPLAIN SELECT * FROM obce_stredocesky_kraj WHERE nazev = 'Mezno';
                                                  QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Index Scan using obce_stredocesky_kraj_nazev_idx on obce_stredocesky_kraj  (cost=0.28..8.29 rows=1 width=41)
   Index Cond: ((nazev)::text = 'Mezno'::text)
(2 rows)

Implementace materializovaných pohledů v 9.3 je základ, který se bude v dalších verzích určitě rozvíjet (už teď jsou připravené návrhy na optimalizaci příkazu REFRESH). Docela se řešilo, jestli má smysl tak základní implementaci pustit ven. Nakonec se usoudilo, že ano. I tato omezená implementace může nahradit většinu vlastních řešení, kterými aplikační programátoři materializované pohledy v Postgresu suplovali. A, samozřejmě je důležité udělat první krok.

COPY

Příkaz COPY slouží k rychlému exportu/importu dat z/do databáze.

PIPE COPY

V 9.3 se COPY nemusí používat pouze vůči souboru, ale vůči libovolnému programu (což se signalizuje použitím klíčového slova PROGRAM).

postgres=# COPY pg_class TO PROGRAM 'gzip -9c - > /tmp/copy.data.gz';
COPY 305
postgres=# CREATE TABLE fo(a int);
CREATE TABLE
postgres=# COPY fo FROM PROGRAM 'seq 10';
COPY 10
postgres=# SELECT * FROM fo;
 a
────
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

postgres=# set client_encoding TO 'latin1';
SET
postgres=# COPY countrylist FROM PROGRAM 'wget -q -O - http://www.andrewpatton.com/countrylist.csv' CSV HEADER;
COPY 272

COPY FREEZE

Další novou volbou příkazu COPY je FREEZE. Každý záznam v Postgresu má několik možných stavů – jedním z těchto stavů je FREEZE – do tohoto stavu se dostanou všechny existující záznamy provedením příkazu VACUUM FREEZE, který, zjednodušeně řečeno, resetuje transactionID aktivních záznamů. Je to cílový stav záznamů, které nechceme nikdy smazat. U velkých databází spuštění VACUUM FREEZE může být komplikované – je potřeba dost místa na disku a také dochází k exkluzivnímu zamknutí tabulky. Řešením může být nastavení FREEZE stavu rovnou při lití dat do databáze – což je přesně to, co dělá COPY FREEZE.

BEGIN;
TRUNCATE vistest;
COPY vistest FROM stdin CSV FREEZE;
COMMIT;

Zvláštností tohoto příkazu je to, že obchází mechanismus, který má v Postgresu na starost viditelnost záznamů. Nově nalité freeznuté záznamy (po commitu transakce) jsou okamžitě viditelné ostatním transakcím, bez ohledu na jejich úroveň izolace.

3. DDL triggery (event triggers)

Event triggery jsou triggery aktivované DDL operací (CREATE, DROP, ALTER). Měly by sloužit k nastavení přístupových práv (tam, kde výchozí práva pro schéma nestačí), k vytvoření (k aktualizaci) audit tabulek a triggerů. Mohou pomoci vyřešit určité situace s přidáváním či rušením Large Objektů, které se chovají jako samostatné objekty. Mohou pomoci se správou uživatelů. Mohou aktivovat podrobnější kontrolu vlastních funkcí (syntaxe, sémantika, formátování).

Opět se jednalo o patch, který se snad stokrát přepisoval a který chytil velké zpoždění, v čehož důsledku není hotová podpora pro PL jazyky. Je ale důležité, že vývoj této funkcionality si svými slepými cestami prošel, a pro 9.4 se čeká poměrně jednoduchá a přímočará finalizace.

CREATE FUNCTION test_event_trigger()
RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
END
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER event_trg
   ON DDL_COMMAND_START
  WHEN tag IN ('CREATE TABLE', 'CREATE FUNCTION')
  EXECUTE PROCEDURE test_event_trigger();

postgres=# CREATE TABLE test1(a int);
NOTICE:  test_event_trigger: ddl_command_start CREATE TABLE
CREATE TABLE

postgres=# CREATE FUNCTION fx1(a int) RETURNS int AS $$ SELECT 1 $$ LANGUAGE sql;
NOTICE:  test_event_trigger: ddl_command_start CREATE FUNCTION
CREATE FUNCTION
postgres=#

Zatím není možné psát generické event triggery v PL/pgSQL – v PL/pgSQL se ani nedozvíte, na kterém objektu došlo k události, která aktivovala trigger. Přístup k interním informacím je možný pouze z triggerů napsaných v C. Budoucí podobu obsluhy event triggerů nastiňuje funkce pg_event_trigger_dropped_objects(). :

CREATE FUNCTION test_event_trigger_for_drops()
        RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        RAISE NOTICE '% dropped object: % %.% %',
                     tg_tag,
                     obj.object_type,
                     obj.schema_name,
                     obj.object_name,
                     obj.object_identity;
    END LOOP;
END
$$;

CREATE EVENT TRIGGER test_event_trigger_for_drops
   ON sql_drop
   EXECUTE PROCEDURE test_event_trigger_for_drops();

postgres=# DROP TABLE obce CASCADE;
NOTICE:  drop cascades to materialized view obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: table public.obce public.obce
NOTICE:  DROP TABLE dropped object: index public.obce_okres_id_idx public.obce_okres_id_idx
NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> obce_okres_id_fk on public.obce
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_a_46471" on public.okresy
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_a_46472" on public.okresy
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_c_46473" on public.obce
NOTICE:  DROP TABLE dropped object: trigger <NULL>.<NULL> "RI_ConstraintTrigger_c_46474" on public.obce
NOTICE:  DROP TABLE dropped object: sequence public.obce_id_seq public.obce_id_seq
NOTICE:  DROP TABLE dropped object: type public.obce_id_seq public.obce_id_seq
NOTICE:  DROP TABLE dropped object: default value <NULL>.<NULL> for public.obce.id
NOTICE:  DROP TABLE dropped object: table constraint public.<NULL> _obce_pkey on public.obce
NOTICE:  DROP TABLE dropped object: index public._obce_pkey public._obce_pkey
NOTICE:  DROP TABLE dropped object: materialized view public.obce_stredocesky_kraj public.obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: index public.obce_stredocesky_kraj_nazev_idx public.obce_stredocesky_kraj_nazev_idx
NOTICE:  DROP TABLE dropped object: type public.obce_stredocesky_kraj public.obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: type public._obce_stredocesky_kraj public.obce_stredocesky_kraj[]
NOTICE:  DROP TABLE dropped object: rule <NULL>.<NULL> "_RETURN" on public.obce_stredocesky_kraj
NOTICE:  DROP TABLE dropped object: type public.obce public.obce
NOTICE:  DROP TABLE dropped object: type public._obce public.obce[]
DROP TABLE

Jinak s event triggery opatrně. Chyba v obslužné funkci zaregistrovaného event triggeru způsobila, že všechny pokusy o aktualizaci obslužné funkce triggeru failovaly, a než jsem si uvědomil, že něco je jinak (a odstranil trigger), tak jsem chvíli nevěděl, která bije. Seznam event triggerů v databázi zobrazí psql příkaz \dy.

postgres=# \dy
                                     List of event triggers
             Name             │  Event   │ Owner │ Enabled │          Procedure           │ Tags
──────────────────────────────┼──────────┼───────┼─────────┼──────────────────────────────┼──────
 test_event_trigger_for_drops │ sql_drop │ pavel │ enabled │ test_event_trigger_for_drops │
(1 row)

postgres=# DROP EVENT TRIGGER test_event_trigger_for_drops;
DROP EVENT TRIGGER

Indexy

V 9.1 se objevila podpora tzv unlogged tables. Jedná se o klasické tabulky, které nejsou jištěné transakčním logem, a pokud dojde k havárii serveru, po restartu je obsah těchto tabulek smazán (bez transakčního logu nelze garantovat jejich obsah). Na těchto tabulkách nebylo možné používat GiST index, což muselo mrzet aktivní uživatele PostGISu. Toto omezení je v 9.3 odstraněno.

Podpora regulárních výrazů v pg_trgm

Modul pg_trgm slouží k transformaci řetězců do trigrammů (třípísmenná kombinace písmen). Nad množinou trigramů lze vytvořit index a nad tímto indexem se dají dělat věci, o kterých jsem si donedávna myslel, že nejsou realizovatelné – např. LIKE %něco%. Predikát ale vždy musí specifikovat minimálně tři znaky.

postgres=# CREATE EXTENSION pg_trgm ;
CREATE EXTENSION


postgres=# CREATE EXTENSION pg_trgm ;
CREATE EXTENSION
postgres=# CREATE INDEX ON obce USING gin (nazev gin_trgm_ops);
CREATE INDEX
postgres=# ANALYZE obce;
ANALYZE

postgres=# EXPLAIN SELECT * FROM obce WHERE nazev LIKE '%šov%';
                                  QUERY PLAN
───────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on obce  (cost=12.45..73.54 rows=59 width=41)
   Recheck Cond: ((nazev)::text ~~ '%šov%'::text)
   ->  Bitmap Index Scan on obce_nazev_idx  (cost=0.00..12.44 rows=59 width=0)
         Index Cond: ((nazev)::text ~~ '%šov%'::text)
(4 rows)

Co je ale bomba, no bomba – spíš taková třešnička v 9.3, je použití indexů pro regulární výrazy:

postgres=# SELECT * FROM obce WHERE nazev ~ '^Ben.*šov';
  id  │ okres_id │         nazev         │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen
──────┼──────────┼───────────────────────┼────────────┼───────────┼──────────┼─────────
 4577 │ CZ0641   │ Benešov               │        327 │       336 │     38.1 │    41.8
 2909 │ CZ0514   │ Benešov u Semil       │        445 │       426 │     36.6 │    40.8
 2220 │ CZ0327   │ Benešovice            │         79 │        92 │     41.0 │    42.9
 2404 │ CZ0421   │ Benešov nad Ploučnicí │       2024 │      1967 │     38.0 │    41.4
 1256 │ CZ0312   │ Benešov nad Černou    │        641 │       668 │     38.4 │    39.8
    2 │ CZ0201   │ Benešov               │       7875 │      8507 │     39.2 │    41.9
(6 rows)

postgres=# EXPLAIN SELECT * FROM obce WHERE nazev ~ '^Ben.*šov';
                                 QUERY PLAN
────────────────────────────────────────────────────────────────────────────
 Index Scan using obce_nazev_idx on obce  (cost=0.15..8.17 rows=1 width=41)
   Index Cond: ((nazev)::text ~ '^Ben.*šov'::text)
(2 rows)

postgres=# SELECT * FROM obce WHERE nazev ~ '^Ben.*šov$';
  id  │ okres_id │  nazev  │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen
──────┼──────────┼─────────┼────────────┼───────────┼──────────┼─────────
 4577 │ CZ0641   │ Benešov │        327 │       336 │     38.1 │    41.8
    2 │ CZ0201   │ Benešov │       7875 │      8507 │     39.2 │    41.9
(2 rows)

postgres=# EXPLAIN SELECT * FROM obce WHERE nazev ~ '^Ben.*šov$';
                                 QUERY PLAN
────────────────────────────────────────────────────────────────────────────
 Index Scan using obce_nazev_idx on obce  (cost=0.15..8.17 rows=1 width=41)
   Index Cond: ((nazev)::text ~ '^Ben.*šov$'::text)
(2 rows)

Foreign Data Wrapers

Foreign Data Wrapers (FDW) jsou extenze (nebo API, podle toho, jak se na to díváme) umožňující přístup k externím datovým zdrojům prostřednictvím SQL. Aktuálně existují drivery pro MySQL, Oracle, JDBC, ODBC, genericky Python driver, atd. Možnosti FDW se ve verzi 9.3 posouvají o krok dále a umožňují i změny dat. Nyní je míč na straně autorů FDW driverů, aby aktualizovali své drivery. Nejdále je v této oblasti referenční implementace – FDW driver pro PostgreSQL. Driver lze využít k připojení k PostgreSQL 8.1 a novější (pro čtení) a k PostgreSQL 8.3 a novější (pro čtení/zápis).

PostgreSQL FDW

9.2 obsahovala pouze jeden FDW a to file_fdw zpřístupňující data uložená v souborech. Driver pro Postgres se nestihl dokončit pro 9.2 včas. Nyní, o rok později, je již hotov a připraven. V Postgresu lze přistupovat pouze k tabulkám v databázi, ke které je uživatel aktuálně přihlášen. Toto omezení se obcházelo, bez jakéhokoliv pohodlí, pomocí extenze dblink. S postgres_fdw driverem lze pracovat s daty v jiných (postgresových) databázích jednoduše a pohodlně.

-- pro jednoduchost neresim přístupová práva
CREATE EXTENSION postgres_fdw ;
CREATE SERVER omega FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'omega');
CREATE USER MAPPING FOR pavel SERVER omega;
CREATE FOREIGN TABLE oo (a int) SERVER omega;

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM oo WHERE a BETWEEN 1 AND 100;
                                                  QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Foreign Scan on public.oo  (cost=100.00..292.01 rows=100 width=4) (actual time=1.659..2.257 rows=101 loops=1)
   Output: a
   Remote SQL: SELECT a FROM public.oo WHERE ((a >= 1)) AND ((a <= 100))
 Total runtime: 3.385 ms
(4 rows)

Tabulky v jiných databázích lze i modifikovat – musí se ale počítat s tím, že tyto modifikace probíhají po řádcích – a i když se používají prepared statements a řádky se identifikují skrz ctid (což je jakoby přístup přes index), tak pro větší změny to může být pomalé, a je pak na místě použít dblink.

postgres=# EXPLAIN ANALYZE VERBOSE DELETE FROM oo WHERE a < 1000;
                                                      QUERY PLAN

──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Delete on public.oo  (cost=100.00..285.03 rows=1001 width=6) (actual time=79.865..79.865 rows=0 loops=1)
   Remote SQL: DELETE FROM public.oo WHERE ctid = $1
   ->  Foreign Scan on public.oo  (cost=100.00..285.03 rows=1001 width=6) (actual time=3.593..6.738 rows=500 loops=1)
         Output: ctid
         Remote SQL: SELECT ctid FROM public.oo WHERE ((a < 1000)) FOR UPDATE

Samozřejmě, že je možné si pohrát s přístupovými právy. Například lze pro databázového uživatele nastavit cizího účet včetně hesla.

CREATE USER MAPPING FOR tom SERVER omega OPTIONS(user 'tom', password 'tomas');

Pozor: hesla jsou v databázi uložená nezašifrovaná – nicméně přístup k nim má pouze superuser – a pouze superuser může provést kompletní dump databáze včetně definic FDW (pro ostatní uživatele se citlivé informace neexportují).

Transakce na cizím serveru kopírují chování transakce na lokálním serveru, nejedná se ovšem o 2PC (two phase commit), ačkoliv je implementace hodně podobná:

postgres=# BEGIN;
BEGIN
postgres=# DELETE FROM oo; -- zároveň start vnější transakce v db omega
DELETE 9001
postgres=# SELECT * FROM oo;
 a
───
(0 rows)

postgres=# ROLLBACK; -- rollback v db postgres, a zároveň v db omega
ROLLBACK
postgres=# SELECT * FROM oo LIMIT 2;
  a
──────
 1000
 1001
(2 rows)

Zámky

FOR KEY SHARE a FOR NO KEY UPDATE zámky

Nepříjemnou vlastností implementace referenční integrity bylo použití stále ještě poměrně silných zámků. Např. referencování klíče při vložení nového řádku blokovalo aktualizace tabulky, která držela klíč.

-- session A
CREATE TABLE foo (a int PRIMARY KEY, b text);
CREATE TABLE bar (a int NOT NULL REFERENCES foo);
INSERT INTO foo VALUES (42);

BEGIN;
-- vytvoří SHARE LOCK na foo
INSERT INTO bar VALUES (42);

-- session B
-- vyžaduje EXCUSIVE LOCK a ten nezíská do ukončení session B
UPDATE foo SET b = 'Hello World' ;

Při troše smůly a souběžných aktualizacích tabulek s primárním a cizím klíčem operace skončily na deadlocku

DROP TABLE B;
DROP TABLE A;

CREATE TABLE A (
AID integer not null,
Col1 integer,
PRIMARY KEY (AID)
);


CREATE TABLE B (
BID integer not null,
AID integer not null,
Col2 integer,
PRIMARY KEY (BID),
FOREIGN KEY (AID) REFERENCES A(AID)
);

INSERT INTO A (AID) VALUES (1);
INSERT INTO B (BID,AID) VALUES (2,1);

Process 1:                             Process 2:
BEGIN;
                                       BEGIN;
UPDATE A SET Col1 = 1 WHERE AID = 1;
                                       UPDATE B SET Col2 = 1 WHERE BID = 2;
UPDATE B SET Col2 = 1 WHERE BID = 2;
                                       UPDATE B SET Col2 = 1 WHERE BID = 2;

postgres=#  UPDATE B SET Col2 = 1 WHERE BID = 2;
ERROR:  deadlock detected
DETAIL:  Process 2769 waits for ShareLock on transaction 1847; blocked by process 3006.
Process 3006 waits for ShareLock on transaction 1848; blocked by process 2769.
HINT:  See server log for query details.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."a" x WHERE "aid" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

Příklady byly převzaty z blogu autora patche.

Po dvouletém vývoji Alvaro Herrera dokončil patch implementující dvě nové úrovně zámků FOR KEY SHARE a FOR NO KEY UPDATE, které jsou použity v systémových triggerech zajišťujících RI (původně FOR SHARE a FOR UPDATE). Nyní, díky slabším zámkům nedochází k tomu, že by insert mohl blokovat update z důvodu RI (samozřejmě, že nesmí dojít ke změně klíče), případně je menší riziko, že update (kdy, stále platí, že update blokuje update) skončí na deadlocku z důvodu implicitních zámků vynucených RI. Implementace těchto nových zámků byla obtížná, a zdlouhavá (nesmělo dojít k narušení zpětné kompatibility) a většina uživatelů Postgresu si toho vůbec nevšimne. Pro mne osobně je to důležitý posun vpřed. S několika zákazníky jsem řešil přesně tyto problémy (a vždy se našlo řešení), nicméně nechtěné zamykání se špatně identifikuje a špatně řeší (musí dojít ke změně struktury tabulek, musí dojít ke změně aplikace).

lock_timeout

Zatím nebyla možnost, jak nastavit maximální délku čekání na získání zámku. Měli jsme možnost omezit dobu provádění příkazu – statement_timeout, měli jsme možnost nastavit dobu čekání v deadlocku (deadlock_timout). Dále jsme mohli určit, že na zámek nechceme čekat vůbec – klauzule NOWAIT. Nyní si můžeme nastavit maximální délku čekání na zámek v konfigurační proměnné lock_timeout. Co možná nemusí být úplně jasné je vztah ke statement_timeout. statement_timeout byl, a je maximální čas exekuce SQL příkazu bez ohledu na to, jestli příkaz byl prováděn nebo čeká na zámky, tj pokud je statement_timeout nenulový, pak smysluplné nastavení lock_timeout musí být menší než statement_timeout.

Datové typy a funkce

Optimalizací alokace paměti došlo k zrychlení aritmetických operací pro typ Numeric o cca 10 %.

Funkce

U funkce format lze nyní nastavit jako parametr šířku a zarovnání (stejně jako u funkce printf v C):

SELECT format('>>%10s<lt;', 'Hello');
     format
----------------
 >>     Hello<<
(1 row)

SELECT format('>>%-10s<<', 'Hello');
     format
----------------
 >>Hello     <<
(1 row)

Pro jednorozměrná pole jsou dvě nové funkce: array_remove a array_replace

SELECT array_remove(array['A','CC','D','C','RR'], 'RR');
 array_remove
--------------
 {A,CC,D,C}
(1 row)

SELECT array_remove('{{1,2,2},{1,4,3}}', 2); -- not allowed
ERROR:  removing elements from multidimensional arrays is not supported

SELECT array_replace(array[1,2,5,4],5,3);
 array_replace
---------------
 {1,2,3,4}
(1 row)

SELECT array_replace(array[1,2,5,4],5,NULL);
 array_replace
---------------
 {1,2,NULL,4}
(1 row)

Typ Range

Pro datový typ range je k dispozici estimátor. PostgreSQL používá optimalizaci dotazů založenou na hledání prováděcího plánu s nejnižší cenou. Předpokladem této optimalizace je, že dokážeme odhadnout efekt jednotlivých predikátů použitých v dotazu. To se daří někdy lépe, jindy hůře. V každém případě na to potřebujeme určitou funkci – v Postgresu se nazývá estimátor. Ta je připravena pro většinu vestavěných typů. Pokud nějaký typ nezná, tak vrátí konstantní procentní odhad – který někdy náhodnou sedí, ale většinou je mimo. 9.3 bude obsahovat podporu estimátoru i pro typ Range.

-- 9.3
postgres=# CREATE TABLE range_test AS
                    SELECT int4range(lb, lb + len) AS r
                        FROM (SELECT (sqrt(-2*ln(random())) * sin(2*pi()*random()) * 1000000)::int as lb,
                                              (-10000*ln(1.0 - random()) + 1)::int as len
                                        FROM generate_series(1,1000000)) x;
SELECT 1000000
postgres=# ANALYZE range_test;
ANALYZE
postgres=# EXPLAIN ANALYZE SELECT * FROM range_test WHERE r && int4range(200000,300000);
                                                    QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Seq Scan on range_test  (cost=0.00..17906.00 rows=42522 width=14) (actual time=0.172..450.784 rows=42417 loops=1)
   Filter: (r && '[200000,300000)'::int4range)
   Rows Removed by Filter: 957583
 Total runtime: 518.769 ms
(4 rows)

-- totéž v 9.2 (pozn. u mne je 9.2 rychlejší, protože je přeložená s vypnutými assercemi)
postgres=#  EXPLAIN ANALYZE SELECT * FROM range_test WHERE r && int4range(200000,300000);
                                                    QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Seq Scan on range_test  (cost=0.00..17907.38 rows=5001 width=32) (actual time=0.116..417.959 rows=42662 loops=1)
   Filter: (r && '[200000,300000)'::int4range)
   Rows Removed by Filter: 957338
 Total runtime: 486.894 ms
(4 rows)

Navíc nově lze pro typ range používat GiST a SP-GiST indexy.

postgres=# CREATE INDEX ON range_test USING gist (r);
CREATE INDEX
postgres=#  EXPLAIN ANALYZE SELECT * FROM range_test WHERE r && int4range(200000,300000);
                                                              QUERY PLAN

──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan on range_test  (cost=1641.83..7572.91 rows=42006 width=14) (actual time=31.144..168.160 rows=42759
loops=1)
   Recheck Cond: (r && '[200000,300000)'::int4range)
   ->  Bitmap Index Scan on range_test_r_idx  (cost=0.00..1631.33 rows=42006 width=0) (actual time=29.984..29.984 row s=42759 loops=1)
         Index Cond: (r && '[200000,300000)'::int4range)
 Total runtime: 235.531 ms
(5 rows)

JSON

Začnu oklikou – extenzí hstore. Tato extenze, respektive datový typ hstore, umožňuje pracovat s množinou dvojic (klíč, hodnota). Je to obdoba hash tabulky v některých programovacích jazycích (na rozdíl od nich neumožňuje vytvořit více úrovňové struktury). S tímto typem může Postgres, do jisté míry, emulovat bezschémové (NoSQL) databáze. Nově můžeme používat několik funkcí pro konverze mezi typy hstore a JSON.

postgres=# CREATE EXTENSION hstore ;
CREATE EXTENSION

-- zkouší podle obsahu identifikovat typ
postgres=# SELECT hstore_to_json_loose(hstore 'a=>10, b=>20, c=>t');
     hstore_to_json_loose
───────────────────────────────
 {"a": 10, "b": 20, "c": true}
(1 row)

-- vše jako řetězec
postgres=# SELECT hstore_to_json(hstore 'a=>10, b=>20, c=>t');
          hstore_to_json
──────────────────────────────────
 {"a": "10", "b": "20", "c": "t"}
(1 row)

Nově také můžeme používat agregační funkci json_agg:

postgres=# CREATE TABLE foo(id integer, attribs hstore);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(1, 'a=>10,b=>ahoj,c=>30');
INSERT 0 1
postgres=# INSERT INTO foo VALUES(2, 'a=>20,b=>Nazdar');
INSERT 0 1
postgres=# SELECT * FROM foo;
 id │              attribs
────┼───────────────────────────────────
  1 │ "a"=>"10", "b"=>"ahoj", "c"=>"30"
  2 │ "a"=>"20", "b"=>"Nazdar"
(2 rows)
postgres=# CREATE TABLE foo(id integer, attribs hstore);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(1, 'a=>10,b=>ahoj,c=>30');
INSERT 0 1
postgres=# INSERT INTO foo VALUES(2, 'a=>20,b=>Nazdar');
INSERT 0 1
postgres=# SELECT * FROM foo;
 id │              attribs
────┼───────────────────────────────────
  1 │ "a"=>"10", "b"=>"ahoj", "c"=>"30"
  2 │ "a"=>"20", "b"=>"Nazdar"
(2 rows)

postgres=# SELECT json_agg(q) FROM foo q;
                         json_agg
───────────────────────────────────────────────────────────
 [{"id":1,"attribs":{"a": "10", "b": "ahoj", "c": "30"}}, ↵
  {"id":2,"attribs":{"a": "20", "b": "Nazdar"}}]
(1 row)

postgres=# SELECT json_agg(q)
                       FROM (SELECT id, hstore_to_json_loose(attribs) attribs
                                      FROM foo) q;
                       json_agg
───────────────────────────────────────────────────────
 [{"id":1,"attribs":{"a": 10, "b": "ahoj", "c": 30}}, ↵
  {"id":2,"attribs":{"a": 20, "b": "Nazdar"}}]
(1 row)

Další výraznou novinkou je přepsání integrovaného JSON parseru. Přímo v Postgresu se tak můžeme dostat k libovolnému atributu JSON dokumentu (bez nutnosti používání externích knihoven). Autorem patche je Andrew Dunstan.

postgres=# SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');
 key │ value
─────┼───────
 a   │ foo
 b   │ bar
(2 rows)

postgres=# SELECT * FROM json_array_elements('[1,true, [2,false]]');
   value
───────────
 1
 true
 [2,false]
(3 rows)

postgres=# SELECT json_array_element('[{"a":"foo"}, "bar"]',0);
 json_array_element
────────────────────
 {"a":"foo"}
(1 row)

postgres=# SELECT json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6');
 json_extract_path_text
────────────────────────
 foo
(1 row)

postgres=# SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
 json_object_keys
──────────────────
 f1
 f2
(2 rows)

postgres=# SELECT json_object_field_text('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}', 'f1');
 json_object_field_text
────────────────────────
 abc
(1 row)

Prostředí pro psaní uložených procedur

PL/Python

V PL/Pythonu je možné přepsat SQLCODE výjimky:

CREATE FUNCTION plpy_raise_spiexception_override() RETURNS void AS $$
exc = plpy.spiexceptions.DivisionByZero()
exc.sqlstate = 'SILLY'
raise exc
$$ LANGUAGE plpythonu;

DO $$
BEGIN
   SELECT plpy_raise_spiexception_override();
EXCEPTION WHEN SQLSTATE 'SILLY' THEN
   -- NOOP
END
$$ LANGUAGE plpgsql;

Nově lze serializovat objekty – jedná se o implementaci funkce __str__()

CREATE FUNCTION result_str_test(cmd text) RETURNS text
AS $$
plan = plpy.prepare(cmd)
result = plpy.execute(plan)
return str(result)
$$ LANGUAGE plpythonu;

SELECT result_str_test($$SELECT 1 AS foo, '11'::text AS bar UNION SELECT 2, '22'$$);
                                   result_str_test
--------------------------------------------------------------------------------------
 < PLyResult status=5 nrows=2 rows=[{'foo': 1, 'bar': '11'}, {'foo': 2, 'bar': '22'}] >
(1 row)

SELECT result_str_test($$CREATE TEMPORARY TABLE foo1 (a int, b text)$$);
           result_str_test
--------------------------------------
 < PLyResult status=4 nrows=0 rows=[] >
(1 row)

PL/pgSQL

V PL/pgSQL letos je jenom jedna viditelná změna – příkaz RETURN a RETURN NEXT může nyní obsahovat i výraz kompozitního typu:

CREATE TYPE footype AS (x int, y varchar);

CREATE OR REPLACE FUNCTION foo() RETURNS footype AS $$
BEGIN
  -- není nutné deklarovat pomocnou proměnnou, ale při použití beztypových
  -- konstant (unknown typ) je nutné přetypování.
  RETURN (1, 'hello')::footype;
END;
$$ LANGUAGE plpgsql;

SELECT foo();
    foo
-----------
 (1,hello)

Podstatnější změna je pro běžného uživatele naprosto transparentní. Všechny SQL příkazy v PL/pgSQL (vyjma těch dynamických) používají cache pro ukládání prováděcích plánů. Při prvním spuštění SQL příkazu (nikoliv nezbytně funkce) se vytvoří prováděcí plán SQL příkazu a ten se používá opakovaně při každém dalším spuštění SQL příkazu. Prováděcí plán v cache zaniká ukončením session nebo zánikem libovolné referencované tabulky.

Doposud celý mechanizmus používání cache prováděcích plánů nebral zřetel na konfigurační proměnnou search_path (něco jako PATH v o.s.). Fakticky se pouštěl dotaz, který odpovídal nastavení této proměnné při prvním volání SQL příkazu. Neřešil se model, kdy se tabulky opakují ve více schématech (např. když každý zákazník má vlastní schéma – nikoliv databázi). Pokud během jednoho přihlášení bude vývojář přistupovat k tabulkám v jednom schématu (a pak se odhlásí), tak všechno bude fungovat podle očekávání. Pokud ale použije poolování spojení a schéma vybírá změnou search_path, tak se bude hrozně brzo divit, jak to vlastně nefunguje (uložené procedury v PL/pgSQL). Nicméně počínaje 9.3 se před vyhodnocením SQL příkazu v PL/pgSQL kontroluje, zda-li nedošlo ke změně search_path, a pokud ano, tak se SQL příkaz přeplánuje. Samozřejmě, že častá změna search_path může vést k neustálému přeplánování prováděcích plánů.

Konzole psql

\gset umožňuje uložit výsledek SQL příkazu do psql proměnných. To se může hodit při client-side skriptování (v kombinaci s bashem nebo makem). Syntaxe je trochu zvláštní, použití je ale jednoduché a praktické. Příkaz \gset se používá jako delimiter (ukončuje zápis a startuje vykonání SQL příkazu), případně vykoná poslední příkaz v historii. Výsledek je uložen v psql proměnných pojmenovaných podle sloupců výsledku. Pokud se příkazu \gset předá parametr, tak pak se tento parametr použije jako prefix názvů proměnných.

postgres=# SELECT 10 as a, 20 as b;
 a  │ b
────┼────
 10 │ 20
(1 row)

postgres=# \gset
postgres=# \echo :a :b
10 20
postgres=# SELECT 30 as a, 40 as b
postgres-# \gset mujprefix_
postgres=# \echo :mujprefix_a :mujprefix_b
30 40

Příkaz \watch je analogií systémového příkazu watch. Opakovaně spouští provádění naposledy zadaného SQL příkazu. Volitelným parametrem je čas čekání v sekundách. Default je 2 sec.

postgres=# SELECT datname, usename, query FROM pg_stat_activity
postgres-# \watch 1
                 Watch every 1s Wed May  1 20:13:47 2013

 datname  │ usename │                        query
──────────┼─────────┼──────────────────────────────────────────────────────
 postgres │ pavel   │
 postgres │ pavel   │ SELECT datname, usename, query FROM pg_stat_activity
(2 rows)

                 Watch every 1s Wed May  1 20:13:48 2013

 datname  │ usename │                        query
──────────┼─────────┼──────────────────────────────────────────────────────
 postgres │ pavel   │
 postgres │ pavel   │ SELECT datname, usename, query FROM pg_stat_activity
(2 rows)

                 Watch every 1s Wed May  1 20:13:49 2013

 datname  │ usename │                        query
──────────┼─────────┼──────────────────────────────────────────────────────
 postgres │ pavel   │
 postgres │ pavel   │ SELECT datname, usename, query FROM pg_stat_activity
(2 rows)

Administrace

Příjemnou téměř neviditelnou změnou je změna implementace sdílené paměti, kdy se přešlo na mmap API (na unixových systémech). Díky tomu se už nemusí nastavovat vyšší SHMMAX než byla hodnota shared_buffers a tudíž výchozí hodnota shared_buffers mohla být zvýšena na 128MB. Což je stále poměrně málo, ale je to příjemnější číslo než dřívějších 28MB.

Pokud používáte pg_dump a máte dostatečně rychlé IO, tak byste mohli ocenit jeho schopnost použít víc CPU – (podpora více jobů). V jednoduchém testu jsem viděl zrychlení – to ale bude pro každého uživatele specifické, podle podmínek, ve kterých provozuje server.

bash-4.1$ time /usr/local/pgsql/bin/pg_dump --jobs=2 --format=d -f /tmp/x1 postgres

real    2m37.514s
user    0m7.812s
sys 0m5.479s
bash-4.1$ time /usr/local/pgsql/bin/pg_dump --jobs=1 --format=d -f /tmp/x2 postgres

real    3m16.207s
user    0m7.414s
sys 0m4.003s

Drobností, která potěší, je „zneviditelnění“ hlášek o vytvoření závislých objektů – indexů, sekvencí:

--výstup 9.2
postgres=# CREATE TABLE foo(a SERIAL PRIMARY KEY, b int);
NOTICE:  CREATE TABLE will create implicit sequence "foo_a_seq" for serial column "foo.a"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE

--vystup 9.3
postgres=# CREATE TABLE foo(a SERIAL PRIMARY KEY, b int);
CREATE TABLE

ALTER ROLE ALL SET xxx

Výchozí konfiguraci (uloženou v souboru postgresql.conf) můžeme dodatečně dynamicky měnit pro jednotlivé uživatele (a případně pro jednotlivé databáze):

postgres=# CREATE ROLE tom LOGIN;
CREATE ROLE
postgres=# ALTER ROLE tom IN DATABASE postgres SET work_mem to '10MB';
ALTER ROLE
postgres=# \c postgres tom
You are now connected to database "postgres" as user "tom".
postgres=> SHOW work_mem ;
 work_mem
──────────
 10MB
(1 row)

Tohle je moje oblíbená funkce. Když dočasně potřebuji zvýšit úroveň logování a musím to udělat pro jednu vybranou databázi, jelikož kdybych to udělal globálně, tak zazdím server.

V 9.3 je možnost přenastavit vybranou konfigurační proměnnou pro všechny uživatele – nejen pro jednoho vybraného:

postgres=# ALTER ROLE ALL SET work_mem to '10MB';
ALTER ROLE
postgres=# \q
bash-4.1$ psql postgres
psql (9.3devel)
Type "help" for help.

postgres=# SHOW work_mem ;
 work_mem
──────────
 10MB
(1 row)

Možnost navázání replikace po výměně mastera

V předchozích verzích se replikace v Postgresu dostala k modelu master – hiearchický multi slave. Implementačním omezením byla nutnost vytvořit nové slaves při změně masteru. 9.3 umožňuje povýšení libovolného slavea na master a přesměrování ostatních slaves z původního masteru na nový master (dříve se všechny slaves musely vytvořit znovu – např. s využitím rsync to nemuselo být nějak náročné, ale určitě to byla práce navíc).

pg_isready

Při použití automatických konfiguračních systémů, které zajišťují jak konfiguraci, tak např. start serverů a počáteční inicializaci (např. puppet) není úplně jednoduché identifikovat čas, kdy server už může přijímat SQL příkazy (po startu nebo restartu). U databázového serveru s transakčním logem může start trvat dvě tři vteřiny, ale také hodiny (po recovery). Proto předdefinované usnutí (sleep) po startu serveru nemusí zafungovat. K zjednodušení skriptování je v Postgresu nová utilitka pg_isready, která čeká skutečně do doby, než server je schopný přijímat příkazy.

bash-4.1$ /usr/local/pgsql/bin/pg_isready --help
pg_isready issues a connection check to a PostgreSQL database

Usage:
  pg_isready [OPTION]...

Options:
  -d, --dbname=DBNAME      database name
  -q, --quiet              run quietly
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port
  -t, --timeout=SECS       seconds to wait when attempting connection, 0 disables (default: 3)
  -U, --username=USERNAME  database username

Report bugs to < pgsql-bugs@postgresql.org >.
bash-4.1$ /usr/local/pgsql/bin/pg_isready
/tmp:5432 - accepting connections

Ostatní

Možná někdo ocení možnost buildu dokumentace v EPUB formátu.

Background worker processes

Silnou stránkou PostgreSQL jsou její extenze, resp. celý systém, který umožňuje psát extenze. Background worker processes (BWP) je nový typ extenzí, které se spouští jako samostatný proces spolu se startem serveru (a ukončují se s ukončením běhu serveru). BWP mají přístup ke sdílené paměti serveru a pomocí signálů mohou komunikovat s ostatními procesy serveru. Do určité míry se jedná o generalizaci (zpřístupnění) mechanismu, který už je v Postgresu delší čas – mám na mysli implementaci procesů autovacuum, bgwriter, …

bash-4.1$ ps ax | grep postgres
 4191 pts/2    S+     0:00 psql postgres
 6284 ?        Ss     0:00 postgres: checkpointer process
 6285 ?        Ss     0:00 postgres: writer process
 6286 ?        Ss     0:00 postgres: wal writer process
 6287 ?        Ss     0:00 postgres: autovacuum launcher process
 6288 ?        Ss     0:00 postgres: stats collector process

Počínaje 9.3 si můžeme přidávat vlastní obslužné procesy. Zatím existuje jen jedna referenční implementace worker_spi. Použití by mělo být docela široké – démon ala cron (scheduler), výpočetní jednotka využívající GPU pro hw akceleraci operace sort, výpočetní jednotka pro multi CPU zpracování SQL příkazu, monitorovací démon (audit), runtime pro uložené procedury … . Myslím si, že tak dva roky bude trvat, než se objeví BWP, které budou víc k užitku než experimentální, ale nepochybuji, že se objeví. Byla by bomba, kdybychom BWP mohli psát v některém z PL jazyků – Perl, Python.

Kontrolní součty datových stránek

Checksumy datových stránek je určitě nejdiskutovanější a nejdiskutabilnější novou funkcí v PostgreSQL 9.3. Je to funkce, bez které se Postgres docela dobře obešel (spolehlivost hw a sw je jinde než před 20 roky). Na druhou stranu je to funkce, která je často explicitně vyžadována korporátní sférou, a je fakt, že nakopnutý index (currupted index) může udělat v datech zvěrstva. Takže se několik let řešilo, jestli checksumy budou nebo nebudou, a v 9.3 se pak ještě řešilo, jakou formou budou implementovány (tak aby měly smysl, v Postgresu se nehraje na marketing). Nebylo vůbec jednoduché najít a napsat kód, který by byl dostatečně rychlý, nebyl nikde patentovaný a byl přenositelný mezi podporovanými platformami. I tak může zapnutí kontrolních součtů citelně zpomalit databázi (v případě, že máte většinu živých dat ve filesystem cache), takže defaultně je tato kontrola neaktivní.

Kontrolu součtů je možné zatím aktivovat pouze v příkazu initdb při vytváření databázového clusteru. Při provozu nelze checksumy zapínat/vypínat.

/usr/local/pgsql/bin/initdb --data-checksums -D /usr/local/pgsql/data

V případě, že se zjistí špatný součet, tak se vypíše varování „page verification failed, calculated checksum xxx but expected yyy“ a podle hodnoty konfigurační proměnné ignore_checksum_failure se pokračuje dále nebo se ukončí provádění příkazu. Výchozí nastavení ignore_checksum_failure je off, tedy že se nevalidní checksum neignoruje. Pro velkou většinu aplikací by zpomalení způsobené výpočtem kontrolního součtu mělo být akceptovatelné (za běžného provozu – v benchmarcích to bude vždy vidět).

Strukturovaná chybová hlášení

U některých chyb musel aplikační vývojář separovat informace z textu hlášky – název tabulky, název sloupce. V ANSI SQL je definován seznam položek, které se, v případě chyby, plní odpovídajícím obsahem. Ty základní jsou k dispozici i v 9.3: SCHEMA_NAME, TABLE_NAME, DATATYPE_NAME, CONSTRAINT_NAME. Pokud SQL příkaz skončí chybou, má programátor možnost se k těmto položkám dostat. Paradoxně, kvůli zdržení (tento patch se několikrát předělával) tyto položky nejsou přístupné z PL/pgSQL (příkaz GET STACKED DIAGNOSTICS), ačkoliv to byla původní motivace pro vývoj tohoto patche.

postgres=# CREATE TABLE foo(a int CHECK (a > 100));
CREATE TABLE
postgres=# \set VERBOSITY verbose
postgres=# INSERT INTO foo VALUES(10);
ERROR:  23514: new row for relation "foo" violates check constraint "foo_a_check"
DETAIL:  Failing row contains (10).
SCHEMA NAME:  public
TABLE NAME:  foo
CONSTRAINT NAME:  foo_a_check
LOCATION:  ExecConstraints, execMain.c:1600

Možná se podivíte, proč není vyplněna položka COLUMN_NAME? Důvodem je interní implementace constraintů v Postgresu – jsou totiž vztaženy k tabulce, nikoliv ke sloupci. Každý sloupcové omezení se transformuje do tabulkového omezení. Případné parsování názvu omezení nemusí být jednoznačné, takže se usoudilo, že bude lepší zobrazovat (plnit) pouze ty položky, jejichž obsah je jednoznačně určený.

-- originální zápis
CREATE TABLE foo(a int CHECK (a > 100));

-- výstup z pg_dumpu
bash-4.1$ /usr/local/pgsql/bin/pg_dump postgres -t foo -s

CREATE TABLE foo (
    a integer,
    CONSTRAINT foo_a_check CHECK ((a > 100))
);

pg_xlogdump

Přiznám se, že debugovat transakční log Postgresu je posledním druhem zábavy, o který bych stál. Přesto převedení transakčního logu může mít svůj význam – někomu to může pomoci napsat novou formu replikace, jinému pochopit fungování databáze, dalšímu pak zjistit příčiny problémů a např. přesně určit bod ukončení obnovy ze zálohy.

pro příkazy

postgres=# SELECT pg_current_xlog_location();
 pg_current_xlog_location
──────────────────────────
 0/17C8347C
(1 row)

postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO b VALUES(10);
INSERT 0 1
postgres=# INSERT INTO b VALUES(20);
INSERT 0 1
postgres=# UPDATE b SET a = 30;
UPDATE 2
postgres=# COMMIT;
COMMIT

je dump

[root@nemesis pavel]# /usr/local/pgsql/bin/pg_xlogdump -p /usr/local/pgsql/data/pg_xlog/ --start 0/17C8347C
rmgr: Heap        len (rec/tot):     31/    59, tx:       1867, lsn: 0/17C8347C, prev 0/17C83454, bkp: 0000, desc: insert: rel 1663/12895/46441; tid 0/2
rmgr: Heap        len (rec/tot):     31/    59, tx:       1867, lsn: 0/17C834B8, prev 0/17C8347C, bkp: 0000, desc: insert: rel 1663/12895/46441; tid 0/3
rmgr: XLOG        len (rec/tot):    144/   172, tx:       1867, lsn: 0/17C834F4, prev 0/17C834B8, bkp: 0000, desc: page hint: base/12895/46441 block 0
rmgr: Heap        len (rec/tot):     47/    75, tx:       1867, lsn: 0/17C835A0, prev 0/17C834F4, bkp: 0000, desc: hot_update: rel 1663/12895/46441; tid 0/2 xmax 1867 ; new tid 0/4 xmax 0
rmgr: Heap        len (rec/tot):     47/    75, tx:       1867, lsn: 0/17C835EC, prev 0/17C835A0, bkp: 0000, desc: hot_update: rel 1663/12895/46441; tid 0/3 xmax 1867 ; new tid 0/5 xmax 0
rmgr: XLOG        len (rec/tot):     68/    96, tx:          0, lsn: 0/17C83638, prev 0/17C835EC, bkp: 0000, desc: checkpoint: redo 0/17C834F4; tli 1; prev tli 1; fpw true; xid 0/1868; oid 54592; multi 1; offset 0; oldest xid 1799 in DB 1; oldest multi 1 in DB 1; oldest running xid 0; online
rmgr: Transaction len (rec/tot):     12/    40, tx:       1867, lsn: 0/17C83698, prev 0/17C83638, bkp: 0000, desc: commit: 2013-05-05 20:57:51.206989 CEST
pg_xlogdump: FATAL:  error in WAL record at 0/17C83698: record with zero length at 0/17C836C0

Připravuje se do 9.4

PostgreSQL vznikal v době, kdy výkonnostním hrdlem bylo primárně IO, a tudíž většina optimalizací je nějak navázána na minimalizaci počtu IO operací. S CPU si nikdo hlavu nelámal (až na pár kritických míst – zamykání, správa paměti). Většinou databáze nic nedělá nebo čeká na disk. Situace se změnila v posledních několika letech. Máme tu FussionIO, SSD disky. K dispozici jsou servery s 128, 256 GB RAM (nebo ještě více). Navíc se leckde PostgreSQL používá jako analytická databáze – pro což rozhodně nebyla navržena. Objevují se nová hrdla, která je nutné odstraňovat – naposledy se ukázalo, že relativně nekomplikovanou úpravou lze urychlit výpočet agregačních funkcí pro typ numeric o 10..40 % (za předpokladu, že hrdlem je CPU). Tento patch je už připravený pro 9.4 (a v GoodData máme backport do 9.2).

Z dalších patchů se už snad dostane na row level security, podrobnější kontrolu plpgsql kódu a další odkládané patche. Tipnul bych si, že hlavním motivem 9.4 bude snaha o dotažení funkcionality přidané v 9.2 a v 9.3. Výhledově je velkým tématem paralelizace exekuce dotazů (nutným předpokladem byla podpora backgroud workerů), aktuálně ale ještě nikdo ani nezačal pracovat na prototypu (i když v EnterpriseDB intenzivně uvažují o paralel sortu). V pokročilé fázi přípravy je implementace logické replikace. Existuje prototyp HStore s podporou vnořených struktur a polí.

PgConf.eu 2012 – Praha, říjen 2012

Evropské sdružení uživatelů PostgreSQL „PostgreSQL Europe“ pořádá každý rok několikadenní celoevropskou konferenci cílenou hlavně na běžné uživatele – první den je možnost navštívit celodenní nebo půldenní kurzy, které vedou ve svém oboru opravdové kapacity a další tři dny jsou paralelní přednášky. Po několika pokusech (i když ty předchozí pokusy nebyly úplně myšleny vážně) se CSPUGu podařilo dostat tuto konferenci do Prahy, a také se CSPUG částečně podílel na její organizaci. Ohromný kus práce odvedl Tomáš Vondra při přípravě coby člen organizačního výboru. Svůj čas věnovali i další členové CSPUGu při zajištění organizace na místě. Měl jsem docela vítr z toho, že se něco nepovede nebo něco selže, ale vše dopadlo na jedničku (i počasí v Praze se alespoň na jeden večer trochu umoudřilo a udělalo se pěkně – a to byla Praha celý listopad utopená v smogové inverzi). Tato konference byla zatím svého druhu největší (cca 300 lidí, kdyby byly větší kapacity, byla by účast ještě větší) a byla atypická i tím, že velkou část hostů tvořili domácí hosté – cca kolem 100 lidí bylo z ČR. Alespoň podle návštěvnosti to vypadá, že Česká republika je postgresovou velmocí. Pro mne osobně byl asi největší zážitek potkat a být nějaký čas s Joe Celkem – což je top SQL guru SQL veterán. Když jsem před deseti lety četl jeho knihy, tak by mne nikdy nenapadlo, že se s tímhle pánem setkám osobně a budu ho moci jeden večer provázet po Praze. Přidávám odkaz na Tomášovu fotogalerii.

PostgreSQL v GoodData – provoz PostgreSQL v cloudu

Letos už cloud není buzzword, a tak snad mohu přidat jeden odstavec o tom, jak také lze použít PostgreSQL v cloudu – konkrétně v GoodData, kde pracuji (další databází, kterou používáme, je sloupcová OLAP databáze Vertica, ale o ní by to bylo v tomto článku offtopic). GoodData dodává Business intelligence řešení jako službu (s webovým rozhraním). Jako databázový backend používáme PostgreSQL. Běžíme na vlastní (patchované) řadě 9.1 (a připravujeme migraci na 9.2). Běžně backportujeme zajímavé patche z 9.2 a 9.3. Případně naše patche tlačíme do upstreamu (9.2 a 9.3) a opět backportujeme do 9.1. K mému překvapení to celé docela dobře funguje, přestože PostgreSQL není optimalizováno pro OLAP a amazoní cloud pro provozování velkých klasických databází. Navíc používáme (v GoodData) Postgres hodně netradičně – na každém serveru je cca 1500 databází o velikosti mezi desítkami MB až stovkami GB – Postgres drží data pro BI aplikaci a v databázích mohou být stovky až desítky tisíc tabulek.

S relativně drobnými úpravami tohle Postgres zvládá – asi nejvážnější problém byl s chováním evidence provozních statistik, kdy původně tato evidence byla v jednom souboru pro celý databázový cluster. Tento soubor se aktualizoval tak, že se na disku vytvořila jeho kopie a starší verze se odstranila. Už při několika stech tabulkách v databázi a několika tisících databází tento mechanismus bral veškerou kapacitu IO (na nepříliš rychlých EBS discích Amazonu). Workaroundem byl přesun do tmpfs, což sníží nároky na IO, ale stále zůstane velký počet systémových volání. Tomáš Vondra přišel s nápadem (a napsal patch) rozdělit tento soubor, tak, že každá databáze má svou evidenci ve svém vlastním souboru. To vyřešilo náš problém, a navíc se Tomášovi podařilo protlačit svůj patch do upstreamu. Před dvěma roky bych nevěřil, že to může fungovat (pracovat takto s Postgresem). Ale funguje to.

Závěr

Málokterý komunitní projekt si udržuje takovou stabilitu a stabilní rychlost vývoje. Perfektně je to vidět z reportu na Ohloh. A to navzdory tomu, že se zvětšuje složitost a rozsah patchů. Je to projekt, který má dlouhou historii, který má výbornou komunitu, aktuálně stabilní a (na komunitní projekt) početný tým vývojářů, má výbornou reputaci, každý rok přitahuje více vývojářů a hlavně, a přestože ještě na něm bude hodně práce, PostgreSQL spolehlivě slouží statisícům uživatelů po celém světě – ČR nevyjímaje. Novou verzi PostgreSQL si můžete stáhnout, vyzkoušet a otestovat.