Zobrazení zdroje stránky PostgreSQL 9.1 - aneb stále vpřed

[[Category:Články]]
[[Category:Verze PostgreSQL]]
Po roce je tu nová verze PostgreSQL - respektive máme tu <i>betu</i> -
a je tudíž jasné, co v 9.1 bude a nebude. V podstatě až na příkaz <i>MERGE</i>, který bude až v 9.2, 
byly plány pro tuto verzi splněny. PostgreSQL nyní podporuje <i>COLLATION</i> podle ANSI/SQL. 
Nechybí ani podpora <i>SQL/MED</i> (čtení externích csv souborů). Celkově změn není tolik jako v 9.0, ale stále 
je jich dost - stále se přidávají nové funkce. Během roku bylo akceptováno několik set patchů 
([http://momjian.us/main/blogs/pgblog/2011.html#April_20_2011_3 zdrojový kód PostgreSQL přesáhl magickou hranici jednoho miliónu řádků]). Uživatelé [http://postgis.refractions.net/ PostGISu] uvítají 
zásadní rozšíření GiST indexů KNNGIST a zatím nevídaný pokrok ve vývoji PL/Pythonu. Pro enterprise 
řešení má význam nová implementace úrovně izolace transakcí SERIALIZABLE. 
Pro ukládání dočasných dat jsou zajímave tzv <i>unlogged tables</i>.

Tím nejdůležitějším, co stalo během posledního roku, byl přechod z CVS na [http://git.postgresql.org/gitweb?p=postgresql.git;a=summary GIT]. Uživatele 
samozřejmě vůbec nezajímá, který systém pro správu zdrojových kódů projekt používá. Nicméně 
způsob, jakým se udržují zdrojové kódy je naprosto zásadní pro vývojáře, pro komunitu. Výběru GITu předcházelo několik let diskuzí.
I tak vlastní konverze nebyla úplně jednoduchá a bezbolestná. Vývojáři PostgreSQL rozhodně nechtěli přijít o 
historii uloženou v CVS repozitáři a o na CVS napojenou infrastrukturu, což se ukázalo jako
problém během pilotního projektu.  Několik měsíců vývojáři z PostgreSQL core týmu participovali na
vývoji GITu - na dopilování konverze z CVS.

==SQL==
Drobnou ale zásadní změnou je přechod na tzv ANSI SQL řetězce. Počínaje 9.1 PostgreSQL implicitně
nepodporuje escape sekvence v řetězcích (pozn. v konfiguraci lze povolit). Pokud jsou potřeba
escape sekvence, musí se použít tzv rozšířené řetězce (<i>extended strings</i>):
<pre>
postgres=# SELECT e'aa\taa';
  ?column?  
------------
 aa      aa
(1 row)
</pre>

Hodnocení 9.1 není úplně jednoduché. Většinu novinek (snad vyjma KNNGIST) bych neoznačil jako převratné,
nebo přelomové. Některé nové funkce jsou systémovou náhradou předchozích improvizací. Jiné aktuálně
nepřinášejí výraznou "přidanou hodnotu", ale otevírají vrátka pro budoucí použití (např. infrastruktura 
proSQL/MED). Kdybych chtěl šokovat, tak bych dal na první místo podporu funkčních závislostí
v klauzuli GROUP BY. Stará poučka, že atribut se musí nacházet buď v agregační funkci nebo
v klauzuli GROUP BY už neplatí:
<pre>
-- sloupec id musí být primárním klíčem tabulky zamestnanci
postgres=# SELECT * FROM zamestnanci;
 id | jmeno | prijmeni 
----+-------+----------
  1 | Pavel | Stehule
  2 | Tomas | Marny
(2 rows)

postgres=# SELECT * FROM mzdy;
 zamestnanec_id | vyplaceno  |  castka  
----------------+------------+----------
              1 | 2010-06-01 | 10000.00
              1 | 2010-07-01 | 10020.00
              2 | 2010-05-01 |  8020.00
              2 | 2010-06-01 | 12020.00
              2 | 2010-07-01 | 13020.00
(5 rows)

-- dotaz napsaný s využitím funkční závislosti
postgres=# SELECT jmeno, prijmeni, sum(castka) 
              FROM zamestnanci 
                   JOIN 
                   mzdy 
                   ON id = zamestnanec_id 
             GROUP BY id;
 jmeno | prijmeni |   sum    
-------+----------+----------
 Pavel | Stehule  | 20020.00
 Tomas | Marny    | 33060.00
(2 rows)
</pre>
Zatím jediná podporovaná funkční závislost je závislost na primárním klíči. Po této funkci
dlouho volali uživatelé MySQL. Nejsem si úplně jistý, jestli budou spokojeni. PostgreSQL stále
nedovolí napsat dotaz s agregační funkcí libovolně.

Podporou tzv [http://developer.postgresql.org/pgdocs/postgres/collation.html COLLATION] PostgreSQL dohání ostatní databáze v podpoře multi jazykových aplikací. Nyní
lze definovat locale na úrovni sloupce. Data v konkrétním sloupci lze řadit jednou podle 
českých pravidel, a podruhé podle jiných pravidel. Drobným zádrhel je platformová závislost 
identifikátoru COLLATE (Microsoft používá jiný systém než unixová locales). Naštěstí lze vytvářet 
vlastní COLLATE a tím rozdíl v názvech mezi jednotlivými platformami překlenout.
<pre>
postgres=# CREATE COLLATION czech FROM "cs_CZ.utf8";
CREATE COLLATION
postgres=# CREATE TABLE lide(prijmeni VARCHAR COLLATE czech);
CREATE TABLE
postgres=# \d lide
            Table "public.lide"
  Column  |       Type        |   Modifiers   
----------+-------------------+---------------
 prijmeni | character varying | collate czech

postgres=# show lc_collate ;
lc_collate
------------
en_US.utf8
(1 row)

postgres=# SELECT * FROM lide ORDER BY 1;
prijmeni  
-----------
Crha
Holý
Hynek
Chromečka
(4 rows)

postgres=# SELECT * FROM lide ORDER BY prijmeni COLLATE "en_US.utf8";
prijmeni  
-----------
Chromečka
Crha
Holý
Hynek
(4 rows)
</pre>
Jediným omezením je, že všechna data v databázi musí mít stejné kódování - tedy nelze mít jeden sloupec
s kódováním latin2, další s latin1 a třetí s UTF8. I z tohoto důvodu se doporučuje preferovat kódování
UTF8 (pozn. oblíbené C collate je použitelné pouze pro 8bit kódování. Jeho obdobou pro UTF8 je ucs_basic 
collation).

Uživatelé čekali na možnost použít příkazy INSERT, UPDATE, DELETE v CTE dotazech. Ta možnost
tu nyní je:
<pre>
-- zřetězení SQL příkazů (DML statements pipelining)
postgres=# SELECT * FROM foo;
 a  
----
 30
 20
 10
(3 rows)

postgres=# SELECT * FROM deleted;
 a 
---
(0 rows)

postgres=# WITH t1 AS (DELETE FROM foo RETURNING *), 
                t2 AS (INSERT INTO deleted 
                          SELECT * FROM t1 RETURNING *) 
             SELECT max(a) FROM t2;
 max 
-----
  30
(1 row)

postgres=# SELECT * FROM foo;
 a 
---
(0 rows)

postgres=# SELECT * FROM deleted;
 a  
----
 30
 20
 10
(3 rows)

-- recursive UPDATE
postgres=# WITH RECURSIVE  t AS (SELECT *, 0 AS ll 
                                    FROM xx 
                                   WHERE parent IS NULL 
                                 UNION ALL
                                 SELECT xx.*, ll + 1  
                                    FROM xx 
                                         JOIN 
                                         t 
                                         ON t.id = xx.parent) UPDATE xx SET level = ll 
                                                                FROM t 
                                                                WHERE t.id = xx.id;
UPDATE 5
postgres=# SELECT * FROM xx;
 id | parent | level 
----+--------+-------
  0 |        |     0
  1 |      0 |     1
  2 |      0 |     1
  3 |      1 |     2
  4 |      3 |     3
(5 rows)
</pre>
Popis přístupu k externím zdrojům je obsahem části SQL standardu SQL/MED. 9.1 obsahuje pilotní
implementaci přístupu k tzv <i>cizím tabulkám</i> - podporován je zatím pouze přístup k textovým souborům
ve formátu čitelném příkazem COPY.
<pre>
postgres=# COPY psc TO '/tmp/psc.data';
COPY 3356
postgres=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
postgres=# CREATE FOREIGN DATA WRAPPER file_fdw2 HANDLER file_fdw_handler VALIDATOR file_fdw_validator;
CREATE FOREIGN DATA WRAPPER
postgres=# CREATE FOREIGN TABLE tbl (
              psc numeric, 
              posta text
           ) 
             SERVER file_server 
             OPTIONS (format 'text', filename '/tmp/psc.data');
CREATE FOREIGN TABLE

postgres=# SELECT * FROM tbl WHERE psc = 25601;
  psc  |      posta      
-------+-----------------
 25601 | Benešov u Prahy
(1 row)

Time: 43.562 ms
postgres=# EXPLAIN SELECT * FROM tbl WHERE psc = 25601;
                       QUERY PLAN                        
---------------------------------------------------------
 Foreign Scan on tbl  (cost=0.00..86.39 rows=4 width=64)
   Filter: (psc = 25601::numeric)
   Foreign File: /tmp/psc.data
   Foreign File Size: 66424
(4 rows)

postgres-# \det+
                       List of foreign tables
 Schema | Table |   Server    |               Options                
--------+-------+-------------+--------------------------------------
 public | tbl   | file_server | {format=text,filename=/tmp/psc.data}
(1 row)
</pre>
9.1 obsahuje pouze základní infrastrukturu potřebnou pro SQL/MED. Handlery pro [http://pgsnake.blogspot.com/2011/04/tinkering-with-sqlmed.html další typy] externích zdrojů přijdou na řadu v [http://archives.postgresql.org/pgsql-hackers/2011-04/msg01531.php následující verzi] - existuje prototyp pro přístup k dalším PostgreSQL databázím - a je tedy jen otázkou času, kdy někdo napíše handler pro MySQL nebo DBI. Externí zdroje jsou, podle standardu SQL/MED, přístupné pouze pro čtení.

Moje oblíbené funkce <i>array_to_string</i> a <i>string_to_array</i> konečně podporují NULL, a to prostřednictvím
třetího parametru, který specifikuje řetězec, kterým se nahradí NULL:
<pre>
postgres=# SELECT array_to_string(array[1,null,1],',','-');
 array_to_string 
-----------------
 1,-,1
(1 row)

postgres=# SELECT string_to_array('1,-,1',',','-')::int[];
 string_to_array 
-----------------
 {1,NULL,1}
(1 row)
</pre>
Pro aplikační programátory jsou k dispozici nové funkce pro práci s řetězci - <i>left</i>, <i>right</i>, <i>reverse</i>, 
<i>concat</i> a <i>concat_ws</i>. Poslední dvě zmiňované funkce můžete znát z MySQL. V PostgreSQL se chovají úplně
stejně. O něco složitější funkce “formát” je navržena k sestavování řetězců různých hlášení, zápisů 
do logu, varování atd. Kromě toho ji lze použít k bezpečnému sestavení SQL řetězce, který se použije
v dynamickém SQL. K tomu slouží formátovací tagy %I, %L, které zajistí správné escapování hodnoty,
tak aby výsledný SQL řetězec byl syntakticky správný a bezpečný vůči SQL injection. Funkce podporuje i
poziční tagy:
<pre>
postgres=# SELECT format('%s %s', 'Hello', 'World');
  format    
-------------
Hello World
(1 row)

postgres=# SELECT format('INSERT INTO %I(%I) VALUES(%L)',
                         'moje tabulka'::regclass, 'muj sloupec','nějaká hodnota');
                              format                               
--------------------------------------------------------------------
INSERT INTO "moje tabulka"("muj sloupec") VALUES('nějaká hodnota')
(1 row)
</pre>
Trochu oklikou se dostávám k původní implementaci klauzule USING v PL/pgSQL příkazu EXECUTE. Parametrické
dynamické SQL (klauzule USING) a funkce <i>format</i> se dobře doplňují. Při správném použití je prakticky
vyloučené napsat aplikaci napadnutelnou útokem typu SQL injection.

Jazyk PL/pgSQL byl rozšířen o cyklus [http://developer.postgresql.org/pgdocs/postgres/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY FOREACH] - iteraci nad polem s možností iterací po řádcích. 
Alespoň z mého pohledu je PL/pgSQL kompletní. Nenapadá mne, co by ještě mělo být do tohoto jazyka přidáno -  
přičemž by zůstal rozumný poměr mezi užitkem pro uživatele a velikostí patche (uvítám diskuzi na toto téma
- o co rozšířit PL/pgSQL?). Pokud budou v budoucnu nějaké změny, tak nejspíš nebudou pro uživatele příliš
viditelné a myslím si, že minimálně v následující verzi žádné nebudou:
<pre>
CREATE FUNCTION sum_values(VARIADIC int[]) RETURNS int8 AS $$
DECLARE
   s int8; x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;
</pre>
Příkaz FOREACH s klauzulí SLICE umožňuje iterovat přes vícerozměrná pole. Iteraci přes položky typu ROW nebo RECORD
lze řešit pomocí knihovny [http://www.pgsql.cz/index.php/PL_toolbox PLToolbox].

Funkce v [http://developer.postgresql.org/pgdocs/postgres/plperl.html PLPerlu] mohou mít parametry typu RECORD. Parametry typu pole se interpretu perlu nyní
předávají v binárním formátu - což by mělo vést k lepšímu výkonu (odpadne serializace a deserializace)
a k eliminaci problémů s vícerozměrnými poli. Změn v [http://developer.postgresql.org/pgdocs/postgres/plpython.html PLPythonu] je tolik, že je nebudu jmenovat, kromě
snad té nejdůležitější - funkce v PLPythonu je validována již v době registrace. Až do verze 9.0
integrace Pythonu pokulhávala za integrací Perlu. Myslím si, že počínaje 9.1 je podpora uložených procedur
v Pythonu na vyšší úrovni než podpora externích uložených procedur v Perlu.

Implementace úrovně [http://wiki.postgresql.org/wiki/Serializable SERIALIZABLE] v PostgreSQL odpovídala ANSI SQL 92, ale už nikoliv aktuálnímu 
pojetí standardu a implementaci v ostatních db (a také potřebám vývojářů). To působilo problémy aplikačním vývojářům při
portaci svých aplikací pro PostgreSQL. Dalším nepříjemným důsledkem bylo špatné 
hodnocení PostgreSQL v komerčních testech, z kterých pg vycházela jako db, která chybně implementuje 
transakce. To by se nyní mělo změnit a to díky práci Kevina Grittnera a Dana Portse. Nová implementace
detekuje zda-li došlo nebo nedošlo k <i>race-condition</i> a v případě, že došlo, stornuje dotčenou
transakci.  Úroveň izolace REPEATABLE READ se nezměnila (v předchozích verzích byly úrovně REPEATABLE READ a SERIALIZABLE 
totožné). Nově mají transakce v úrovni SERIALIZABLE o poznáná větší režii, takže platí: co nejkratší
transakce, pokud uvnitř transakce nedochází k modifikaci dat, tak je vhodné transakci označit jako
READ ONLY, a konečně - vyvarovat se používání klauzulí FOR UPDATE, FOR SHARE - jelikož jsou
v SERIALIZABLE úrovni zbytečné. Také je nutné dávat pozor na počet aktivních spojeních, která zustanou ve stavu "idle in transaction".
Nepříjemné důsledky jsou o poznání horší než u předchozích verzí. Podpora úrovně SERIALIZABLE zatím nebyla přidána do Hot StandBy
režimu, takže v případě, že se používá binární replikace, je nejvyšší možná úroveň izolace transakcí REPEATABLE READ.

Minimum uživatelů zná mechanismus tzv. <i>rules</i>. S jejich pomocí lze do jisté míry předefinovat SQL
příkazy - lze např. vytvořit aktualizovatelné pohledy. Rules je mechanismus, který vystupuje z šera
dávnověku Postgresu - ještě z dob, kdy procedurální rozšíření a triggery byly v SQL tabu. Díky tzv
INSTEAD TRIGGERům by se PostgreSQL mohlo zbavit rules - minimálně jeho uživatelé. INSTEAD TRIGGERY jsou
klasické triggery definované nad pohledem. Jejich prostřednictvím lze snadno vytvořit aktualizovatelné
pohledy. Dovedu si představit několik málo situací, kdy se to může hodit. Naučil jsem se nebát se 
triggerů, ale snažím se triggery psát tak, abych uvnitř triggerů pokud možno co nejméně modifikoval data 
(to neplatí pro audit tabulky plněné triggery):
<pre>
postgres=# \h CREATE TRIGGER 
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )
</pre>

==Administrace==
Administrace externích modulů by se měla zjednodušit díky tzv. <i>rozšířením</i> (extensions). V podstatě jde o 
propracovanější evidenci externích zákaznických funkcí - náhradu za dříve používané install a uninstall skripty.
Práce s těmito skripty nebyla úplně přímočará a při troše smůly mohli mít běžní uživatelé problémy (zejména při
upgrade databáze). Extensions jsou pouze evidencí - není to nástroj odpovídající oráklovským packages nebo
modulům z DB2 (dle ANSI). Primárním cílem je zjednodušit instalaci externích modulů:
<pre>
postgres=# CREATE EXTENSION citext;
CREATE EXTENSION
postgres=# \dx
                        List of installed extensions
  Name  | Version | Schema |                   Description                    
--------+---------+--------+--------------------------------------------------
 citext | 1.0     | public | data type for case-insensitive character strings
(1 row)

postgres=# DROP EXTENSION citext ;
DROP EXTENSION

postgres=# select * from pg_available_extensions;
        name        | default_version | installed_version |                               comment                                
--------------------+-----------------+-------------------+----------------------------------------------------------------------
 citext             | 1.0             |                   | data type for case-insensitive character strings
 chkpass            | 1.0             |                   | data type for auto-encrypted passwords
 file_fdw           | 1.0             |                   | foreign-data wrapper for flat file access
 cube               | 1.0             |                   | data type for multidimensional cubes
 pg_stat_statements | 1.0             |                   | track execution statistics of all SQL statements executed
 pg_buffercache     | 1.0             |                   | examine the shared buffer cache
 dict_xsyn          | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance      | 1.0             |                   | calculate great-circle distances on the surface of the Earth
 xml2               | 1.0             |                   | XPath querying and XSLT
 moddatetime        | 1.0             |                   | functions for tracking last modification time
 dict_int           | 1.0             |                   | text search dictionary template for integers
 lo                 | 1.0             |                   | Large Object maintenance
 pageinspect        | 1.0             |                   | inspect the contents of database pages at a low level
 pgcrypto           | 1.0             |                   | cryptographic functions
 pgstattuple        | 1.0             |                   | show tuple-level statistics
 pg_freespacemap    | 1.0             |                   | examine the free space map (FSM)
 btree_gist         | 1.0             |                   | support for indexing common datatypes in GiST
 dblink             | 1.0             |                   | connect to other PostgreSQL databases from within a database
 intarray           | 1.0             |                   | functions, operators, and index support for 1-D arrays of integers
 adminpack          | 1.0             |                   | administrative functions for PostgreSQL
 unaccent           | 1.0             |                   | text search dictionary that removes accents
 intagg             | 1.0             |                   | integer aggregator and enumerator (obsolete)
 pgrowlocks         | 1.0             |                   | show row-level locking information
 pg_trgm            | 1.0             |                   | text similarity measurement and index searching based on trigrams
 btree_gin          | 1.0             |                   | support for indexing common datatypes in GIN
 tablefunc          | 1.0             |                   | functions that manipulate whole tables, including crosstab
 autoinc            | 1.0             |                   | functions for autoincrementing fields
 isn                | 1.0             |                   | data types for international product numbering standards
 timetravel         | 1.0             |                   | functions for implementing time travel
 fuzzystrmatch      | 1.0             |                   | determine similarities and distance between strings
 tsearch2           | 1.0             |                   | compatibility package for pre-8.3 text search functions
 insert_username    | 1.0             |                   | functions for tracking who changed a table
 hstore             | 1.0             |                   | data type for storing sets of (key, value) pairs
 ltree              | 1.0             |                   | data type for hierarchical tree-like structures
 seg                | 1.0             |                   | data type for representing line segments or floating-point intervals
 test_parser        | 1.0             |                   | example of a custom parser for full-text search
 refint             | 1.0             |                   | functions for implementing referential integrity (obsolete)
(37 rows)
</pre>

Vytváření binárních online záloh zjednoduší nová aplikace [http://developer.postgresql.org/pgdocs/postgres/app-pgbasebackup.html <i>pg_basebackup</i>].
Pro zajištění replikace a zálohování je nutné mít status REPLICATION a odpovídající záznam v pg_hba.conf. 
<pre>
postgres=# ALTER ROLE pavel REPLICATION;
ALTER ROLE
</pre>
Upravený soubor <i>pg_hba.conf</i>:
<pre>
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident
local   replication     pavel                                   ident
</pre>
Změny v <i>postgres.conf</i> (minimálně jeden wal sender a aktivovaný reim archivace nebo Hot Standby):
<pre>
wal_level = archive             # minimal, archive, or hot_standby
archive_mode = on               # allows archiving to be done
max_wal_senders = 1             # max number of walsender processes
archive_command = '/bin/true'   # command to use to archive a logfile segment
wal_keep_segments = 100         # in logfile segments, 16MB each; 0 disables
</pre>
Po provedení příkazu mám v adresáři <i>zaloha_pg</i> funkční zálohu db clusteru:
<pre>
[pavel@nemesis ~]$ /usr/local/pgsql91/bin/pg_basebackup -D ~/zaloha_pg/ -c fast -P -v -xxlog start point: 0/C000020
34932/18547 kB (100%) 1/1 tablespaces (                              )00000001000000000000000C)
xlog end point: 0/C000094
pg_basebackup: base backup completed.
</pre>
pg_basebackup lze použít nejen na lokální db (pokud je přístup k databázi, tak lze použít pg_basebackup).

<i>pg_dump</i> umožňuje vytvářet zálohu v tzv "directory" formátu (volba -Fd). Výsledkem zálohování není jeden
soubor nýbrž adresář, kde je záloha každé tabulky umístěna v separátním souboru:
<pre>
postgres=# \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | f1   | table | pavel
 public | foo  | table | pavel
(2 rows)

[pavel@nemesis xy]$ /usr/local/pgsql/bin/pg_dump -Fd postgres -f zaloha
[pavel@nemesis xy]$ cd zaloha/
[pavel@nemesis zaloha]$ ls -l
total 12
-rw-rw-r--. 1 pavel pavel   28 Mar 25 14:58 2712.dat.gz
-rw-rw-r--. 1 pavel pavel   31 Mar 25 14:58 2713.dat.gz
-rw-rw-r--. 1 pavel pavel 2506 Mar 25 14:58 toc.dat
</pre>

Pokud chceme nějakým způsobem upravit data, tak zvlášť u větších a velkých
databází je uložení do separátních souborů nutnost.

Mezi drobné maličkosti, které potěší, je rozšířený autocomplete v <i>psql</i>. Dalším malým zpříjemněním je 
zvětšení limitu u položek typu VARCHAR bez nutnosti změn v datových souborech - změní se pouze položka v
jedné systémové tabulce.

Počínaje verzí 9.0 PostgreSQL podporuje asynchronní replikaci založenou na exportu transakčního logu.
9.1 rozšiřuje nabídku intergované replikace o synchronní replikaci. Synchronní a asynchronní replikaci 
lze kombinovat. Synchronní replikace je navázána na transakci - způsob zajištění transakce je určen 
systémovou proměnnou <i>synchronous_replication(boolean)</i>.

Vývojáři pracující s uloženými procedurami možná ocení metapříkaz \sf pro zobrazení zdrojového textu funkce.
Perličkou je možnost nastavit výchozí řádek pro externí editor v metapříkazu \ef (editace funkce):
<pre>
postgres=# \sf+ f
       CREATE OR REPLACE FUNCTION public.f(a integer)
        RETURNS integer
        LANGUAGE plpgsql
1       AS $function$
2       BEGIN
3             RETURN a + 1;
4       END;
5       $function$
</pre>

==Výkon==

<i>Unlogged tables</i> jsou tabulky, které nejsou jištěny transakčním logem - podporují transakce, ale 
pokud dojde k havárii, tak je jejich obsah smazán, protože jej nelze ověřit vůči transakčnímu logu. 
V určitých případech se tento typ tabulek může hodit - ať už to jsou různé ETL úlohy nebo klasická 
správa HTTP sessions. V další verzi (9.2) by se nad tímto typem tabulek měly implementovat tzv 
<i>globální dočasné tabulky</i>. Jelikož se změny obsahu v těchto tabulkách nezapisují do transakčního logu,
jsou všechny operace - INSERT, UPDATE, DELETE, COPY několikanásobně rychlejší. Pro zjištění rychlosti
jsem použil databázi PSC, která je ve formátu SQL.

Pokud jsem tuto databázi neupravil, tak že INSERTy byly zapouzdřeny v transakci - pak import této db
trval cca 40 sec (po vložení do transakce cca 2sec):
<pre>
[pavel@nemesis Downloads]$ time psql.5491 postgres -c '\i databaze_psc_2007-07-01.sql' -q
psql.5491:databaze_psc_2007-07-01.sql:9: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "psc_pkey" for table "psc"

real	0m39.954s
user	0m0.239s
sys	0m0.128s
</pre>
Pokud jsem tabulku vytvořil jako unlogged:
<pre>
CREATE UNLOGGED TABLE psc (
  psc numeric(11) NOT NULL default '0',
  posta varchar(64) NOT NULL default '',
  PRIMARY KEY  (psc)
);
</pre>
tak se import zkrátil cca na dvě sec (aniž bych musel použít explicitní transakci):
<pre>
[pavel@nemesis Downloads]$ time psql.5491 postgres -c '\i databaze_psc_2007-07-01.sql' -q
psql.5491:databaze_psc_2007-07-01.sql:11: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "psc_pkey" for table "psc"

real	0m2.146s
user	0m0.067s
sys	0m0.057s
</pre>
Jednou ze změn, která není viditelná, ale potěší, je schopnost planneru optimalizovat dotaz <t>SELECT max(id) FROM tab</t>, 
tak aby se použil index nad sloupcem id, bez ohledu na to, zda-li je tabulka tab tabulkou nebo <i>partition</i>. 
<pre>
postgres=# EXPLAIN SELECT max(a) FROM data;
                                                 QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
Aggregate  (cost=10000009955.25..10000009955.26 rows=1 width=4)
  ->  Merge Append  (cost=10000000168.77..10000009449.25 rows=202400 width=4)
        Sort Key: public.data.a
        ->  Sort  (cost=10000000168.75..10000000174.75 rows=2400 width=4)
              Sort Key: public.data.a
              ->  Seq Scan on data  (cost=10000000000.00..10000000034.00 rows=2400 width=4)
        ->  Index Scan Backward using a_00000_a_idx on a_00000 data  (cost=0.00..2780.26 rows=100000 width=4)
        ->  Index Scan Backward using a_00001_a_idx on a_00001 data  (cost=0.00..2780.26 rows=100000 width=4)

postgres=# explain SELECT a FROM data ORDER BY 1 limit 10;
                                                QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
Limit  (cost=85.89..86.35 rows=10 width=4)
  ->  Result  (cost=85.89..9366.37 rows=202400 width=4)
        ->  Merge Append  (cost=85.89..9366.37 rows=202400 width=4)
              Sort Key: public.data.a
              ->  Sort  (cost=85.86..91.86 rows=2400 width=4)
                    Sort Key: public.data.a
                    ->  Seq Scan on data  (cost=0.00..34.00 rows=2400 width=4)
              ->  Index Scan using a_00000_a_idx on a_00000 data  (cost=0.00..2780.26 rows=100000 width=4)
              ->  Index Scan using a_00001_a_idx on a_00001 data  (cost=0.00..2780.26 rows=100000 width=4)
(9 rows)
</pre>
Skutečně, v 9.1 si planner mnohem lépe rozumí s partitioningem. Co se bohužel nezměnilo je 
způsob vytváření partitions. Je docela možné, že na to dojde v 9.2.

Problém, který se stále vrací je přetížení IO při checkpointu. Vyřeší se, a s růstem kapacit RAM se po nějakém
čase objeví znovu - při 16GB RAM se na ext3 vyskytují (testováním potvrzeno viz [http://blog.2ndquadrant.com/en/2011/02/linux-filesystems-and-postgres.html linux-filesystems-and-postgres])
problémy s latencí dotazů (a např. na XFS latence dotazů je cca desetinásobně menší). Greg Smith s Robertem
Haasem navrhli a realizovali řešení, které by mělo umožňovat bezproblémový provoz PostgreSQL i na ext3. Jelikož
mám všude ext4 tak to, jak se jim to povedlo, nemohu posoudit. Při mém testování se ukazuje 9.1 o fous (cca 5%) rychlejší
než 9.0 při velmi intenzivní zátěži. 

Jeden z externích modulů [http://developer.postgresql.org/pgdocs/postgres/pgtrgm.html pg_trgm] je nyní možné využít i pro indexaci dotazů s podmínkou:
<pre>
 WHERE sloupec LIKE '%něco%'
</pre>
Není to úplně zadarmo - index nad sloupcem s trigramy je několikanásobně větší než klasický index (to může
působit problémy u velkých tabulek) - do nedávna jsem nevěřil, že je něco takového vůbec možné. 
<pre>
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
postgres=# CREATE INDEX ON psc USING gin (posta gin_trgm_ops);
CREATE INDEX

postgres=#  SELECT * FROM psc WHERE posta LIKE '%Běl%';
  psc  |       posta       
-------+-------------------
 34526 | Bělá nad Radbuzou
 56905 | Bělá nad Svitavou
 29421 | Bělá pod Bezdězem
 79085 | Bělá pod Pradědem
 38743 | Bělčice
 78315 | Bělkovice-Lašťany
 75364 | Bělotín
 58261 | Česká Bělá
 33152 | Dolní Bělá
 50781 | Lázně Bělohrad
 53343 | Rohovládova Bělá
 51703 | Skuhrov nad Bělou
(12 rows)

Time: 3.209 ms

postgres=# EXPLAIN SELECT * FROM psc WHERE posta LIKE '%Běl%';
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Bitmap Heap Scan on psc  (cost=12.26..34.47 rows=34 width=19)
   Recheck Cond: ((posta)::text ~~ '%Běl%'::text)
   ->  Bitmap Index Scan on psc_posta_idx  (cost=0.00..12.25 rows=34 width=0)
         Index Cond: ((posta)::text ~~ '%Běl%'::text)
(4 rows)

postgres=# drop INDEX psc_posta_idx;
DROP INDEX

postgres=#  SELECT * FROM psc WHERE posta LIKE '%Běl%';
  psc  |       posta       
-------+-------------------
 34526 | Bělá nad Radbuzou
 56905 | Bělá nad Svitavou
 29421 | Bělá pod Bezdězem
 79085 | Bělá pod Pradědem
 38743 | Bělčice
 78315 | Bělkovice-Lašťany
 75364 | Bělotín
 58261 | Česká Bělá
 33152 | Dolní Bělá
 50781 | Lázně Bělohrad
 53343 | Rohovládova Bělá
 51703 | Skuhrov nad Bělou
(12 rows)

Time: 8.071 ms
</pre>
Trigramy lze použít i pro podporu operátoru ILIKE:
<pre>
postgres=# CREATE INDEX ON psc USING gin (posta gin_trgm_ops);
CREATE INDEX

postgres=#  SELECT * FROM psc WHERE posta ILIKE '%běl%';
  psc  |       posta       
-------+-------------------
 34526 | Bělá nad Radbuzou
 56905 | Bělá nad Svitavou
 29421 | Bělá pod Bezdězem
 79085 | Bělá pod Pradědem
 38743 | Bělčice
 78315 | Bělkovice-Lašťany
 75364 | Bělotín
 58261 | Česká Bělá
 33152 | Dolní Bělá
 50781 | Lázně Bělohrad
 53343 | Rohovládova Bělá
 51703 | Skuhrov nad Bělou
(12 rows)

Time: 5.428 ms

postgres=# DROP INDEX psc_posta_idx;
DROP INDEX

postgres=#  SELECT * FROM psc WHERE posta ILIKE '%běl%';
  psc  |       posta       
-------+-------------------
 34526 | Bělá nad Radbuzou
 56905 | Bělá nad Svitavou
 29421 | Bělá pod Bezdězem
 79085 | Bělá pod Pradědem
 38743 | Bělčice
 78315 | Bělkovice-Lašťany
 75364 | Bělotín
 58261 | Česká Bělá
 33152 | Dolní Bělá
 50781 | Lázně Bělohrad
 53343 | Rohovládova Bělá
 51703 | Skuhrov nad Bělou
(12 rows)

Time: 23.874 ms
</pre>
<i>GiST</i> index dosud nepodporoval operaci ORDER BY. Pomocí tohoto indexu bylo možné filtrovat, ale nebylo
možné řadit filtrovaná data. GiST umožňoval rychlé dotazy typu - najdi všechny objekty z okolí x km, ale
už neumožnil efektivní zpracování dotazu typu - najdi 10 nejbližších objektů. Implementací algoritmu
<i>k nearest neighbourhood - KNNGIST</i> umožňuje využít GiST index i pro tento typ úloh:
<pre>
SELECT position <-> point(500,500) 
   FROM test 
  ORDER BY position <-> point(500,500) 
  LIMIT 10;
</pre> 

PostgreSQL je pravděpodobně první databází, která implementuje KNN algoritmus.

Výše zmíněné rozšíření lze použít nejen pro geodata. [http://www.depesz.com/index.php/tag/postgresql/ Hubert Lubaczewski] prezentoval použití <i>KNNGiST</i> i pro
dohledání nejpodobnějších slov na základě vzdálenosti trigramů viz [http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-knngist/ http://www.depesz.com/index.php/2010/12/11/waiting-for-9-1-knngist]:
<pre>
postgres=# SELECT *, similarity(posta, 'benesov') 
              FROM psc 
             ORDER BY posta <-> 'benesov' 
             LIMIT 10;
  psc  |       posta        | similarity 
-------+--------------------+------------
 51237 | Benecko            |   0.333333
 67506 | Benetice           |   0.307692
 74722 | Dolní Benešov      |   0.294118
 79312 | Horní Benešov      |   0.294118
 51206 | Benešov u Semil    |   0.263158
 25601 | Benešov u Prahy    |   0.263158
 67953 | Benešov u Boskovic |   0.238095
 38282 | Benešov nad Černou |   0.227273
 33041 | Bezvěrov           |   0.214286
 68333 | Nesovice           |   0.214286
(10 rows)

postgres=# EXPLAIN SELECT *, similarity(posta, 'benesov') 
                      FROM psc 
                     ORDER BY posta <-> 'benesov' 
                     LIMIT 10;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.87 rows=10 width=19)
   ->  Index Scan using psc_posta_idx on psc  (cost=0.00..291.76 rows=3356 width=19)
         Order By: ((posta)::text <-> 'benesov'::text)
(3 rows)
</pre>
Fantazii se meze nekladou - myslím si, že především uživatelé PostGISu kvůli KNNGiSTu přejdou na 9.1 velice
brzo.

==CSPUG==
Neziskové sdružení CSPUG vzniklo za účelem zajišťování propagačních a osvětových aktivit ohledně
PostgreSQL, SQL, atd. Letošní P2D2 bylo premiérové v tom, že jeho organizace byla výlučně záležitost
CSPUGu. V P2D2 chceme pokračovat, počítá se ovšem i s [http://www.pgsql.cz/index.php/Setk%C3%A1n%C3%AD_CSPUG jinými pravidelnými akcemi].
Pokud byste měli zájem podpořit CSPUG nebo chuť se zapojit, staňte členy sdružení. 

==Na čem se pracuje?==
Vývojáři PostgreSQL byli v posledních čtyřech letech pod určitým psychologickým tlakem - počínaje 8.0 
přicházelo více patchů než se dařilo zpracovávat - při stávající složitosti kódu a požadavku na kvalitu
je docela běžné, že se patch dostal do kódu po cca dvou letech a jeho autor musel kód několikrát přepsat.
Tento tlak vedl ke změnám ve vývojového procesu, změně systému pro správu kódu. Počínaje 8.4 se počet 
čekajících patchů začal snižovat. 9.1 je výjmečná v tom, že se podařilo zpracovat všechny patche. Počet
a složitost patchů, které se přesouvají do 9.2 je [https://commitfest.postgresql.org/action/commitfest_view?id=10 "nezvykle" nižší a menší]. Od 9.2 bych čekal větší úpravy
v planneru - hodně se diskutuje a multidimenzionálních statistikách - hledá se způsob jak zpřístupnit
a zjednodušit konfiguraci replikace. Ze seznamu patchů připravené pro 9.2 lze jmenovat podporu SQL příkazu
MERGE, podporu generického typu RANGE, a desítky drobných úprav a vylepšení. Od 9.2 nečekám, že přinese
zásadní nové funkce, ale že se bude snáze konfigurovat a používat. Postupně vznikají nástroje, které by
mají pomoci s administrací serveru a ty jsou začleňovány do jádra - viz např. pg_basebackup. Dalším nástrojem
pro administraci replikovaných serverů je [https://github.com/greg2ndQuadrant/repmgr/blob/master/README.rst repmgr].
Diskutuje se o způsobu přístupu k souborovému systému - diskutuje se možnost použítí mmap. Je připraven
patch, který obsahuje podporu typu JSON. Diskutuje se o interních optimalizacích, diskutuje se o podpoře
"skutečných" uložených procedur - s možností explicitního řízení transakcí, s podporou multirecordsetů.

Díky podpoře CZ.NICu mohu pracovat na implementaci podpory jazyka pro vývoj uložených procedur definovaného
standardem SQL/PSM. Tento programovací jazyk by měl doplnit (nikoliv nahradit) jazyk PL/pgSQL,
který vychází z jazyka PL/SQL fy. Oracle (vycházím ze svých zkušeností s PL/pgSQL). Na rozdíl od PL/pgSQL 
je moje implementace SQL/PSM mnohem  statičtější, což na jednu stranu umožňuje důkladnější kontroly při 
registraci funkce (na všechny potenciální chyby se přijde během registrace nebo prvního spuštění), příčemž
ale uživatel bude nucen častěji použít <i>dynamické SQL</i>. Absolutní kompatibilita je u SQL databází iluzí a  
platí to i pro uložené procedury. Standard je věc jedna - věc druhá je fakt, že standard nepopisuje 
určité specifické konstrukce, vzory, které jsou i z historických důvodů implementovány různě. Přirovnal 
bych to k C - existuje standard, ale trochu složitější aplikace se programují jinak v OS UNIX a jinak v OS 
MS Windows. Totéž lze říci i o jazyku SQL/PSM. Nemusíte se učit s každou databází nový jazyk, ale vždy 
musíte respektovat specifika té či oné databáze (s pomocí speciálních knihoven lze minimalizovat rozdíly 
v kódu pro jednotlivé db).

Poznávacím znamením SQL/PSM je obsluha chyb pomocí tzv. handlerů:
<pre>
create or replace function test38()
returns int as $$
begin
  declare aux int;
  declare s int default 0;
  declare done boolean default false;
  declare c1 cursor for select a from footab;
  declare continue handler for not found set done = true;
  open c1;
  fetch c1 into aux;
  while not done do
    set s = s + aux;
    fetch c1 into aux;
  end while;
  return s;
end;
$$ language psm0;
</pre>
Výše uvedený kód (tělo funkce) spustíte na MySQL, v DB2 a v řadě dalších databází. Implementace SQL/PSM v 
PostgreSQL umožňuje použít i další vzor, který je častý v DB2 (využití magických proměnných SQLSTATE nebo SQLCODE):
<pre>
create or replace function test28(a int)
returns int as $$
begin
  declare sqlstate char(5);
  declare aux int;
  declare s int default 0;
  declare cx cursor for select footab.a from footab;
  open cx;
  fetch cx into aux;
  while sqlstate = '00000' do
    set s = s + aux;
    fetch cx into aux;
  end while;
  close cx;
  return s;
end;
$$ language psm0;
</pre>
Ve standardu nalezneme komfortní konstrukci FOR (používá se ještě jednodušeji než v FOR v PL/pgSQL):
<pre>
create or replace function test37()
returns int as $$
begin
  declare s int default 0;
  for select * from footab do
    set s = s + a;
  end for;
  return s;
end;
$$ language psm0;
</pre>
Aktuálně je kompletní implementace základní části standardu SQL/PSM. Nula na konci názvu znamená,
že se jedná o prototyp. Před nasazením do produkce je nutné provést revizi. Mým cílem je 
možnost používat PSM v produkčních prostředích někdy koncem roku 2011, možná v půli roku 2012 
(měl by být k dispozici spolu s příští verzí PostgreSQL (tj 9.2)).
----------
Související články
<ncl>Category:Verze PostgreSQL</ncl>