Get best of the week

魔鬼十二中有多少新东西？

我们仅谈论PostgreSQL13。4月8日，发生了“冻结”事件-PostgreSQL 功能冻结，现在只有该日期之前接受的那些功能才包含在该版本中。

很难说出这种革命性的版本。它没有任何主要的，概念上的变化。另外，我也想在PG12中的JSONPath补丁旁边看到诸如JSON / SQL标准的表和函数之类的重要补丁，没有时间输入它。现成的嵌入式存储没有出现-只有接口正在最终确定。但是改进的清单仍然令人印象深刻。我们已经对Devil's Dozen中包含的补丁程序做了一个相当完整的摘要。


对SQL命令的更改


CREATE DATABASE ... LOCALE

实用程序initdb，createdb并且团队CREATE COLLATION具有一个设置LOCALE，可让您指定rightLC_CTYPE和的值LC_COLLATE。现在，团队中出现了相同的机会CREATE DATABASE：

CREATE DATABASE db_koi8r TEMPLATE template0 
    ENCODING 'KOI8R' LOCALE 'ru_RU.KOI8R';

ALTER VIEW ... RENAME COLUMN

现在可以使用命令更改视图中的列名ALTER VIEW。以前，这需要重新创建视图。

假设您忘了给列命名：

CREATE VIEW uptime AS SELECT current_timestamp, current_timestamp - pg_postmaster_start_time();
SELECT * FROM uptime;

       current_timestamp       |    ?column?     
-------------------------------+-----------------
 2020-03-23 15:37:00.088824+03 | 04:18:24.897856

可以解决：

ALTER VIEW uptime RENAME COLUMN "?column?" TO uptime;
SELECT * FROM uptime;

       current_timestamp       |     uptime      
-------------------------------+-----------------
 2020-03-23 15:37:40.726516+03 | 04:19:05.535548


ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION

现在可以使表的生成列为普通列，即删除表达式以对其求值：

CREATE TABLE payments (
    id integer PRIMARY KEY,
    amount numeric(18,2),
    income_tax numeric(18,2) GENERATED ALWAYS AS (amount*0.13) STORED
);
INSERT INTO payments(id, amount) VALUES(1, 42);
\d payments

                                     Table "public.payments"
   Column   |     Type      | Collation | Nullable |                   Default            
        
------------+---------------+-----------+----------+--------------------------------------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          | generated always as ((amount * 0.13))
 stored
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

随后，他们决定应明确设置Income_tax。删除表达式：

ALTER TABLE payments ALTER COLUMN income_tax DROP EXPRESSION;
\d payments

                   Table "public.payments"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          |
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

当然，该列中的现有数据并没有消失：

SELECT * FROM payments;

 id | amount | income_tax
----+--------+------------
  1 |  42.00 |       5.46


DROP DATABASE ... FORCE
如果要删除数据库而不等待所有用户断开连接，则可以使用newFORCE命令选项DROP DATABASE。

CREATE DATABASE db;

连接到新数据库：

CREATE EXTENSION IF NOT EXISTS dblink;
SELECT dblink_connect('dbname=db');

 dblink_connect
----------------
 OK

现在，我们将删除并强制中断pg_terminate_backend打开的连接：

DROP DATABASE db WITH (FORCE);

ALTER TYPE ... SET STORAGE
该命令ALTER TYPE允许基本数据类型更改各种属性，尤其是存储策略。以前，您只能将其设置为一个团队CREATE TYPE。

为了演示，我们不会创建新的基本类型，而是使用现有的-tsquery。但首先，创建一个单独的数据库并连接到它：

CREATE DATABASE db;
\c db

tsquery数据类型使用存储策略plain，因此此类型的表的列将获得相同的策略：

SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';

 typname | typstorage
---------+------------
 tsquery | p

CREATE TABLE queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'queries'::regclass AND attname = 'query';

 attname | attstorage
---------+------------
 query   | p

如果需要对新表使用其他策略，则可以更改基本类型：

ALTER TYPE tsquery SET (storage=external);
SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';

 typname | typstorage
---------+------------
 tsquery | e

新表中的存储类型也将更改：

CREATE TABLE new_queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'new_queries'::regclass AND attname = 'query';

 attname | attstorage
---------+------------
 query   | e

应当牢记，更改涉及使用TOAST的策略plain不能更改为：

ALTER TYPE tsquery SET (storage=plain);

ERROR:  cannot change type's storage to PLAIN

因此，实验是在一个单独的数据库中进行的，因此不能删除。

ALTER STATISTICS ... SET STATISTICS该

命令CREATE STATISTICS允许您为表列的选定组合收集最常用值的列表。收集的最常见值的数量由参数确定default_statistics_target。现在可以使用以下命令更改特定统计信息的值：

ALTER STATISTICS  SET STATISTICS _;

用WITH TIES选项先抓取
如您所知SELECT，LIMIT您可以使用SQL标准中定义的语法来代替指定命令：

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS ONLY;

 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
(2 rows)

现在，它FETCH支持短语WITH TIES，它将所有“相关”行添加到输出中（如果仅考虑排序条件，则这些行等于已选择的行）：

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS WITH TIES;

 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
 2 | 2.2
(3 rows)

内置函数和数据类型


get_random_uuid
新函数get_random_uuid返回版本4 UUID值（随机值）：

SELECT gen_random_uuid();

           gen_random_uuid            
--------------------------------------
 25e02793-80c0-438c-be07-c94b966c43ab

该功能对于在分布式系统中生成唯一的UUID值很有用。
以前，您必须使用uuid-ossp或pgcrypto库。

