PostgreSQL 9.0 - nový začátek

Z PostgreSQL
Verze z 7. 6. 2021, 12:50, kterou vytvořil PavelStehule (diskuse | příspěvky)
(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
Skočit na navigaci Skočit na vyhledávání

PostgreSQL 9.0 - nový začátek

Z mého pohledu byl rok spojený s pracemi na 9.0 asi nejklidnějším rokem, který pamatuji (z pohledu vývojáře): Organizace vývoje založená na commitfestech se osvědčuje. Vývojáři, kteří pracují na náročných, problematických funkcí se už smířili s tím, že se nic nedá uspěchat, a koneckonců loňský rok Všem možná připomněl, že život není jen programování. Prioritou 9.0 byla integrace podpory replikace. Po podpoře replikací uživatelé volali dlouho, a letos se konečně dočkali. Administrace 9.0 by opět měla být o něco pohodlnější, a nové funkce by mohli ocenit i aplikační vývojáři používající PostgreSQL. Kromě nových funkcí došlo k řadě úprav Planeru a optimalizátoru, takže generované plány by měly být o něco robustnější. 9.0 je také první 64bitovou verzí pro Microsoft Windows. PostgreSQL na Windows by mohlo běžet o fous lépe i díky podpoře inline funkcí pro jiné překladače než je gcc.

Pokud nedojde k nečekaným problémům, tak začátkem dubna by mohla být venku beta a někdy začátkem léta ostrá verze.

Co je nového pro aplikační vývojáře (změny v SQL)?

Asi největší změny se týkají triggerů, constraintů. Pokud používáte ecpg, měli byste si všimnou razantního pokroku (nově podpora dynamických kurzorů, SQLDA, příkazu DESCRIBE, podpora out-of-scope kurzorů). Nově podporovaná funkcionalita je výsledkem portu většího projektu z informixu Zoltána Boszormenyie.

Sloupcové a podmíněné triggery

V některých případech chceme volat trigger pouze v případě, že došlo ke změně konkrétního sloupce či sloupců. Tento požadavek nyní můžeme vyjádřit zápisem (čímž může dojít k snížení počtu volání triggeru): 

CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
   FOR EACH STATEMENT 
   EXECUTE PROCEDURE trigger_func();

Dále máme možnost zápisem omezit volání triggeru splněním zadaných podmínek: 

CREATE TRIGGER modified_any 
   BEFORE UPDATE OF a 
   ON main_table
   FOR EACH ROW 
   WHEN (OLD.* IS DISTINCT FROM NEW.*) 
   EXECUTE PROCEDURE trigger_func();

Odložitelná omezení jednoznačnosti (Deferrable unique constraints)

V postgresu dosud nebylo možné použít odložená omezení jednoznačnosti (To odložení znamená, že se omezení kontroluje těsně před potvrzením transakce, nikoliv ihned po provedení příkazu. Dočasně tak mohou být v db duplicitní hodnoty.). To způsobovalo problémy např. při přečíslování primárních klíčů. Nyní lze použít volbu DEFERRABLE i pro omezení UNIQUE. Jako každý odložený test vyžaduje určitou paměť navíc - takže doporučuji to s odloženými omezeními nepřehánět. 

pavel@postgres:5432=# CREATE TABLE test(a int UNIQUE DEFERRABLE, b int UNIQUE);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_a_key" for table "test"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_b_key" for table "test"
CREATE TABLE
Time: 42,845 ms
pavel@postgres:5432=# INSERT INTO test SELECT generate_series(1,4),generate_series(1,4);
INSERT 0 4
Time: 3,157 ms
pavel@postgres:5432=# SELECT * FROM test;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
(4 rows)

Time: 1,342 ms
pavel@postgres:5432=# UPDATE test SET a = a + 1;
UPDATE 4
Time: 428,162 ms
pavel@postgres:5432=# SELECT * FROM test;
 a | b 
---+---
 2 | 1
 3 | 2
 4 | 3
 5 | 4
(4 rows)

Time: 0,652 ms
pavel@postgres:5432=# UPDATE test SET b = b + 1;
ERROR:  duplicate key value violates unique constraint "test_b_key"
DETAIL:  Key (b)=(2) already exists.
pavel@postgres:5432=#

Zobecněná vylučující omezení

V SQL databázích je běžné jedno vylučující omezení UNIQUE, které zamezuje výskytu duplicitních hodnot ve sloupci. Tento typ omezení lze zobecnit. Například můžeme chtít vložení překrývajících se polygonů nebo vložení překrývajících se intervalů. Tato funkce byla implementována v rámci podpory temporálních databází. Na příkladu je demonstrováno omezení odpovídající UNIQUE omezení - tj. nesmí se vyskytnout žádná stejná hodnota (proto operátor =). Pokud taková hodnota existuje - dohledá se pomocí indexu - dojde k vyvolání výjimky. 

postgres=# CREATE TABLE omega(a integer, EXCLUDE (a with =));
NOTICE:  CREATE TABLE / EXCLUDE will create implicit index "omega_a_exclusion" for table "omega"
CREATE TABLE
Time: 56,056 ms
postgres=# insert into omega values(10);
INSERT 0 1
Time: 1,456 ms
postgres=# INSERT INTO omega VALUES(10);
ERROR:  conflicting key value violates exclusion constraint "omega_a_exclusion"
DETAIL:  Key (a)=(10) conflicts with existing key (a)=(10).

Podpora ANSI SQL klauzule ORDER BY v agregační funkci

U nových agregačních funkcí, jako je např. xml_agg nebo array_agg je důležité v jakém pořadí jsou data zpracovávaná. Klauzule ORDER BY umožňuje určit pořadí: 

postgres=# SELECT array_agg(distinct a ORDER BY a DESC), array_agg(a ORDER BY a) FROM foo;
-[ RECORD 1 ]------------------------------------------------
array_agg | {9652,8078,7671,7642,5048,4650,3886,2450,732,647}
array_agg | {647,732,2450,3886,4650,5048,7642,7671,8078,9652}

Poznámka: Nově můžete používat agregační funkci string_agg, která je analogií funkcím group_concat v MySQL nebo listagg v Oracle.

Contrib modul pro ořezání diakritiky

Našince potěší volitelný doplněk "unaccent", který, jak je zřejmé z názvu, odstraňuje diakritiku z textu (tento modul pracuje pouze s kódováním UTF): 

postgres=# SELECT unaccent('žluťoučký kůň');
   unaccent    
───────────────
 zlutoucky kun
(1 row)

Modul lze integrovat s fulltextem.

Podpora "frames" u analytických funkcí

Nově je možné analytické funkce používat s definicí rámce ROWS BETWEEN x PRECEDING AND x FOLLOWING: 

postgres=# SELECT a, 
                  array_agg(a) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 
              FROM a;
 a | array_agg 
---+-----------
 0 | {0,0}     
 0 | {0,0,1}   
 1 | {0,1,1}   
 1 | {1,1,1}   
 1 | {1,1,2}   
 2 | {1,2,2}   
 2 | {2,2,2}   
 2 | {2,2,3}   
 3 | {2,3}     
(9 rows)

JOIN REMOVAL (Table Elimination)

PostgreSQL 9.0 umí odstranit nevyužité relace z dotazu aniž by došlo ke změně výsledku dotazu. Tato funkce je užitečná zejména tehdy, když je SQL dotaz zadrátovaný do ORM systému nebo pohledu. Mějme tabulky A(id,a), B(id,b), C(id,c), platí A.id = B.id = C.id. 

postgres=# CREATE TABLE a(id int primary key, a int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
postgres=# CREATE TABLE b(id int primary key, b int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b"
CREATE TABLE
postgres=# CREATE TABLE c(id int primary key, c int);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "c_pkey" for table "c"
CREATE TABLE
postgres=# CREATE VIEW v AS SELECT a,b,c FROM a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON b.id = c.id;
CREATE VIEW
postgres=# EXPLAIN SELECT a FROM v; -- doslo k vypusteni C
                           QUERY PLAN                            
-----------------------------------------------------------------
 Hash Left Join  (cost=58.15..121.65 rows=2140 width=8)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)
   ->  Hash  (cost=31.40..31.40 rows=2140 width=4)
         ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=4)
