Zobrazení zdroje stránky PostgreSQL 15 (2022)

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

=PostgreSQL 15=

V článku věnovaném PostgreSQL 14 jsem napsal, že novinky v této verzi
nejsou pro uživatele Postgresu extra viditelné. Většina vylepšení byla
ukryta "pod kapotou". U patnáctky to rozhodně neplatí. Téměř úplná podpora [https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016 ANSI SQL/JSON]
je bomba, a úplností tohoto implementace standardu je Postgres v čele
SQL databází (také se na tomto patchi pracovalo od roku 2017).
Implementace příkazu <code>MERGE</code>, opět naprosto jasně uživatelsky viditelná
funkce. Podpora [https://icu.unicode.org/ ICU] collations pro celé databáze, atd atd.  Další rok
vývoje je dobře vidět.

==SQL==

===MERGE===  

SQL rozeznává základní DML přkazy: <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>.
Kombinací těchto příkazů je příkaz, který se často označuje jako
<code>UPSERT</code>. V Postgresu už pěknou řádku let máme možnost použít klauzuli
<code>ON CONFLICT DO</code> příkazu <code>INSERT</code>, což je efektivně právě příkaz <i>UPSERT</i>. V
ANSI/SQL je UPSERTem příkaz <code>MERGE</code>. <code>INSERT ON CONFLICT DO</code> má o něco
jednodušší syntax, jedná se ale o proprietární rozšíření SQL v
PostgreSQL.  Syntaxe příkazu <code>MERGE</code> je definovaná standardem (a není
extra složitá). Navíc podporuje i mazání řádků.

<pre>
postgres=# SELECT * FROM foo;
┌────┬────┐
│ id │ v  │
╞════╪════╡
│  1 │ 10 │
└────┴────┘
(1 row)

postgres=# SELECT * FROM boo;
┌────┬────┐
│ id │ v  │
╞════╪════╡
│  1 │ 20 │
│  2 │ 30 │
└────┴────┘
(2 rows)

postgres=# MERGE INTO foo
              USING boo ON foo.id = boo.id
               WHEN MATCHED THEN UPDATE SET v = boo.v
               WHEN NOT MATCHED THEN INSERT VALUES(id, v);
MERGE 2

postgres=# SELECT * FROM foo;
┌────┬────┐
│ id │ v  │
╞════╪════╡
│  1 │ 20 │
│  2 │ 30 │
└────┴────┘
(2 rows)
</pre>

V Postgresu můžeme také použít příkaz <code>INSERT ON CONFLICT</code>:
<pre>
-- zapis pres korelovany poddotaz
INSERT INTO foo
   SELECT id, v FROM boo b
  ON CONFLICT(id)
   DO UPDATE SET v = (SELECT v FROM boo WHERE foo.id = boo.id);
	
-- zapis pres exluded
INSERT INTO foo
   SELECT boo.id, boo.v FROM boo
         LEFT JOIN foo ON boo.id = foo.id
  ON CONFLICT(id)
   DO UPDATE SET v = EXCLUDED.v;
</pre>

Ačkoliv <code>MERGE</code> a <code>INSERT ON CONFLICT DO</code> dělají
skoro totéž, nejsou implementovány stejně, a na různých datech mohou být
jinak rychlé. Na jednoduchých příkladech, které jsem si dělal, jsem
si nevšiml signifikantních rozdílů v rychlosti. Spíš  tam vnímám drobnou
nuanci v sémantice. <code>MERGE</code> je primárně hromadný příkaz.
Syntaxe <code>INSERT ON CONFLICT DO</code> se hodí pro práci s jedním řádkem.
Hlavní benefit je nejspíš pro datové analytiky, kteří znají příkaz
<code>MERGE</code> z jiných databází, a nemusí se přepínat na proprietární
příkaz v Postgresu.
 
===Omezení (constraint) <code>UNIQUE NULLS DISTINCT</code>===

Omezení <code>UNIQUE</code> zaručuje unikátní hodnoty ve sloupci. Za 
unikátní hodnotu se ale nepovažuje <code>NULL</code>. Což je to, co 
většinou chceme. V některých případech můžeme požadovat, aby hodnota 
<code>NULL</code> byla ve sloupci pouze jednou. Tento požadavek můžeme 
řešit funkčním indexem a zrovna tak i podmíněným indexem. I když tento 
index bude malý (bude vždy obsahovat max. jednu hodnotu), bude to další 
index navíc. V Postgresu 15 můžeme u omezení <code>UNIQUE</code> použít 
frázi <code>NULLS DISTINCT</code>, čímž dosáhneme požadovaného chování 
omezení (<code>NULL</code> bude považováno za hodnotu, která má být 
unikátní).

===JSON===

V Postgresu 15 můžeme používat funkce pro práci s typem <code>JSON</code>
definované v  ANSI/SQL 2016 v části SQL/JSON. Prototyp implementace byl k
dispozici relativně brzo. Tuším už v roce 2018. Byl to ale mega patch,
který bylo potřeba rozdělit na menší části, zrevidovat a dočistit.
Většina kódu je poměrně jednoduchá vyjma implementace <i>JSONPath</i> a
implementace funkce <code>JSON_TABLE</code>. Podpora JSONPath se dostala
do Postgresu 12. Zbytek, včetně <code>JSON_TABLE</code>, se dostal do nové
verze. V MySQL je už asi dva, možná tři roky implementace
<code>JSON_TABLE</code>, která obsahuje základ, který je dostačující více
než na 99%. Patch s implementací do Postgresu obsahoval úplnou
implementaci standardu včetně tzv plánů (což dost zkomplikovalo review
patche). V Postgresu jsme na podporu SQL/JSON museli čekat relativně
dlouho. Nyní má ale Postgres jednu z nejúplnějších implementací SQL/JSON.

Nové funkce lze rozdělit do několika kategorií: dotazovací funkce
(<code>JSON_EXISTS</code>, <code>JSON_QUERY</code>,
<code>JSON_VALUE</code>),  funkce pro vytvoření a serializaci JSONu
(<code>JSON</code>, <code>JSON_SCALAR</code>, <code>JSON_SERIALIZE</code>),
funkci pro transformaci JSONu na relaci (<code>JSON_TABLE</code>) a
operátor <code>IS JSON</code>.

Začnu samopopisnou ukázkou operátoru <code>IS JSON ...</code>
<pre>
postgres=# SELECT '[1,2,3]' IS JSON ARRAY;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# SELECT '{"a":1, "b":"ahoj"}' IS JSON OBJECT;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# SELECT '"ahoj"' IS JSON SCALAR;
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)
</pre>