min_scale和trim_scale用于数字类型的值

该函数min_scale确定数字的小数部分中的有效位数，并且该函数trim_scale丢弃非有效零：

SELECT n, min_scale(n), trim_scale(n) FROM (VALUES(42.42000)) as t(n);

    n     | min_scale | trim_scale
----------+-----------+------------
 42.42000 |         2 |      42.42

gcd和lcm

数学函数部分中的补货。现在，您可以快速找到最大的公因数（gcm）和最小的公因数（lcm）：

SELECT gcd(54,24), lcm(54,24);

 gcd | lcm
-----+-----
   6 | 216

类型pg_lsn聚合函数的最小值和最大值集合函数增加了对

数据类型，并允许您执行以下形式的查询：pg_lsnminmax

SELECT min(restart_lsn) FROM pg_replication_slots;
SELECT min(sent_lsn) FROM pg_stat_replication;

检查函数返回值的类型修饰符

在以前的版本中，未检查类型修饰符的函数返回值。

假设有一个用于存储货币单位的类型和一个返回所得税金额的函数：

CREATE TYPE currency AS (
    amount numeric(18,2),
    code   text
);
CREATE FUNCTION income_tax(quantity currency) RETURNS currency
    AS 'SELECT quantity.amount * 0.13, quantity.code' LANGUAGE SQL;

调用该函数，我们希望得到两位小数，但是，我们得到四位。即使在函数调用后进行显式转换也无济于事（第三列）：

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, ''))\gx

-[ RECORD 1 ]--
amount | 5.5146
code   | 
amount | 5.5146

在版本13中，结果是正确的：

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, ''))\gx

-[ RECORD 1 ]
amount | 5.51
code   | 
amount | 5.51

to_date（）和to_timestamp（）

函数中的本地化名称to_date还to_timestamp学习了理解月份和星期几的本地化名称。以前只能使用英文名称：

SELECT to_date(', 24  2020', 'TMDay, DD TMMonth YYYY');

  to_date   
------------
 2020-03-24

normalize和IS NORMALIZED

为了符合SQL标准，已经添加了normalize（）函数以对Unicode字符串进行标准化，并且IS NORMALIZED谓词可以检查字符串是否被标准化。

SELECT str, normalize(str, NFC) AS nfc,
       str IS NFC NORMALIZED AS is_nfc_normalized,
       normalize(str, NFKC) AS nfkc,
       str IS NFKC NORMALIZED AS is_nfkc_normalized
FROM (VALUES(U&'\0032\2075')) AS vals(str)\gx

-[ RECORD 1 ]------+---
str                | 2⁵
nfc                | 2⁵
is_nfc_normalized  | t
nfkc               | 25
is_nfkc_normalized | f

阅读有关UNICODE规范化表格的更多信息。

用于64位事务号的Xid8类型和xid8_current（）函数为64位事务号

添加了新的xid8数据类型。但是，不，这并不意味着PostgreSQL转向了64位事务：一切都像以前一样工作。但是，一些函数返回一个新的类型，例如，现在推荐使用，而不是旧的功能pg_current_xact_id txid_current，这回INT8，等等。N.

新多态数据类型anycompatible家庭

添加类型anycompatible，anycompatiblearray，anycompatiblenonarray，anycompatiblerange。与家庭类型不同anyelement，新类型允许您使用不完全相同但实际上兼容的类型。

在下面的示例中，该函数maximum作为参数定义为anycompatible传递integer和numeric。对于这两种类型，返回值将转换为公共值：

CREATE FUNCTION maximum(a anycompatible, b anycompatible) RETURNS anycompatible
	AS 'SELECT CASE WHEN a > b THEN a ELSE b END' LANGUAGE SQL;

SELECT f, pg_typeof(f) FROM maximum(42, 42.42) f;

   f   | pg_typeof
-------+-----------
 42.42 | numeric

此外，类型anycompatible-和any-是两个独立的类型集：

CREATE FUNCTION first_elems(a anyarray, b anycompatiblearray)
    RETURNS TABLE(a anyelement, b anycompatible) AS $$
        SELECT ROW(a[array_lower(a,1)], b[array_lower(b,1)]);
    $$ LANGUAGE sql;
SELECT first_elems(ARRAY[1,2,3], ARRAY['a','b','c']) AS str, 
       first_elems(ARRAY[1.1,2.2,3.3],ARRAY[true,false]) bool;

  str  |  bool   
-------+---------
 (1,a) | (1.1,t)

程序语言


PL / Perl中bool类型的转换
最近，Ivan Panchenko（Postgres Professional）的 bool_plperl的TRANSFORM已提交。Postgres像PL或Perl中那样将布尔值传递给PL或Perlt或在PL / Perl中传递f，但对于Perl而言，它f不是布尔 false，而是字母f，即 在逻辑上，真理。可以通过不同的方式解决此问题（请参阅对应关系），但是汤姆·莱恩（Tom Lane）认为，为布尔创建TRANSFORM是最实用的。

在PL / pgSQL中快速执行简单表达式

简单表达式（至少不包含表调用且不需要锁）会更快。以前，在这些情况下，每个周期上与调度程序联系的时间都花了很多时间。

CREATE OR REPLACE FUNCTION slow_pi() RETURNS double precision AS $$
    DECLARE
        a double precision := 1;
        s double precision := 1;
        r double precision := 0;
    BEGIN
        FOR i IN 1 .. 10000000 LOOP
            r := r + s/a; a := a + 2; s := -s;
        END LOOP;
        RETURN 4*r;
    END;
    $$ LANGUAGE plpgsql;