(5 rows)

postgres=# EXPLAIN SELECT c FROM v; -- plny dotaz
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Hash Left Join  (cost=116.30..211.90 rows=2140 width=4)
   Hash Cond: (b.id = c.id)
   ->  Hash Left Join  (cost=58.15..121.65 rows=2140 width=4)
         Hash Cond: (a.id = b.id)
         ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=4)
         ->  Hash  (cost=31.40..31.40 rows=2140 width=4)
               ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=4)
   ->  Hash  (cost=31.40..31.40 rows=2140 width=8)
         ->  Seq Scan on c  (cost=0.00..31.40 rows=2140 width=8)
(9 rows)

Tyto automatické úpravy dotazu nenahradí zdravý úsudek vývojáře. Očekává se zlepšení výkonu v případě použití jednodušších "hloupých" ORM systémů, případně pohledů.

Parametrická notifikace

Kombinace NOTIFY/LISTEN je známa všem aplikačním vývojářům PostgreSQL. Umožňuje poslat signál ve směru server->přihlášení klienti, tedy v opačném směru než je obvyklé. Zásadním omezením byla nemožnost poslat zároveň s notifikací data - čímž by se klient dozvěděl, co se stalo. Dosud klient pouze zjistil, že se něco stalo. V devítce je možné přibalit k signálu až 8 KB řetězec. 