Funkce <code>json</code> provádí přetypování na typ <code>JSON</code> z
typu <code>text</code>, <code>bytea</code>, ... <code>json_scalar</code> generuje json ze ze
základních SQL skalárních typů. <code>json_serialize</code> umožňuje
serializovat hodnotu do <code>bytea</code> a umožňuje změnit kódování nebo formát . V
tuto chvíli je podporován pouze kódování UTF8 a formát JSON. V dalších
verzích mohou být podporována jiná kódování nebo formáty (např. BSON):

<pre>
postgres=# SELECT json('{"a":1}');
┌─────────┐
│  json   │
╞═════════╡
│ {"a":1} │
└─────────┘
(1 row)

postgres=# SELECT JSON_SCALAR('ahoj'), JSON_SCALAR(current_date), JSON_SCALAR(1);
┌─────────────┬──────────────┬─────────────┐
│ json_scalar │ json_scalar  │ json_scalar │
╞═════════════╪══════════════╪═════════════╡
│ "ahoj"      │ "2022-04-27" │ 1           │
└─────────────┴──────────────┴─────────────┘
(1 row)

postgres=# SELECT JSON_SERIALIZE(json('{"a":1}') RETURNING bytea);
┌──────────────────┐
│  json_serialize  │
╞══════════════════╡
│ \x7b2261223a317d │
└──────────────────┘
(1 row)
</pre>

Parametrem dotazovacích funkcí je JSON hodnota a JSONPath výraz.
<code>JSON_EXISTS</code> vrací <code>true</code>, pokud výraz vrací alespoň jednu
hodnotu. <code>json_value</code> vrací přesně jednu hodnotu,
<code>JSON_QUERY</code> vrací objekt nebo pole. Dalšími klauzulemi lze
reagovat na prázdný výsledek nebo chybu:

<pre>
postgres=# SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
┌─────────────┐
│ json_exists │
╞═════════════╡
│ t           │
└─────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '"ahoj"', '$' RETURNING text);
┌────────────┐
│ json_value │
╞════════════╡
│ ahoj       │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
┌────────────┐
│  ?column?  │
╞════════════╡
│ 2017-03-01 │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.a' RETURNING int);
┌────────────┐
│ json_value │
╞════════════╡
│          1 │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING int);
┌────────────┐
│ json_value │
╞════════════╡
│          ∅ │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING int DEFAULT 10 ON EMPTY);
┌────────────┐
│ json_value │
╞════════════╡
│         10 │
└────────────┘
(1 row)

postgres=# SELECT JSON_VALUE(jsonb '{"a": 1}', '$.b' RETURNING int ERROR ON EMPTY ERROR ON ERROR);
ERROR:  no SQL/JSON item
</pre>

Syntaxe těchto funkcí je docela bohatá. Je to vidět u funkce
<code>JSON_QUERY</code>  a tuplem pak u <code>JSON_TABLE</code>:

<pre>
postgres=# SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
┌────────────┐
│ json_query │
╞════════════╡
│ "aaa"      │
└────────────┘
(1 row)

postgres=# SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
┌────────────┐
│ json_query │
╞════════════╡
│ aaa        │
└────────────┘
(1 row)

postgres=# SELECT JSON_QUERY(jsonb '[{"a":10, "b": 20}, {"a": 30, "b":100}]', '$[*].a' );
┌────────────┐
│ json_query │
╞════════════╡
│ ∅          │
└────────────┘
(1 row)

postgres=# SELECT JSON_QUERY(jsonb '[{"a":10, "b": 20}, {"a": 30, "b":100}]', '$[*].a' ERROR ON ERROR );
ERROR:  JSON path expression in JSON_QUERY should return singleton item without wrapper
HINT:  use WITH WRAPPER clause to wrap SQL/JSON item sequence into array

postgres=# SELECT JSON_QUERY(jsonb '[{"a":10, "b": 20}, {"a": 30, "b":100}]', '$[*].a' WITH WRAPPER );
┌────────────┐
│ json_query │
╞════════════╡
│ [10, 30]   │
└────────────┘
(1 row)
</pre>

Pokud JSONPath výraz vrací více než jednu hodnotu, tak výsledkem je
chyba, která je, by default, ignorována (v laxním režimu). Pro
vytvoření pole z vrácených hodnot musíme použít klauzule <code>WITH
WRAPPER</code> (zkrácená forma pro <code>WITH ARRAY WRAPPER</code>). V tuto
chvíli návratovou hodnotou může být pouze JSON pole, nikoliv pole v
PostgreSQL.

Před třemi roky jsem dělal review, teď jsem si s těmito funkcemi chvíli
hrál. Základ funguje dobře, ale zatím nejsou využité možnosti, které v
Postgresu jsou.Tady to určitě bude chtít ještě pár let
práce. Navíc, díky tomu, že standard SQL/JSON je relativně nový, a
relativně komplexní, tak je hrozně málo lidí, kteří by měli potřebné
zkušenosti, znalosti a i třeba názor, jak by implementace v Postgresu
měla vypadat.

Funkce <code>JSON_TABLE</code> je obdobou funkce <code>XMLTABLE</code> na
steroidech. Oproti <code>XMLTABLE</code> podporuje nesting, a navíc
umožňuje určit, jak se zanořená data mají reprezentovat (V JSONu máme
denormalizovaná data, a ve zmíněné funkci máme několik způsobů, jak je
normalizovat). To, co jsme museli v <code>XMLTABLE</code> řešit na SQL
úrovni (<code>JOIN</code>, vícenásobné volání <code>XMLTABLE</code>), to můžeme v
<code>JSON_TABLE</code> udělat přímo. Výhodou je rychlost. Nevýhodou
naopak možnost narazit na limity paměti, a mnohem komplikovanější syntax
<code>JSON_TABLE</code> (i když i tuto funkci lze také používat jednoduše):

<pre>
postgres=# SELECT * FROM foo;
┌────────────────────────────────────────────────────────────────────────────────┐
│                                       a                                        │
╞════════════════════════════════════════════════════════════════════════════════╡
│ {"a": "ahoj", "b": [10, 20, 30], "c": {"name": "pavel", "surname": "stehule"}} │
└────────────────────────────────────────────────────────────────────────────────┘
(1 row)

postgres=# SELECT JSON_TABLE.*
             FROM foo,
                  JSON_TABLE(a, '$' COLUMNS (a text, b int[]));
┌──────┬────────────┐
│  a   │     b      │
╞══════╪════════════╡
│ ahoj │ {10,20,30} │
└──────┴────────────┘
(1 row)

postgres=# SELECT json_table.*
            FROM foo,
                 JSON_TABLE(a, '$' COLUMNS (a text,
                               NESTED PATH '$.b[*]' COLUMNS (b int path '$'),
                               NESTED PATH '$.c' COLUMNS (name text, surname text)));
