Zobrazení zdroje stránky PostgreSQL 13 (2020)

<i>Autor: Pavel Stěhule, 2020</i>

=PostgreSQL 13=

Při vývoji databáze jdou proti sobě dvě síly. Tou první je snaha o co "nejchytřejší" optimalizátor,
a co nejrobustnější, nejspolehlivější provoz podepřený co nejpodrobnějším monitoringem. Chytřejší
optimalizátor si lépe poradí s komplikovanými dotazy. Bohužel chytřejší optimalizátor je pomalejší
a náročnější na zdroje, a tudíž zpomalí vykonávání jednoduchých dotazů, kde se jeho možnosti neuplatní.
Podrobnější monitoring je také náročnější na zdroje(IO, CPU).

Aby výkon databáze s každou novou verzí výrazně neklesal (nebo jen neklesal) neustále se reviduje kód
a neustále se hledají optimalizace na všech úrovních. A to je ta druhá síla. Není až tak úplně
komplikované vymyslet novou optimalizaci dotazu, ale je docela komplikované ji implementovat ve
stávajícím systému, tak aby se drtivá většina dotazů nezpomalila (pro velké množství aplikací je 90%
kvantil dotazů pod 10ms).

Mně osobně přijde, že letošní verze není není charakteristická nějakou výraznou novou funkcionalitou.
Ale nejde si nevšimnout desítek poměrně důležitých interních optimalizací (na některých se pracovalo
několik let - rozhodně se tedy nejedná pouze o mikro optimalizace).

==Optimalizace==

===Deduplikace klíčů v indexu===

Zvláště u indexů cizích klíčů se vyskytuje velké množství duplicitních hodnot. Postgres 13 podporuje
deduplikaci klíčů uložených v indexu. K deduplikaci (k redukci
duplicitních klíčů) dochází ve chvíli dělení koncové stránky indexu (neprovádí se okamžitě). Díky tomu
by měla mít mít minimální režii. Na mých testovacích datech ([http://pgsql.cz/files/obce.sql.gz obce ČR]) je velikost indexu nad cizím
klíčem (<code>obce.okres_id</code>) méně než poloviční 64kB (ve verzi 13) versus 160kB (ve verzi 12).

===Incremental sort===

Pokud jsem chtěl ve starších verzích urychlit více sloupcové řazení indexem, musel jsem mít odpovídající
více sloupcový index. A pokud jsem měl více kombinací, tak jsem musel mít víc více-sloupcových indexů.
Více indexů znamená pomalejší <code>INSERT</code>, <code>UPDATE</code>, pomalejší <code>VACUUM</code>.
Nová verze umí načíst data v určitém pořadí z indexu, a pak je do-seřadit.

Jelikož nemám velká data, tak si musím snížit <code>work_mem</code> (a tím si prodražít sort):
<pre>
SET work_mem TO '64kB';

-- Postgres 12
postgres=> EXPLAIN SELECT * 
                     FROM obce
                    ORDER BY okres_id, pocet_muzu + pocet_zen DESC;
┌────────────────────────────────────────────────────────────────┐
│                           QUERY PLAN                           │
╞════════════════════════════════════════════════════════════════╡
│ Sort  (cost=916.18..931.80 rows=6250 width=45)                 │
│   Sort Key: okres_id, ((pocet_muzu + pocet_zen)) DESC          │
│   ->  Seq Scan on obce  (cost=0.00..137.12 rows=6250 width=45) │
└────────────────────────────────────────────────────────────────┘
(3 rows)

postgres=> EXPLAIN SELECT * 
                     FROM obce 
                    ORDER BY okres_id, nazev;
┌────────────────────────────────────────────────────────────────┐
│                           QUERY PLAN                           │
╞════════════════════════════════════════════════════════════════╡
│ Sort  (cost=900.55..916.18 rows=6250 width=41)                 │
│   Sort Key: okres_id, nazev                                    │
│   ->  Seq Scan on obce  (cost=0.00..121.50 rows=6250 width=41) │
└────────────────────────────────────────────────────────────────┘
(3 rows)

-- Postgres 13
postgres=> EXPLAIN SELECT * 
                     FROM obce 
                    ORDER BY okres_id, pocet_muzu + pocet_zen DESC;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                        QUERY PLAN                                        │
╞══════════════════════════════════════════════════════════════════════════════════════════╡
│ Incremental Sort  (cost=7.17..618.34 rows=6250 width=45)                                 │
│   Sort Key: okres_id, ((pocet_muzu + pocet_zen)) DESC                                    │
│   Presorted Key: okres_id                                                                │
│   ->  Index Scan using obce_okres_id_idx on obce  (cost=0.28..206.12 rows=6250 width=45) │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)

postgres=> EXPLAIN SELECT * 
                     FROM obce 
                    ORDER BY okres_id, nazev;
┌──────────────────────────────────────────────────────────────────────────────────────────┐
│                                        QUERY PLAN                                        │
╞══════════════════════════════════════════════════════════════════════════════════════════╡
│ Incremental Sort  (cost=6.97..602.71 rows=6250 width=41)                                 │
│   Sort Key: okres_id, nazev                                                              │
│   Presorted Key: okres_id                                                                │
│   ->  Index Scan using obce_okres_id_idx on obce  (cost=0.28..190.49 rows=6250 width=41) │
└──────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)
</pre>
Tím, že se neřadí celá tabulka naráz, ale blokově, tak se zvyšuje šance, že se řazený blok vejde
do paměti, a bude možné použít <i>quick sort místo</i> pomalejšího, náročnějšího <i>external sortu</i>.

===HashAgg respektuje nastavení work_mem===

Konfigurační proměnná [https://www.postgresql.org/docs/current/runtime-config-resource.html <code>work_mem</code> ] se používá při plánování dotazu jako limit pro rozhodování mezi
paměťově náročnějšími nebo naopak úspornějšími algoritmy. Používá se také při vykonávání dotazů jako
limit paměťových bufferů. Po naplnění bufferů se začne zapisovat do dočasných souborů. Tak by se měly
chovat veškeré operace, které startuje executor dotazů. Výjimkou byla hash agregace (agregace, kdy
mezivýsledky agregace jsou uložené v hashovací tabulce). Popsané chování nebylo ve starších verzích
implementováno. Několikrát jsem se dostal do situace, kdy jsem musel penalizovat <code>hash_agg</code>, protože
jinak docházelo ke swapování nebo k zabití Postgresu OOM kilerem. Jednalo se často o <code>SELECT DISTINCT</code>
nad extrémně velkými tabulkami, kde muselo dojít ke špatnému <code>ndistinct</code> odhadu. V nové verzi již <code>hash_agg</code>
není výjimkou, a agregace by měla být bezpečná (a v některých případech bohužel pomalejší, jako 
celek ale mnohem stabilnější):

Např
<pre>
postgres=> SET work_mem to '200MB';
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT 'AHOJ' || i FROM generate_series(1,1000000) g(i);
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                QUERY PLAN                                                                 │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate  (cost=20000.00..20003.50 rows=200 width=32) (actual time=686.754..884.658 rows=1000000 loops=1)                            │
│   Group Key: ('AHOJ'::text || (i)::text)                                                                                                  │
│   Peak Memory Usage: 98321 kB                                                                                                             │
│   ->  Function Scan on generate_series g  (cost=0.00..17500.00 rows=1000000 width=32) (actual time=100.446..304.289 rows=1000000 loops=1) │
│ Planning Time: 0.178 ms                                                                                                                   │
│ Execution Time: 935.348 ms                                                                                                                │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
</pre>
Zde je vidět, že tento dotaz spotřeboval cca 100MB paměti. Zrovna tak by se choval ve starších verzích
Postgresu a to bez ohledu na nastavení <code>work_mem</code>.
<pre>
postgres=> SET work_mem to '4MB';
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT 'AHOJ' || i FROM generate_series(1,1000000) g(i);
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                QUERY PLAN                                                                 │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate  (cost=20000.00..20003.50 rows=200 width=32) (actual time=594.709..1158.370 rows=1000000 loops=1)                           │
│   Group Key: ('AHOJ'::text || (i)::text)                                                                                                  │
│   Peak Memory Usage: 5465 kB                                                                                                              │
│   Disk Usage: 28848 kB                                                                                                                    │
│   HashAgg Batches: 116                                                                                                                    │
│   ->  Function Scan on generate_series g  (cost=0.00..17500.00 rows=1000000 width=32) (actual time=110.250..334.606 rows=1000000 loops=1) │
│ Planning Time: 0.176 ms                                                                                                                   │
│ Execution Time: 1201.208 ms                                                                                                               │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)
</pre>
Pokud omezím <code>work_mem</code>, tak stejně dojde k přetečení, nicméně skutečná hodnota se bezproblémově vejde
do dvojnásobku s kterým se tak nějak počítá. Na druhou stranu agregace je o 265ms pomalejší (cca 25%). Určitě může
dojít k situacím, kdy agregace (na verzi 13) bude o 25% pomalejší, ale nemělo by dojít k situaci, kdy
Vám agregace odrovná server (protože vyžere veškerou paměť). Pokud byl odhad počtu unikátních hodnot v pořádku, tak i starší verze 
nepoužívaly <code>hash_agg</code>:
<pre>
postgres=> SET work_mem to '64kB'; -- 12
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT nazev 
                             FROM foo;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                       QUERY PLAN                                                       │
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Unique  (cost=16385.34..16916.59 rows=5344 width=10) (actual time=291.662..326.179 rows=5342 loops=1)                  │
│   ->  Sort  (cost=16385.34..16650.96 rows=106250 width=10) (actual time=291.660..315.851 rows=106250 loops=1)          │
│         Sort Key: nazev                                                                                                │
│         Sort Method: external merge  Disk: 2192kB                                                                      │
│         ->  Seq Scan on foo  (cost=0.00..2065.50 rows=106250 width=10) (actual time=0.021..13.926 rows=106250 loops=1) │
│ Planning Time: 0.099 ms                                                                                                │
│ Execution Time: 326.833 ms                                                                                             │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

