PostgreSQL 9.2 (2012)

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

Vývoj 9.2 pokračoval v kolejích vyjetých 9.0 a 9.1. Díky tomu, že v loni a předloni se vyřešily některé diskutabilní otázky (správa zdrojového kódu, název, vývojový cyklus), tak poslední rok byl hlavně a jen o práci. Ne, že by se občas nevedly bouřlivé diskuze. Ale téměř vždy se poměrně rychle podařilo najít rozumný kompromis - často i řešení, které bylo kvalitnější než původní návrhy. Poměrně razantních změn doznala architektura (nový proces pro CHECKPOINT). Víc než obvykle se měnil optimalizátor dotazů a změn nebyl ušetřen ani executor (zrychlení řazení o 20%). Některé koncepty, na kterých se pracovalo posledních několik let, dozrály a dostaly se do jádra (LEEKPROOF funkce, datový typ RANGE, datový typ JSON, podpora SP-GiST indexů). Třešničkou na dortu je funkcionalita, kterou uživatelé roky žádali - tzv index only scan. Konečně byl čas a prostor otestovat a připravit PostgreSQL pro o něco výkonnější hw než je zatím obvyklé. Pokud máte dost paměti na to, aby se Vám databáze vešla do paměti a více než 32 procesorů, pak byste měli zaregistrovat výrazné zrychlení. Dostalo se i na úspornost provozu - v případě, že PostgreSQL nebude zatížen, tak i servisní procesy budou v klidu (bez zbytečných periodických aktivit).

Změny v PL a SQL

Funkce pg_trigger_depth() nám umožňuje včas kontrolovaně zastavit rekurzi triggeru. Přiznám se, že si nedovedu představit praktické použití této funkce vyjma debugování a řízeného vyhození výjimky (na internetu je možné dohledat několik příkladů - např. pro určení, zda-li by záznam modifikovaný z triggeru nebo z aplikace). Nově jsou k dispozici podmíněné příkazy ALTER - ALTER [TABLE|VIEW|SEQUENCE|INDEX) IF EXISTS,

GET STACKED DIAGNOSTICS v PL/pgSQL

Na jednom projektu, na kterém jsem spolupracoval jsem potřeboval zachytit výjimku, zalogovat ji, a přeposlat dál. Což v PL/pgSQL bylo možné jen částečně, takže jsem narazil. V PL/pgSQL jsem neměl způsob, jak se dostat k jednotlivým položkám výjimky vyjma SQLERRM a SQLCODE. Po implementaci příkazu GET STACKED DIAGNOSTICS jsou veškerá data z výjimky (včetně kontextu) čitelná i z PL/pgSQL. 

create or replace function stacked_diagnostics_test() returns void as $$
declare _detail text;
        _hint text;
        _message text;
begin
  perform raise_test();
exception when others then
  get stacked diagnostics
        _message = message_text,
        _detail = pg_exception_detail,
        _hint = pg_exception_hint;
  raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
end;
$$ language plpgsql;
select stacked_diagnostics_test();

Použití pojmenovaných parametrů v SQL

Počínaje 9.2 padá omezení pro SQL funkce, kde nebylo možné používat pojmenované parametry - tj parametry funkce byly přistupné jen poziční notaci ($1, $2, $3). V 9.2 můžeme používat jména parametrů. Pozn. nekontrolují se kolize identifikátorů - vyšší prioritu mají SQL identifikátory. Parametr funkce je dostupný i pomocí notace název_funkce.název_parametru: 

create or replace function omega_b(a int) 
returns int as $$ 
select b from omega where a = omega_b.a 
$$ language sql;

Notifikační trigger - Trigger Change Notification

Návštěvníci posledních dvou P2D2 měli možnost se seznámit s mechanismem LISTEN/NOTIFY - jedná se o asynchronní notifikaci server/clients. Praktické použití tohoto mechanismu by měla zjednodušit funkce triggered_change_notification z modulu tcn. Tato funkce, pokud se použije jako obsluha triggeru rozesílá všem klientům přihlášeným k odběru zprávy tcn notifikaci o změně obsahu tabulky: 