┌──────┬────┬───────┬─────────┐
│  a   │ b  │ name  │ surname │
╞══════╪════╪═══════╪═════════╡
│ ahoj │ 10 │ ∅     │ ∅       │
│ ahoj │ 20 │ ∅     │ ∅       │
│ ahoj │ 30 │ ∅     │ ∅       │
│ ahoj │  ∅ │ pavel │ stehule │
└──────┴────┴───────┴─────────┘
(4 rows)
</pre>

JSON se zpracovává postupně - napřed cesta učená primárním JSONPath výrazem, poté se
zpracovávají sourozenecké zanořené cesty. Sloupce, které generuje jiná zanořená cesta
se doplňují hodnotou <code>NULL</code>. V poslední ukázce jsou dvě
zanořené sloupce. V prvním případě je nastavený sloupec <code>b</code> a
sloupce <code>name</code> a <code>surname</code> jsou <code>NULL</code>. V
druhém případě (pro druhou zanořenou cestu) je to přesně opačně.

Popsaný způsob vytváření výsledné relace z primární cesty a ze zanořených
cest nemusí vždy vyhovovat. Pomocí tzv plánů lze hodně volně specifikovat
jiný způsob:

<pre>
postgres=# SELECT json_table.*
             FROM foo,
                  JSON_TABLE(a, '$' AS p0 COLUMNS (a text,
                                NESTED PATH '$.b[*]' AS p1 COLUMNS (b int path '$'),
                                NESTED PATH '$.c' as p2 COLUMNS (name text, surname text))
                             PLAN (p0 INNER (p1 CROSS p2)));
┌──────┬────┬───────┬─────────┐
│  a   │ b  │ name  │ surname │
╞══════╪════╪═══════╪═════════╡
│ ahoj │ 10 │ pavel │ stehule │
│ ahoj │ 20 │ pavel │ stehule │
│ ahoj │ 30 │ pavel │ stehule │
└──────┴────┴───────┴─────────┘
(3 rows)
</pre>

Lze vybrat pouze určité kombinace z plánů <code>INNER</code>, <code>OUTER</code>,
<code>UNION</code> a <code>CROSS</code>. <code>INNER</code> a
<code>OUTER</code> plány (analogie k <code>INNER JOIN</code> a <code>LEFT
OUTER JOIN</code>) se používají pro vazbu primární a nested cesty.
<code>UNION</code> a <code>CROSS</code> se používají pro vazbu mezi nested
cestami (analogie k <code>FULL OUTER JOIN</code> a <code>CROSS
JOIN</code>). Výchozí plány jsou <code>OUTER</code> a <code>UNION</code>, a
lze je změnit v klauzuli <code>PLAN DEFAULT</code>:

<pre>
postgres=# SELECT json_table.*
             FROM foo,
                  JSON_TABLE(a, '$' AS p0 COLUMNS (a text,
                                NESTED PATH '$.b[*]' AS p1 COLUMNS (b int path '$'),
                                NESTED PATH '$.c' AS p2 COLUMNS (name text, surname text))
                             PLAN DEFAULT(cross));
┌──────┬────┬───────┬─────────┐
│  a   │ b  │ name  │ surname │
╞══════╪════╪═══════╪═════════╡
│ ahoj │ 10 │ pavel │ stehule │
│ ahoj │ 20 │ pavel │ stehule │
│ ahoj │ 30 │ pavel │ stehule │
└──────┴────┴───────┴─────────┘
(3 rows)
</pre>


===regexp funkce===

