Verze z 8. 5. 2022, 06:29

Autor: Pavel Stěhule, 2021

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

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)

V Postgresu můžeme také použít příkaz INSERT ON CONFLICT:

-- 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;

Ačkoliv MERGE a INSERT ON CONFLICT DO 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. MERGE je primárně hromadný příkaz. Syntaxe INSERT ON CONFLICT DO se hodí pro práci s jedním řádkem. Hlavní benefit je nejspíš pro datové analytiky, kteří znají příkaz MERGE z jiných databází, a nemusí se přepínat na proprietární příkaz v Postgresu.

Omezení (constraint) `UNIQUE NULLS DISTINCT`

Omezení UNIQUE zaručuje unikátní hodnoty ve sloupci. Za unikátní hodnotu se ale nepovažuje NULL. Což je to, co většinou chceme. V některých případech můžeme požadovat, aby hodnota NULL 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í UNIQUE použít frázi NULLS DISTINCT, čímž dosáhneme požadovaného chování omezení (NULL 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 JSON 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 JSONPath a implementace funkce JSON_TABLE. Podpora JSONPath se dostala do Postgresu 12. Zbytek, včetně JSON_TABLE, se dostal do nové verze. V MySQL je už asi dva, možná tři roky implementace JSON_TABLE, 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 (JSON_EXISTS, JSON_QUERY, JSON_VALUE), funkce pro vytvoření a serializaci JSONu (JSON, JSON_SCALAR, JSON_SERIALIZE), funkci pro transformaci JSONu na relaci (JSON_TABLE) a operátor IS JSON.

Začnu samopopisnou ukázkou operátoru IS JSON ...

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)

Funkce json provádí přetypování na typ JSON z typu text, bytea, ... json_scalar generuje json ze ze základních SQL skalárních typů. json_serialize umožňuje serializovat hodnotu do bytea 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):

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)

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

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

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

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)

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 WITH WRAPPER (zkrácená forma pro WITH ARRAY WRAPPER). 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 JSON_TABLE je obdobou funkce XMLTABLE na steroidech. Oproti XMLTABLE 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 XMLTABLE řešit na SQL úrovni (JOIN, vícenásobné volání XMLTABLE), to můžeme v JSON_TABLE udělat přímo. Výhodou je rychlost. Nevýhodou naopak možnost narazit na limity paměti, a mnohem komplikovanější syntax JSON_TABLE (i když i tuto funkci lze také používat jednoduše):

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)

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 NULL. V poslední ukázce jsou dvě zanořené sloupce. V prvním případě je nastavený sloupec b a sloupce name a surname jsou NULL. 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:

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)

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

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)

regexp funkce

V Postgresu budou 4 nové funkce pro práci s regulárními výrazy: regexp_count, regexp_instr, regexp_like a regexp_substr. Stávající funkce regexp_replace 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 NULL (Oracle nerozlišuje mezi NULLem a prázdným řetězcem). Pokud byste chtěli použít tyto funkce ve starších verzích Postgresu, najdete je v [href="https://github.com/orafce/orafce Orafce], navíc upravené tak, aby poskytovaly maximální možnou kompatibilitu s Oraclem (včetně přístupu k NULL):

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)

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 \dconfig, 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 \d* příkazů. V pluskové variantě zobrazí i přístupová práva:

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)

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

postgres=# \getenv home HOME
postgres=# \echo :home
/home/pavel

Spíš bonbonkem je možnost použití pageru ve výstupu generovaném příkazem \watch. Tento backslash příkaz provede každých n 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 PSQL_WATCH_PAGER, tak aby obsahovala volání pageru s příslušným nastavením. Například pro <a href="https://github.com/okbob/pspg">pspg</a> je nutné použít přepínač --stream.

tab-complete v psql 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 psql vytvořit vícenásobný příkaz, tak se pro oddělení příkazů uvnitř vícenásobného příkazu používá symbol \;. Od verze 15 pak se zobrazuje výstup všech příkazů multi příkazu:

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

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

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

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 `public`

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 public. 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 PUBLIC z tohoto schématu:

REVOKE ALL ON SCHEMA public FROM PUBLIC

Počínaje verzí 15 už bude výchozí nastavení k schématu public restriktivnější - přístup bude mít pouze vlastník databáze. Pokud by aplikace vyžadovala PUBLIC přístup, tak jednoduše se dá nagrantovat buďto přímo v konkrétní databázi nebo v template1. 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).