postgres=# create extension tcn;
CREATE EXTENSION
postgres=# create table t1(id serial primary key, 
                           a int, 
                           b int);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
postgres=# create trigger t1_notify 
              after insert or update or delete on tab 
              for each row execute procedure triggered_change_notification();
CREATE TRIGGER
postgres=# listen tcn;
LISTEN
postgres=# insert into t1(a,b) values(10,20);
INSERT 0 1
Asynchronous notification "tcn" with payload ""t1",I,"id"='4'" received from server process with PID 10152.
postgres=# insert into t1(a,b) values(11,40);
INSERT 0 1
Asynchronous notification "tcn" with payload ""t1",I,"id"='5'" received from server process with PID 10152.
postgres=# delete from t1;
DELETE 2
Asynchronous notification "tcn" with payload ""t1",D,"id"='4'" received from server process with PID 10152.
Asynchronous notification "tcn" with payload ""t1",D,"id"='5'" received from server process with PID 10152.

NOT VALID CHECK constraints

Přidání omezující podmínky (constraint) do větší tabulky může být časově docela náročné, kontrolují se všechny záznamy v tabulce. V důsledku odpovídající příkaz ALTER TABLE bude trvat dlouho - a to může být na zatížené databázi docela problém (například kvůli zamykání - po dobu kontroly bude tabulka více-méně read only). Tento problém může být částečně vyřešen rozdělením operace do dvou kroků - přidáním nevalidní omezující podmínky. V3echny nově přidané záznamy pak musí splňovat nově přidanou, byť nevalidní podmínku - která může být validována dodatečně (při nižší zátěži). V další verzi by pak validace měla vyžadovat i méně agresivní zámky. 

ALTER TABLE test ADD CHECK ( field >= 0 ) NOT VALID;
ALTER TABLE test VALIDATE CONSTRAINT test_field_check;

Nové datové typy

Datové typy RANGE

Počínaje 9.2 si vývojář může zaregistrovat vlastní datové typy třídy RANGE. Hodnoty z třídy RANGE slouží k popisu nějakého rozsahu nebo intervalu. Hodí se například pro temporální databáze. Nejčastěji používané typy jsou již připravené: 

postgres=> \dT *range*
                       List of data types
   Schema   |   Name    |              Description              
------------+-----------+---------------------------------------
 pg_catalog | anyrange  | 
 pg_catalog | daterange | range of dates
 pg_catalog | int4range | range of integers
 pg_catalog | int8range | range of bigints
 pg_catalog | numrange  | range of numerics
 pg_catalog | tsrange   | range of timestamps without time zone
 pg_catalog | tstzrange | range of timestamps with time zone
(7 rows)

Název typu slouží jako i jako konstruktor hodnoty: 

postgres=> select daterange(current_date, current_date + 4);
        daterange        
-------------------------
 [2012-02-24,2012-02-28)

postgres=> select numrange(10,30) n1, numrange '[10,20]' n2;
   n1    |   n2    
---------+---------
 [10,30) | [10,20]
(1 row)

K dispozici je několik funkcí: empty, non_empty, lower (spodní limit), upper (horní limit), možnost zjištění překryvu, možnost určení průniku, test zda RANGE obsahuje hodnotu: 

-- průnik
postgres=> select int4range(10, 20) * int4range(15, 25);
 ?column? 
----------
 [15,20)
(1 row)

Původní motivací byla podpora temporálních databází - pomocí "exclusion constraints" můžeme jednoduše zajistit, že hodnoty typu RANGE budou disjunktivní (tj. nesmí dojít k překryvu rezervací): 

create table rezervace(trvani tsrange);

alter table rezervace
  add exclude using gist (trvani WITH &&);

insert into  rezervace values
  ( '[2010-01-01 11:30, 2010-01-01 13:00)' );