V Postgresu budou 4 nové funkce pro práci s regulárními výrazy:
<code>regexp_count</code>, <code>regexp_instr</code>,
<code>regexp_like</code> a <code>regexp_substr</code>. Stávající funkce
<code>regexp_replace</code> je rozšířena o další volitelné parametry. API
je téměř kompatibilní s Oraclem, odkud je převzato. Což znamená, že je to
trochu Oraclovština (není úplně konzistentní). Na druhou stranu, když už
něco vypadá jako funkce převzatá z Oracle, tak mi přijde lepší, když se
bude chovat jako funkce převzatá z Oracle. Rozdíl vůči Oracle je v
přístupu k <code>NULL</code> (Oracle nerozlišuje mezi <code>NULL</code>em a
prázdným řetězcem). Pokud byste chtěli použít tyto funkce ve starších
verzích Postgresu, najdete je v [https://github.com/orafce/orafce <code>Orafce</code>], navíc upravené tak,
aby poskytovaly maximální možnou kompatibilitu s Oraclem (včetně přístupu
k <code>NULL</code>):

<pre>
postgres=# SELECT regexp_instr('abcabcabc', 'a.c', 2);
┌──────────────┐
│ regexp_instr │
╞══════════════╡
│            4 │
└──────────────┘
(1 row)

postgres=# SELECT regexp_like('Steven', '^Ste(v|ph)en$');
┌─────────────┐
│ regexp_like │
╞═════════════╡
│ t           │
└─────────────┘
(1 row)
</pre>

Jinak nové funkce pro práci s regexpama mi přijdou docela užitečné, a
buďto zjednodušují některé operace s řetězci nebo umožňují operace, které
se starším API nebylo možné provést. Po pravdě řečeno, dřív když chtěl
člověk něco extra s regexpy v Postgresu, tak si napsal funkci v PL/Perl,
a nic neřešil. Na druhou stranu se dnes PostgreSQL provozuje v cloudu nebo v
prostředích, kde není dostupná veškerá funkcionalita, a kde si člověk
nemůže doinstalovat "svoje" extenze, a tudíž implementace v jádře dává
smysl.

==psql==

Ve verzi 15 můžeme používat dva nové backslash příkazů. Posledním
implementovaným je <code>\dconfig</code>, který bez dalších parametrů
zobrazí konfigurační hodnoty změněné na daném serveru. Parametr u tohoto
příkazu funguje podobně jako u ostatních <code>\d*</code> příkazů. V
pluskové variantě zobrazí i přístupová práva:

<pre>
postgres=# \dconfig 
                   List of non-default configuration parameters
┌──────────────────────────────────┬──────────────────────────────────────────────┐
│            Parameter             │                    Value                     │
╞══════════════════════════════════╪══════════════════════════════════════════════╡
│ application_name                 │ psql                                         │
│ client_encoding                  │ UTF8                                         │
│ client_min_messages              │ warning                                      │
│ config_file                      │ /usr/local/pgsql/master/data/postgresql.conf │
│ data_directory                   │ /usr/local/pgsql/master/data                 │
│ DateStyle                        │ ISO, DMY                                     │
│ hba_file                         │ /usr/local/pgsql/master/data/pg_hba.conf     │
│ check_function_bodies            │ off                                          │
│ ident_file                       │ /usr/local/pgsql/master/data/pg_ident.conf   │
│ lc_collate                       │ cs_CZ.UTF-8                                  │
│ lc_ctype                         │ cs_CZ.UTF-8                                  │
│ lc_messages                      │ cs_CZ.UTF-8                                  │
│ lc_monetary                      │ cs_CZ.UTF-8                                  │
│ lc_numeric                       │ cs_CZ.UTF-8                                  │
│ lc_time                          │ cs_CZ.UTF-8                                  │
│ log_timezone                     │ Europe/Prague                                │
│ max_stack_depth                  │ 2MB                                          │
│ search_path                      │ public, pg_catalog                           │
│ server_encoding                  │ UTF8                                         │
│ shared_memory_size               │ 143MB                                        │
│ shared_memory_size_in_huge_pages │ 72                                           │
│ TimeZone                         │ Europe/Prague                                │
│ wal_buffers                      │ 4MB                                          │
└──────────────────────────────────┴──────────────────────────────────────────────┘
(23 rows)

postgres=# \dconfig *mem*
      List of configuration parameters
┌──────────────────────────────────┬───────┐
│            Parameter             │ Value │
╞══════════════════════════════════╪═══════╡
│ autovacuum_work_mem              │ -1    │
│ dynamic_shared_memory_type       │ posix │
│ enable_memoize                   │ on    │
│ hash_mem_multiplier              │ 2     │
│ logical_decoding_work_mem        │ 64MB  │
│ maintenance_work_mem             │ 64MB  │
│ min_dynamic_shared_memory        │ 0     │
│ shared_memory_size               │ 143MB │
│ shared_memory_size_in_huge_pages │ 72    │
│ shared_memory_type               │ mmap  │
│ work_mem                         │ 4MB   │
└──────────────────────────────────┴───────┘
(11 rows)
</pre>

Druhým novým backslash příkazem je <code>\getenv</code>, kterým můžeme
zkopírovat hodnotu prostředí do psql proměnné:

<pre>
postgres=# \getenv home HOME
postgres=# \echo :home
/home/pavel
</pre>

Spíš bonbonkem je možnost použití pageru ve výstupu generovaném příkazem
<code>\watch</code>. Tento backslash příkaz provede každých <code>n</code> sec
zadaný příkaz a zobrazí jeho výsledek. V předchozích verzích
výstup šel přímo do terminálu, což funguje, ale nemusí to být vždy "user
friendly". Od patnáctky máme možnost nastavit konfigurační proměnnou psql
<code>PSQL_WATCH_PAGER</code>, tak aby obsahovala volání pageru s
příslušným nastavením.  Například pro [https://github.com/okbob/pspg <code>pspg</code>] je nutné použít
přepínač <code>--stream</code>.

tab-complete v <code>psql</code> by nyní měl zachovávat velikost písmen
(pokud nepíšeme název objektu).

Specifickou vlastností Postgresu jsou vícenásobné příkazy
(multicommands). Vícenásobné příkazy se posílají v jednom packetu a
Postgres je vykoná v rámci jedné transakce. U historických verzí Postgresu se příkazy
zapsané na jedné řádce oddělené středníkem vykonaly jako vícenásobný
příkaz. Výhodou je o něco rychlejší zpracování (redukuje se síťový
provoz), nevýhodou je zobrazení pouze výsledku posledního příkazu. U
moderních verzí už středník odděluje příkaz bez ohledu jestli jsou
zapsané na jedné řádce nebo nejsou. Pokud chcete v <code>psql</code>
vytvořit vícenásobný příkaz, tak se pro oddělení příkazů uvnitř
vícenásobného příkazu používá symbol <code>\;</code>.  Od verze 15 pak se
zobrazuje výstup všech příkazů multi příkazu:

<pre>
postgres=# select 1\;select 2;
┌──────────┐
│ ?column? │
╞══════════╡
│        1 │
└──────────┘
(1 row)

┌──────────┐
│ ?column? │
╞══════════╡
│        2 │
└──────────┘
(1 row)

-- PostgreSQL 14
postgres=# select 1\;select 2;
┌──────────┐
│ ?column? │
╞══════════╡
│        2 │
└──────────┘
(1 row)
</pre>

Samo o sobě to velký smysl nemá. Drtivá většina uživatelů vůbec netuší o
existenci vícenásobných příkazů. Tato funkce je příprava pro podporu
procedur vracející multi recordset. To je typická vlastnost T-SQL (MSSQL
nebo Sybase). Když jsem před nějakými jedna dvaceti roky s MS SQL
začínal, tak mi to přišlo jako docela zajímavá funkce, dnes bych byl
opatrnější v jejím používání. S multirecordsetem pak už v čistém SQL nic
neuděláte, a způsob který používá postgres (tabulkové funkce nebo v
terminologii Postgresu Set Returning Functions) mi přijde mnohem
praktičtější. V každém případě chybějící podpora procedur vracejících
multi recordsety může omezovat, komplikovat portace aplikací z MSSQL
(případně Sybase) do Postgresu. To by se v příštích verzích mělo změnit.

==Administrace==

===Změna výchozích práv k schématu <code>public</code>===


K tomuto kroku vývojáři Postgresu dlouho sbírali odvahu a sám jsem moc
nevěřil, že by se takový krok někdy uskutečnil. V PostgreSQL má
dominantní pozici schéma <code>public</code>. Většina aplikací ukládá data
do tohoto schématu. Pravděpodobně všechny legacy aplikace. Ve výchozím
nastavení kdokoliv může v tomto schématu dělat cokoliv (se svými
objekty). Schéma v Postgresu neurčuje vlastníka (oproti Oracle). Nemůžete
číst data z objektů, které si vytvořil jiný uživatel (pokud vám k nim
nedal práva), ale můžete si vytvořit tabulku, a tu plnit dokud nedojde
místo na disku.

Proto se doporučuje odstranit práva <code>PUBLIC</code> z tohoto schématu:

<pre>
REVOKE ALL ON SCHEMA public FROM PUBLIC
</pre>

Počínaje verzí 15 už bude výchozí nastavení k schématu <code>public</code>
restriktivnější - přístup bude mít pouze vlastník databáze. Pokud by
aplikace vyžadovala <code>PUBLIC</code> přístup, tak jednoduše se dá
nagrantovat buďto přímo v konkrétní databázi nebo v
<code>template1</code>. Samozřejmě, že lepší je opravit aplikaci, tak aby
běžela s explicitně nastavenými právy.

===Podpora collations===

PostgreSQL může pro porovnávání řetězců použít dvě metody. Buďto využít
funkcionalitu locales v glibc nebo použít funkce z knihovny libicu.
Výchozí a po roky osvědčenou metodou bylo používání knihovny glibc. V
posledních několika letech bohužel došlo k úpravám implementace v glibc.
Nová verze glibc řadí v některých locales jinak než stará verze. To má
velice nepříjemný dopad na indexy. Všechny indexy nad textovými
položkami je nutné reindexovat, jinak může dojít k chybám a poškození
konzistence indexů a potažmo konzistence databáze. Těmto problémům lze do
jisté míry zabránit sledováním verze knihovny, která se používá k
porovnání řetězců. Předpokládá se, že stejná verze garantuje stejné
řazení. Verzování již dříve bylo dostupné pro libicu. Nyní je dostupné i
pro glibc (plus ručně volaná kontrola kompatibility).

<pre>
postgres=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE:  version has not changed
ALTER DATABASE
</pre>

Možnosti knihovny glibc ohledně collations jsou relativně omezené. S
knihovnou libicu můžeme vytvářet nová deterministická (případně
nedeteministická) collations. Teprve ale od nové verze je možné použít
collation postavené nad libicu jako výchozí collation databáze. V nové
verzi můžeme výchozí collation databáze nastavit i vůči libicu (pouze ale
pro deterministické collation (řetězce jsou stejné pokud jejich binární
obsah je stejný)). V následujícím příkladu je vytvořena databáze s
collate, kde číselné hodnoty jsou řazené numericky a velká písmena jsou
upřednostněna před malými písmeny:

<pre>
postgres=# CREATE DATABASE omega
            locale_provider=icu
            icu_locale='cs-u-kn-true-kf-upper'
            lc_collate="C" TEMPLATE='template0';
CREATE DATABASE

postgres=# \c omega

omega=# VALUES('Chrnek'),('Crha'),('Drulák'),('Horák'),
              ('Malý'),('malý'),('a1'),('a2'),('a100')
          ORDER BY 1;
┌─────────┐
│ column1 │
╞═════════╡
│ a1      │
│ a2      │
│ a100    │
│ Crha    │
│ Drulák  │
│ Horák   │
│ Chrnek  │
│ Malý    │
│ malý    │
└─────────┘
(9 rows)

omega=# VALUES('Chrnek'),('Crha'),('Drulák'),('Horák'),
              ('Malý'),('malý'),('a1'),('a2'),('a100')
          ORDER BY column1 COLLATE "cs-x-icu";
┌─────────┐
│ column1 │
╞═════════╡
│ a1      │
│ a100    │
│ a2      │
│ Crha    │
│ Drulák  │
│ Horák   │
│ Chrnek  │
│ malý    │
│ Malý    │
└─────────┘
(9 rows)
</pre>

Vygenerovat konfigurační string pro libicu je trochu porod (pro mne, kdo
s tím v životě nedělal), ale pak už to funguje. Porovnávání řetězců by
mělo být přes libicu rychlejší. Jedna ze zásadních optimalizací je vůči
glibc (z důvodu chyby v glibc) blokovaná. O kolik - záleží na délkách
řetězců, použitém locales, a samozřejmě i velikosti dat. Zatímco
verzování v glibc je workaround, verzování v libicu je postavené na
nativní vlastnosti libicu.

===Security invoker pohledy===

Pohledy se z pohledu identity uživatele chovají podobně jako
<code>security definer</code> funkce. Pokud se dívám na nějakou relaci
skrz pohled, tak používám přístupová práva vlastníka pohledu.  V drtivé
většině případů se chování pohledů z této perspektivy řešit nemusí, a
ostatně já sám si nevzpomínám, že bych to někdy během let co dělám s
Postgresem řešil. Pokud se ale začne používat <i>Row Level Security</i>, tak
změna identity (ztráta identity) znemožňuje použití pohledů. Od této
verze lze vytvářet <code>security invoker</code> pohledy:

<pre>
CREATE VIEW new_type_view with (security_invoker = true ) AS SELECT * FROM source_data;
</pre>

===EXPLAIN a pg_stat_statements===

Skoro v každé z nedávných verzí došlo k rozšíření zobrazovaných (a
sbíraných) detailů o exekuci dotazů. V nové verzi (s flagem
<code>BUFFERS</code>) můžeme v explainu vidět velikost operací nad
dočasnými soubory. Počet operací nad dočasnými soubory je vidět i v
reportu <code>pg_stat_statements</code> (pro vynucení external sortu dávám
<code>work_mem</code> na minimum):

<pre>
postgres=# SET work_mem TO '64kB';
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT nazev FROM obce ORDER BY nazev;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    QUERY PLAN                                                     │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Unique  (cost=781.01..812.26 rows=5342 width=10) (actual time=24.102..27.916 rows=5342 loops=1)                   │
│   Buffers: shared hit=59, temp read=26 written=32                                                                 │
│   ->  Sort  (cost=781.01..796.63 rows=6250 width=10) (actual time=24.099..26.300 rows=6250 loops=1)               │
│         Sort Key: nazev                                                                                           │
│         Sort Method: external merge  Disk: 112kB                                                                  │
│         Buffers: shared hit=59, temp read=26 written=32                                                           │
│         ->  Seq Scan on obce  (cost=0.00..121.50 rows=6250 width=10) (actual time=0.031..1.895 rows=6250 loops=1) │
│               Buffers: shared hit=59                                                                              │
│ Planning Time: 0.195 ms                                                                                           │
│ Execution Time: 28.517 ms                                                                                         │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(10 rows)
</pre>

Zajímavou informací může být velikost zápisu do wal logu:

<pre>
postgres=# EXPLAIN (ANALYZE, WAL, BUFFERS) INSERT INTO foo VALUES(10),(20);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                 QUERY PLAN                                                  │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Insert on foo  (cost=0.00..0.03 rows=0 width=0) (actual time=0.085..0.087 rows=0 loops=1)                   │
│   Buffers: shared hit=2                                                                                     │
│   WAL: records=2 bytes=118                                                                                  │
│   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.011..0.020 rows=2 loops=1) │
│ Planning Time: 0.081 ms                                                                                     │
│ Execution Time: 0.133 ms                                                                                    │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
</pre>

V <code>pg_stat_statements</code> také nově najdeme souhrné statistiky JIT výrazů (Just In Time kompilace výrazů). JIT u komplexnějších déletrvajících dotazů může výrazně pomoct, ale naopak u složitějších krátkých dotazů má nepříjemně velkou režii. Je zcela patrné, že aktivace JIT poze na základě ceny dotazu nestačí. Uvažuje se i kalkulaci ceny JITu na základě komplexity dotazu. Než se problém s neadekvátním použitím JITu vyřeší, můžete nyní pomocí <code>pg_stat_statements</code> snadno identifikovat dotazy, kde je doba JIT kompilace neadekvátní celkové době exekuce.

===Možnost povolit přístup ke konfiguračním proměnným původně zpřístupněných pouze super uživateli===

Konfigurace Postgresu je postavená na používání tzv konfiguračních
proměnných. Často se setkáte se zkratkou GUC (Grand Unified
Configuration). Tyto proměnné se typicky dělí podle možnosti nastavení:
nastavení pouze v konfigu, nastavení superuserem, nastavení běžným
uživatelem. I některé nekritické parametry vyžadují super usera. Od nové
verze je možné povolit přístup slabším rolím k těmto parametrům.

<pre>
postgres=# CREATE ROLE tom LOGIN;
CREATE ROLE

postgres=# GRANT SET ON PARAMETER autovacuum_max_workers TO tom ;
GRANT
</pre>

Pozor. Efektivně tento mechanizmus funguje pouze na proměnných, kde pro
modifikaci je vyžadovaný superuser. Na ostatních konfiguračních
proměnných lze práva nastavovat, ale nemá to žádný reálný efekt.
Například by někdo mohl chtít znepřístupnit změnu <code>work_mem</code>
běžným uživatelům. To nejde. V commit message je to zdůvodněno nemožností
zajištění omezeného přístupu k GUC definovanými extenzemi. Diskuzi k
tomuto patchi jsem nesledoval, takže netuším nakolik je toto omezení
finální.

===Informace o využití sdílené paměti===

V Postgresu se masivně používá sdílená pamět. Drtivá většina této paměti
se používá jako sdílená cache datových stránek. Ve sdílené paměti je řada
dalších cache, a nyní i provozní statistiky. Většinou chcete znát
velikost sdílené paměti, protože ji chcete dostat do huge pages (pozor -
na některých virtualizačních platformách a při určité zátěži to možná
nechcete). V PostgreSQL 15 se jednoduše dostaneme k velikosti sdílené
paměti i k počtu nezbytných huge pages:

<pre>
postgres=# SHOW shared_memory_size;
┌────────────────────┐
│ shared_memory_size │
╞════════════════════╡
│ 145MB              │
└────────────────────┘
(1 row)

postgres=# SHOW shared_memory_size_in_huge_pages;
┌──────────────────────────────────┐
│ shared_memory_size_in_huge_pages │
╞══════════════════════════════════╡
│ 73                               │
└──────────────────────────────────┘
(1 row)
</pre>

===Logování ve formátu JSON===

Co si vzpomínám, tak parsování logů Postgresu nebylo úplně triviální, a
to i když se použil formát CSV. Dneska je formát JSON podporován všude, a
od verze 15 Postgres umí logovat v tomto formátu.  V konfiguráku je
potřeba zapnout <code>log_destination = 'jsonlog'</code>,
<code>logging_collector = on</code>, a restartovat Postgres. V dedikovaném
souboru s příponou <code>.json</code> je pak log ve stylu:

<pre>
{"timestamp":"2022-04-25 10:07:31.784 CEST","user":"pavel","dbname":"postgres","pid":199522,"remote_host":"[local]","session_id":"626656c1.30b62","line_num":1,"ps":"SELECT","session_start":"2022-04-25 10:07:29 CEST","vxid":"3/3","txid":0,"error_severity":"ERROR","state_code":"22012","message":"division by zero","statement":"select 0/0;","application_name":"psql","backend_type":"clientbackend","query_id":-8981983488509566257}
</pre>

===Nastavení komprimace===

Výchozí komprimační metodou v PostgreSQL je PGLZ. Pokud si vybavuji, tak
komprimace se v Postgresu aplikuje při zápisu větších než 2KB hodnot, při
zápisu do transakčního logu a u backupů. V předchozí verzi přibyla
možnost nastavit komprimaci TOAST hodnot metodou LZ4. V syntetických
benchmarkách (realita může být jiná) komprimuje LZ4 cca o 10% hůře než
PGLZ, ale 2x rychleji. V patnáctce můžeme LZ4 použít i pro komprimaci
transakčních logů.

Komprimovat data umí také <code>pg_basebackup</code> s hodnotou
<code>gzip</code> volby <code>--compression</code>. Nově můžeme použít
hodnotu <code>server-gzip</code> (nebo <code>server-lz4</code>, která
vynutí komprimaci dat serveru už na straně serveru. Zvlášť na pomalých
sítích nebo na VPNkách komprimace na straně serveru <a href="http://rhaas.blogspot.com/2022/02/server-side-lz4-backup-compression.html">razantně zrychluje
klonování</a>. 

===Replikace===

Skrz logickou replikaci se nyní protlačí dvou fázový commit. To umožňuje
použít logickou replikaci i pro datově kritické aplikace. Co jsem četl na
blogu autora, tak se spíš jedná o první krok k budoucí možnosti budovat
nad Postgresem a logickou replikací distribuované databáze, než o reálně
použitelnou kompletní funkci. I když na druhou stranu 2PC opravdu výrazně
navyšuje garanci konzistence dat v distribuované databázi. 

Zjednodušuje se zveřejnění všech tabulek ve schématu klauzulí <code>FOR
ALL TABLES IN SCHEMA</code> příkazu <code>CREATE PUBLICATION</code>. Pokud
se použije tato klauzule, tak nově přidaná tabulka se automaticky přidá
do seznamu zveřejněných tabulek. Pro refresh odběru je nutné zavolat příkaz
<code>ALTER SUBSCRIPTION REFRESH PUBLICATION</code>.

Novinkou v PostgreSQL 15 je možnost publikovat (replikovat) pouze vybrané
sloupce tabulky. Představuji si, že užitek to může mít jak bezpečnostní,
tak výkonnostní. Do účetnictví nepotřebuji posílat oskenovanou fakturu
uloženou jako hodnotu <code>bytea</code> typu.

Nově Postgres obsahuje pohled <code>pg_stat_subscription_workers</code> se
statiskami odběrů (statistiku uvidíme na straně konzumenta logické
replikace).

==Optimalizace==

===Optimalizace filtrování monotonních window funkcí===

Planner nyní dokáže rozpoznat monotonní window funkce jako je <code>row_number</code>
nebo <code>rank</code>, a vygenrovat plán s uzlem <code>Run Condition:</code>, který
dokáže efektivněji filtrovat (filtr se posune v prováděcím plánu níže)
řádky s výsledkem použité window funkce. Největší přínos této
optimalizace je v případech, kdy není použita klauzule <code>PARTITION BY</code>.

<pre>
postgres=# EXPLAIN ANALYZE SELECT * FROM (SELECT row_number() OVER (), * FROM obce) s WHERE row_number <= 3;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                QUERY PLAN                                                │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ WindowAgg  (cost=0.00..199.62 rows=6250 width=49) (actual time=0.050..0.060 rows=3 loops=1)              │
│   Run Condition: (row_number() OVER (?) <= 3)                                                            │
│   ->  Seq Scan on obce  (cost=0.00..121.50 rows=6250 width=41) (actual time=0.028..0.030 rows=4 loops=1) │
│ Planning Time: 0.309 ms                                                                                  │
│ Execution Time: 0.138 ms                                                                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

-- PostgreSQL 14:
postgres=# EXPLAIN ANALYZE SELECT * FROM (SELECT row_number() OVER (), * FROM obce) s WHERE row_number <= 3;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    QUERY PLAN                                                     │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s  (cost=0.00..277.75 rows=2083 width=49) (actual time=0.081..11.640 rows=3 loops=1)             │
│   Filter: (s.row_number <= 3)                                                                                     │
│   Rows Removed by Filter: 6247                                                                                    │
│   ->  WindowAgg  (cost=0.00..199.62 rows=6250 width=49) (actual time=0.077..10.270 rows=6250 loops=1)             │
│         ->  Seq Scan on obce  (cost=0.00..121.50 rows=6250 width=41) (actual time=0.058..1.996 rows=6250 loops=1) │
│ Planning Time: 0.998 ms                                                                                           │
│ Execution Time: 11.725 ms                                                                                         │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
</pre>

I u komplexnějších použití window funkcí (s klauzulí
<code>PARTITION BY</code>) tato optimalizace může pomoci (s vytvořeným
vícesloupcovým funkčnm indexem <code>create index on obce(okres_id,
(pocet_muzu + pocet_zen));</code>):

<pre>
postgres=# EXPLAIN ANALYZE SELECT *
                             FROM (SELECT row_number() OVER (PARTITION BY okres_id
                                                             ORDER BY pocet_muzu + pocet_zen), * 
                                     FROM obce) s
                            WHERE row_number <= 3;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                    QUERY PLAN                                                                     │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s  (cost=0.28..540.67 rows=6250 width=49) (actual time=0.100..14.362 rows=227 loops=1)                                           │
│   ->  WindowAgg  (cost=0.28..478.17 rows=6250 width=53) (actual time=0.098..14.228 rows=227 loops=1)                                              │
│         Run Condition: (row_number() OVER (?) <= 3)                                                                                               │
│         ->  Index Scan using obce_okres_id_expr_idx on obce  (cost=0.28..353.17 rows=6250 width=45) (actual time=0.061..10.190 rows=6250 loops=1) │
│ Planning Time: 0.357 ms                                                                                                                           │
│ Execution Time: 14.554 ms                                                                                                                         │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)

-- PostgreSQL 14:
postgres=# EXPLAIN ANALYZE SELECT *
                             FROM (SELECT row_number() OVER (PARTITION BY okres_id
                                                             ORDER BY pocet_muzu + pocet_zen), * 
                                     FROM obce) s
                            WHERE row_number <= 3;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                    QUERY PLAN                                                                    │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Subquery Scan on s  (cost=0.28..556.29 rows=2083 width=49) (actual time=0.083..26.563 rows=227 loops=1)                                          │
│   Filter: (s.row_number <= 3)                                                                                                                    │
│   Rows Removed by Filter: 6023                                                                                                                   │
│   ->  WindowAgg  (cost=0.28..478.17 rows=6250 width=53) (actual time=0.079..25.158 rows=6250 loops=1)                                            │
│         ->  Index Scan using obce_okres_id_expr_idx on obce  (cost=0.28..353.17 rows=6250 width=45) (actual time=0.051..9.041 rows=6250 loops=1) │
│ Planning Time: 0.315 ms                                                                                                                          │
│ Execution Time: 26.698 ms                                                                                                                        │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)
</pre>