CREATE OR REPLACE FUNCTION foo(a varchar)
RETURNS VOID AS $$
BEGIN
  PERFORM pg_notify('xxx', a);
  RETURN;
END;
$$ LANGUAGE plpgsql;

pavel@postgres:5432=# LISTEN xxx;
LISTEN

pavel@postgres:5432=# SELECT foo('Hello');
 foo
-----

(1 row)

Asynchronous notification "xxx" with payload "Hello" received from server process with PID 4730.

Co je nového pro vývojáře uložených procedur?

Dvě nejdůležitější novinky v 9.0 jsou, v podstatě, pro programátora neviditelné - přesto jsou naprosto zásadní. První malou, o to důležitější změnou, je možnost používat jazyk PL/pgSQL po instalaci bez nutnosti explicitní registrace a nastavení práv. Další neviditelnou funkcí je integrace SQL parseru do parseru PL/pgSQL. Integrace "skutečného" SQL parseru umožňuje podstatně lepší a přesnější chybovou diagnostiku SQL příkazů v PL/pgSQL kódu. Mimo-jiné je nyní možné detekovat kolizi SQL identifikátorů a identifikátorů proměnných. Možná nebudu sám, pro koho je tato změna v PL/pgSQL nejdůležitější za posledních pět let. 

postgres=# 
create or replace function foo() 
returns void as $$
declare a integer;                                              
begin              
  for a in select a from omega -- < skrytá a zákeřná chyba > 
  loop                         
    raise notice '%', a;
  end loop; 
end; 
$$ language plpgsql;
CREATE FUNCTION
Time: 3,501 ms
postgres=# select foo();
ERROR:  column reference "a" is ambiguous
LINE 1: select a from omega
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select a from omega
CONTEXT:  PL/pgSQL function "foo" line 3 at FOR over SELECT rows

Podpora jmenné a kombinované notace (named and mixed notation) zápisu parametrů volání funkce