insert into  rezervace values
  ( '[2010-01-01 14:45, 2010-01-01 15:45)' );

insert into  rezervace values
  ( '[2010-01-01 15:00, 2010-01-01 16:00)' );
ERROR:  conflicting key value violates exclusion constraint "rezervace_trvani_excl"
DETAIL:  Key (trvani)=(["2010-01-01 15:00:00","2010-01-01 16:00:00")) conflicts with existing key (trvani)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")).

Bez typů RANGE se k uložení používaly dvě nezávislé hodnoty - což v některých sutuacích vedlo k tomu, že se nepoužil index a bylo nutné převést data do multidimenzionálního prostoru a použít multidimenzionální indexy. S typy RANGE je takový postup minulostí.

JSON

9.2 obsahuje podporu pro JSON. Je to hodně podobné podpoře XML. Dokument je uložen jako text (nicméně díky TOASTu může být zkomprimován) a databáze zajistí jeho validitu. Dále jsou k dispozici dvě funkce - array_to_json a row_to_json, které umožní vygenerovat dokument v JSONu. Podpora JSONu není v ANSI SQL, jako je XML a tudíž se využívají pouze generické mechanismy a nástroje PostgreSQL - nedošlo k úpravě parseru. SQL/XML poskytuje o hodně pohodlnější interface. Na druhou stranu, díky jednoduchosti JSONu není pro jeho sestavení a validaci potřeba žádná externí knihovna (jako je tomu u XML, kde je to celé postavené nad libXML2), a tudíž je vygenerování JSONu o hodně rychlejší než vygenerování XML: 

postgres=# create table f(a int, b boolean, c text, d date);
CREATE TABLE
postgres=# insert into f values(10,true, 'Ahoj', current_date),
                               (20, false,'Světe', current_date+1);
INSERT 0 2
postgres=#  select array_to_json(array_agg(f), true) 
               from f;
                  array_to_json                   
──────────────────────────────────────────────────
 [{"a":10,"b":true,"c":"Ahoj","d":"2012-02-25"}, ↵
  {"a":20,"b":null,"c":"Světe","d":"2012-02-26"}]
(1 row)

postgres=# select row_to_json(f) 
              from f;
                  row_to_json                   
────────────────────────────────────────────────
 {"a":10,"b":true,"c":"Ahoj","d":"2012-02-25"}
 {"a":20,"b":null,"c":"Světe","d":"2012-02-26"}
(2 rows)

-- pro projekci je nutné použít derivovanou tabulku
postgres=#  select array_to_json(array_agg(x), true) from (select a,b from f ) x;
    array_to_json    
─────────────────────
 [{"a":10,"b":true},↵
  {"a":20,"b":null}]
(1 row)

-- druhý parametr u funkcí je nepovinný
postgres=# select array_to_json(array_agg(row_to_json(f))) from f;
                                          array_to_json                                          
─────────────────────────────────────────────────────────────────────────────────────────────────
 [{"a":10,"b":true,"c":"Ahoj","d":"2012-02-25"},{"a":20,"b":false,"c":"Světe","d":"2012-02-26"}]
(1 row)

Bezpečnost

security bariers

Pohledy lze v SQL databázi použít také k omezení přístupu k datům. Z důvodu implementace pohledů v PostgreSQL nezajišťují pohledy skutečně bezpečný přístup (pokud běžný uživatel může vytvářet funkce v PL/pgSQL - (viz leak views)). K prolomení ochrany potřebujeme funkci s minimální cenou, která zobrazí na ladící výstup hodnotu parametru: 

create or replace function public.fx(integer)
 returns boolean
 language plpgsql
 cost 1e-05
as $function$
begin
  raise notice '%', $1;
  return true;
end;
$function$

Vyrobím si tabulku omega, a pohled omega_view, přičemž pohled omega_view bude přístupný všem uživatelům a bude zobrazovat pouze liché řádky: 