在PG12中调用slow_pi（）：

SELECT slow_pi();

          slow_pi      
    --------------------
     3.1415925535898497
    (1 row)
    Time: 13060,650 ms (00:13,061)

现在在PG13中：
SELECT slow_pi();

          slow_pi      
    --------------------
     3.1415925535897915
    (1 row)
    Time: 2108,464 ms (00:02,108)

受信任的扩展而不是pg_pltemplate

系统目录减少了一个表。已删除pg_pltemplate。它存储了执行过程中所需的过程语言的属性CREATE LANGUAGE。现在，我们决定从相应语言的扩展脚本中注册属性pg_pltemplate，并摆脱表本身。但是，为了实施该计划，有必要为数据库所有者（无超级用户权限）提供从扩展脚本创建可信语言的可能性。确实，例如，现在要创建plpgsql，数据库所有者不必是超级用户。

收到如下。新的逻辑参数已出现在扩展控制文件中trusted。如果两个参数trusted和superuser如果包含此扩展名，则扩展名不仅可以由超级用户创建，还可以由拥有CREATE当前数据库（及其所有者）权限的用户创建。执行此类扩展的脚本时，将使用初始化集群的超级用户权限。尽管扩展本身的所有者将是创建用户，但扩展创建的对象将属于该扩展。

这些更改的重要后果：

  • 可信扩展为第三方开发人员创建其他可信语言开辟了道路。现在我们只限于plpgsql，plperl和pltcl。
  • 这pg_pltemplate是很难写了plpython指的是语言的第二个版本。否则，pg_pltemplate是过渡到python 3的步骤（必要但不充分）。

指标


B树
的压缩由Peter最终传达了一个由Anastasia Lubennikova（Postgres Professional）和Peter Geigan（Peter Geoghegan）编写的，期待已久的重要补丁（工作已于2015年开始）。 Nastya设法在PGconf India上谈论了这一点。 Postgres学会了通过重复数据删除显着减少B树索引的大小，即节省重复索引键。这些索引经过了认真的重新设计，因此可以进行压缩而不会失去与以前版本索引的兼容性。重复数据删除的想法来自更灵活的索引架构，如 GIN（反向索引-广义反向索引）。

在这些索引中比在 B树中更常见时，存在键对应于大量记录的情况。例如，在进行文字处理时，通常会在多个文档中找到相同的令牌。并且它仅在索引中存储一次。直到最近，B树索引还不知道如何执行此操作。

B树索引与GIN索引的不同之处主要在于叶页。根据与相同键值相关的记录数，可以使用以下选项：该页面仅包含一个发布列表-TID列表（索引记录的标识符），如果该列表很小，并且如果TID很大，那么将存储而不是值列表新的“树枝”-链接到其他页面，如发布列表或其他树枝（它们称为发布树）。

这样的树结构类似于B树，但是在本质细节上有所不同：例如，用于在GIN中浏览同一树级别的页面的列表是单向的，而不是双向的。因此，不容易实现新的，具有重复数据删除功能的索引与旧版本的良好兼容性。改进确实花费了3年多的时间。还必须弄清清洁机制（微真空）和其他细微差别。

在性能测试中，所有适用重复数据删除的索引缩水了约3倍。压缩重复项还有助于唯一索引，从而消除了表更改率很高时索引膨胀的问题。可以在索引设置级别连接和断开新行为。

在不需要的地方不会按GIN索引进行完全扫描
在某些情况下，此修补程序可以避免整个GIN索引完全通过。尽管GIN索引支持某些操作，但这些操作是通过对索引进行全面扫描来执行的。以全文列搜索的索引为例tsvector。如果搜索查询的形式为“给定单词以外的任何内容”，则整个索引将必须全部读取。但是，如果请求中存在另一个条件，该条件不需要完全扫描索引，则仍将完全扫描索引。

通过新的优化，将首先使用更准确的条件，使您可以从索引中获得收益，然后将再次检查结果以考虑另一个限制。比较版本12（缓冲区）中读取的页面数：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');

                                             QUERY PLAN                                             
------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5167
   Buffers: shared hit=24 read=27405
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=24 read=22238
 Planning Time: 0.283 ms
 Execution Time: 3258.234 ms

新版本中的缓冲区数：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');

                                             QUERY PLAN                                             
---------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5156
   Buffers: shared hit=5179
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=23
 Planning Time: 0.250 ms
 Execution Time: 8.779 ms

当使用三字母组和检查数组的出现时，可能会遇到类似的情况。

运算符类的参数
在PostgreSQL中，许多索引访问方法是一种“框架”，它采用搜索算法的高级实现，使用页面和锁以及WAL日志。使用操作符类绑定到特定的数据类型和操作符。

到目前为止，操作员类还没有参数。例如，对于全文搜索，可以使用带有一类运算符的GiST索引tsvector_ops（关于此处的GiST运算符的类别））此类运算符使用签名树，并且签名长度是固定的（124个字节）。现在，您可以显式指定长度，这使您可以控制索引大小和效率（哈希冲突数）之间的平衡：

CREATE INDEX ON mail_messages USING gist(tsv tsvector_ops(siglen=1024));

对于其他使用签名树的GiST运算符类，对于启动器也进行了类似的更改，这些签名树适用于hstore，intarray，ltree和pg_trgm扩展。
但是，构想此更改的主要思想是将JSONPath表达式传递给GIN索引的能力，这样就不会为整个JSON文档建立索引，而仅对它的必要部分进行索引。在许多情况下，这将从根本上减少索引的大小。但是这项工作仍有待完成。