Standardně většina programovacích jazyků nabízí tzv poziční notaci předávání předávání parametrů - tj. hodnoty se parametrům funkce přiřazují na základě pozice. Kromě poziční notace ještě existují notace jmenná a kombinovaná (vycházím z terminologie používané jazykem ADA). V PostgreSQL se pro pojmenované parametry používá zápis hodnota AS název: 

CREATE FUNCTION dfunc(a int, b int, c int = 0, d int = 0)
  RETURNS TABLE (a int, b int, c int, d int) as $$
  SELECT $1, $2, $3, $4;
$$ LANGUAGE sql;

SELECT (dfunc(10,20,30)).*;
 a  | b  | c  | d 
----+----+----+---
 10 | 20 | 30 | 0
(1 row)

SELECT (dfunc(10 AS a, 20 AS b, 30 AS c)).*;
 a  | b  | c  | d 
----+----+----+---
 10 | 20 | 30 | 0
(1 row)
 
SELECT * FROM dfunc(10 AS a, 20 AS b);
 a  | b  | c | d 
----+----+---+---
 10 | 20 | 0 | 0
(1 row)

SELECT * FROM dfunc(10 AS b, 20 AS a);
 a  | b  | c | d 
----+----+---+---
 20 | 10 | 0 | 0
(1 row)

SELECT * FROM dfunc(1,2);
 a | b | c | d 
---+---+---+---
 1 | 2 | 0 | 0
(1 row)

SELECT * FROM dfunc(1,2,3 AS c);
 a | b | c | d 
---+---+---+---
 1 | 2 | 3 | 0
(1 row)

IN parametry funkce již nejsou pouze pro čtení

V PL/pgSQL by proměnné odpovídající parametrům funkce chráněné proti zápisu, stejně jako v PL/SQL (Oracle) nebo v jazyce ADA. V PL/pgSQL je toto omezení poměrně umělé - z důvodu jiné syntaxe volání funkce a předávání parametrů i bezdůvodné. Takže s ohledem na uživatele došlo k vypuštění tohoto omezení (ve shodě se standardem SQL/PSM).

PL/Python a PL/Python3, PL/Perl(u)

Poměrně zásadních úprav doznala podpora uložených procedur v Pythonu. Tento jazyk je rozšířen zejména mezi uživateli PostGISu. Zásadním krokem vpřed je podpora Pythonu 3. Dvojková řada nyní podporuje Unicode. Parametry funkce typu pole jsou nyní mapovány přímo na pole Pythonu: 

CREATE FUNCTION test_type_conversion_array_int4(x int4[]) 
RETURNS int4[] AS $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpythonu;
SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]);
INFO:  ([0, 100], <type 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {0,100}
(1 row)

SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]);
INFO:  ([0, -100, 55], <type 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {0,-100,55}
(1 row)

Zásadních změn se dočkal kód PL/Perlu - výsledkem by měla být podrobnější diagnostika spolu s větším počtem vestavěných pseudo nativních perlovských funkcí (quote_literal, quote_nullable, quote_ident, encode_bytea, decode_bytea, looks_like_number, encode_array_literal, encode_array_constructor).

Co je nového pro administrátory?

Anonymní funkční bloky - příkaz DO

Anonymní funkční blok je, v podstatě, tělo funkce, které se ihned provádí (neplést s anonymními funkcemi). Výhodou anonymních funkčních bloků je možnost provádět komplexnější operace bez nutnosti definovat novou funkci: 

DO $$
DECLARE r record;
BEGIN 
  FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
  LOOP
    RAISE NOTICE '%, %', r.roomno, r.comment;
  END LOOP;
END
$$ LANGUAGE plpgsql;

ALTER DEFAULT PRIVILEGES

Po vytvoření databázového objektu (tabulka, schéma, pohled) s ním může pracovat pouze jeho vlastník. Prvním krokem je většinou nastavení přístupových práv. Tato práce "navíc" svádí administrátory k jednoduchému kroku - zpřístupnění objektu všem pro všechno (GRANT ALL FOR PUBLIC). Díky práci Petra Jelínka je možné nastavit výchozí práva, která se aplikují na všechny nově vytvořené objekty ve schématu: 

ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT SELECT ON TABLE TO regressuser1;
ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLE TO regressuser1;

Hromadné nastavení práv

Všiml jsem si, že tuto funkci požadovali zejména bývalí uživatelé MySQL. Nyní tedy příkaz GRANT umožňuje nastavit práva všechny databázové objekty daného typu ve schématu: 

GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA

Možnost nastavení systémových proměnných na databázi a uživatele

PostgreSQL již dříve umožňoval nastavení proměnných per uživatel (např. nastavení WORK_MEM, log_min_duration_statement. Počínaje touto verzí můžeme upřesnit nastavení ještě pro konkrétní databáze: 

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

Identifikace aplikace

Název aplikace application_name je další atribut, který (pokud je zadán) může pomoci s identifikací SQL příkazu v logu a v tabulce aktuálně prováděných SQL příkazů. Hodnotu tohoto atributu lze zadat v connection stringu nebo příkazem SET.

Automatické generování názvu indexu

Nyní máme možnost vynechat název indexu v příkazu CREATE INDEX. Systém vygeneruje jednoznačný - čitelný - název: 

-- nazev indexu neuveden - system pouzije concur_heap_expr_idx
CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));