postgres=# create table omega(a int);
CREATE TABLE
postgres=# create view omega_view as select a from omega where a % 2 = 1;
CREATE VIEW
postgres=# insert into omega values(1),(2),(3);
INSERT 0 3
postgres=# grant select on omega_view to public;
GRANT
postgres=# set role to tom;
SET
postgres=> select * from omega;
ERROR:  permission denied for relation omega
postgres=> select * from omega_view;
 a 
───
 1
 3
(2 rows)

Nicméně, pokud Tom použije funkci fx, tak se dostane i k sudým řádkům (sice pouze skrz ladící výstup, ale i tak to může být problém): 

postgres=> select a from omega_view where fx(a);
NOTICE:  1
NOTICE:  2
NOTICE:  3
  ...
 a 
───
 1
 3
(2 rows)

Kde je zakopaný pes? Optimalizace díky flatteningu a nižší ceně upřednostní funkci fx() před operátorem modulo: 

postgres=> explain select a from omega_view where fx(a);
                      QUERY PLAN                      
──────────────────────────────────────────────────────
 Seq Scan on omega  (cost=0.00..46.00 rows=4 width=4)
   Filter: (fx(a) AND ((a % 2) = 1))
(2 rows)

Výsledek je ok, ale zabezpečení bylo prolomeno. Donedávna byla dvě možní řešení - pohled nadefinovat s klauzulí OFFSET 0 (čímž se ale vyblokuje optimalizace) nebo běžnému uživateli odepřít možnost definovat vlastní funkce: 

-- utocnik neuvidi nepatricna data, ale take nedojde k pouziti pripadneho indexu
postgres=> explain select a from omega_view where fx(a) and a between 1 and 10;
                                  QUERY PLAN                                   
───────────────────────────────────────────────────────────────────────────────
 Subquery Scan on omega_view  (cost=0.00..190.80 rows=1 width=4)
   Filter: (fx(omega_view.a) AND (omega_view.a >= 1) AND (omega_view.a <= 10))
   ->  Limit  (cost=0.00..190.04 rows=50 width=4)
         ->  Seq Scan on omega  (cost=0.00..190.04 rows=50 width=4)
               Filter: ((a % 2) = 1)

V 9.2 se tento bezpečnostní nebo výkonnostní problém řeší systémově - zavádí se tzv bezpečnostní bariéry, které blokují některé potenciálně rizikové optimalizace: 

create view omega_view with (security_barrier=true) as select a from omega where a % 2 = 1;
postgres=# set role to tom;
SET
postgres=> select a from omega_view where fx(a) and a between 1 and 10;
NOTICE:  1
NOTICE:  3
NOTICE:  5
NOTICE:  7
NOTICE:  9
 a 
───
 1
 3
 5
 7
 9
(5 rows)

postgres=> explain select a from omega_view where fx(a) and a between 1 and 10;
                                     QUERY PLAN                                     
────────────────────────────────────────────────────────────────────────────────────
 Subquery Scan on omega_view  (cost=0.00..8.49 rows=1 width=4)
   Filter: fx(omega_view.a)
   ->  Index Only Scan using omega_a_idx on omega  (cost=0.00..8.48 rows=1 width=4)
         Index Cond: ((a >= 1) AND (a <= 10))
         Filter: ((a % 2) = 1)
(5 rows)

Takže pokud plánujete použít pohled pro omezení přístupu k datům, nezapomeňte pohled označit atributem security_barier.

Výkon

Jako obvykle došlo k úpravám planneru (optimalizace). Optimalizátor v 9.2 by si měl o něco lépe poradit s dotazy, kde optimální plán vyžaduje zanořený nested-loop. Robert Haas (a nejen on) věnoval hromadu času odstranění některých interních zámků, což by se mělo projevit na platformách, kde je dostatek paměti (databáze se vejde beze zbytku do paměti) a dostatek CPU (obvykle 32 CPU a více). Minimálně v ČR vím o jedné instalaci PostgreSQL, kde by rozhodně měli 9.2 z tohoto důvodu vyzkoušet. Docela zásadně se optimalizovala implementace ORDER BY. V případě, že je možné provést seřazení pouze v paměti, tak můžete očekávat 20-30% zrychlení.