===Další optimalizace===

Podporu optimalizátoru dostala i funkce <code>starts_with</code> (všimněte
si mnohem lepšího odhadu v prováděcím plánu):

<pre>
postgres=# EXPLAIN ANALYZE SELECT * FROM obce WHERE starts_with(nazev, 'Be');
┌───────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                            QUERY PLAN                                             │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..137.12 rows=67 width=41) (actual time=0.056..6.676 rows=48 loops=1) │
│   Filter: starts_with((nazev)::text, 'Be'::text)                                                  │
│   Rows Removed by Filter: 6202                                                                    │
│ Planning Time: 0.405 ms                                                                           │
│ Execution Time: 6.774 ms                                                                          │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

-- postgresql 14
postgres=# EXPLAIN ANALYZE SELECT * FROM obce WHERE starts_with(nazev, 'Be');
┌─────────────────────────────────────────────────────────────────────────────────────────────────────
│                                             QUERY PLAN                                              
╞═════════════════════════════════════════════════════════════════════════════════════════════════════
│ Seq Scan on obce  (cost=0.00..137.12 rows=2083 width=41) (actual time=0.030..5.704 rows=48 loops=1) 
│   Filter: starts_with((nazev)::text, 'Be'::text)                                                    
│   Rows Removed by Filter: 6202                                                                      
│ Planning Time: 0.144 ms                                                                             
│ Execution Time: 5.756 ms                                                                            
└─────────────────────────────────────────────────────────────────────────────────────────────────────
(5 rows)
</pre>