Nové VACUUM FULL

Kód příkazu VACUUM FULL byl kompletně přepsán. Nová implementace by se měla lépe vypořádat s masivními změnami obsahu databáze, měla by být rychlejší a navíc zvládne redukovat alokovaný diskový prostor indexů (jako kdyby došlo ke sloučení příkazů VACUUM FULL a REINDEX). Pozor - vyžaduje více volného místa na disku.

Nastavení parametrů tablespace (seq_page_cost,random_page_cost)

ALTER TABLESPACE testspace SET (random_page_cost = 1.0);

Ochrana postmastera před OOM kilerem

Možným problémem provozu PostgreSQL - při nedostatku paměti, je ukončení procesu postmaster. Čím se fakticky ukončuje PostgreSQL. V typické konfiguraci má PostgreSQL alokováno atypicky hodně sdílené paměti (používá se jako cache), a tudíž je pro OOM kilera první na ráně. Nastavením /proc/self/oom_adj se pozornost OOM kilera přesune na jiné procesy.

REPLIKACE

Stále je podporováno Slony I - nyní ve verzi 2.0. Slony je tu už pět let a jedná se o vyladěné a prověřené řešení. Nová verze slony vyžaduje minimálně verzi pg 8.3 - která obsahuje replikační API (hooks) (díky čemuž bylo možné zjednodušit a vyčistit kód). Nicméně zájem vývojářů i uživatelů se soustřeďuje jinam. Devítka obsahuje podporu replikace založené na exportu transakčního logu. Fakticky se jedná o evoluci tzv. warm standby režimu z 8.4. Integrovaná podpora replikace v 9.0 stojí na dvou základních pilířích - Hot Standby režimu a průběžnému exportu transakčního logu (streaming replication). Hot Standby režim umožňuje provozovat pg v read only režimu - požadavky na změny obsahu a struktury dat mohou přijít pouze prostřednictvím importu transakčního logu. Za normálních okolností pg exportuje transakční log po 16MB segmentech, což je pro replikaci nepraktické. Streaming replication exportuje přírůstky v transakčním logu průběžně.

Vytvoření repliky je poměrně triviální: 

# master setting - postgres.conf
archive_mode = on
archive_command = 'cp "%p" /tmp/wal_archive/"%f"'
max_wal_senders = 1

Tato změna konfigurace vyžaduje restart db. Vlastní klonovaní databáze může proběhnout kdykoliv později (již bez nutnosti restartu). Stačí zahájit zálohování: 