Také implementace příkazu COPY doznala změn - v komentáři k patchi je poznámka, že urychlení by se mělo týkat hlavně úzkých dlouhých tabulek a paralelního načítání. Z jednoduchého syntetického testu (obnova dvou cca 250MB tabulek) mi vychází trochu něco jiného - zhruba 20% zrychlení v jednom vlákně (z 76 sec na 60 sec) a zhruba 2.5% zpomalení (z 40 na 41 sec) v importu ve dvou vláknech (výsledky berte hodně orientačně - test byl velice jednoduchý a provedený na notebooku (starší DELL 830), nikoliv odpovídajícím železe).

Chování příkazu COMMIT lze ovlivnit nastavením proměnné synchronous_commit. Tato proměnná ovlivňuje, co všechno se musí stát, než DB "prohlásí" příkaz COMMIT za provedený. Výchozí nastavení ON je maximálně bezpečné. Naopak nastavení OFF bezpečné není - v případě pádu můžeme přijít o transakce, přestože databáze potvrdila klientu jejich úspěšné ukončení (nicméně i v případě ztráty posledních transakcí nedojde k porušení konzistence databáze). V 9.2 můžeme použít pro nastavení této proměnné hodnoty "remote_write" a "local". "remote_write" má smysl pro synchronní replikaci - způsobí, že pro dokončení COMMITu na masteru stačí zápis do transakčního logu repliky aniž by se čekalo na fsync (vynucený zápis cache). Při této úrovni můžete přijít o transakce pouze v případě, že dojde zároveň k havárii primárního serveru a repliky. O něco vyšší úroveň "local" vynutí čekání na dokončení fsyncu na primárním serveru (bez čekání na fsync repliky). A když už jsem nakousl replikaci - v 9.2 nechybí podpora pro víceúrovňovou replikaci - master/slave/slave - tj. replikovat lze i repliku.

SP-GiST

Teodor Sigaev and a Bartunov napsali podporu pro další třídu indexů: SP-GiST. Nad touto třídou indexů lze vybudovat quad-trees, k-d trees, suffix trees indexy, které pro vybrané spektrum úloh jsou vhodnější než GiST a to ať dobou přístupu, tak dobou vytváření indexu. Jedná se o horkou novinku - význam se ukáže během několika dalších let (až dojde k integraci v PostGISu - komunita kolem PostGISu sponzorovala vývoj). V prezentacích se demonstruje až 3-5 násobné zrychlení vůči GiSTu. Jelikož nemám k dispozici vhodná data, tak předávám, jak jsem dostal. 

--
-- SP-GiST
--
CREATE TABLE quad_point_tbl AS
    SELECT point(unique1,unique2) AS p FROM tenk1;
INSERT INTO quad_point_tbl
    SELECT '(333.0,400.0)'::point FROM generate_series(1,1000);
CREATE INDEX sp_quad_ind ON quad_point_tbl USING spgist (p);
CREATE TABLE kd_point_tbl AS SELECT * FROM quad_point_tbl;
CREATE INDEX sp_kd_ind ON kd_point_tbl USING spgist (p kd_point_ops);
CREATE TABLE suffix_text_tbl AS
    SELECT name AS t FROM road;
INSERT INTO suffix_text_tbl
    SELECT '0123456789abcdef' FROM generate_series(1,1000);
INSERT INTO suffix_text_tbl VALUES ('0123456789abcde');
INSERT INTO suffix_text_tbl VALUES ('0123456789abcdefF');
CREATE INDEX sp_suff_ind ON suffix_text_tbl USING spgist (t);

Index only scan