奥列格·巴图诺夫（Oleg Bartunov）的想法，尼基塔·格鲁霍夫（Nikita Glukhov）和亚历山大·科罗特科夫（Alexander Korotkov）的实现（均为Postgres Professional的三位）。

添加了运算符<->（框，点）
，缺少的操作也添加到了GiST和SP-GiST的 kNN中。在PG12中使用几何类型point和box您可以使用距离运算符<->(point, box)，它将通过GiST和SP-GiST索引加快搜索速度。但是<->(box, point)，尽管他box已经理解了与更复杂的类型（多边形和圆形）之间的距离，但对他对称的操作员尚未实现。

CREATE TABLE points(point point);
CREATE TABLE boxes(box box);
INSERT INTO points VALUES('1,2','3,4');
INSERT INTO boxes VALUES(box(point(3,4),point(5,6)), box(point(13,14),point(15,16)));

在PG12中：
SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;

:    : box <-> point

如果反之亦然，那么一切正常：

SELECT p.point, b.box, p.point  <-> b.box distance FROM points AS p, boxes AS b ORDER BY distance;

 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403

在PG13中：

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;

 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403

此操作将加快GiST和SP-GiST索引。

请注意，在PG13中，如果您询问：

SELECT count(*) FROM pg_operator WHERE oprname = '<->';
 count 
-------
    28

如果在PG12中执行相同的操作，则将获得20个条目：在第13版中，该列表最多补充了8个运算符。

杰森


支持jsonpath的.datetime（）方法。

这是 PG12没有时间完成的大量 JSONPath补丁程序中不成功的补丁程序之一。 JSON / SQL标准的一部分。问题在于，JSONPath修补程序系列中的所有功能都是不可变的，但是日期比较考虑了当前时区，该时区可能在会话期间发生变化。

在这种情况下，我们允许现有的不可变函数对非不可变比较抛出错误。同时，此修补程序具有带有后缀_tz的功能，该功能在带时区的操作中稳定运行。

新功能-jsonb_set_lax函数

通常，松懈是jsonb函数的非严格（不同于严格）操作模式。在这种情况下，该函数将在其中一个参数为NULL的情况下运行。与严格版本不同-jsonb_set（）-它具有一个附加参数，该参数指示在NULL情况下的操作。选项：use_json_null / raise_exception / return_target / delete_key。感兴趣的用户建议的选项。

优化了一些jsonb函数，做了很多

优化。，主要是通过Nikita Glukhov（Postgres Professional）的努力。但是分析这种情况下的每一个观点都是没有意义的：首先，它们的丰富性将使本来已经很短的文章膨胀；其次，更改与内部设备有关，并非每个用户都感兴趣。因此，我们将仅列出其中的大多数：

  1. 优化函数JsonbExtractScalar（）;
  2. 优化的运算符＃>>，函数jsonb_each_text（），jsonb_array_elements_text（）;
  3. 优化了对get_jsonb_path_all（）中的JsonbContainer类型的识别；
  4. 从轻量级宏JsonbContainerIsXxx（）代替了从JsonbIterator迭代器获取第一个令牌；
  5. 更方便的密钥提取-findJsonbKeyInObject（）;
  6. 优化存储结果findJsonbValueFromContainer（）和getIthJsonbValueFromContainer（）;
  7. get_jsonb_path_all(), ;
    JsonbValueAsText.

, SQL/JSON: JSON_TABLE SQL/JSON: functions. . , . . PG14. JSONPath .


pgbench


用于运行性能测试的实用程序进行了一系列改进。在初始化阶段有关于任务执行的统计信息，更直观的结论，能够查看内置脚本的代码，可以在分区的帐户表上进行测试。

此外，我们添加了\aset与相似的命令\gset，但允许为一次发送的多个请求的变量设置值。发送到服务器以执行的以下行设置变量one和two：
SELECT 1 AS one \; SELECT 2 AS two \aset

pg_dump


pg_dump学会了从第三方表中卸载数据。通过该参数，--include-foreign-data您可以指定第三方服务器的列表，这些服务器的表中的数据将被卸载。

小心使用此卸载。事实并非必须将数据上传到第三方服务器。另外，很有可能在恢复期间第三方服务器将不可用。或第三方服务器只能允许读取，而不能写入数据。

psql


一系列小补丁使psql更舒适：

  • 改进了几个团队的制表符完成度。
  • 除了\echo将字符串发送到STDOUT之外，新命令还将字符串\warn发送到标准错误输出（STDERR）。
  • \d toast- . \d+ .
  • \dt+ «Persistence» (unlogged) (permanent).
  • \e , ; ( \g*). , psql .
  • . PROMPT1 PROMPT2 %x.
  • 新命令访问方法的详细资料：\dAc，\dAf，\dAo，\dAp
  • \g现在，您可以在方括号中指定受支持的任何选项\pset。他们将仅对当前团队采取行动。


libpq


与PostgreSQL相关的微小变化：

重新索引数据库


reindexdb实用程序 的新--jobs参数设置了将在其中同时重建索引的数据库连接数。

pg_rewind


实用程序的局限性正在逐渐消除，可能性正在增加。
首先，pg_rewind 现在可以记录要恢复的信息（因为pg_basebackup可以做到），如果没有通过检查点停止实例，则可以开始恢复和随后关闭实例（必须在之前手动完成）。