SELECT pg_start_backup('copy');
... zkopírování datového adresáře db master na server, kde poběží slave
SELECT pg_stop_backup();

V db, která slouží jako slave je nutné upravit zpět konfiguraci a přidat soubor recovery.conf: 

# recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.20.20 port=5432 user=postgres'
trigger_file = '/usr/local/pgsql/data-replika/finish.replication'
restore_command = 'cp -i /tmp/wal_archive/%f "%p" </dev/null'

Před startem repliky je třeba zkopírovat archivované transakční logy. Po spuštění slave db běží v read-only režimu. 

postgres=> insert into foo values(100);
ERROR:  cannot execute INSERT in a read-only transaction

Jak Slony, tak replikace v Hot Standby režimu mají své pro a proti. Především je Hot Standby neprověřená technologie. Na druhou stranu, pokud chcete replikovat kompletní db cluster, tak si Vás Hot Standby získá snadnou instalací, možností vytvoření repliky za běhu, možností změny režimu ze slave na master bez nutnosti restartu db a relativně snadnou administrací. Výhodou může být i fakt, že replikace nijak zvlášť neomezuje funkcionalitu master db (přestože určitou zátěž představuje (je nutné exportovat transakční logy), očekává se menší režie nežli u Slony). Nevýhodou je naopak závislost na exportu transakčního logu (pokud se již nepoužívá export transakčního logu pro zálohování). Pokud by došlo k výpadku spojení, tak na základě uloženého transakčního logu se může slave db sesynchronizovat. Zatím ovšem není dořešeno odstraňování již nepotřebných segmentů logu - to je nutné řešit externími skripty.

Termíny ukončení podpory jednotlivých verzí

Mezi vývojáři PostgreSQL je poměrně dobrá shoda ohledně politiky podpory, která je poměrně jednoduchá. Po dobu pěti let od vydání budou vycházet opravné verze. Na této politice se vývojáři dohodli během posledních dvou let. Předtím ne že by se nikdo podporou nezabýval, ale nedošlo ke shodě. V loňském roce byly dohodnuty i termíny ukončení podpory:

Termíny ukončení podpory jednotlivých verzí PostgreSQL
Verze Ukončení podpory
PostgreSQL 7.4 červenec 2010 (prodloužená podpora)
PostgreSQL 8.0 červenec 2010 (prodloužená podpora) - v případě MS Windows již byla podpora ukončena
PostgreSQL 8.1 listopad 2010
PostgreSQL 8.2 prosinec 2011
PostgreSQL 8.3 leden 2013
PostgreSQL 8.4 červenec 2014

U starších verzích se podpora prodloužila, tak aby uživatelé měli dost času přejít na novější verzi.

Escapování psql proměnných

V psql je možné použít rozšířenou syntaxi pro expanzi proměnné podle účelu (jako identifikátor nebo jako řetězec). Podpora této syntaxe by měla usnadnit psaní skriptů v psql: 

postgres=#\set prom nejaky.text
postgres=# select :prom;
ERROR: missing FROM-clause entry for table "nejaky"

postgres=# SELECT :'prom';
  ?column?
-------------
 nejaky.text

postgres=# SELECT :'prom' :"prom";
 nejaky.text 
-------------
 nejaky.text