Od předchozí verze se dohledání hodnoty v delším seznamu hodnot (val IN
(var1, var2, ...)) efektivněji nahrazuje hledáním v hashovací tabulce. Ve
verzi 15 se používá stejná optimalizace i na operaci <code>NOT IN
(seznam)</code>.

V implementaci operace <code>sort</code>  došlo v PostgreSQL 15  k
několika mikrooptimalizacím. Na menších datech zrychlení bude spíš jenom
v procentech. Na větších datech by to mohlo být zajímavější. Zvětšením
bufferu používaného pro komunikaci mezi paralelně bežícími procesy
zpracování jednoho dotazu by mělo dojít k zajímavému zrychlení výpočtu
paralelních dotazů. Nezkoušel jsem, ale nemám důvod nevěřit autorům.
Jednou z důležitých interních struktu je tzv <code>tuplestore</code>. Do
velikosti <code>work_mem</code> si drží data v paměti. Pokud je potřeba
uložit víc dat než je nastavení <code>work_mem</code>, tak data ukládá do
dočasného souboru. Od nové verze se používá jiný mechanismus alokace
paměti, díky kterému stejná data zaberou méně paměti. Asi největšího
efektu se dosáhne u 4 bajtových integerů - 6000 hodnot v 14ce vyžaduje
915kB, v 15ce pouze 806kB. Tím se mírně zvyšuje šance, že data zůstanou
čistě v RAMce, a že při sortu se použije rychlejší quick sort namísto
external sortu. V reálu ten efekt nemusí být tak velký, ale poskytuje to
vývojářům určitou rezervu. Jiné nové optimalizace mohou mít jinak
nastavený worst case, a díky různým byť malým mikrooptimalizacím je nová
verze ve všech ohledech rychlejší nebo alespoň stejně rychlá.