Jednou z nevýhod multigenerační architektury (minimálně její implementace v PostgreSQL) byla nutnost každý záznam vybraný pomocí indexu ověřit na zdrojové tabulce (a i tehdy, když index obsahoval všechna požadovaná data). Tento nedostatek se nyní podařilo vyřešit - díky implementaci tzv visibility maps (primárně byly implementovány pro optimalizaci provádění příkazu VACUUM). Podpora metody index only scan může relativně razantně zrychlit některé dotazy typu SELECT agg(x) FROM tab WHERE x = konstanta: 

postgres=# \dt+
                    List of relations
 Schema | Name  | Type  |  Owner   |  Size  | Description 
--------+-------+-------+----------+--------+-------------
 public | omega | table | postgres | 349 MB | 
(1 row)

postgres=# select count(*) from omega;
  count   
----------
 10100000
(1 row)

postgres=# vacuum analyze omega;
VACUUM
Time: 897.607 ms

postgres=# select count(*) from omega where a between 1 and 500;
 count  
--------
 504350
(1 row)

Time: 1312.926 ms

postgres=# create index on omega(a);
CREATE INDEX
Time: 17334.431 ms

postgres=# select count(*) from omega where a between 1 and 500;
 count  
--------
 504350
(1 row)

Time: 77.910 ms

postgres=# select sum(b) from omega where a between 501 and 1000;
    sum     
------------
 2512689175
(1 row)

Time: 1216.973 ms

postgres=# create index on omega(a,b);
CREATE INDEX
Time: 19097.679 ms

postgres=# select sum(b) from omega where a between 501 and 1000;
    sum     
------------
 2512689175
(1 row)

Time: 88.614 ms

postgres=# explain select sum(b) from omega where a between 501 and 1000;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=17134.85..17134.86 rows=1 width=4)
   ->  Index Only Scan using omega_a_b_idx on omega  (cost=0.00..15855.79 rows=511623 width=4)
         Index Cond: ((a >= 501) AND (a <= 1000))
(3 rows)

Pozor - aby se mohl použít index only scan, je nutné tabulku nejdříve "zvakuovat".VACUUM aktualizuje případně vytvoří mapu viditelnosti (visibility map), a pokud ta pro tabulku neexistuje, pak není možné použít index only scan .

Prepared statements

Předpřipravené dotazy (prepared statements) jsou užitečnou pomůckou v boji s SQL injection. V předchozích verzích ovšem trpěly nepěkným neduhem – dotaz byl optimalizován „na slepo“ pro nejpravděpodobnější hodnoty parametrů. V některých případech se prováděcí plán připraveného dotazu mohl znatelně lišit od optimálního prováděcího plánu – a bylo nutné použít dynamické SQL (častěji se s tím setkávají vývojáři v PL/pgSQL). V 9.2 se s vlastní optimalizací dotazu počká až na zpracování dotazu, kdy jsou známé parametry dotazu a pro ně se dohledá optimální prováděcí plán. V případech, kdy je pravděpodobné, že generický plán bude podobný plánu generovaného se znalostí parametrů, se použije generický plán. 

postgres=# prepare xx(int) as select * from gg where a = $1;
PREPARE
postgres=# explain execute xx(1);
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on gg  (cost=0.00..1694.25 rows=99973 width=4)
   Filter: (a = 1)
(2 rows)

postgres=# explain execute xx(0);
                                QUERY PLAN
---------------------------------------------------------------------------
 Index Only Scan using gg_a_idx on gg  (cost=0.00..17.30 rows=127 width=4)
   Index Cond: (a = 0)
(2 rows)

API

Vlastní zpracování (příjem) dat v libpq (libpq row processor)

libpq je knihovna zajišťující komunikaci mezi databází a klientem. Při zpracování dotazu se průběžně odesílají data (řádky) ze serveru na klienta, kde se v libpq ukládají do paměti (pole). Po úspěšném dokončení dotazu vrátí libpq klientské aplikaci ukazatel na pole s výsledkem dotazu. Tento režim je jednoduchý a praktický, vyjma případů, kdy se na klienta posílají gigabajty dat (takže klient vyžírá paměť) a nebo kdy chceme přijatá data zpracovávat průběžně. V předchozích verzích bylo jediné (a správné) řešení použití kurzorů (příkazy DECLARE CURSOR a FETCH), které umožňují postupné zpracování dotazu. Občas se ale kurzor nehodí - třeba proto, že nechcete měnit logiku klienta - a pak se může hodit možnost změnit způsob příjmu řádků v libpq (např. - "online" plnění db gridu). Nyní je libpq hook PQrowProcessor: 