Možná rizika migrace na 9.0

  • bylo odstraněno automatické doplňování klauzule FROM. Toto chování bylo pozůstatkem akademické (experimentální éry). Pokud jste použili jako kvalifikátor sloupce název tabulky (např. SELECT nazev_tabulky.sloupec), tak nebylo nutné doplňovat zapisovat klauzuli FROM. Minimálně dvě verze zpět je toto chování blokované - #add_missing_from = off (hrozí riziko nechtěného cross-joinu), a nyní došlo k úplnému odstranění. Nové chování se projeví syntaktickou chybou a je nutné postižené SQL příkazy přepsat.
  • aktivní detekce kolizí identifikátorů v PL/pgSQL. V předchozích verzích prostředí PL/pgSQL nedokázalo identifikovat kolizi identifikátorů. Výsledkem byl povětšinou nefunkční kód, který byl opraven již během vývoje. Nicméně, narazil jsem i na několik let používaný (přestože nefunkční kód), a tak je pravděpodobné, že tato chyba se objeví i jinde. Nové chování se projeví syntaktickou chybou. Doporučuji postiženou PL/pgSQL funkci přepsat - s velkou pravděpodobností se jedná o chybu. Případně lze vrátit chování parseru (nedoporučuji) nastavením plpgsql.variable_conflict = use_variable v postgres.conf
  • S předchozím bodem souvisí i další možný problém. Pokud název proměnné je zároveň klíčovým slovem v SQL pravděpodobně dojde k zobrazení syntaktické chyby. V předchozích verzích se jako identifikátory proměnných nesměly použít pouze klíčová slova v PL/pgSQL.
CREATE OR REPLACE FUNCTION test(text) 
RETURNS text AS $$
DECLARE table text = $1; 
BEGIN 
  RETURN quote_ident(table); 
END; 
$$ LANGUAGE plpgsql;

postgres=# SELECT test('moje tab'); --< 8.4 >
    test    
------------
 "moje tab"
(1 row)

-- 9.0
postgres=# CREATE OR REPLACE FUNCTION test(text) 
postgres-# RETURNS text AS $$
postgres$# DECLARE table text = $1; 
postgres$# BEGIN 
postgres$#   RETURN quote_ident(table); 
postgres$# END; 
postgres$# $$ LANGUAGE plpgsql;
ERROR:  syntax error at or near "table"
LINE 5:   RETURN quote_ident(table); 
                             ^
  • změna výstupního formátu typu bytea. V předchozích verzích byly hodnoty typu bytea zobrazeny jako escaped string. Nyní se používá formát hexadecimálních čísel. Předchozí výstupní formát lze vrátit zpět nastavením bytea_output = escape. Na nový formát se přešlo z důvodu výkonu.

Co je nového pro uživatele?

Prokoukla konzole. Pokud používáte UTF terminál, můžete aktivovat zobrazení dekorací (okrajů tabulek) pomocí unicode znaků: 

postgres=# \pset linestyle unicode
postgres=# SELECT * FROM (values(10,20,30),(40,50,60)) x(a,b,c);
 a  │ b  │ c  
────┼────┼────
 10 │ 20 │ 30
 40 │ 50 │ 60
(2 rows)

pgAdmin 1.10

Nová verze pgAdmina obsahuje dvě zásadnější novinky: grafický návrhář dotazů a integrované skriptovací prostředí ne nepodobné T-SQL - pgScript. Query builder je záležitost hlavně pro začátečníky - profík píše dotazy v ruce - no možná, že i profík se někdy sníží k použití QB, pravda ovšem je, že dokud jsem používal QB, tak jsem se nenaučil pořádně SQL. pgScript je něco, co mne naplňuje tichým smutkem. Netuším, proč bylo potřeba vytvořit klon T-SQL. Přidání pgScriptu do pgAdmina je opravdu vařením pejsko-kočičího dortu - což bohužel platí pro celý pgAdmin. pgScript měl být postaven nad syntaxí PL/pgSQL nebo ještě lépe SQL/PSM. Bohužel už se stalo: 

DECLARE @I, @J, @T, @G;
SET @I = 0;
SET @G1 = INTEGER(10, 29, 1); /* Random integer generator
                             Unique numbers between 10 and 29 */
SET @G2 = STRING(10, 20, 3); /* Random string generator
                             3 words between 10 and 20 characters */
WHILE @I < 20
BEGIN
    SET @T = 'table' + CAST (@I AS STRING);

    SET @J = 0;
    WHILE @J < 20
    BEGIN
        INSERT INTO @T VALUES (@G1, '@G2');
        SET @J = @J + 1;
    END

    SET @I = @I + 1;