Odhady rekurzivních dotazů pracují s magickou konstantou <code>10</code>. 
Nově je možné změnit tuto konstantu v konfigurační proměnné
<code>recursive_worktable_factor</code>.

==Ostatní==

Uložené procedury lze v Postgresu psát v několika programovacích
jazycích. Nejčastější je PL/pgSQL, který vychází z Oraclovského PL/SQL.
Výrazně méně se používá PL/Perl a PL/Python. I když se používají méně,
mají své využití. V nové verzi došlo k odstranění podpory dvojkového
Pythonu. Tudíž procedury napsané v Pythonu 2 je nutné přemigrovat do
Pythonu 3.

V provozních statistikách si Postgres udržuje informace o počtu operací
nad tabulkami, indexy, atd. Doposud se tato data držela v paměti
dedikovaného procesu a v dočasných souborech. U databází s větším počtem
databázových objektů tyto dočasné soubory mohly dosáhnout megabajtů a
mohly se až 2x za sekundu přepisovat (v GD jsme s tím měli docela velký
problém, a adresář se statistikami jsme linkovali na efemeral disky).
Komunikace s procesem dedikovaným pro uložení  statistik probíhala
prostřednictvím UDP. Od verze 15 jsou provozní statistiky uložené ve
sdílené paměti.