-- zde se ukazuje Postgres 13 jako mnohem rychlejší
postgres=> SET work_mem to '64kB';
SET
postgres=> EXPLAIN ANALYZE SELECT DISTINCT nazev
                             FROM foo;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    QUERY PLAN                                                    │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ HashAggregate  (cost=6481.52..7572.52 rows=5341 width=10) (actual time=56.153..93.098 rows=5342 loops=1)         │
│   Group Key: nazev                                                                                               │
│   Planned Partitions: 4                                                                                          │
│   Peak Memory Usage: 93 kB                                                                                       │
│   Disk Usage: 6264 kB                                                                                            │
│   HashAgg Batches: 20                                                                                            │
│   ->  Seq Scan on foo  (cost=0.00..2065.50 rows=106250 width=10) (actual time=0.012..11.785 rows=106250 loops=1) │
│ Planning Time: 0.982 ms                                                                                          │
│ Execution Time: 94.789 ms                                                                                        │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
</pre>
Sort je výrazně pomalejší už kvůli použití implicitního [https://cs.wikipedia.org/wiki/Locale <i>collate</i> ], což je v mém případě <code>"cs_CZ"</code>. V tomto ohledu
je implementace v Postgresu 13 ohromný skok kupředu (a jak to vypadá finální vyřešení roky trvajícího problému,
který nebyl příliš častý, ale pokud se vyskytl, tak byl velmi nepříjemný).

===Vícesloupcové statistiky===

Připravil jsem si pomocnou tabulku <code>obce2</code> se sloupcem popisující velikost obce v textové podobě, a 
sloupcem, který určuje jestli je obec okresní město nebo nikoliv.
<pre>
postgres=> SELECT *
             FROM obce2
            LIMIT 10;
┌────┬──────────┬─────────────┬────────────┬───────────┬─────────────┬────────────────────┐
│ id │ okres_id │    nazev    │ pocet_muzu │ pocet_zen │  velikost   │ je_okresnim_mestem │
╞════╪══════════╪═════════════╪════════════╪═══════════╪═════════════╪════════════════════╡
│  1 │ CZ0100   │ Praha       │     608316 │    640710 │ extra velka │ t                  │
│  2 │ CZ0201   │ Benešov     │       7875 │      8507 │ stredni     │ t                  │
│  3 │ CZ0201   │ Bernartice  │        108 │       115 │ mala        │ f                  │
│  4 │ CZ0201   │ Bílkovice   │         93 │        89 │ mala        │ f                  │
│  5 │ CZ0201   │ Blažejovice │         52 │        48 │ mala        │ f                  │
│  6 │ CZ0201   │ Borovnice   │         39 │        37 │ mala        │ f                  │
│  7 │ CZ0201   │ Bukovany    │        364 │       372 │ mala        │ f                  │
│  8 │ CZ0201   │ Bystřice    │       2124 │      2096 │ stredni     │ f                  │
│  9 │ CZ0201   │ Ctiboř      │         55 │        50 │ mala        │ f                  │
│ 10 │ CZ0201   │ Čakov       │         65 │        60 │ mala        │ f                  │
└────┴──────────┴─────────────┴────────────┴───────────┴─────────────┴────────────────────┘
(10 rows)
</pre>
Zkusím napsat dotaz, ve kterém by se měl projevit efekt [https://www.postgresql.org/docs/current/sql-createstatistics.html vícesloupcových statistik ]:
<pre>
postgres=> EXPLAIN ANALYZE SELECT * 
                             FROM obce2 
                            WHERE velikost = 'stredni' AND je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                             │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2  (cost=0.00..133.12 rows=16 width=35) (actual time=0.032..2.371 rows=40 loops=1) │
│   Filter: (je_okresnim_mestem AND ((velikost)::text = 'stredni'::text))                            │
│   Rows Removed by Filter: 6210                                                                     │
│ Planning Time: 0.140 ms                                                                            │
│ Execution Time: 2.412 ms                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

CREATE STATISTICS obce_velikost_je_okresnim_mestem(mcv) ON velikost, je_okresnim_mestem FROM obce2; 
ANALYZE obce2;

postgres=> EXPLAIN ANALYZE SELECT * 
                             FROM obce2 
                            WHERE velikost ='stredni' AND je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                             │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2  (cost=0.00..133.12 rows=40 width=35) (actual time=0.027..2.440 rows=40 loops=1) │
│   Filter: (je_okresnim_mestem AND ((velikost)::text = 'stredni'::text))                            │
│   Rows Removed by Filter: 6210                                                                     │
│ Planning Time: 0.481 ms                                                                            │
│ Execution Time: 2.480 ms                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
</pre>
V nové verzi se více sloupcové statistiky uplatní i pro filtr seznamem hodnot:
<pre>
postgres=> EXPLAIN ANALYZE SELECT * 
                             FROM obce2 
                            WHERE velikost IN ('stredni', 'velka') AND je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                             │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2  (cost=0.00..133.12 rows=67 width=35) (actual time=0.025..2.225 rows=67 loops=1) │
│   Filter: (je_okresnim_mestem AND ((velikost)::text = ANY ('{stredni,velka}'::text[])))            │
│   Rows Removed by Filter: 6183                                                                     │
│ Planning Time: 0.495 ms                                                                            │
│ Execution Time: 2.266 ms                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
</pre>
nebo filtr podle existence prvku v poli:
<pre>
postgres=> EXPLAIN ANALYZE SELECT * 
                             FROM obce2 
                            WHERE velikost = ANY (ARRAY['stredni','velka']) and je_okresnim_mestem;
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                             │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce2  (cost=0.00..133.12 rows=67 width=35) (actual time=0.028..2.241 rows=67 loops=1) │
│   Filter: (je_okresnim_mestem AND ((velikost)::text = ANY ('{stredni,velka}'::text[])))            │
│   Rows Removed by Filter: 6183                                                                     │
│ Planning Time: 0.178 ms                                                                            │
│ Execution Time: 2.282 ms                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
</pre>

V nové verzi je také možné explicitně nastavit přesnost vícesloupcových statistik příkazem:
<pre>
ALTER STATISTICS stat_name SET STATISTICS target_value;
</pre>

===Optimalizace evaluace výrazů v PLpgSQL===

Letos se podařila docela výrazná optimalizace v interpretu [https://www.postgresql.org/docs/current/plpgsql.html <i>PLpgSQL</i> ]. Tento jazyk (prostředí) slouží
(měl by sloužit) primárně jako lepidlo pro SQL dotazy, a sám o sobě téměř nikdy nepředstavuje úzké
hrdlo. Nedoporučuje se provádět výpočetně náročné úlohy v tomto jazyku. Na druhou stranu toto prostředí
není až tak pomalé, a po letošní optimalizaci už se nějaký ten výpočet dá zvládnout i v PLpgSQL. Pro
ukázku jsem zvolil implementaci odhadu hodnoty &pi; (což je zrovna to, co bych v [https://postgres.cz/wiki/PL/pgSQL PLpgSQL ] za normálních
okolností nikdy nedělal):
<pre>
CREATE OR REPLACE FUNCTION pi_est_1(n int)
RETURNS numeric AS $$
DECLARE
  accum double precision DEFAULT 1.0;
  c1 double precision DEFAULT 2.0;
  c2 double precision DEFAULT 1.0;
BEGIN
  FOR i IN 1..n
  LOOP
    accum := accum * ((c1 * c1) / (c2 * (c2 + 2.0)));
    c1 := c1 + 2.0;
    c2 := c2 + 2.0;
  END LOOP;
  RETURN accum * 2.0;
END;
$$ LANGUAGE plpgsql;

postgres=> select pi_est_1(1000000);
┌──────────────────┐
│     pi_est_1     │
╞══════════════════╡
│ 3.14159186819215 │
└──────────────────┘
(1 row)

Time: 332,436 ms
</pre>
Milion iterací trvalo cca 0.3 sec na mé 9 let staré T520 (podobný kód v Lue trval 110ms a 290ms
v Pythonu (v Pythonu s vyšší přesností)). V předchozí verzi ten samý kód běžel 835ms.

V běžném kódu takové zrychlení určitě nebude - úzkým hrdlem budou vždy operace s daty v databázi,
načíst a připravit data z databáze je samozřejmě výrazně náročnější než spočítat pár výrazů.

==psql==

Ve verzi 13 jsou v <code>psql</code> pouze kosmetické změny. Nově je to příkaz <code>\warn</code>, kterým se zapíše řetězec
do <code>stderr</code>. Nově máme možnost parametrizovat zobrazení u variant příkazu <code>\g</code>. Použitá syntax pro zápis
seznamu parametrů formátu je docela specifická (oddělovačem je mezera) a náhodou koreluje se zápisem connection
stringu.
<pre>
postgres=> SELECT 10, 'ahoj', 20;
┌──────────┬──────────┬──────────┐
│ ?column? │ ?column? │ ?column? │
╞══════════╪══════════╪══════════╡
│       10 │ ahoj     │       20 │
└──────────┴──────────┴──────────┘
(1 row)

postgres=> SELECT 10, 'ahoj', 20 \g (format=csv tuples_only=on)
10,ahoj,20
</pre>
K čemu se to hodí? Můžeme si snáz vytvářet <code>psql</code> makra, jako v následujícím příkladu. Makro, které
výsledek dotazu zobrazí v <i>gplot(u)</i>:
<pre>
postgres=> \set gnuplot '\\g (format=csv) |gnuplot -p -e "set datafile separator \',\'; set key autotitle columnhead; set terminal dumb enhanced;" -e' 

postgres=> SELECT i, sin(i) FROM generate_series(0, 6.3, 0.05) g(i) :gnuplot "plot '-'with lines ls 1"

                                                                               
    1 +--------------------------------------------------------------------+   
      |         ***      ***        +        +         +         +         |   
  0.8 |-+     ***          **                                  sin *******-|   
      |      **              **                                            |   
  0.6 |-+   **                **                                         +-|   
      |    *                    *                                          |   
  0.4 |-+ *                      *                                       +-|   
      |  *                        *                                        |   
  0.2 |-*                          *                                     +-|   
      |*                            *                                      |   
    0 |-+                            *                              *    +-|   
      |                               *                            *       |   
 -0.2 |-+                              *                          *      +-|   
      |                                 *                        *         |   
 -0.4 |-+                                *                      *        +-|   
      |                                   *                    *           |   
 -0.6 |-+                                  *                 **          +-|   
      |                                     **              **             |   
 -0.8 |-+                                    ***          **             +-|   
      |         +         +         +        + ***     +***      +         |   
   -1 +--------------------------------------------------------------------+   
      0         1         2         3        4         5         6         7   
</pre>
Bohužel <i>Gnome Terminal</i> neumí jemnou grafiku (<i>ReGIS</i> nebo [https://stackoverflow.com/questions/60629539/can-graph-with-lines-be-generated-in-gnuplot-in-a-linux-terminal <i>SIXEL</i> ]), takže žádná velká paráda se zde dělat
nedá (třeba jednou v budoucnu se možnosti Gnome terminálu zlepší - [https://www.macterm.net/ terminál ] na mac Os by jemnou grafiku měl zvládat).

Zajímavou novinkou, která je vidět ve výchozí konfiguraci je zobrazení stavu transakce v promtu.
<pre>
postgres=> \conninfo -- autocommit on
You are connected to database "postgres" as user "pavel" via socket in "/tmp" at port "5432".
postgres=> BEGIN; 
BEGIN
postgres=*> SELECT 1; -- otevřená transakce - symbol *
┌──────────┐
│ ?column? │
╞══════════╡
│        1 │
└──────────┘
(1 row)

postgres=*> SELECT x;
ERROR:  column "x" does not exist
LINE 1: SELECT x;
               ^
postgres=!> ROLLBACK; -- poškozená transakce - symbol !
ROLLBACK
postgres=> 
</pre>
A když už popisuji nastavení promptu, počínaje touto verzí lze nastavit neviditelnou položku o stejné
šířce jako jako je <code>PROMPT1</code> do <code>PROMPT2</code> (vztahuje se na druhý řádek a následující řádky). Pokud ve výchozí
konfiguraci napíši dotaz přes více řádků, pak se bude aplikovat <code>PROMPT2</code> a v <code>psql</code> uvidím následující
řádky:
<pre>
postgres=> SELECT *
postgres->   FROM obce;
</pre>
<code>PROMPT2</code> docela překáží při kopírování (z okna terminálu). Pokud bych si zase <code>PROMPT2</code> nastavil na
prázdný řetězec, tak budu mít rozhozené formátování. V nové verzi lze nastavit neviditelný <code>PROMPT2</code> s
šířkou která vždy odpovídá <code>PROMPT1</code>:
<pre>
postgres=> \set PROMPT2 %w
postgres=> SELECT *
              FROM obce;
</pre>

==Administrace==

===<code>DROP DATABASE FORCE</code>===

I tak banální operace jako je dropnutí (odstranění) databáze může být někdy problém. Postgres neumožní
odstranění databáze, pokud je k ní někdo připojený. U malé aplikace nebude problém dohledat a zavřít
příslušné spojení. V systémech, kde do databází přistupují desítky různých aplikací navíc ještě s poolovanými
spojeními, to už může být těžší. Přikaz [https://www.postgresql.org/docs/devel/sql-dropdatabase.html <code>DROP DATABASE</code> ] v nové verzi umožňuje další parametrizaci. Zatím
jediným parametrem je parametr <code>FORCE</code>. Tento parametr zajistí uzavření všech spojení a zajistí odstranění
databáze bez čekání na zavření spojení. Pozor na to. To, že <code>DROP DATABASE</code> ve starší verzi vždy čekalo,
až k databázi nebude nikdo připojený (5 sec a pak skončilo chybou), určitě mnohým DBA zachránilo kůži.

===Možnost modifikace systémových tabulek a jejich parametrů===

Je obecně přijímaným pravidlem, že na systémové tabulky se nesahá. Jako vždy a všude existují vyjímky.
Někdy je nutné nastavit mnohem agresivnější autovacuum a to i u systémových tabulek. Ve starších verzích
se muselo restartovat do single modu. V této verzi už může potřebný zásah udělat superuser po nastavení
proměnné <code>allow_system_table_mods</code>. Opět pozor - nevhodnou úpravou systémové tabulky můžete zrušit celou
databázi (možná opravitelnou v single modu) a s určitou dávkou štěstí nebo smůly i celou instanci.
<pre>
postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_vacuum_scale_factor = 0);
ERROR:  permission denied: "pg_depend" is a system catalog
postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_vacuum_threshold = 1000);
ERROR:  permission denied: "pg_depend" is a system catalog
postgres=# set allow_system_table_mods = on;
SET
postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE
postgres=# ALTER TABLE pg_catalog.pg_depend SET (autovacuum_analyze_threshold = 1000);
ALTER TABLE
</pre>
Nyní bude tabulka <code>pg_depend</code> vacuovaná vždy po modifikaci jednoho tisíce řádků.

===Bezpečné extenze (trusted extensions)===

Drtivou většinu extenzí mohl aktivovat pouze superuser. Zřejmě u většiny extenzí je to zbytečně
přísné omezení, jelikož instalace samotných extenzí se provádí pod adminem operačního systému,
který tím pádem za instalované extenze zodpovídá. V nové verzi se proto zavádí nový pojem - bezpečná
(trusted) extenze, kterou může instalovat i běžný uživatel, který má právo <code>CREATE</code> pro danou databázi.
<pre>
SELECT * 
  FROM pg_available_extension_versions() e(name, version, superuser, trusted, relocatable, schema, requires, comment) 
 WHERE trusted;
</pre>
Aktuálně se za bezpečné považují extenze: <code>btree_gin</code>, <code>btree_gist</code>, <code>citext</code>, <code>cube</code>, <code>dict_int</code>, <code>earthdistance</code>, <code>fuzzystrmatch</code>, <code>hstore</code>, <code>intarray</code>, <code>isn</code>, <code>lo</code>, <code>ltree</code>, <code>pg_trgm</code>, <code>pgcrypto</code>, <code>plpgsql</code>, <code>seg</code>, <code>tablefunc</code>, <code>tcn</code>, <code>tsm_system_rows</code>, <code>tsm_system_time</code>, <code>unaccent</code>.

===Vzorkování logování dotazů===

Vzorkování (sampling) dotazů se už dostalo do masteru předchozí verze, ale na poslední chvíli se tato
funkce revertovala. Jak se ukázalo, originální návrh šel proti stávajícímu logování (pomalých) dotazů.
Nově máme dvě konfigurační proměnné: <code>log_min_duration_sample</code> a <code>log_statement_sample_rate</code> (0.0 - 1.0).

Platilo, a bude platit, že pokud příkaz trvá déle než je hodnota <code>log_min_duration_statement</code>, tak se
vždy zaloguje. Takže např. pro <code>log_min_duration_statement='200ms'</code> jakýkoliv dotaz nad 200ms bude zalogován.
Logováním všech rychlejších dotazů, která jsou většinou častější (na soudobých serverech s OLTP
provozem je 95% kvantil dost často 20ms), bych si mohl způsobit vážné provozní problémy zahlcením IO
(ať už by šlo o výkon nebo kapacitu). Naopak, při zvážení rizik, je dobré, alespoň občas zalogovat
vše, nebo téměř vše, aby vývojáři měli přehled jaké dotazy generuje aplikace. 

Riziko zahlcení IO můžeme redukovat vzorkováním (zalogujeme zhruba každý n-tý dotaz (filtrování je
založené na generování náhodného čísla od 0 do 1 a jeho porovnání s hodnotou <code>log_statement_sample_rate</code>)).

Předpokládá se, že <code>log_min_duration_sample < log_min_duration_statement</code>. Pokud by tomu bylo naopak, tak
<code>log_min_duration_sample</code> nemá žádný efekt.
<pre>
# pro zalogování cca setiny všech dotazů do 200ms, a všech nad 200ms
log_min_duration_sample = 0
log_min_duration_sample_rate = 0.01
log_min_duration_statement = '200ms'
</pre>

===Logování vázaných parametrů===

V případě chyby dotazu, který používá vázané proměnné, může být problém, že se zaloguje chybný
dotaz, ale už se nezalogují parametry dotazu tzv. vázané parametry (bind parameters):
<pre>
2020-04-14 14:14:31.500 CEST [132468] ERROR:  value too long for type character varying(4)
2020-04-14 14:14:31.500 CEST [132468] STATEMENT:  insert into foo values($1)
</pre>
Sice si nezahltíme logy, ale těžko budeme zjišťovat, co se vlastně stalo. V nové verzi můžeme
poměrně pohodlně si nastavit logování parametrů tak, že nastavíme nenulový maximální limit
pro zalogovaný paremetr (pokud je jeho hodnota větší, tak se ořízne):
<pre>
SET log_parameter_max_length_on_error = 64

2020-04-14 14:34:19.769 CEST [132932] ERROR:  value too long for type character varying(4)
2020-04-14 14:34:19.769 CEST [132932] CONTEXT:  extended query with parameters: $1 = 'Ahoj svete'
2020-04-14 14:34:19.769 CEST [132932] STATEMENT:  insert into foo values($1)
</pre>

Pokud bych chtěl zalogovat parametry i u dotazů, které neskončily chybou, pak mohu nastavit 
<code>log_parameter_max_length</code>. Výchozí hodnota je -1, což znamená, že runtime nemá řešit velikost a parametr
má vždy zobrazit. 0 v tomto parametru vypne logování parametrů a číslo vyšší než 0 udává maximální
velikost zalogované hodnoty v bajtech (po oříznutí).
<pre>
log_min_duration_statement = 0 # zaloguj vše
log_parameter_max_length = -1 # zaloguj kompletní obsah (default)
</pre>
V logu:
<pre>
2020-04-14 14:42:45.968 CEST [133091] LOG:  duration: 0.444 ms  parse <unnamed>: insert into foo values($1)
2020-04-14 14:42:45.968 CEST [133091] LOG:  duration: 0.115 ms  bind <unnamed>: insert into foo values($1)
2020-04-14 14:42:45.968 CEST [133091] DETAIL:  parameters: $1 = 'Ahoj'
2020-04-14 14:42:45.968 CEST [133091] LOG:  duration: 0.106 ms  execute <unnamed>: insert into foo values($1)
2020-04-14 14:42:45.968 CEST [133091] DETAIL:  parameters: $1 = 'Ahoj'
</pre>

===Progres report===

Postupně, ke všem déle trvajícím úlohám, jsou k dispozici odpovídající progress pohledy. Ve verzi 13
je to pro příkaz <code>ANALYZE</code> pohled <code>pg_stat_progress_analyze</code>, a pro příkaz [https://www.postgresql.org/docs/devel/app-pgbasebackup.html <code>pg_base_backup</code> ] pohled <code>pg_stat_progress_basebackup</code>.
Bylo by hezké, kdyby někdo se začínajících programátorů prointegrovali tyto pohledy s psqlkem.

===Zneplatnění (invalidace) replikačních slotů===

Relativně častým důvodem nedostatku místa na disku je zapomenutý replikační slot. Replikační slot v Postgresu
je pojmenovaný databázový objekt, na který se odkazuje sekundární server, a který udržuje informaci o komunikaci
mezi primárním serverem (replikovaným serverem) a sekundárním serverem (replikou). Pokud replikace běží
vůči replikačnímu slotu, tak máme garantováno, že jde vždy obnovit přerušenou replikaci. Replikační slot
na primárním serveru zabraňuje rotaci nezreplikovaných transakčních logů.

Určitě se několikrát stalo, že uživatel zastavil repliku a neodstranil replikační slot. Od toho momentu
se mu na primárním serveru začaly hromadit transakční logy. Buďto si toho všiml, a replikační slot odstranil,
nebo mu došlo místo na disku. V nové verzi je možné nastavit maximální velikost transakčních logů chráněných
před rotací replikačním slotem a to konfigurační proměnnou <code>max_slot_wal_keep_size</code>. Po dosažení této velikosti
je slot zneplatněn, a rezervované transakční logy budou odstraněné. Stav slotu je vidět ve sloupci
<code>wal_status</code> v pohledu <code>pg_replication_slots</code>. Pozor, ve výchozí konfiguraci je tato pojistka neaktivní.

===<code>pg_rewind</code>===

Pomocí nástroje (aplikace) [https://www.postgresql.org/docs/devel/app-pgrewind.html <code>pg_rewind</code> ] můžeme "opravit" instanci, která se používala jako primární server,
a poté ji použít jako hot standby server. V nové verzi tato aplikace automaticky zjistí, jestli byl server
čistě vypnut. Pokud nebyl, tak jej nastartuje v single modu, aby na instanci mohla proběhnout obnova
po havárii (crash recovery).

Nově také implementuje přepínač <code>-R</code> (se stejným významem jako v <code>pg_basebackup</code>). Ve starších verzích
tato volba způsobila vygenerování souboru <code>recovery.conf</code>, který rovnou obsahoval konfiguraci, která
nám umožnila nahodit instanci jako standby server. Nově se už <code>recovery.conf</code> nepoužívá. Potřebná
konfigurace se zapíše do <code>postgresql.auto.conf</code> a vygeneruje se signální soubor.

Od verze 13 <code>pg_rewind</code> si také může stáhnout chybějící transakční logy (potřebné pro modifikaci instance)
z archívu transakčních logů (umí použít konfigurační proměnnou <code>restore_command</code>). Ve starších verzích
se transakční logy musely kopírovat ručně. 

===Ostatní===

S volbou <code>PARALLEL</code> příkaz <code>VACUUM</code> může souběžně vacuuovat indexy nad vacuovanou tabulkou. Tuto volbu
nelze použít v příkazu <code>VACUUM FULL</code>.

V tabulce <code>pg_stat_activity</code> je nový sloupec <code>leader_pid</code>. Zde je
aktuální číslo procesu nebo číslo rodičovského procesu v případě pomocných procesů nastartovaných
pro paralelní zpracování dotazu.

Snad všichni známe funkci a smysl servisního procesu <i>autovacuum</i>. Monitoruje změny v tabulce, a při
dosažení určitého poměru změněných řádků vůči celkovému počtu řádků určité tabulky spustí <code>VACUUM</code> nebo <code>ANALYZE</code>
nad touto tabulkou. Na tabulkách, do kterých se pouze vkládalo, se ve starších verzích příkaz <code>VACUUM</code>
nespouštěl. Po dosažení určitého počtu transakcí se automaticky pustil příkaz <code>VACUUM FREEZE</code>, který mohl
nad takovými tabulkami nepříjemně dlouho intenzivně zatěžovat IO, právě proto, že tyto tabulky nikdy 
nebyly vakuované běžným (líným) příkazem <code>VACUUM</code>. Od této verze se bude příkaz <code>VACUUM</code> pouštět automaticky
i nad tabulkami, kde se pouze vkládá. 

Pro zajištění konzistence databáze, implementace transakcí (ACID) si databáze udržuje určitá interní data.
Rychlý přístup k těmto interním (perzistentním) datům umožňuje LRU cache (v Postgresu se používá zkratka
SRLU "S" jako Simple). Práce s těmito daty je pro uživatele transparentní. V případě, že se tato data
zvětší nad únosnou mez, může dojít k výkonnostním problémů. Doposud se tato situace dost obtížně
detekovala. S novým stat pohledem <code>pg_stat_slru</code> by to mělo být výrazně jednodušší.

Přes nový pohled <code>pg_shmem_allocations</code> se lze pohodlně podívat na alokace ve sdílené paměti.
<pre>
postgres=> SELECT * FROM pg_shmem_allocations ORDER BY allocated_size DESC;
┌─────────────────────────────────────┬───────────┬───────────┬────────────────┐
│                name                 │    off    │   size    │ allocated_size │
╞═════════════════════════════════════╪═══════════╪═══════════╪════════════════╡
│ Buffer Blocks                       │   6442752 │ 134217728 │      134217728 │
│ &lt;anonymous&gt;                         │           │   4726400 │        4726400 │
│ XLOG Ctl                            │     53504 │   4208272 │        4208384 │
│                                     │ 147147008 │   1922816 │        1922816 │
│ Buffer Descriptors                  │   5394176 │   1048576 │        1048576 │
│ clog                                │   4262272 │    529184 │         529280 │
│ Buffer IO Locks                     │ 140660480 │    524288 │         524288 │
│ Checkpointer Data                   │ 146669056 │    393280 │         393344 │
│ Checkpoint BufferIds                │ 141184768 │    327680 │         327680 │
│ subtrans                            │   4925312 │    267040 │         267136 │
│ oldserxid                           │ 146098176 │    133600 │         133632 │
│ multixact_member                    │   5259392 │    133600 │         133632 │
│ commit_timestamp                    │   4791552 │    133600 │         133632 │
│ Backend Activity Buffer             │ 146453376 │    132096 │         132096 │
│ shmInvalBuffer                      │ 146590208 │     69464 │          69504 │
│ async                               │ 147080064 │     66848 │          66944 │
</pre>

Některé operace s daty mohou být akcelerovány přednačítáním (prefetch). Míru přednačítání určuje
proměnná <code>effective_io_concurrency</code>. Adekvátní nastavení této hodnoty je mně neznámá alchymie vyššího
zasvěcení (doporučuje se nastavit od hodnot 4, 200, 1000, ..). Prefetch má smysl i pro servisní
operace. Ve starších verzích se používala pofidérní heuristika, kterou v nové verzi nahradila
konfigurační proměnná <code>maintenance_io_concurrency</code>.

Pomocí <code>pg_dumpu</code> můžeme nově můžeme exportovat i obsah cizích tabulek (přepínač <code>--include-foreign-data=název_cizího_serveru</code>).

<code>pg_basebackup</code> nově generuje tzv backup manifest. To je soubor formátu JSON s informacemi popisujícími
záložní soubory (obsah zálohy). Na základě tohoto souboru pak nový nástroj <code>pg_verifybackup</code> může provést
kontrolu zálohy.

Logická replikace je v Postgresu třetím rokem (počínaje verzí 10), a nyní se pracuje na větší robustnosti
provozu. Jedním z problémů byla vysoká spotřeba paměti na primárním serveru v případě replikace
transakcí s velkým množstvím subtransakcí. V Postgresu k tomu stačí blok se zachytáváním výjimky v PLpgSQL.
V nové verzi by problém s pamětí alokovanou replikací (konkrétně procesem <i>walsender</i>) měl být redukován
limitem <code>logical_decoding_work_mem</code>. Po naplnění bufferů o této velikosti se pracovní data pro replikaci
začnou serializovat na disk. Výchozí hodnota je 64MB.

Upravila se reakce serveru na signál <i>promote</i> (přepnutí z režimu hot standby do režimu primárního). V předchozích
verzích mohl tento signál čekat na ukončení pauzy (stav pause). Pokud byla replikace zapauzovaná, tak
ani <i>promote</i> replikaci nerozjel, tudíž se server nemohl doreplikovat a nemohl změnit na primární server. V nové
verzi <i>promote</i> zruší pauzu, server se doreplikuje a <i>promote</i> se provede bez v drtivé většině nechtěného
odkladu.

==Podpora Unicode==

===Podpora escape sekvencí v databázích s jiným než UTF8 kódováním===

Escape zápis unicode znaků by je nyní možný i v těch případech, kdy databáze je v jiném kódování než
UTF8
<pre>
postgres=> CREATE DATABASE pokus WITH template='template0' encoding='latin2' locale='cs_CZ.latin2';
CREATE DATABASE
postgres=> \c pokus
You are now connected to database "pokus" as user "pavel".

pokus=# INSERT INTO foo VALUES(U&'\010D'); -- č
INSERT 0 1
pokus=# INSERT INTO foo VALUES(U&'\0424'); -- Ф 
ERROR:  character with byte sequence 0xd0 0xa4 in encoding "UTF8" has no equivalent in encoding "LATIN2"
LINE 1: INSERT INTO foo VALUES(U&'\0424');
                                  ^
</pre>
===Normalizace Unicode znaků===

V znakové sadě Unicode může být jeden znak kódován různě. Např. dlouhé velké A "Á" může být zakódováno
jako U00C1 nebo sekvence U0041, U0301 (pozn. naštěstí se druhá forma příliš nepoužívá). Před porovnáním
řetězců na shodu by se řetězce měly tzv normalizovat.
<pre>
postgres=> SELECT U&'\00C1' c1,U&'\0041\0301' c2, U&'\00C1' = U&'\0041\0301' eq;
┌────┬────┬────┐
│ c1 │ c2 │ eq │
╞════╪════╪════╡
│ Á  │ Á  │ f  │
└────┴────┴────┘
(1 row)
</pre>
Jak vidíte, dva stejné znaky jsou pro Postgres různé znaky. V nové verzi můžeme použít funkci <code>normalize</code>
a test <code>IS NORMALIZED</code>:
<pre>
postgres=> SELECT U&'\00C1' c1,
                  U&'\0041\0301' c2,
                  U&'\00C1' = U&'\0041\0301' eq, 
                  U&'\00C1' IS NORMALIZED,
                  U&'\0041\0301' IS NORMALIZED,
                  normalize(U&'\00C1') = normalize(U&'\0041\0301') eq2;
┌────┬────┬────┬───────────────┬───────────────┬─────┐
│ c1 │ c2 │ eq │ is_normalized │ is_normalized │ eq2 │
╞════╪════╪════╪═══════════════╪═══════════════╪═════╡
│ Á  │ Á  │ f  │ t             │ f             │ t   │
└────┴────┴────┴───────────────┴───────────────┴─────┘
(1 row)
</pre>

==Partitioning==

Oproti loňské verzi letos nejsou žádné dramatické změny v implementaci partitioningu. Určitě krokem
kupředu je podpora <code>BEFORE</code> triggerů nad partitiovanými tabulkami. Zde zůstává jedno omezení.
Záznam i po úpravě v before triggeru musí zůstat ve stejné partition.

Pro logickou replikace byly partitions samostatné tabulky a replikovaly se samostatně. Nově je možné nastavit
publikační sadě příznak <code>publish_via_partition_root</code>. Tím se replikace změn obsahu partitions přesměruje
na kořenovou partitiovanou tabulku. U takto nakonfigurované replikace může mít příjemce (konzument)
jinou strukturu partitions než má producent.

Postgres už dříve podporoval <i>partitionwise join</i> (snahou je spojovat menší partitions místo větších
partitiovaných tabulek). Ve starší verzi <i>partitionwise join</i> byl možný pouze tehdy, když počet partitions
spojovaných partitiovaných tabulek byl stejný. Nový algoritmus pouze vyžaduje, aby každá partition
partitiované tabulky byla joinována přesně s jednou partition druhé partitiované tabulky. Nově je
možné <i>partitionwise join</i> použít i pro <code>FULL JOIN</code>. 

==SQL==

===FETCH FIRST WITH TIES===

Klauzule <code>WITH TIES</code> volitelně doplňuje klauzuli <code>FETCH FIRST n</code>, která je ANSI SQL analogií proprietární
kluzule <code>LIMIT</code>, která jak jistě víte, omezí počet řádků na výstupu. <code>FETCH FIRST n WITH TIES</code> omezí počet
řádků na n plus ještě ty řádky, kde hodnota podle které se řadí je stejná jako u n tého řádku. 

Dejme tomu, že bych chtěl vidět 3 nejmenší obce z české republiky. Napíši jednoduchý dotaz
<pre>
postgres=> SELECT *
             FROM obce
            ORDER BY pocet_zen + pocet_muzu
            FETCH FIRST 3 ROW ONLY;
┌──────┬──────────┬─────────┬────────────┬───────────┬──────────┬─────────┐
│  id  │ okres_id │  nazev  │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪═════════╪════════════╪═══════════╪══════════╪═════════╡
│ 5028 │ CZ0646   │ Březina │          3 │         0 │     60.2 │     0.0 │
│ 1244 │ CZ0311   │ Vlkov   │          9 │         9 │     55.3 │    53.7 │
│ 2158 │ CZ0326   │ Čilá    │          8 │        14 │     46.6 │    65.9 │
└──────┴──────────┴─────────┴────────────┴───────────┴──────────┴─────────┘
(3 rows)
</pre>
Nicméně právě u tohoto dotazu výsledek není úplný. Existuje obec Vysoká Lhota se stejným počtem obyvatel
jako obec Čilá. V závislosti na fyzickém uložení, algoritmu řazení se nějak zvolila jedna obec a druhá
se nezobrazila. Pokud bych chtěl zabránit této situaci, tak mohu použít klauzuli <code>WITH TIES</code>:
<pre>
postgres=> SELECT *
             FROM obce 
            ORDER BY pocet_zen + pocet_muzu 
            FETCH FIRST 3 ROW WITH TIES;
┌──────┬──────────┬──────────────┬────────────┬───────────┬──────────┬─────────┐
│  id  │ okres_id │    nazev     │ pocet_muzu │ pocet_zen │ vek_muzu │ vek_zen │
╞══════╪══════════╪══════════════╪════════════╪═══════════╪══════════╪═════════╡
│ 5028 │ CZ0646   │ Březina      │          3 │         0 │     60.2 │     0.0 │
│ 1244 │ CZ0311   │ Vlkov        │          9 │         9 │     55.3 │    53.7 │
│ 2158 │ CZ0326   │ Čilá         │          8 │        14 │     46.6 │    65.9 │
│ 4225 │ CZ0633   │ Vysoká Lhota │         10 │        12 │     58.0 │    65.5 │
└──────┴──────────┴──────────────┴────────────┴───────────┴──────────┴─────────┘
(4 rows)
</pre>
Nestane se mi, že bych v důsledku oříznutí přišel o nějaký řádek. Na druhou stranu nemusím dostat přesně
n řádků (v mezních případech mohu dostat signifikantně více řádků, což by bylo řešitelné zanořením a dalším
oříznutím). Čím víc nad tím přemýšlím, tak mi to přijde jako šikovná věc, a konečně důvod, abych se naučil
(a občas použil) ANSI SQL syntax (proprietární klauzule <code>LIMIT</code> má, přeci jen, výrazně kratší zápis).

===Nové funkce a rozšíření stávajících funkcí===

Nově jsou v Postgresu k dispozici funkce <code>gcd</code> (<i>greatest common divisor</i>) největší společný dělitel a <code>lcm</code>
(<i>least common multiple</i>) nejmenší společný násobek. Tyto funkce zatím nikomu nechyběly. Do Postgresu
se implementovaly spíš z důvodu úplnosti knihovny funkcí.

Naopak implementace funkcí <code>trim_scale</code> a <code>num_scale</code> byla dlouho na seznamu Postgresového ToDo ([https://wiki.postgresql.org/wiki/Todo komunitní ToDo ] je spíš skrumáž nápadů, co by se někdy mohlo, než seriózně míněný seznam toho, co by se mělo udělat). <code>trim_scale</code>
ořezává zbytečné nuly za desetinou čárkou u čísel typu <code>numeric</code>:
<pre>
postgres=> SELECT 10.20 * 10.40;
┌──────────┐
│ ?column? │
╞══════════╡
│ 106.0800 │
└──────────┘
(1 row)

postgres=> SELECT trim_scale(10.20 * 10.40), min_scale(10.20 * 10.40);
┌────────────┬───────────┐
│ trim_scale │ min_scale │
╞════════════╪═══════════╡
│     106.08 │         2 │
└────────────┴───────────┘
(1 row)
</pre>
<code>min_scale</code> vrací počet nezbytných desetinných čísel z čísla, tak aby nedošlo ke ztrátě informace.

Funkce <code>to_char</code>, <code>to_timestamp</code> nově pracují se formátovacím vzorem <code>FF1</code>-<code>FF6</code>. Tyto nové vzory slouží k 
určení desetiné části sekundy (desetina sec, setina sec, tisícina sec, ...)
<pre>
postgres=> SELECT to_char(current_timestamp, 'hh:mm:ss.ff1');
┌────────────┐
│  to_char   │
╞════════════╡
│ 07:04:44.7 │
└────────────┘
(1 row)
</pre>
Nově se také zavádí <code>SSSSS</code>, který vyjadřuje počet vteřin po půlnoci. 
<pre>
postgres=> SELECT to_char(current_timestamp, 'YYYY-MM-DD SSSSS');
┌──────────────────┐
│     to_char      │
╞══════════════════╡
│ 2020-04-14 25378 │
└──────────────────┘
(1 row)
</pre>
Funkce <code>to_char</code> roky podporuje vzor <code>TM</code> (<i>translated mode</i>), který zobrazuje hodnoty v národním prostředí.
Počínaje verzí 13 můžeme tento vzor použít i ve funkcích <code>to_date</code> a <code>to_timestamp</code>:
<pre>
postgres=> SELECT to_char(current_date, 'DD.MM.YYYY TMDAY DD TMMONTH');
┌───────────────────────────┐
│          to_char          │
╞═══════════════════════════╡
│ 14.04.2020 ÚTERÝ 14 DUBNA │
└───────────────────────────┘
(1 row)

postgres=> SELECT to_date('15 dub 2020', 'DD TMMON YYYY'), to_date('15 dubna 2020', 'DD TMMONTH YYYY');
┌────────────┬────────────┐
│  to_date   │  to_date   │
╞════════════╪════════════╡
│ 2020-04-15 │ 2020-04-15 │
└────────────┴────────────┘
(1 row)
</pre>
Je to dost citlivé na zápis, takže pro češtinu to bude použitelné pouze pro parsování strojově generovaných
dat, kde je dodržená konvence. 

Vloni se vedla [https://www.postgresql.org/message-id/flat/37E2F9B3-B65B-4AF2-B2E9-436ADE37D670%40gida.in#bb5619b727a56da3635784d953dc1f0d diskuze ] ohledně chování funkce <code>jsonb_set</code>.
Pro některé uživatele není jednoduché rozlišit mezi SQL NULLem (a odpovídajícím chováním) a JSON nullem a jeho jiným chováním.

U <code>STRICT</code> funkcí jako je např. <code>jsonb_set</code> se neřeší typ <code>NULL</code> hodnoty - <code>NULL</code> je <code>NULL</code>. 
<pre>
-- typový NULL, nicméně stále SQL NULL
postgres=> SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}', null::jsonb);
┌───────────┐
│ jsonb_set │
╞═══════════╡
│           │
└───────────┘
(1 row)

-- použití JSONB nullu
postgres=> SELECT jsonb_set('{"f1":1,"f2":2}', '{f2}', 'null'::jsonb);
┌───────────────────────┐
│       jsonb_set       │
╞═══════════════════════╡
│ {"f1": 1, "f2": null} │
└───────────────────────┘
(1 row)
</pre>
Změnit nekompatibilním způsobem chování funkce je i pro vývojáře Postgresu i pro uživatele nepříjemná
záležitost, a tudíž jediným řešením je zavedení nové funkce. Tou je funkce <code>jsonb_set_lax</code>, která není
<code>STRICT</code> (a tudíž si ošetřuje <code>NULL</code> vstup sama), a kde uživatel může (skrze volitelné parametry funkce)
určit chování funkce v případě, že vstupní argument je <code>NULL</code> (výchozí je <code>"use_json_null"</code>, alternativy
<code>"raise_exception"</code>, <code>"delete_key"</code> a <code>"return_target"</code>).
<pre>
postgres=> select jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null);
┌─────────────────────────────────────────┐
│              jsonb_set_lax              │
╞═════════════════════════════════════════╡
│ [{"f1": 99, "f2": null, "f3": null}, 2] │
└─────────────────────────────────────────┘
(1 row)
</pre>
Změnil se algoritmus pro výpočet odmocniny logaritmu (nově se používá <i>Karatsuba</i>), který je přesnější
(správně se zaokrouhluje poslední číslice) a rychlejší (3-5x u malých čísel a cca 10x u velkých čísel).
Tuto změnu možná ocení uživatele PostGISu.

===Nová třída polymorfních datových typů - anycompatible===

Polymorfní typy v PostgreSQL jsou analogií generickým typům v programovacích jazycích, které podporu
generik mají. V předchozích verzích Postgresu byly tři základní třídy: <code>"any"</code> (musí se zapisovat v
uvozovkách, protože <code>any</code> je klíčové slovo), <code>anyenum</code> a <code>anyelement</code>. <code>"any"</code> je možné použít pouze pro vstupní
argumenty a pouze pro funkce implementované v jazyku C. Na typ <code>anyelement</code> navazují typy <code>anyarray</code>,
<code>anynonarray</code> a <code>anyrange</code>. Funkce, které používají typ <code>anyelement</code> jsou typově velice striktní, což je
někdy chtěná vlastnost, ale také často vlastnost nechtěná, omezující (např. většina vestavěných funkcí
se tak nechová):
<pre>
CREATE OR REPLACE FUNCTION public.make_array(anyelement, anyelement)
RETURNS anyarray
AS $$
  SELECT ARRAY[$1, $2];
$$
LANGUAGE sql IMMUTABLE;

postgres=> SELECT make_array(1, 1);
┌────────────┐
│ make_array │
╞════════════╡
│ {1,1}      │
└────────────┘
(1 row)

postgres=> SELECT make_array(.1, .1);
┌────────────┐
│ make_array │
╞════════════╡
│ {0.1,0.1}  │
└────────────┘
(1 row)

postgres=> SELECT make_array(.1, 1);
ERROR:  function make_array(numeric, integer) does not exist
LINE 1: SELECT make_array(.1,1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
</pre>
Na příkladu je vidět, že poslední volání funkce selhalo, protože typ <code>numeric</code> není stejným typem jako
typ <code>integer</code>. Pokud by funkce použila novou třídu <code>anycompatible</code> (pozor, je úplně nezávislá na <code>anyelement</code>),
tak se nejprve otestuje, zda-li jsou skutečné typy anycompatible argumentů ze stejné kategorie (text,
číslo, datum, ..) a pokud ano, tak se hodnoty těchto parametrů převedou na společný typ:
<pre>
postgres=> SELECT make_array(.1, 1);
┌────────────┐
│ make_array │
╞════════════╡
│ {0.1,1}    │
└────────────┘
(1 row)

postgres=> \gdesc
┌────────────┬───────────┐
│   Column   │   Type    │
╞════════════╪═══════════╡
│ make_array │ numeric[] │
└────────────┴───────────┘
(1 row)

postgres=> SELECT make_array(.1::double precision, 1);
┌────────────┐
│ make_array │
╞════════════╡
│ {0.1,1}    │
└────────────┘
(1 row)

postgres=> \gdesc
┌────────────┬────────────────────┐
│   Column   │        Type        │
╞════════════╪════════════════════╡
│ make_array │ double precision[] │
└────────────┴────────────────────┘
(1 row)
</pre>
Pomocí této třídy polymorfních typů se jednoduše implementují funkce jako <code>nvl</code> (z Oracle):
<pre>
CREATE OR REPLACE FUNCTION nvl(anycompatible, anycompatible)
RETURNS anycompatible AS $$
  SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;
</pre>
Nebude to sice absolutně kompatibilní s Oraclem, ale pro téměř všechny rozumné kombinace
to kompatibilní bude. Pozn. Oracle u této a podobných funkcích [https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html#GUID-3AB61E54-9201-4D6A-B48A-79F4C4A034B2 převede ] všechny parametry
na text, pokud je první parametr text. Postgres naopak implicitní přetypování na text nikdy
nedělá. [https://github.com/orafce/orafce <code>Orafce</code> ] si pomáhá <i>přetížením funkcí</i> (overloadingem):
<pre>
CREATE OR REPLACE FUNCTION nvl(text, anyelement)
RETURNS text AS $$
  -- vynucené přetypování
  SELECT coalesce($1, $2::text)
$$ LANGUAGE sql IMMUTABLE;

-- první parametr určuje datový typ
CREATE OR REPLACE FUNCTION nvl(numeric, text)
RETURNS numeric AS $$
  -- vynucené přetypování
  SELECT coalesce($1, $2::numeric)
$$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION nvl(date, text)
RETURNS date AS $$
  -- vynucené přetypování
  SELECT coalesce($1, $2::date)
$$ LANGUAGE sql IMMUTABLE;
</pre>

To ale není úplně korektní (používá se nezdokumentovaná (negarantovaná) vlastnost Postgresu). Pro
minimalizaci režie migrace z Oracle do Postgresu je to bohužel nutné (a nečekám, že by Postgres
o tuto nezdokumentovanou vlastnost přišel). Typový systém Oracle je pro nejčastěji používané datové
typy (varchar2, number a date) výrazně jednodušší a dynamičtější než u Postgresu, a nelze jej bez výjimky
emulovat v Postgresu (kde se používá výrazně statičtější striktnější složitější typový systém).

Obecně se nedoporučuje přetěžovat polymorfní parametry funkcí. Pokud se přetížení funkcí
neudělá správně, tak pak při volání funkce runtime nemůže (nemusí) jednoznačně určit instanci funkce,
což vždy skončí runtime chybou (záměrně vytvořené nevhodné přetížení funkce nvl): 
<pre>
ERROR:  function nvl(integer, numeric) is not unique
LINE 1: select nvl(1, 1.1);
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
</pre>

Je bezpečnější se nespoléhat na implicitní přetypování, a v Postgresu si nekorektní (alespoň z pohledu
Postgresu) situace ošetřit explicitním přetypováním (tudíž mít pouze polymorfní funkci), tj výraz
<code>nvl('AHOJ', 1)</code> přepsat na <code>nvl('AHOJ', 1::text)</code>.

==Explain==

Poměrně značného rozšíření se dočkal příkaz <code>EXPLAIN</code> (a odpovídajícím způsobem i extenze <i>auto_explain</i>).
S volbou <code>SETTINGS</code> se do explainu propíše veškeré nedefaultní nastavení, které ovlivňuje optimalizátor
Postgresu.
<pre>
postgres=> EXPLAIN (ANALYZE, SETTINGS)
            SELECT *
              FROM obce 
             ORDER BY pocet_muzu + pocet_zen DESC
             LIMIT 10;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    QUERY PLAN                                                     │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Limit  (cost=272.19..272.21 rows=10 width=45) (actual time=11.244..11.246 rows=10 loops=1)                        │
│   ->  Sort  (cost=272.19..287.81 rows=6250 width=45) (actual time=11.241..11.242 rows=10 loops=1)                 │
│         Sort Key: ((pocet_muzu + pocet_zen)) DESC                                                                 │
│         Sort Method: top-N heapsort  Memory: 27kB                                                                 │
│         ->  Seq Scan on obce  (cost=0.00..137.12 rows=6250 width=45) (actual time=0.037..4.854 rows=6250 loops=1) │
│ Settings: work_mem = '64kB'                                                                                       │
│ Planning Time: 0.268 ms                                                                                           │
│ Execution Time: 11.323 ms                                                                                         │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)
</pre>
Naopak s volbou <code>WAL</code> se zobrazí informace o zápisu do transakčního logu:
<pre>
postgres=> EXPLAIN (ANALYZE,WAL,BUFFERS)
            INSERT INTO test SELECT generate_series(1,1000);
┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY PLAN                                             │
╞════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Insert on test  (cost=0.00..15.02 rows=1000 width=4) (actual time=1.675..1.675 rows=0 loops=1)     │
│   Buffers: shared hit=1010 dirtied=5 written=5                                                     │
│   WAL:  records=1000  bytes=59000                                                                  │
│   ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.004..0.148 rows=1000 loops=1) │
│         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)     │
│ Planning Time: 0.053 ms                                                                            │
│ Execution Time: 1.703 ms                                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
</pre>
Zajímavou novou funkcí je zobrazení počtu přístupu k bufferům v době plánování dotazu (planning time). Několikrát jsem
se setkal s tím, že v důsledku nafouklých (bloated) indexů se silně zpomalilo plánování dotazů. Postgres
už v době plánování dotazů pracuje s potenciálními indexy a například dohledává minimum, maximum (a zejména
pokud se jedná o dotazy na začátku session, tak si plní lokální (session) cache systémového katalogu).
<pre>
postgres=> EXPLAIN (BUFFERS, ANALYZE) 
            SELECT *
              FROM obce
             WHERE okres_id = 'CZ0201';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                          QUERY PLAN                                                          │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Index Scan using obce_okres_id_idx on obce  (cost=0.28..14.49 rows=114 width=41) (actual time=0.026..0.057 rows=114 loops=1) │
│   Index Cond: ((okres_id)::text = 'CZ0201'::text)                                                                            │
│   Buffers: shared hit=4                                                                                                      │
│ Planning Time: 1.188 ms                                                                                                      │
│   Buffers: shared hit=124                                                                                                    │
│ Execution Time: 0.106 ms                                                                                                     │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
</pre>
Tak jak rozšířily možnosti příkazu <code>EXPLAIN</code>, tak obdobným způsobem se rozšířily možnosti extenzí 
<i>pg_stat_statements</i> a <i>auto_explain</i>. V pohledu <code>pg_stat_statements</code> jsou nyní nově metriky kvantifikující
zápis do transakčního logu způsobený monitorovaným příkazem. Pokud se zapne <code>pg_stat_statements.track_planning</code>,
pak je možné v <code>pg_stat_statements</code> vidět režii planeru.

==Pro vývojáře postgresu==

Voláním funkce <code>MemoryContextMemAllocated</code> lze zjistit velikost alokované paměti v daném kontextu včetně
jeho potomků. <code>StringInfo</code> API - sada funkcí pro práci s dynamicky alokovanými řetězci lze používat i 
v klientských aplikacích (frontend - <code>pg_waldump</code>, ..). Nová konfigurační proměnná <code>backtrace_functions</code>
umožňuje nastavit seznam funkcí, u kterých se v případě chyby vypíše backtrace do logu Postgresu.
<pre>
set backtrace_functions = 'int4div';
postgres=> select 10/0;
ERROR:  division by zero

2020-04-14 09:26:07.925 CEST [121307] ERROR:  division by zero
2020-04-14 09:26:07.925 CEST [121307] BACKTRACE:
    postgres: pavel postgres [local] SELECT(int4div+0x60) [0x84ec3b]
    postgres: pavel postgres [local] SELECT() [0x65ffef]
    postgres: pavel postgres [local] SELECT(ExecInterpExprStillValid+0x2e) [0x65d069]
    postgres: pavel postgres [local] SELECT(evaluate_expr+0x80) [0x7324dc]
    postgres: pavel postgres [local] SELECT() [0x732697]
    postgres: pavel postgres [local] SELECT() [0x7345e8]
    postgres: pavel postgres [local] SELECT() [0x7329cf]
    postgres: pavel postgres [local] SELECT(expression_tree_mutator+0xa63) [0x6d062c]
    postgres: pavel postgres [local] SELECT() [0x733abb]
    postgres: pavel postgres [local] SELECT(expression_tree_mutator+0xb8c) [0x6d0755]
    postgres: pavel postgres [local] SELECT() [0x733abb]
    postgres: pavel postgres [local] SELECT(eval_const_expressions+0x40) [0x733c0c]
    postgres: pavel postgres [local] SELECT() [0x71632d]
    postgres: pavel postgres [local] SELECT(subquery_planner+0x373) [0x71ef8e]
    postgres: pavel postgres [local] SELECT(standard_planner+0x181) [0x71fc06]
    postgres: pavel postgres [local] SELECT(planner+0x19) [0x7201cb]
    postgres: pavel postgres [local] SELECT(pg_plan_query+0x44) [0x7ec79a]
    postgres: pavel postgres [local] SELECT(pg_plan_queries+0x40) [0x7ec849]
    postgres: pavel postgres [local] SELECT() [0x7ecd1e]
    postgres: pavel postgres [local] SELECT(PostgresMain+0x74e) [0x7eec5e]
    postgres: pavel postgres [local] SELECT() [0x7644aa]
    postgres: pavel postgres [local] SELECT() [0x7672bd]
    postgres: pavel postgres [local] SELECT() [0x7674c1]
    postgres: pavel postgres [local] SELECT(PostmasterMain+0xfa8) [0x76889a]
    postgres: pavel postgres [local] SELECT(main+0x1e9) [0x6b8a52]
    /lib64/libc.so.6(__libc_start_main+0xf2) [0x7f0d9a937042]
    postgres: pavel postgres [local] SELECT(_start+0x2e) [0x47f2de]
2020-04-14 09:26:07.925 CEST [121307] STATEMENT:  select 10/0;
</pre>
Backtrace se zaloguje i v případě neplatného assertu. <code>gdb</code> backtrace to určitě nenahradí,
určitě ale nějaká informace v tom je.


==Co se nestihlo==

Myslím si, že ve čtrnáctce bychom se mohli dočkat podpory JSONu podle ANSI/SQL včetně funkce <code>json_table</code>.
Doufám, že snad se už do upstreamu dostane můj patch na schema variables (analogie globálních proměnných
v Oracle). Dost nadějně vypadá patch implementující globální dočasné tabulky. Pořád se pracuje na 
index skip scanu. Tomáš pracuje na funkcionálních statikách. Dost daleko je patch implementující
online materializované pohledy. V další verzi by se měl objevit nový datový typ <code>multirange</code>. 

==Závěr==
Na bývalém OHLOHu (dnes Synopsys) mne zaujalo [https://www.openhub.net/p/postgres hodnocení Postgresu ].
Pracnost kódu je odhadnuta na 601 člověko-let. Dnes má Postgres dva milióny řádek kódu. Při hrubém odhadu
(a pokud bych vzal za základ platy v ČR) může být cena vývoje Postgresu přes miliardu korun. 

Ve třináctce je pro uživatele viditelných nových funkcí minimum. Drtivá většina změn je pod povrchem.
Čtrnáctka by mohla být, co se týče uživatelsky viditelných funkcí, opačná. Tudíž bude se na co těšit.

----------
<DPL>category=Verze PostgreSQL</DPL>
[[Category:Články]]
[[Category:Verze PostgreSQL]]