其次，pg_rewind学会了使用WAL归档文件。
在该实用程序找到两台服务器之间的WAL分歧点之后，它应该建立一个需要复制到目标群集的所有页面的列表，以消除差异。为此，该实用程序需要从找到的点开始的所有WAL文件。如果必需的WAL文件在目标群集上不可用，则该实用程序之前无法执行其工作。

如果指定了新的-c或--restore-target-wal开关，则使用Alexey Kondratov（Postgres Professional）的此修补程序，pg_rewind将能够使用restore_command参数从日志文件的存档中读取丢失的WAL段。

pg_waldump


pg_waldump将解密准备好的交易记录。

amcheck


amcheck扩展已经学会了更好地识别 B树索引中的损坏。
顺便说一句，现在服务器日志中有关损坏页面的消息对于索引和表将有所不同。

页面检查


该heap_tuple_infomask_flagspageinspect扩展功能解密字段的值 infomask和infomask2函数返回heap_page_items。在调查数据损坏情况时很有用。

postgres_fdw


用户名映射级别的超级用户可以允许普通用户使用没有密码的连接：

ALTER USER MAPPING FOR  SERVER 
    OPTIONS (ADD password_required 'false');

这样做尤其重要，因此sslkey和sslcert可用作连接参数。

管理包


adminpack扩展具有一个新功能- pg_file_sync。使用它，您可以对服务器写入磁盘的文件执行fsync，例如通过pg_file_write或COPY TO。

监控方式


pg_stat_slru


在服务器的共享内存中，不仅有很大的缓冲区高速缓存，而且还有许多其他更简单的高速缓存（例如，用于事务状态的高速缓存）。他们使用一种简单的算法来排挤最不频繁使用的页面（简单的最近最少使用的页面或SLRU）。到现在为止，这样的高速缓存“正常工作”，但是有必要对其进行监视，首先是让PostgreSQL内核的开发人员确定是否需要对其进行更改。为此目的，出现了pg_stat_slru的新视图。

pg_stat_activity


在视图中，pg_stat_activity 新列的leader_id。对于参与并行请求的流程，它会填充前导流程的编号。而领导过程leader_id是一个过程号pid。
以下查询显示哪些查询和哪些进程当前正在并行运行：

SELECT query, leader_pid, 
    array_agg(pid) filter(WHERE leader_pid != pid) AS members
  FROM pg_stat_activity
 WHERE leader_pid IS NOT NULL
 GROUP BY query, leader_pid;

等待事件列表发生了变化。添加了两个新事件：BackupWaitWalArchive和RecoveryPause。另外两个名称更准确：RecoveryWalStream -> RecoveryRetrieveRetryInterval，RecoveryWalAll -> RecoveryWalStream。副本上

又发生了两个新的等待事件：（RecoveryConflictSnapshot与VACUUM冲突，这删除了行的必需版本）和RecoveryConflictTablespace（与删除表空间有关的冲突）。

pg_stat_statements


到目前为止，该扩展程序已将pg_stat_statements带有FOR UPDATE或不带有短语的请求视为同一请求。现在，与FOR UPDATE 的请求将单独记录。

收集的信息量有所增加。从现在开始，不仅记录了有关用于执行命令的资源的信息，而且还记录了有关生成的日记帐分录的统计信息。新的演示文稿列：wal_bytes-生成的记录wal_records数量，-生成的记录wal_num_fpw数量，-整页图像（整页写入）数。

这要归功于准备好的跟踪WAL使用的基础结构。因此，现在EXPLAIN有了一个新选项，WAL它将显示生成的记录的数量：

CREATE TABLE t (id int);
EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF, SUMMARY OFF)
    INSERT INTO t VALUES(1);

              QUERY PLAN              
--------------------------------------
 Insert on t (actual rows=0 loops=1)
   WAL:  records=1  bytes=59
   ->  Result (actual rows=1 loops=1)

拓auto_explain，VACUUM小号VERBOSE，并且autovacuum还使用WAL的创建基础设施，将输出量。

回到pg_stat_statements。如果启用了新参数pg_stat_statements.track_planning，则将为每个操作员记录与调度程序有关的其他统计信息：计划生成数量；总计划时间；一个计划的最短和最长时间，以及均值和标准差。

对分配给调度程序的资源的计费反映在与无关的另一个补丁中pg_stat_statements。EXPLAIN该选项BUFFERS将报告计划阶段使用的缓冲区数量：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) 
    SELECT * FROM pg_class;

                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

杂志


  • log_statement_sample_rate SQL, , log_min_duration_sample( ).
    , log_min_duration_statement , .. log_min_duration_statement , log_min_duration_sample, , log_statement_sample_rate.
    , log_transaction_sample_rate , , .
  • , ( log_min_error_statement), . log_parameter_max_length_on_error. 0, .
    log_parameter_max_length_on_error SQL, , .
    ( log_statements log_duration) , : log_parameter_max_length, , .
  • 现在，您可以将进程类型（pg_stat_activity.backend_type）写入服务器日志。为此log_line_prefix，在参数中提供了特殊符号%b。而且，如果日志以csv（log_destination=csvlog）格式编写，则该列backend_type已包含在其中。


进展


新视图pg_stat_progress_analyze还pg_stat_progress_basebackup使您可以跟踪团队的统计信息收集进度，ANALYZE并分别创建该实用程序的备份副本pg_basebackup。

优化


在计划阶段在FROM子句中计算不可变函数
Aleksandr Kuzmenkov和Aleksandr Parfyonov（均来自Postgres Professional）的补丁在FROM调用包含实际上是常量的函数调用的情况下会有所帮助。在这种情况下，不是建立连接，而是在请求的必要位置替换常量值。