typedef struct pgDataValue
{
    int         len;            /* data length in bytes, or <0 if NULL */
    const char *value;          /* data value, without zero-termination */
} PGdataValue;

typedef int (*PQrowProcessor) (PGresult *res, const PGdataValue *columns,
                               const char **errmsgp, void *param);

Prvním uživatelem tohoto hooku je modul dblink, kde se výsledek přesměrovává do objektu typu tuple store místo do pole jako dříve. Tuple store "inteligentně" ukládá data do paměti nebo do souboru v případě, že velikost ukládaných dat přesáhne work_mem.

Poznámka

Toto API se ještě během finalizece změnilo - původně bylo založené na callbacku - nyní stávající funkce umí vracet jeden řádek.

UI

Tentokrát žádné razantní změny nečekejte - spíš jenom maličkosti, jako je třeba autocomplete v psql, který zachovává velikosti písmen i u klíčových slov, nebo automatické formátování definic pohledů. Funkce pg_cancel_backend může použít i "obyčejný" uživatel na své dotazy. V minulých verzích tato funkce vyžadovala právo superuser.

S podporou víceúrovňové replikace (kaskádové replikace) souvisí i možnost použít pg_basebackup (fyzická online záloha) vůči replice. pg_dump poskytoval možnost, jak vyexportovat vybrané tabulky. Nyní s parametrem --exclude-table umožňuje nezálohovat vybrané tabulky (typicky logy). Konzole psql 9.2 podporuje metapříkaz \ir, kde je relativní cesta vztažena k importovanému souboru (a nikoliv k aktuálnímu pracovnímu souboru).

Cízí zdroje se dočkaly podpory statistik - příkaz ANALYZE lze pustit i nad externí tabulkou. Akurátní statistiky by se měly projevit v optimalizaci prováděcího plánu dotazu.

Automatické formátování definic pohledů

Potěšilo mne, že Andrew Dunstan použil můj oblíbený způsob zápisu, který navrhl Joe Celko. 

postgres=# create table f1(a int);
CREATE TABLE
postgres=# create table f2(a int);
CREATE TABLE
postgres=# create view v1 as select f1.a as a, f2.a as b from f1,f2 where f1.a = f2.a;
CREATE VIEW

postgres=# \d+ v1
                   View "public.v1"
 Column │  Type   │ Modifiers │ Storage │ Description 
────────┼─────────┼───────────┼─────────┼─────────────
 a      │ integer │           │ plain   │ 
 b      │ integer │           │ plain   │ 
View definition:
 SELECT f1.a, f2.a AS b
   FROM f1, f2
  WHERE f1.a = f2.a;

Změny v pg_stat_*

Na výkon serveru má vliv i skutečnost, jestli máme nebo nemáme možnost identifikovat (nebo alespoň detekovat) situace, které vedou k nižšímu výkonu. Na celkovém výkonu aplikace se podepíší kromě jiného deadlocky a použití dočasných souborů. Výskyt deadlocků nebo použití tmp souborů bylo možné vyčíst už dříve - z logů. V 9.1 byly přidány do pohledu pg_stat_database sloupce obsahující celkový počet deadlocků a celkovou velikost zapsaných dočasných souborů: 

postgres=> select datname, temp_files, temp_bytes, deadlocks, stats_reset from pg_stat_database;
  datname  | temp_files | temp_bytes | deadlocks |          stats_reset          
-----------+------------+------------+-----------+-------------------------------
 template1 |          0 |          0 |         0 | 
 template0 |          0 |          0 |         0 | 
 postgres  |         10 | 1267789120 |         0 | 2012-02-23 07:24:10.488168+01