postgres=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE:  version has not changed
ALTER DATABASE

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:

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)

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 security definer 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 Row Level Security, tak změna identity (ztráta identity) znemožňuje použití pohledů. Od této verze lze vytvářet security invoker pohledy:

CREATE VIEW new_type_view with (security_invoker = true ) AS SELECT * FROM source_data;

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 BUFFERS) 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 pg_stat_statements (pro vynucení external sortu dávám work_mem na minimum):

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)

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

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)

V pg_stat_statements 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í pg_stat_statements 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.

postgres=# CREATE ROLE tom LOGIN;
CREATE ROLE

postgres=# GRANT SET ON PARAMETER autovacuum_max_workers TO tom ;
GRANT

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 work_mem 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:

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)

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 log_destination = 'jsonlog', logging_collector = on, a restartovat Postgres. V dedikovaném souboru s příponou .json je pak log ve stylu:

{"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}

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é pg_basebackup s hodnotou gzip volby --compression. Nově můžeme použít hodnotu server-gzip (nebo server-lz4, 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í FOR ALL TABLES IN SCHEMA příkazu CREATE PUBLICATION. 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 ALTER SUBSCRIPTION REFRESH PUBLICATION.

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 bytea typu.

Nově Postgres obsahuje pohled pg_stat_subscription_workers 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 row_number nebo rank, a vygenrovat plán s uzlem Run Condition:, 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 PARTITION BY.

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)

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

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)

Další optimalizace

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

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)

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 NOT IN (seznam).

V implementaci operace sort 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 tuplestore. Do velikosti work_mem si drží data v paměti. Pokud je potřeba uložit víc dat než je nastavení work_mem, 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 10. Nově je možné změnit tuto konstantu v konfigurační proměnné recursive_worktable_factor.

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 CREATE DATABASE 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ší share_buffers. Pokud je ale zdrojová databáze (default template1) 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 STRATEGY

Od verze 14 je v Postgresu funkce pg_log_backend_memory_contexts, 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í CASE. 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 pg_waldump. Nově tento nástroj doplňuje extenze pg_walinspect, která umožňuje prohlížet obsah transakčního logu z SQL.

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

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

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)

Kontinuální backupy se v Postgresu implementují skrze shellový příkaz zadaný v konfigurační proměnné archive_command. 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 archive_command kódem v extenzi (s callbackem). Extenze se aktivuje uvedením jejího názvu v konfigurační proměnné archive_library.

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 jsonb by se měl přejmenovat na json (aby to odpovídalo ANSI/SQL), a stávající typjson by měl být implementován jen jako varianta jsonb (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.

@@ Řádek 408: / Řádek 408: @@
 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 <a href="https://github.com/orafce/orafce"><code>Orafce</code></a>, navíc upravené tak,
+verzích Postgresu, najdete je v [href="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>):

PostgreSQL 15 (2022): Porovnání verzí

Verze z 8. 5. 2022, 06:29

Obsah

PostgreSQL 15

SQL

MERGE

Omezení (constraint) `UNIQUE NULLS DISTINCT`

JSON

regexp funkce

psql

Administrace

Změna výchozích práv k schématu `public`

Podpora collations

Security invoker pohledy

EXPLAIN a pg_stat_statements

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

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

Logování ve formátu JSON

Nastavení komprimace

Replikace

Optimalizace

Optimalizace filtrování monotonních window funkcí

Další optimalizace

Ostatní

Co se nestihlo

Navigační menu

PostgreSQL 15 (2022): Porovnání verzí

Verze z 8. 5. 2022, 06:29

PostgreSQL 15

SQL

MERGE

Omezení (constraint) UNIQUE NULLS DISTINCT

JSON

regexp funkce

psql

Administrace

Změna výchozích práv k schématu public

Podpora collations

Security invoker pohledy

EXPLAIN a pg_stat_statements

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

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

Logování ve formátu JSON

Nastavení komprimace

Replikace

Optimalizace

Optimalizace filtrování monotonních window funkcí

Další optimalizace

Ostatní

Co se nestihlo

Navigační menu

Hledat

Omezení (constraint) `UNIQUE NULLS DISTINCT`

Změna výchozích práv k schématu `public`