这是与全文搜索相关的查询示例的发生方式：

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q
ORDER BY rank DESC;

                              QUERY PLAN                              
------------------------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, '''tuple'''::tsquery)) DESC
   ->  Bitmap Heap Scan on mail_messages
         Recheck Cond: (tsv @@ '''tuple'''::tsquery)
         ->  Bitmap Index Scan on mail_messages_tsv_idx
               Index Cond: (tsv @@ '''tuple'''::tsquery)

没有连接，并且在计划阶段已经在查询中替换了值'tuple':: tsquery。第12版的情况完全不同：

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank                            
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q                            
ORDER BY rank DESC;

                          QUERY PLAN                         
-----------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, q.q)) DESC
   ->  Nested Loop
         ->  Function Scan on q
         ->  Bitmap Heap Scan on mail_messages
               Recheck Cond: (tsv @@ q.q)
               ->  Bitmap Index Scan on mail_messages_tsv_idx
                     Index Cond: (tsv @@ q.q)


增量排序

当需要通过许多键（k1，k2，k3 ...）进行排序时，调度程序现在可以利用以下知识：已通过多个第一个键（例如，k1和k2）对数据进行了排序。在这种情况下，您无法再次对所有数据重新排序，而是将它们分为具有相同值k1和k2的连续组，并通过键k3进行“排序”。

因此，整个分类分为几个较小的连续类型。这减少了所需的内存量，并且还允许您在所有排序完成之前发出第一个数据。

例如，在票证表的demobase中，在ticket_id列上有一个索引。从索引接收的数据将按ticket_id排序，因此以下查询将使用增量排序：

EXPLAIN (analyze, costs off, timing off)
SELECT * FROM tickets ORDER BY ticket_no, passenger_id;

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

可以使用enable_incrementalsort参数禁用增量排序功能。在这种情况下，排序将花费更长的时间：