END

Strojově přístupný formát výpisu prováděcího plánu

Vývojáře aplikací (GUI, analýzy logů), které pracují s výpisem prováděcích plánů (příkaz: EXPLAIN), potěší strojově rozpoznatelný výpis ve formátu XML, JSON, a YAML: 

postgres=# EXPLAIN (FORMAT xml) SELECT 10;
                        QUERY PLAN                        
----------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">
   <Query>
     <Plan>
       <Node-Type>Result</Node-Type>
       <Startup-Cost>0.00</Startup-Cost>
       <Total-Cost>0.01</Total-Cost>
       <Plan-Rows>1</Plan-Rows>
       <Plan-Width>0</Plan-Width>
     </Plan>
   </Query>
 </explain>
(1 row)

postgres=# EXPLAIN (FORMAT json) SELECT 10;
          QUERY PLAN          
------------------------------
 [
   {
     "Plan": {
       "Node Type": "Result",
       "Startup Cost": 0.00,
       "Total Cost": 0.01,
       "Plan Rows": 1,
       "Plan Width": 0
     }
   }
 ]
(1 row)

Rozšířená konzole

Enhanced psql je externí (můj) projekt, který má za cíl rozšířit možnosti standardní PostgreSQL konzole psql. epsql je částečně experimentální platforma - nicméně některé funkce z epsql již byly portovány zpět do psql. Novinkou epsql 9.0 je možnost definování vlastních příkazů. Tato verze již obsahuje triviální makrojazyk (metapříkazy \forc, \ifdef, ...):

Describe table

Obdoba příkazu desc MySQL: 

\newcommand desc
\ifdef 1
\d :1
\else
\echo 'missing table name'
\endifdef
\endnewcommand

postgres=# desc tab
       Table "public.tab"
┌────────┬─────────┬───────────┐
│ Column │  Type   │ Modifiers │
├────────┼─────────┼───────────┤
│ a      │ integer │           │
│ b      │ integer │           │
│ c      │ integer │           │
└────────┴─────────┴───────────┘
Indexes:
    "ff" btree (a)

Top Ten

Zobrazí seznam tabulek řazený podle velikosti tabulky: 

\newcommand \tt
\ifdef 1
select relname, relpages, reltuples from pg_class order by relpages desc limit :1;
\else
select relname, relpages, reltuples from pg_class order by relpages desc;
\endifdef
\endnewcommand

postgres=# \tt 3
┌──────────────┬──────────┬───────────┐
│   relname    │ relpages │ reltuples │
├──────────────┼──────────┼───────────┤
│ pg_proc      │       54 │      2232 │
│ pg_depend    │       41 │      5557 │
│ pg_attribute │       36 │      1960 │
└──────────────┴──────────┴───────────┘
(3 rows)

Založení CSPUGu

Pro zastřešení aktivit kolem PostgreSQL - nyní je to zejména pořádání výroční konference a zabezpečení wiki - jsme založili CSPUG (Czech and Slovak Users Group). Stanovy sdružení jsou ke stažení na stránkách CSPUGu. Členové výboru jsou: Pavel Hák, Pavel Stěhule a Julius Štroffek. Stále nabíráme nové členy.

O čem se mluví

Ohledně PostgreSQL je těžké být prorokem. Každý vývojář má své plány (viz seznam níže). Já bych si vsadil na partitioning.

  • Podpora old-school režimu izolace transakcí SERIALISABLE,
  • Podpora automatického vytváření partitions,
  • Podpora filtrů příkazu COPY,
  • Index only scans,
  • Podpora SQL/MED - dotazy do jiné db (náhrada za dblink).

Související články

Citováno z „http://postgres.cz/index.php?title=PostgreSQL_9.0_-_nový_začátek&oldid=616“