(3 rows)

Znáte pgFouine? Pokud ne, tak se na něj určitě podívejte - je to SQL profiler. Hodně zjednodušenou variantu (modul pg_stat_statements) objevíte i v Postgresu. Nově tento modul umí normalizovat dotazy. Normalizací se, v tomto případě, myslí nahrazení konstant parametrem: 

postgres=# select * from pg_proc where proname = 'sin';
 ...

postgres=# select * from pg_stat_statements where query like '%pg_proc%';
─[ RECORD 1 ]───────┬─────────────────────────────────────────
userid              │ 16384
dbid                │ 12870
query               │ select * from pg_proc where proname = ?;
calls               │ 4
total_time          │ 0.000743
rows                │ 4
shared_blks_hit     │ 10
shared_blks_read    │ 2
shared_blks_dirtied │ 0
shared_blks_written │ 0
local_blks_hit      │ 0
local_blks_read     │ 0
local_blks_dirtied  │ 0
local_blks_written  │ 0
temp_blks_read      │ 0
temp_blks_written   │ 0
time_read           │ 0
time_write          │ 0

Modul se zavádí skrze změnu konfigurace Postgresu: 

shared_preload_libraries = 'pg_stat_statements'     # (change requires restart)

Závěr, a co se letos nestihlo

Intenzivně se pracuje a diskutuje nad možností CRC součtů datových stránek. Ještě pár týdnů potřebuje patch implementující DDL triggery. Já jsem si od loňského léta hrál s možností důkladnější kontroly funkcí v plpgsql. Aktuálně už se asi vyjasnila požadovaná funkcionalita, pro kterou existuje patch a teď se hledá způsob, jak minimalizovat duplicitní kód v PL/pgSQL (pokud píšete v PL/pgSQL tak mohu jedině doporučit opatchovat si PL/pgSQL): 

select plpgsql_check_function('f1()', fatal_errors := false);
                         plpgsql_check_function                         
------------------------------------------------------------------------
 error:42703:4:SQL statement:column "c" of relation "t1" does not exist
 Query: update t1 set c = 30
 --                   ^
 error:42P01:7:RAISE:missing FROM-clause entry for table "r"
 Query: SELECT r.c
 --            ^
 error:42601:7:RAISE:too few parameters specified for RAISE
(7 rows)

Dost času se věnovalo minimalizaci zamykání vynuceného implementací referenční integrity. Bohužel navržené řešení způsobovalo cca 10% propad výkonu, takže se do Postgresu letos nedostalo - ale docela bych si vsadil, že příští rok už se do jádra dostane. Zajímavou možností bude podpora indexů pro regulární výrazy.

Myslím si, že 9.2ou se částečně uzavírá dost hektická etapa vývoje PostgreSQL, kdy se nestíhalo dostatečně rychle aplikovat patche do jádra. Během této etapy bylo vždy více patchů než se stihlo aplikovat během jednoho cyklu. Každý rok se do vývoje zapojilo více vývojářů a každý rok nová verze obsahovala více nové funkcionality než verze předchozí. Nemyslím si, že bude dále pokračovat v takovém tempu a předpokládám, že vývoj trochu zvolní. V příštím roce se více prostoru dostane různým extenzím PostgreSQL a postupnému vylepšování optimalizátoru, tam kde dnes občas selhává (prepared statements, sledování korelace mezi sloupci) . V dalších letech pravděpodobně dojde k integraci projektu Postgres-XC - tj podpoře multi master replikace a nativního HA řešení.Co bude dál - kdo ví - nyní je PostgreSQL výborná OLTP databáze - ukazuje se, že by uživatelé rádi používali PostgreSQL i jako analytickou databázi. PostgreSQL zatím na analytiku nad archivními daty optimalizovaný není, to by se ale mohlo výhledově (5 let) změnit.

Související články

Citováno z „http://postgres.cz/index.php?title=PostgreSQL_9.2_(2012)&oldid=614“