SET enable_incrementalsort = off;
EXPLAIN (analyze, costs off, timing off)
SELECT * FROM tickets ORDER BY ticket_no, passenger_id;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Gather Merge (actual rows=2949857 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort (actual rows=983286 loops=3)
         Sort Key: ticket_no, passenger_id
         Sort Method: external merge  Disk: 107024kB
         Worker 0:  Sort Method: external merge  Disk: 116744kB
         Worker 1:  Sort Method: external merge  Disk: 107200kB
         ->  Parallel Seq Scan on tickets (actual rows=983286 loops=3)
 Planning Time: 3.994 ms
 Execution Time: 12291.844 ms

增量排序的想法由Alexander Korotkov（Postgres Professional）于2013年提出，现在，七年后，James Coleman将补丁带到了一个社区接受的州。

TRUNCATE加速
进行TRUNCATE扫描shared_buffers以从共享内存中删除表缓冲区时。以前，对每个表层执行了三次扫描：MAIN（主数据层），FSM（可用空间图），VM（可见性图）。现在，逻辑已更改，而不是三次操作，缓冲区仅扫描一次。值较大时，shared_buffers这将带来明显的收益。

部分减压吐司
当不需要完全阅读TOAST时，只需将其限制在开始或接近开始处的一个切片上，那么完全打开就没有意义。压缩的TOAST会以迭代方式读取：读取一段，如果没有必要的数据，则将其扩展并继续读取。由Google暑期编程学生Binguo Bao提出，他举了一个例子：

CREATE TABLE slicingtest (
     id serial PRIMARY KEY,
     a text
);
INSERT INTO slicingtest (a) SELECT
    repeat('1234567890-=abcdefghijklmnopqrstuvwxyz', 1000000) AS a FROM
    generate_series(1,100);
\timing
SELECT sum(length(substr(a, 0, 20))) FROM slicingtest;

Time: 28.123 ms

使用补丁程序，速度提高了一个数量级：

Time: 2.306 ms

并行VACUUM
在有关此主题的文章中， Yegor Rogov详细解释了并行化中的这一重要步骤。简而言之：“修补泽田正彦，可让您并行执行清洁。该表本身仍由一个（引导）进程清除，但是要清理索引，它现在可以启动后台工作流，每个索引一个。在手动模式下，这可以加快清理具有多个索引的大型表的速度；自动清洁尚未使用此功能。”

粘贴到桌子时自动清洁
对于此补丁程序（也称为Berserk自动真空），我们需要感谢Dorofei Proleskovsky，他提出了以下问题的解决方案：自动清理不会出现在仅附加表中，因为它们没有行的“失效”版本。因此，可见性图不会更新，仅索引扫描无效，并且在进行清理以防止事务计数器溢出时，它需要立即执行大量工作。现在，这种情况已得到解决：自动清洁也将在添加生产线时起作用。出现了两个新的服务器参数（autovacuum_vacuum_insert_threshold和autovacuum_vacuum_insert_scale_factor），类似于修改参数（autovacuum_vacuum_threshold和autovacuum_vacuum_scale_factor）。

哈希聚合内存管理
散列聚合可能需要的RAM比调度程序所考虑的要多，并且要比中指示的要多work_mem。以前，这种调度程序错误导致以下事实：work_mem忽略了大小，并且根据操作或到达OOM Killer的需要分配了足够的内存。现在，该算法可能不会超出范围work_mem，并且如有必要，请使用磁盘上的临时文件。为了控制调度程序的行为，出现了以下参数：enable_groupingsets_hash_disk和enable_hashagg_disk。

使用生成的列优化表的UPDATE
在版本12中，即使此更改没有任何影响，在任何行更新期间都会重新计算生成的列。现在，仅在确实需要时（如果其基本列已更改），才将重新计算它们。

例如，这种优化可以显着加快使用生成的类型列的表的更新tsvector，因为该函数to_tsvector()非常昂贵。

从触发器访问已更改列的列表
一个小补丁，将一个TriggerData已更改列的位图添加到结构中。常规触发器功能（例如tsvector_update_trigger()或）可以使用此信息lo_manage()，以免做不必要的工作。

评估时使用几个高级统计信息
在PG12中，调度程序无法同时对同一张表使用多个高级统计信息。例如，设想一种情况，其中为不同的列集构造了两个高级统计信息，而一组和另一组中的列都参与了查询。现在，计划者可以访问所有可用信息。

并行化和COPY的基础结构（另请参阅此补丁。）
PostgreSQL并发仍然适用于只读查询。编写者有很多困难，其中之一是阻止同时执行一项任务（包括在一个通用并行组中）的进程。可以相信，此类进程的锁不会冲突-例如，多个进程可以在同一表上持有排他锁。这需要内核开发人员的特别注意，但是否则他们将不断陷入僵局。
但是有两个例外：

  • 关系扩展锁，在将新页面添加到数据文件的末尾时捕获，并且
  • 页面锁定，将GIN索引项从等待列表移到主树时使用。

（您可以在本文中阅读更多内容。）
即使在同一并行组的进程之间（实现此补丁程序），此类锁也应发生冲突。但是，这些锁定永远不会导致死锁，因此将它们从扫描中排除。

对于用户而言，通常没有什么变化，但是此补丁很重要，因为，首先，它为并行INSERT和COPY铺平了道路；其次，它消除了高负载条件下的PostgreSQL瓶颈之一（可以在报告中听到）HL ++）。

安全


SKH PRH
素剂已使用现已失效的SKIP协议代替了替换的 EDH素剂（Diffie-Hellman临时钥匙）。

initdb：身份验证
的默认设置已更改initdb启动时，本地和网络连接的默认访问设置已更改。现在，在pg_hba.conf用于本地连接的代替认证方法trust将是peer（或MD5如果不支持对等体），和md5用于网络连接。最初，讨论了更宽松的措施：文档中的警告。再难一点：scram-sha-256。结果，我们决定将自己限制为peer和md5。

使用explicit_bzero
重要补丁。 bzero（）和explicit_bzero（）OS函数将包含的字节写入指示的内存区域'\0'（例如，参见Linux）。这些补丁仅仅是开始：内存中有很多部分可以保留密码和其他敏感信息。我们决定从libpq之类的地方开始，在该地方，带有密码的整个文件在读取.pgpass后可以保留在内存中，从关闭连接后进行清理开始。在be-secure-common.c中，现在覆盖了SSL中输入的秘密短语，该短语出现在错误的行（路径）中。

在libpq中添加了“ password_protocol”参数
此修补程序允许libpq控制在连接期间使用哪种密码传输协议。收到此参数后，如果协议比指定的协议弱，libpq将拒绝认证。默认情况下，此参数plaintext（即所有协议）均适用。

TRUNCATE的强制访问
此修补程序使扩展能够嵌入TRUNCATE操作的强制访问控制（MAC）。现在，其权限将由sepgsql扩展检查。SELinux参考策略和基于Redhat的Linux发行版不支持对db_table {truncate}进行SELinux检查。在这种情况下，将以'deny_unknown'等于1的情况使用sepgsql，并且TRUNCATE将失败。

GUC ssl_passphrase_command的可用性
一个简单但有用的补丁。现在，只有超级用户才能看到ssl_passphrase_command参数的值。该参数指定当需要密码来解密SSL文件（例如私钥）时调用的外部命令。

本土化


版本控制libc归类规则
对于ICU归类规则，版本号存储在数据库中。每次使用该规则（排序，字符比较）时，都会使用OS中ICU库中的当前版本检查保存的版本号，如果出现差异，则会发出警告。这使您发现根据修改后的排序规则构建的某些索引可能不正确，应该重新构建。通过使用命令重建索引，ALTER COLLATION ... REFRESH VERSION可以更新数据库中排序规则的版本，并且不再发出警告。

但这仅适用于ICU。现在，版本号也存储为libc排序规则：

SELECT collname, collversion FROM pg_collation WHERE collname = 'ru_RU';

 collname | collversion
----------+-------------
 ru_RU    | 2.27

当操作系统中的库发生更改时，可以发出警告。鉴于向glibc 2.28的过渡非常相关，在glibc 2.28中，许多排序规则已更改，因此应重建相应的索引。

但是直到他们切换到2.28，一切都变得平静了：
ALTER COLLATION "ru_RU" REFRESH VERSION;

NOTICE:  version has not changed
ALTER COLLATION

全文搜索


全文搜索希腊语
没有评论。

dict_int学会了处理绝对值
。dict_int模板字典（又名扩展名）增加了从数字中删除符号的功能。


CREATE EXTENSION dict_int;
SELECT ts_lexize('intdict', '-123');

ts_lexize
-----------
 {-123}
(1 row)

ALTER TEXT SEARCH DICTIONARY intdict (ABSVAL = true);
SELECT ts_lexize('intdict', '-123');

ts_lexize
-----------
 {123}
(1 row)

即，这次确认了绝对值。

分区


分区表上的
行触发器之前在版本12中，您不能BEFORE在分区表上创建行触发器。请在单独的部分上-但不要一次在整个表格上。现在，BEFORE FOR EACH ROW在分区表上创建的触发器将被自动继承并适用于所有部分。但是条件是，如果它是上的触发器UPDATE，则只能在当前部分中更改其中的分区键。

在逻辑复制中支持分区表
以前，在发布中包括分区表都会导致错误：

CREATE PUBLICATION pub FOR TABLE p;

ERROR:  "p" is a partitioned table
DETAIL:  Adding partitioned tables to publications is not supported.
HINT:  You can add the table partitions individually.

现在可以了。

改进的分段JOIN算法
从第11版开始，调度程序可以逐节连接分区表，但前提是各节的边界完全匹配。现在，该算法已得到改进：如果一个表的部分完全包含在另一个表的部分中，即使它们的大小不匹配（例如，如果一个表按天划分而另一个表按月划分），该算法也将起作用。新算法可用于按范围和列表进行分区。

分段
完全外部联接现在，分段化联接适用于用短语记录的完全外部联接USING。

桌布


在这个有吸引力且充满希望但又困难的领域中，关于PostgreSQL 12并没有根本的进步。没有现成的插件存储（如zheap堆以外的插件存储），但是API的工作仍在继续。

确定表大小的更高层次的抽象方法
Robert Haas重新编写了代码，更改了其结构以支持抽象层，因此将来不再需要复制代码。这是指estimate_rel_size-表的层（叉）的大小。

您可以将表访问方法与relcache一起使用，
此修补程序将表访问方法的内存管理功能引入了索引方法的功能。

tableam和TOAST
TOAST主要用于存储heap因此，在创建新的表访问方法时，您可以采用两种方式：帮助新方法的开发人员将插入，更新和删除TOAST记录集成到其中，或者委托TOAST使用传统的PostgreSQL存储-堆进行编码。一系列5个修补程序使用元组插槽来实现插入/更新/删除操作，并且可以帮助双向执行。

同步


pg_receivewal和pg_recvologic中
的 Fsync错误处理继续对抗 fsync（）。 PostgreSQL认为成功的fsync（）调用意味着文件中的所有数据都已刷新到磁盘，但这并不总是会发生（取决于OS），并且可能导致数据丢失。 PG13认为有必要处理这些实用程序pg_receivewal和pg_recvlogical。当前，默认行为是这样的：这些实用程序会将fsync错误写入日志，恢复连接并继续进行，就好像什么都没发生一样。结果，WAL包含有关已成功复制的文件的信息，而实际上这些文件并未正确刷新到磁盘。因此最好中断该实用程序。还讨论了pg_dump，pg_basebackup，pg_rewind和pg_checksums的命运，但是到目前为止，它们仅限于这两个。

防止对fsync（）设置不正确的标志的保护措施
此修补程序检查在接收到fsync（）的文件描述符时是否正确设置了标志-目录仅开放供读取，文件仅开放用于写入，或两者皆开放。

备份与复制


在到达恢复点之前的恢复过程中暂停
如果在恢复过程中WAL已经结束recovery_target_time并且尚未达到指定的WAL，则服务器将完成恢复并进入正常操作模式。现在事实并非如此。如日志中所报告的，恢复过程将暂停，管理员将有机会插入丢失的WAL段并继续恢复。

ignore_invalid_pages参数
当副本上的恢复过程在WAL记录中找到指向无效页面的链接时，panic-a发生。启用它将有助于启用参数ignore_invalid_pages。恢复将继续，可能会丢失完整性，数据和其他最严重的后果。该参数供服务器开发人员使用，在仍然需要尝试完成恢复并启动副本的情况下，应使用该参数。

在不重新启动
Sergey Kornilov的补丁程序的情况下更改primary_conninfo，该补丁程序允许您更改设置primary_conninfo，primary_slot_name而wal_receiver_create_temp_slot无需重新启动服务器。实际上，为此，他们recovery.conf在第12版中放弃了该文件。 Pg_basebackup

备份
清单现在创建了一个“清单”-一个JSON文件，其中包含有关所进行的备份的信息（文件名和大小，必要的WAL文件以及所有内容的校验和）。
新的pg_validatebackup实用程序检查备份是否符合清单，并使用pg_waldump实用程序检查恢复所需的WAL文件的可用性和正确性（这仅适用于备份本身内部的WAL文件，不适用于存档中的WAL文件）。
这将使您能够检测到备份文件已损坏或消失，或者由于缺少必要的日志文件而使恢复变得不可能的情况。

限制未读数据复制插槽复制
插槽是一种方便但危险的机制：如果客户端未按时从插槽读取数据，则未读WAL记录会占用服务器上的所有空间。现在使用参数max_slot_wal_keep_size您可以限制未读数据可以占用的最大磁盘空间量。如果在下一个检查点发现大小超出限制，则禁用该插槽，并释放该位置。

视窗



Windows 10 对Unix套接字的支持Windows 10支持Unix域套接字，尽管默认情况下禁用它们。

文献资料


文档中有两个新应用程序。
经过长时间的讨论，出现了附录M术语表。词汇表中目前有101个术语。

使用变量来突出显示控制台实用程序的诊断消息的颜色的功能PG_COLOR较早。这是目前记录在附录N.色彩支持。Peter Eisentrout在此修补程序中的初衷是使彩色输出默认处于打开状态。对于那些不想这样做的人，建议显式设置变量NO_COLOR。但是，在讨论补丁的人们中，有更多的反对邮件颜色区分的反对者。因此，他们决定只记录可用的机会。我们在文档中获得了第一级的新部分。


PG13, , PG14 . , . .

More articles:


All Articles