Nově také příkaz <code>CREATE DATABASE</code> nevyžaduje checkpointy
(starší verze provedly na jednu novou databázi dva checkpointy).
Checkpointy mohou být nepříjemně náročné na databázích s větším provozem
a větší <code>share_buffers</code>.  Pokud je ale zdrojová databáze
(default <code>template1</code>) větší, pak nová metoda může být naopak
výrazně pomalejší. Ke staršímu způsobu se lze vrátit použitím klauzule
<code>STRATEGY</code>

Od verze 14 je v Postgresu funkce
<code>pg_log_backend_memory_contexts</code>, která do logu Postgresu
vypíše seznam a velikost používaných paměťových kontextů. Tato informace
může být zajímavá pro diagnostiku problémů s pamětí, případně pro
diagnostiku memory leaků. Dříve tuto funkci mohl volat pouze uživatel s
právy superusera. Od nové verze může super user nagrantovat exekuci této
funkce i uživatelům bez superusera.

Nově také Postgres umí při zpracování dotazu na cizím serveru provést
vzdáleně výrazy obsahující <code>CASE</code>. Optimalizátor by měl také
častěji používat asynchronní čtení dat z cizího serveru.

Pro zobrazení obsahu transakčních logů máme v Postgresu nástroj
<code>pg_waldump</code>. Nově tento nástroj doplňuje extenze
<code>pg_walinspect</code>, která umožňuje prohlížet obsah transakčního
logu z SQL.

<pre>
postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info('0/14F9A30', '0/15011D7');
 start_lsn |  end_lsn  | prev_lsn  | xid | resource_manager | record_type  | record_length | main_data_length | fpi_length |     description     
-----------+-----------+-----------+-----+------------------+--------------+---------------+------------------+------------+---------------------
 0/14FA118 | 0/14FB4B0 | 0/14F9958 | 725 | Btree            | INSERT_LEAF  |          5013 |                2 |       4960 | off 246
 0/14FB4B0 | 0/14FD050 | 0/14FA118 | 725 | Btree            | INSERT_LEAF  |          7045 |                2 |       6992 | off 130
 0/14FD050 | 0/14FD0A8 | 0/14FB4B0 | 725 | Heap2            | MULTI_INSERT |            85 |                6 |          0 | 1 tuples flags 0x02
 0/14FD0A8 | 0/14FD0F0 | 0/14FD050 | 725 | Btree            | INSERT_LEAF  |            72 |                2 |          0 | off 155
 0/14FD0F0 | 0/14FD138 | 0/14FD0A8 | 725 | Btree            | INSERT_LEAF  |            72 |                2 |          0 | off 134
 0/14FD138 | 0/14FD210 | 0/14FD0F0 | 725 | Heap             | INSERT       |           211 |                3 |          0 | off 11 flags 0x00
 0/14FD210 | 0/14FD250 | 0/14FD138 | 725 | Btree            | INSERT_LEAF  |            64 |                2 |          0 | off 246
</pre>

Hodně šikovné neatomické typy v Postgresu jsou typ <code>range</code> a
<code>multirange</code> (od PostgreSQL 14). Nově je možné použít funkci
<code>range_agg</code> i pro typ <code>multirange</code> (to je spíš
dodělávka implementace <code>multirange</code> z minulé verze):

<pre>
postgres=# SELECT range_agg(nmr) FROM (values ('{[1,2]}'::nummultirange), ('{[5,6]}'::nummultirange)) t(nmr);
┌───────────────┐
│   range_agg   │
╞═══════════════╡
│ {[1,2],[5,6]} │
└───────────────┘
(1 row)

postgres=# SELECT range_agg(nmr) FROM (values ('{[1,2]}'::nummultirange), ('{[2,3]}'::nummultirange)) t(nmr);
┌───────────┐
│ range_agg │
╞═══════════╡
│ {[1,3]}   │
└───────────┘
(1 row)
</pre>

Kontinuální backupy se v Postgresu implementují skrze shellový příkaz
zadaný v konfigurační proměnné <code>archive_command</code>. Na zatížených
serverech už může mít volání shellu nezanedbatelnou režii a způsobovat
další latence. Od PostgreSQL 15 je možné nahradit
<code>archive_command</code> kódem v extenzi (s callbackem). Extenze se
aktivuje uvedením jejího názvu v konfigurační proměnné
<code>archive_library</code>.

Po mnoha letech se "dokončila" implementace unlogged tabulek, a to
podporou unlogged sekvencí.

===Co se nestihlo===

Rozdělané práce je pořád dost. Do PostgreSQL 15 se nedostala replikace
sekvencí. Plánuje se přepis implementace kontroly referenční integrity.
Letos se trochu posunul vývoj implementace session proměnných, ale je na
tom ještě dost práce. Uvažuje se o velké změně v implementaci jsonu.
Stávající typ <code>jsonb</code> by se měl přejmenovat na <code>json</code>
(aby to odpovídalo ANSI/SQL), a stávající  typ<code>json</code> by měl být
implementován jen jako varianta <code>jsonb</code> (s jinou serializací).
Tomáš Vondra pracuje na mezi tabulkových statistikách, pracuje se na
integraci šifrování. Na tak velkém projektu jako je PostgreSQL a
implementace ANSI/SQL je práce pořád dost.

Kvůli rizikům spojeným s covidem se přesunula konference P2D2 z únorového
termínu na červen. Díky tomu Vás mohu pozvat na letos 14 ročník této
konference. Koná se pod patronací FSV v budově Fakulty architektury v
Dejvicích 2. 6. 2022. Setkat se můžete s našimi i zahraničními vývojáři
PostgreSQL (z hierarchie vývojářů dvou top vývojářů s právem commitu do
repozitáře Postgresu). Detaily naleznete na webové stránce
[https://p2d2.cz/rocnik-2022 https://p2d2.cz/rocnik-2022].

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