PostgreSQL 12 新特性学习-SQL、配置参数调整

原创柚子身上依 2025-02-24

253

1.SQL调整

PostgreSQL 11 新特性学习-架构调整、SQL、PL/pgSQL功能增强

本节介绍与 SQL 语句相关的新功能。

1.1.ALTER TABLE

现在可以更改某些系统目录的属性。

postgres=#  ALTER TABLE pg_attribute SET (autovacuum_vacuum_scale_factor=0) ;
ALTER TABLE

1.2.ALTER TYPE ADD VALUE

ALTER TYPE ADD VALUE 语句现在在事务块中可用。但是添加的值不能在事务块中使用。

postgres=# create table t_mood (c1 mood);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# ALTER TYPE mood ADD VALUE 'angry';
ALTER TYPE
postgres=# insert into t_mood values('happy');
INSERT 0 1
postgres=# insert into t_mood values('sad');
INSERT 0 1
postgres=# insert into t_mood values('angry');
ERROR:  unsafe use of new value "angry" of enum type mood
LINE 1: insert into t_mood values('angry');
                                  ^
HINT:  New enum values must be committed before they can be used.
postgres=# commit;
ROLLBACK
postgres=# select * from t_mood;
 c1 
----
(0 rows)

1.3.COMMIT/ROLLBACK AND CHAIN

可以添加 CHAIN 子句，该子句在提交或丢弃（ROLLBACK）事务后立即启动新事务。在 COMMIT 语句或 ROLLBACK 语句中指定 AND CHAIN 子句。指定 “AND NO CHAIN” 以显式拒绝 CHAIN 子句。这些语句也可以在使用 PL/pgSQL 的 PROCEDURE 中使用。

postgres=# create table t_chain(id int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into t_chain values(1);
INSERT 0 1
postgres=# commit and chain;
COMMIT
postgres=# insert into t_chain values(2);
INSERT 0 1
postgres=# rollback and chain;
ROLLBACK
postgres=# insert into t_chain values(3);
INSERT 0 1
postgres=# commit;
COMMIT
postgres=# select * from t_chain;
 id 
----
  1
  3
(2 rows)

在以 CHAIN 子句启动的事务中，事务隔离级别等属性与前一个事务保持一致。

postgres=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres=# COMMIT AND CHAIN ;
COMMIT
postgres=# SHOW transaction_isolation ;
 transaction_isolation 
-----------------------
 serializable
(1 row)

postgres=# COMMIT ;
COMMIT
postgres=# SHOW transaction_isolation ;
 transaction_isolation 
-----------------------
 read committed
(1 row)

1.4.COPY

对 COPY 语句进行了以下增强。

COPY FROM

现在可以只存储与 COPY FROM 语句中指定的条件匹配的表数据。

$ echo "id,name,age,department,salary
1,John,28,HR,50000
2,Jane,34,IT,60000
3,Mark,25,HR,45000
4,Lisa,41,IT,80000
5,Paul,38,Finance,75000
6,Susan,32,Finance,72000">employees.csv

postgres=# CREATE TABLE employees (
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     name VARCHAR(100),
postgres(#     age INT,
postgres(#     department VARCHAR(50),
postgres(#     salary INT
postgres(# );
CREATE TABLE

postgres=# COPY employees(id, name, age, department, salary) FROM  /var/lib/postgresql/employees.csv'
WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',')
WHERE age > 30;
COPY 4
postgres=# select * from employees;
 id | name  | age | department | salary 
----+-------+-----+------------+--------
  2 | Jane  |  34 | IT         |  60000
  4 | Lisa  |  41 | IT         |  80000
  5 | Paul  |  38 | Finance    |  75000
  6 | Susan |  32 | Finance    |  72000
(4 rows)

它可以通过 psql 命令的 \copy 命令类似地执行。

postgres=# \copy employees FROM '/var/lib/postgresql/employees.csv' WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',') WHERE age <=30;
COPY 2
postgres=# select * from employees;
 id | name  | age | department | salary 
----+-------+-----+------------+--------
  2 | Jane  |  34 | IT         |  60000
  4 | Lisa  |  41 | IT         |  80000
  5 | Paul  |  38 | Finance    |  75000
  6 | Susan |  32 | Finance    |  72000
  1 | John  |  28 | HR         |  50000
  3 | Mark  |  25 | HR         |  45000
(6 rows)

COPY FREEZE

对分区表执行 COPY FREEZE 语句会导致错误。此规范也适用于 PostgreSQL 11.2 及更高版本。

-- 非分区表
postgres=# begin;
BEGIN
postgres=# CREATE TABLE employees (                                                                                          
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50),
    salary INT
);
CREATE TABLE
postgres=# copy employees FROM '/var/lib/postgresql/employees.csv' WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',',freeze true);
COPY 6

--分区表
postgres=#  CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION 
postgres-# BY RANGE(c1) ;
CREATE TABLE
postgres=# CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM 
postgres-# (0) TO (100) ;
CREATE TABLE
postgres=# CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES FROM 
postgres-# (100) TO (200) ;
CREATE TABLE
postgres=# COPY part1 FROM '/var/lib/postgresql/employees.csv' CSV FREEZE ;
ERROR:  cannot perform COPY FREEZE on a partitioned table

1.5.CREATE AGGREGATE

能够在 CREATE AGGREGATE 语句中使用 OR REPLACE 子句。

postgres=# CREATE OR REPLACE AGGREGATE sum_ints(int) (
     sfunc = int4pl,  -- Function to accumulate sum (addition)
     stype = int4     -- State type is integer
 );
CREATE AGGREGATE
postgres=# CREATE TABLE test_numbers (
    num int
);
CREATE TABLE
INSERT INTO test_numbers (num) VALUES (1), (2), (3), (4), (5);
INSERT 0 5
postgres=# SELECT sum_ints(num) FROM test_numbers;
 sum_ints 
----------
       15
(1 row)

1.6.CREATE COLLATION

可以将 DETERMINISTIC 指定为 CREATE COLLATION 语句中的附加选项。此参数的默认值为 TRUE。

postgres=# CREATE COLLATION my_collation (
postgres(#   LC_COLLATE = 'en_US.UTF-8',
postgres(#   LC_CTYPE = 'en_US.UTF-8',
postgres(#   PROVIDER = 'icu',
postgres(#   DETERMINISTIC = TRUE,
postgres(#   VERSION = '1.0'
postgres(# );
WARNING:  collation "my_collation" has version mismatch
DETAIL:  The collation in the database was created using version 1.0, but the operating system provides version 153.120.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION public.my_collation REFRESH VERSION, or build PostgreSQL with the right library version.
CREATE COLLATION

postgres=# \dO
                              List of collations
 Schema |     Name     |   Collate   |    Ctype    | Provider | Deterministic? 
--------+--------------+-------------+-------------+----------+----------------
 public | my_collation | en_US.UTF-8 | en_US.UTF-8 | icu      | yes
(1 row)

1.7.CREATE INDEX

创建 GiST 索引

include 现在可用于 GiST 索引。

postgres=# CREATE TABLE data2(c1 INT, c2 box, c3 VARCHAR(10)) ;
CREATE TABLE
postgres=# CREATE INDEX idx1_data2 ON data2 USING gist (c2) INCLUDE (c1) ;
CREATE INDEX
postgres=# \d data2
                      Table "public.data2"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           |          | 
 c2     | box                   |           |          | 
 c3     | character varying(10) |           |          | 
Indexes:
    "idx1_data2" gist (c2) INCLUDE (c1)

GiST 索引和 VACUUM

空闲页面在 VACUUM 后可以重用。

创建 GIN 索引时的 WAL

创建GIN 索引时的 WAL 使用量显著减少。

1.8.CREATE STATISTICS

mcv 子句可以在 CREATE STATISTICS 语句中使用。此值表示多变量最常见值（MCV）。它扩展常规 MCV 列表并跟踪最频繁的值组合。获取的统计信息存储在 pg_statistic_ext_data 目录的 stxmcv 列中。

postgres=# CREATE TABLE stat1 (c1 NUMERIC, c2 NUMERIC, c3  
postgres(# VARCHAR(10)) ;
CREATE TABLE
postgres=# CREATE STATISTICS mcv_stat1(mcv) ON c1, c2 FROM 
postgres-# stat1 ;
CREATE STATISTICS

postgres=# insert into stat1 values(1,1,'a');
INSERT 0 1
postgres=# insert into stat1 values(2,2,'b');
INSERT 0 1
postgres=# analyze stat1;
ANALYZE
postgres=# select * from pg_statistic_ext_data\gx
-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
stxoid           | 57387
stxdndistinct    | 
stxddependencies | 
stxdmcv          | \xc251a6e101000000020000000200a4060000a4060000020000001000000010000000ffffffff00000000020000001000000010000000ffffffff0000000004000000008001000400000000800200040000000080010004000000008002000000000000000000e03f000000000000d03f000000000000000000000000e03f000000000000d03f01000100

1.9.CREATE TABLE

对 CREATE TABLE 语句进行了以下增强。

GENERATED 列

生成列是根据表的计算结果定义为列的值。列定义时指定 GENERATED ALWAYS AS （calculation expression） STORED 语句。

postgres=# CREATE TABLE gen1(c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(20) GENERATED ALWAYS AS (c1 || c2) stored);
CREATE TABLE
postgres=# \d gen1
                                              Table "public.gen1"
 Column |         Type          | Collation | Nullable |                        Default                        
--------+-----------------------+-----------+----------+-------------------------------------------------------
 c1     | character varying(10) |           |          | 
 c2     | character varying(10) |           |          | 
 c3     | character varying(20) |           |          | generated always as (((c1::text || c2::text))) stored

不能直接在 INSERT 和 UPDATE 语句中为生成的列指定值。只有 DEFAULT 子句有效。

postgres=# insert into gen1 values('a','d');
INSERT 0 1
postgres=# insert into gen1 values('b','e','a');
ERROR:  cannot insert into column "c3"
DETAIL:  Column "c3" is a generated column.
postgres=# insert into gen1 values('b','e',default);
INSERT 0 1

postgres=# select * from gen1;
 c1 | c2 | c3 
----+----+----
 a  | d  | ad
 b  | e  | be
(2 rows)

生成的列值是在执行 INSERT 或 UPDATE 语句时计算的，计算的值是物理存储的。

postgres=# create extension pageinspect;
CREATE EXTENSION
postgres=#  SELECT heap_page_items(get_raw_page('gen1', 0)) ;
                        heap_page_items                        
---------------------------------------------------------------
 (1,8160,1,31,551,0,0,"(0,1)",3,2306,24,,,"\\x05610564076164")
 (2,8128,1,31,552,0,0,"(0,2)",3,2306,24,,,"\\x05620565076265")
 (3,8096,1,31,553,0,0,"(0,3)",3,2306,24,,,"\\x05620565076265")
(3 rows)

在上面的示例中，对于第一行显示 c1 列是 ‘a’ （= 0x61），c2 列是 ‘b’ （= 0x64），c3 列是 ‘ABCD’ （= 0x6164）。通过pg_attribute 字典的 attgenerated 列中显示 “s” 可以找到生成列。此外information_schema.column_column_usage 表是新添加的。

postgres=#  SELECT attname, attgenerated FROM pg_attribute WHERE attrelid='gen1'::regclass and attname IN ('c1', 'c2', 'c3') ;
 attname | attgenerated 
---------+--------------
 c1      | 
 c2      | 
 c3      | s
(3 rows)
postgres=# SELECT * FROM information_schema.column_column_usage ;
 table_catalog | table_schema | table_name | column_name | dependent_column 
---------------+--------------+------------+-------------+------------------
 postgres      | public       | gen1       | c1          | c3
 postgres      | public       | gen1       | c2          | c3
(2 rows)

不能将生成列指定为分区键。并且无法定义依赖于其他生成列的生成列。

postgres=# CREATE TABLE pgen1(c1 INT, c2 INT, c3 INT GENERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ;
ERROR:  cannot use generated column in partition key
LINE 1: ...NERATED ALWAYS AS (c1 + c2) STORED) PARTITION BY RANGE(c3) ;
                                                                  ^
DETAIL:  Column "c3" is a generated column.

postgres=# CREATE TABLE gen2 (c1 INT, c2 INT GENERATED ALWAYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STORED) ;
ERROR:  cannot use generated column "c2" in column generation expression
LINE 1: ...AYS AS (c1*2) STORED, c3 INT GENERATED ALWAYS AS (c2*2) STOR...
                                                             ^
DETAIL:  A generated column cannot reference another generated column.

分区表定义的 TABLESPACE 子句

现在，在创建分区表时启用 TABLESPACE 子句。在以前的版本中忽略了 TABLESPACE 子句。分区表的 TABLESPACE 子句的值在分区创建时成为默认表空间。

postgres=#  create tablespace ts1 location '/var/lib/postgresql/ts1';
CREATE TABLESPACE
postgres=# CREATE TABLE parttbs(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST(c1) TABLESPACE ts1 ;
CREATE TABLE
postgres=# CREATE TABLE parttbsv1 PARTITION OF parttbs FOR VALUES IN (100) ;
CREATE TABLE

postgres=# \d+ parttbs  
                                   Partitioned table "public.parttbs"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | numeric               |           |          |         | main     |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Partition key: LIST (c1)
Partitions: parttbsv1 FOR VALUES IN ('100')
Tablespace: "ts1"

postgres=# \d+ parttbsv1
                                        Table "public.parttbsv1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | numeric               |           |          |         | main     |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Partition of: parttbs FOR VALUES IN ('100')
Partition constraint: ((c1 IS NOT NULL) AND (c1 = '100'::numeric))
Tablespace: "ts1"
Access method: heap

分区表的 FOR VALUES 子句

现在可以在分区的 FOR VALUES 子句中指定公式或函数，而不仅是文本值。执行 CREATE TABLE 语句时，指定的公式只执行一次，计算值存储在表定义中。

postgres=# CREATE TABLE parttbsv2 PARTITION OF parttbs FOR VALUES IN (power(2,3)) ;
CREATE TABLE
postgres=# \d+ parttbsv2
                                        Table "public.parttbsv2"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | numeric               |           |          |         | main     |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Partition of: parttbs FOR VALUES IN ('8')
Partition constraint: ((c1 IS NOT NULL) AND (c1 = '8'::numeric))
Tablespace: "ts1"
Access method: heap

对分区表的外键引用

分区表可以作为外键引用。

postgres=# CREATE TABLE fkey1(c1 INT PRIMARY KEY, c2 VARCHAR(10)) PARTITION BY RANGE(c1) ;
CREATE TABLE
postgres=# CREATE TABLE fkey1v1 PARTITION OF fkey1 FOR VALUES FROM (0) TO (1000000) ;
CREATE TABLE
postgres=# CREATE TABLE fkey1v2 PARTITION OF fkey1 FOR VALUES FROM (1000000) TO (2000000) ;
CREATE TABLE
postgres=# CREATE TABLE ref1(c1 INT REFERENCES fkey1(c1), c2 VARCHAR(10)) ;
CREATE TABLE

在 PostgreSQL 11 中，尝试创建 ref1 表时，出现 “ERROR: foreign key constraints are not supported on partitioned tables “错误。

索引的 VACUUM 处理

通过在 WITH 子句中指定 VACUUM_INDEX_CLEANUP = OFF，它将能够禁用索引的 VACUUM 进程。默认值为 ‘ON’，VACUUM 将像以前一样执行。指定 TOAST。对于 TOAST 表，VACUUM_INDEX_CLEANUP = OFF。

postgres=# CREATE TABLE vacuum1(c1 INT, c2 VARCHAR(10)) WITH (VACUUM_INDEX_CLEANUP = OFF) ; 
CREATE TABLE
postgres=# insert into vacuum1 values(1,'a');
INSERT 0 1
postgres=# insert into vacuum1 values(2,'b');
INSERT 0 1
postgres=# insert into vacuum1 values(3,'c');
INSERT 0 1
postgres=# \d+ vacuum1
                                         Table "public.vacuum1"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | integer               |           |          |         | plain    |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Access method: heap
Options: vacuum_index_cleanup=off

postgres=# delete from vacuum1 where c1 in(1,2);
DELETE 2
-- 创建表设置为 vacuum_index_cleanup=off，所以索引数据未清理
postgres=# vacuum vacuum1;
VACUUM
postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum1', 0));
 lp | t_xmin | t_xmax | t_ctid |                infomask                 | infomask2 
----+--------+--------+--------+-----------------------------------------+-----------
  1 |        |        |        |                                         | 
  2 |        |        |        |                                         | 
  3 |    568 |      0 | (0,3)  | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH | 
(3 rows)

postgres=# SELECT * FROM bt_page_items('vacuum1_c1_idx', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,1) |      16 | f     | f    | 01 00 00 00 00 00 00 00
          2 | (0,2) |      16 | f     | f    | 02 00 00 00 00 00 00 00
          3 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
(3 rows)

-- vacuum 时添加选择index_cleanup on，索引数据被清理
postgres=# VACUUM (INDEX_CLEANUP on) vacuum1;
VACUUM
postgres=# SELECT * FROM bt_page_items('vacuum1_c1_idx', 1);
 itemoffset | ctid  | itemlen | nulls | vars |          data           
------------+-------+---------+-------+------+-------------------------
          1 | (0,3) |      16 | f     | f    | 03 00 00 00 00 00 00 00
(1 row)

postgres=# select lp, t_xmin, t_xmax, t_ctid,
postgres-#       infomask(t_infomask, 1) as infomask,
postgres-#       infomask(t_infomask2, 2) as infomask2
postgres-# from heap_page_items(get_raw_page('vacuum1', 0));
 lp | t_xmin | t_xmax | t_ctid |                infomask                 | infomask2 
----+--------+--------+--------+-----------------------------------------+-----------
  1 |        |        |        |                                         | 
  2 |        |        |        |                                         | 
  3 |    568 |      0 | (0,3)  | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH | 
(3 rows)

截断表末尾的空块

VACUUM_TRUNCATE 已添加到表的属性中。确定在执行 VACUUM 时是否释放表末尾的截断空块。默认值为 ‘ON’，它像以前一样截断空白区域。当指定为 ‘OFF’ 时，不执行此操作。

postgres=# CREATE TABLE vacuum2(c1 INT, c2 VARCHAR(10)) WITH (VACUUM_TRUNCATE = OFF) ;
CREATE TABLE

postgres=# insert into vacuum2 values(1,'a');
INSERT 0 1
postgres=# insert into vacuum2 values(2,'b');
INSERT 0 1
postgres=# insert into vacuum2 values(3,'c');
INSERT 0 1
postgres=# select * from vacuum2;
 c1 | c2 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)

postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum2', 1));
ERROR:  block number 1 is out of range for relation "vacuum2"
postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum2', 0));
 lp | t_xmin | t_xmax | t_ctid |                infomask                 | infomask2 
----+--------+--------+--------+-----------------------------------------+-----------
  1 |    605 |      0 | (0,1)  | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH | 
  2 |    606 |      0 | (0,2)  | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH | 
  3 |    607 |      0 | (0,3)  | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH | 
(3 rows)

postgres=# \d+ vacuum2
                                         Table "public.vacuum2"
 Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
 c1     | integer               |           |          |         | plain    |              | 
 c2     | character varying(10) |           |          |         | extended |              | 
Access method: heap
Options: vacuum_truncate=off

postgres=# delete from vacuum2;
DELETE 3
postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum2', 0));
 lp | t_xmin | t_xmax | t_ctid |          infomask          |     infomask2      
----+--------+--------+--------+----------------------------+--------------------
  1 |    605 |    608 | (0,1)  | XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
  2 |    606 |    608 | (0,2)  | XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
  3 |    607 |    608 | (0,3)  | XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
(3 rows)

postgres=# select * from vacuum2;
 c1 | c2 
----+----
(0 rows)

postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum2', 0));
 lp | t_xmin | t_xmax | t_ctid |                 infomask                  |     infomask2      
----+--------+--------+--------+-------------------------------------------+--------------------
  1 |    605 |    608 | (0,1)  | XMAX_COMMITTED|XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
  2 |    606 |    608 | (0,2)  | XMAX_COMMITTED|XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
  3 |    607 |    608 | (0,3)  | XMAX_COMMITTED|XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
(3 rows)

-- 表选项 VACUUM_TRUNCATE = OFF，表未截断空快
postgres=# vacuum vacuum2;
VACUUM
postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum2', 0));
 lp | t_xmin | t_xmax | t_ctid | infomask | infomask2 
----+--------+--------+--------+----------+-----------
  1 |        |        |        |          | 
  2 |        |        |        |          | 
  3 |        |        |        |          | 
(3 rows)

-- vacuum指定truncate选项后，表末尾空快被截断
postgres=# vacuum (truncate) vacuum2;
VACUUM
postgres=# select lp, t_xmin, t_xmax, t_ctid,
      infomask(t_infomask, 1) as infomask,
      infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('vacuum2', 0));
ERROR:  block number 0 is out of range for relation "vacuum2"

1.10.EXPLAIN

现在可以在 EXPLAIN 语句中指定 SETTINGS ON 选项。此选项输出与正在从默认值更改的执行计划相关的参数的信息。

postgres=# SET random_page_cost = 1.0 ;
SET                                         ^
postgres=# \d data1
                            Table "public.data1"
 Column |  Type   | Collation | Nullable |              Default              
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('data1_id_seq'::regclass)
 col1   | bytea   |           |          | 
Indexes:
    "data1_pkey" PRIMARY KEY, btree (id)

postgres=# 
postgres=# EXPLAIN (SETTINGS ON) SELECT * FROM data1 WHERE id=100 ;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Scan using data1_pkey on data1  (cost=0.15..2.17 rows=1 width=36)
   Index Cond: (id = 100)
 Settings: random_page_cost = '1'
(3 rows)

1.11.REINDEX CONCURRENTLY

现在可以将 CONCURRENTLY 选项添加到 REINDEX 语句中。通过减小锁定范围，可以使应用程序操作与重新索引共存。此功能是通过临时创建新索引（{index_name}_ccnew）并将其替换为旧索引来实现的。

postgres=# REINDEX (VERBOSE) TABLE CONCURRENTLY data1;
INFO:  index "public.data1_pkey" was reindexed
INFO:  index "pg_toast.pg_toast_40966_index" was reindexed
INFO:  table "public.data1" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.17 s.
REINDEX

根据 REINDEX 语句的更改，reindexdb 命令中也添加了 --concurrently 选项。

$ reindexdb --dbname postgres --echo --concurrently
SELECT pg_catalog.set_config('search_path', '', false);
REINDEX DATABASE CONCURRENTLY postgres;
WARNING:  cannot reindex system catalogs concurrently, skipping al

1.12.PL/pgSQL 附加检查

现在可以为参数 plpgsql.extra_warnings 指定以下值都可以在执行函数时输出其他警告或错误。

指定值为 strict_multi_assignment

当 SELECT INTO 语句输出的列数与输入变量数不匹配时，会发出警告。在下面的示例中，函数中出现两个警告。

postgres=# SET plpgsql.extra_warnings = 'strict_multi_assignment';
SET
postgres=# CREATE OR REPLACE FUNCTION strict1() 
postgres-#   RETURNS void 
postgres-#   LANGUAGE plpgsql 
postgres-# AS $$ 
postgres$#   DECLARE 
postgres$#      x INTEGER ; 
postgres$#      y INTEGER ; 
postgres$#   BEGIN 
postgres$#      SELECT 1 INTO x, y ; 
postgres$#      SELECT 1, 2, 3 INTO x, y ; 
postgres$#   END ; 
postgres$# $$ ;
CREATE FUNCTION
postgres=# SELECT strict1() ;
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
 strict1 
---------
 
(1 row)

指定值为 too_many_rows
当 SELECT INTO 语句返回多条记录时，将生成错误，并且该过程的执行将停止。

postgres=# SET plpgsql.extra_errors to 'too_many_rows' ;
SET
postgres=# DO $$                                        
DECLARE x INTEGER ; 
BEGIN 
SELECT generate_series(1,2) INTO x ; 
RAISE NOTICE 'test output' ; 
END ; 
$$ ;
ERROR:  query returned more than one row
HINT:  Make sure the query returns a single row, or use LIMIT 1.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement

1.13.VACUUM / ANALYZE

VACUUM 和 ANALYZE 语句中添加了以下功能。

SKIP_LOCKED

在锁定的表上执行 VACUUM 或 ANALYZE 语句时，它一直在等待释放锁定。在 PostgreSQL 12 中，添加了一个选项 SKIP_LOCKED 子句来跳过锁定的表。跳过处理时，将输出 WARNING 级别（自动 VACUUM 的 LOG 级别）日志。即使跳过 SQLSTATE，也被视为成功。

--SESSION 1
postgres=# begin;
BEGIN
postgres=# LOCK TABLE data1 IN EXCLUSIVE MODE ;
LOCK TABLE

--SESSION 2
postgres=# VACUUM (SKIP_LOCKED) data1;
WARNING:  skipping vacuum of "data1" --- lock not available
VACUUM
postgres=# \echo :SQLSTATE
00000

选项规范语法

在 VACUUM 语句和 ANALYZE 语句中，现在可以指定使用 TRUE/FALSE 或 ON/OFF 执行的操作。

postgres=# VACUUM (VERBOSE OFF, FULL ON, ANALYZE ON) data1 ;
VACUUM
postgres=# VACUUM (VERBOSE TRUE, FULL TRUE, ANALYZE FALSE) data1 ;
INFO:  vacuuming "public.data1"
INFO:  "data1": found 0 removable, 2 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

将 VACUUM 作为索引选项

通过在 VACUUM 语句中指定 OFF to INDEX_CLEANUP 子句，将能够抑制索引的 VACUUM 进程。如果省略此规范，则操作取决于 table 的 VACUUM_INDEX_CLEANUP 属性。

postgres=# VACUUM (VERBOSE ON, INDEX_CLEANUP OFF) data1 ;
INFO:  vacuuming "public.data1"
INFO:  "data1": found 2 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 754
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "pg_toast.pg_toast_40966"
INFO:  "pg_toast_40966": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 755
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

在表末尾禁止进行自由页截断处理

TRUNCATE 子句已添加到 VACUUM 语句中。通过为此属性指定 OFF，可以在表的末尾禁止自由页删除处理。如果省略，则取决于 table 的 VACUUM_TRUNCATE 属性。

postgres=# VACUUM (VERBOSE ON, TRUNCATE OFF) vacuum2;
INFO:  vacuuming "public.vacuum2"
INFO:  "vacuum2": removed 90 row versions in 1 pages
INFO:  "vacuum2": found 90 removable, 678 nonremovable row versions in 4 out of 4 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 768
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# 
postgres=# 
postgres=# VACUUM (VERBOSE ON, TRUNCATE on) vacuum2;
INFO:  vacuuming "public.vacuum2"
INFO:  "vacuum2": found 0 removable, 678 nonremovable row versions in 4 out of 4 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 768
There were 90 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "vacuum2": truncated 4 to 3 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

1.14.WITH SELECT

之前版本中 WITH 子句中指定的公共表表达式（CTE）都是 MATERIALIZED。在 PostgreSQL 12 中，默认行为已更改为 NOT MATERIALIZED。为了更改这些行为，可以在 WITH 子句中指定 MATERIALIZED 或 NOT MATERIALIZED。指定 NOT MATERIALIZED 子句允许将 WHERE 子句的规范下推到 WITH 子句中。在下面的示例中，使用 NOT MATERIALIZED 子句表明选择了索引搜索，并且成本降低了。

-- WITH NOT MATERIALIZED
postgres=# EXPLAIN analyze WITH s AS NOT MATERIALIZED (SELECT * FROM data1) SELECT * FROM s WHERE id=100 ;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using data1_pkey on data1  (cost=0.29..8.30 rows=1 width=36) (actual time=0.016..0.017 rows=1 loops=1)
   Index Cond: (id = 100)
 Planning Time: 0.061 ms
 Execution Time: 0.028 ms
(4 rows)

-- WITH MATERIALIZED
postgres=# EXPLAIN analyze WITH s AS MATERIALIZED (SELECT * FROM data1) SELECT * FROM s WHERE id=100 ;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 CTE Scan on s  (cost=130.00..332.50 rows=45 width=36) (actual time=0.028..2.136 rows=1 loops=1)
   Filter: (id = 100)
   Rows Removed by Filter: 8999
   CTE s
     ->  Seq Scan on data1  (cost=0.00..130.00 rows=9000 width=36) (actual time=0.011..0.801 rows=9000 loops=1)
 Planning Time: 0.055 ms
 Execution Time: 2.296 ms
(7 rows)

1.15.Functions

添加/增强了以下功能。

SQL/JSON

提供了 SQL 2016 标准中提出的一些与 SQL/JSON 相关的功能。

jsonb_path_query_array

postgres=# SELECT jsonb_path_query_array('{"a":[1,2,3,4,5]}','$.a[*] ? (@ >= $min && @ <= $max)', '{"min":2,"max":4}') ;
 jsonb_path_query_array 
------------------------
 [2, 3, 4]
(1 row)

添加了以下函数。
JSON/SQL 函数

函数名称	描述
jsonb_path_exists	Checks whether the JSON path returns any item for the specified JSON value.
jsonb_path_match	Returns JSON path predicate result for the specified JSON value. Only the first result item is taken into account.
jsonb_path_query	Gets all JSON items returned by JSON path for the specified JSON value.
jsonb_path_query_array	Gets all JSON items returned by JSON path for the specified JSON value and wraps result into an array.
jsonb_path_query_first	Gets the first JSON item returned by JSON path for the specified JSON value.

pg_partition_tree

pg_partition_tree 是显示分区表的树状结构的功能。它还支持分层分区结构。在参数中指定分区表。指定分区表或分区以外的对象名称将返回 NULL。

postgres=# SELECT * FROM pg_partition_tree('part1') ;
  relid  | parentrelid | isleaf | level 
---------+-------------+--------+-------
 part1   |             | f      |     0
 part1v1 | part1       | t      |     1
 part1v2 | part1       | t      |     1
(3 rows)

postgres=# SELECT * FROM pg_partition_tree('part1v1') ;
  relid  | parentrelid | isleaf | level 
---------+-------------+--------+-------
 part1v1 | part1       | t      |     0
(1 row)

postgres=# SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total FROM pg_partition_tree('part1') ;
 total 
-------
 32 kB
(1 row)
postgres=# SELECT * FROM pg_partition_tree('data1') ;
 relid | parentrelid | isleaf | level 
-------+-------------+--------+-------
(0 rows)

pg_partition_root

pg_partition_root 是一个函数，用于返回指定分区的顶级分区表名称。在以下示例中，创建了一个子分区并执行了 pg_partition_root 函数。

postgres=# CREATE TABLE part2(c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) PARTITION BY LIST(c1) ;
CREATE TABLE
postgres=# CREATE TABLE part2v1 PARTITION OF part2 FOR VALUES IN (100) PARTITION BY LIST (c2) ;
CREATE TABLE
postgres=# CREATE TABLE part2v1v1 PARTITION OF part2v1 FOR  VALUES IN (200) ;
CREATE TABLE
postgres=# SELECT pg_partition_root('part2v1v1') ;
 pg_partition_root 
-------------------
 part2
(1 row)

pg_partition_ancestors

pg_partition_ancestors 函数将列表打印到包含指定分区的分区表的父级。

postgres=# CREATE TABLE part3(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST(c1) ; 
CREATE TABLE                                                ^
postgres=# CREATE TABLE part3v1 PARTITION OF part3 FOR VALUES IN (100) ;
CREATE TABLE
postgres=# SELECT pg_partition_ancestors('part3v1') ;
 pg_partition_ancestors 
------------------------
 part3v1
 part3
(2 rows)

pg_promote

此函数将备用实例提升为主实例。以前需要执行 pg_ctl promote 命令。设置是否等待的参数（默认为 true）和等待的秒数（默认为 60 秒）。如果处理失败或未在等待时间内完成提升主库，则此函数返回 false，否则返回 true。

-- 只能在recovery的库执行
postgres=# SELECT pg_promote(true, 90) ; 
pg_promote ------------ 
t 
(1 row)

pg_ls_tmpdir

添加了 pg_ls_tmpdir 函数，以返回已保存临时数据的文件名列表。在参数中指定 TABLESPACE 的 OID。如果省略，则假定指定了 pg_default。此功能需要 SUPERUSER 权限或 pg_monitor 角色。

postgres=# SELECT * FROM pg_ls_tmpdir() ; 
name       |   size    |      modification 
------------------+-----------+------------------------ 
pgsql_tmp36911.6 | 148955136 | 2024-10-04 11:57:36-04 
pgsql_tmp37050.0 | 139722752 | 2024-10-04 11:57:36-04 
pgsql_tmp37051.0 | 138403840 | 2024-10-04 11:57:36-04 
(3 rows)

pg_ls_archive_statusdir

新增 pg_ls_archive_statusdir 功能，用于获取归档文件的状态。此函数搜索 ${PGDATA}/pg_wal/archive_status 目录，并输出文件名、大小和修改日期。它不会输出存档的 WAL 文件的实际信息。此功能需要 SUPERUSER 权限或 pg_monitor 角色。

postgres=# SELECT * FROM pg_ls_archive_statusdir() ; 
name              | size |      modification 
-------------------------------+------+------------------------ 
00000001000000000000000D.done |    
00000001000000000000000E.done |    
00000001000000000000000F.done |    
(3 rows)

date_trunc

此函数现在可以设置 Timezone。

postgres=# SELECT date_trunc('hour', TIMESTAMP WITH TIME ZONE '2025-01-24 20:38:40+00', 'Asia/Tokyo') ;
       date_trunc       
------------------------
 2025-01-24 20:00:00+00
(1 row)

unnest

unnest 函数新增了support属性,优化器可以更准确的预估行。

PostgreSQL 11


postgres=# explain analyze select * from unnest(array[1,2,3]);
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Function Scan on unnest  (cost=0.00..1.00 rows=100 width=4) (actual time=0.043..0.043 rows=3 loops=1)
 Planning Time: 0.154 ms
 Execution Time: 0.080 ms
(3 rows)

PostgreSQL 12

postgres=# explain analyze select * from unnest(array[1,2,3]);
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Function Scan on unnest  (cost=0.00..0.03 rows=3 width=4) (actual time=0.004..0.005 rows=3 loops=1)
 Planning Time: 0.029 ms
 Execution Time: 0.013 ms
(3 rows)

postgres=# \sf unnest(anyarray);
ERROR:  expected a right parenthesis
postgres=# \sf unnest(anyarray) 
CREATE OR REPLACE FUNCTION pg_catalog.unnest(anyarray)
 RETURNS SETOF anyelement
 LANGUAGE internal
 IMMUTABLE PARALLEL SAFE STRICT ROWS 100 SUPPORT array_unnest_support
AS $function$array_unnest$function$

双曲函数

添加了 SQL：standards 2016 中包含的以下双曲函数

函数名称	描述
log10	base 10 logarithm
sinh	hyperbolic sine
cosh	hyperbolic cosine
tanh	hyperbolic tangent
asinh	inverse hyperbolic sine
acosh	inverse hyperbolic cosine
atanh	inverse hyperbolic tangent

postgres=# SELECT log10(100), sinh(1) ;
 log10 |        sinh        
-------+--------------------
     2 | 1.1752011936438014
(1 row)

拷贝复制槽

提供了创建现有复制槽副本的函数。pg_copy_physical_replication_slot 函数和 pg_copy_logical_replication_slot 函数根据复制槽的类型提供。使用的复制槽才能创建副本。

postgres=# SELECT pg_create_physical_replication_slot('slot_1') ;
 pg_create_physical_replication_slot 
-------------------------------------
 (slot_1,)
(1 row)

postgres=# SELECT pg_copy_physical_replication_slot('slot_1','slot_c') ; 
ERROR:  cannot copy a replication slot that doesn't reserve WAL

2.配置参数

在 PostgreSQL 12 中以下参数已更改。

2.1.增加参数

参数名称	描述	默认值
archive_cleanup_command	Migrate from recovery.conf (sighup)	-
data_sync_retry (Also added to 11.2)	Behavior when fsync system call fails (postmaster)	off
default_table_access_method	Default storage engine name (user)	heap
log_statement_sample_rate	Rate of output of SQL statement to log (superuser)	1
plan_cache_mode	Changed the behavior of caching execution plans (user)	auto
primary_conninfo	Migration from recovery.conf file (postmaster)	-
primary_slot_name	Migrate from recovery.conf (postmaster)	-
promote_trigger_file	Migrate from recovery.conf (sighup)	-
recovery_end_command	Migrate from recovery.conf (sighup)	-
recovery_min_apply_delay	Migrate from recovery.conf (sighup)	0
recovery_target	Migrate from recovery.conf (postmaster)	-
recovery_target_action	Migrate from recovery.conf (postmaster)	pause
recovery_target_inclusive	Migrate from recovery.conf (postmaster)	on
recovery_target_lsn	Migrate from recovery.conf (postmaster)	-
recovery_target_name	Migrate from recovery.conf (postmaster)	-
recovery_target_time	Migrate from recovery.conf (postmaster)	-
recovery_target_timeline	Migrate from recovery.conf (postmaster)	latest
recovery_target_xid	Migrate from recovery.conf (postmaster)	-
restore_comand	Migrate from recovery.conf (postmaster)	-
shared_memory_type	Type of shared memory (postmaster)	OS depend
ssl_library	Name of library providing SSL function (internal)	-
ssl_max_protocol_version	Maximum version of SSL protocol to support	-
ssl_min_protocol_version	Minimum version of SSL protocol to support (sighup)	TLSv1
tcp_user_timeout	TCP timeout specification (user)	0
wal_init_zero	Fill-in WAL file to zero (superuser)	on
wal_recycle	Recycle WAL File (superuser)	on

primary_conninfo

指定主实例的连接字符串。之前是在 recovery.conf 文件中指定的。application_name 项的默认值通常是 “walreceiver”，但是当指定 cluster_name 参数时，cluster_name的值已更改为默认值。pg_stat_replication 中 application_name 列的值将更改。

ssl_library

此参数指示提供 SSL 功能的库的名称。在 Red Hat Enterprise Linux 环境中执行 configure 命令时，如果指定了 --with-openssl，则参数值为 “OpenSSL”。

postgres=# show ssl_library ;
 ssl_library 
-------------
 OpenSSL
(1 row)

shared_memory_type

此参数指定共享内存的类型（例如 shared_buffers）。

参数名称	描述	默认值
mmap	Use an anonymous memory map.	mmap
sysv	Use System V shared memory.	shmget
windows	Use Windows shared memory.	CreateFileMapping

此参数在 Linux 上的默认值为 mmap。这与 PostgreSQL 9.3 及更高版本的行为相同。非常小的 System V 共享内存和大多数共享内存是使用内存映射文件（mmap）配置的。将此参数设置为 sysv 允许您恢复到 PostgreSQL 9.2 之前的行为。在这种情况下，请使用 System V 共享内存配置所有共享内存。

plan_cache_mode

此参数设置缓存准备好的语句（由 PREPARE 语句创建）的执行计划的方法。默认值为 auto，这与以前的版本的行为相同。通常，每次 EXECUTE 语句执行 PREPARE 语句创建的 SQL 语句时，都会生成一个执行计划。在下面的示例中，可以看到执行计划根据 EXECUTE 语句中指定的参数而变化。它表示存储在 C2 列中的数据不均匀，plan0 数据很少（执行索引搜索），并且 plan1 数据很多（搜索整个表）。

postgres=> PREPARE sel1(VARCHAR) AS SELECT * FROM plan1 WHERE c2=$1 ; 
PREPARE 
postgres=> EXPLAIN EXECUTE sel1('plan0') ; 
                               QUERY PLAN ----------------------------------------------------------------------- 
 Index Scan using idx1_plan1 on plan1  (cost=0.42..4.44 rows=1 width=12) 
   Index Cond: ((c2)::text = 'plan0'::text) 
(2 rows) 
postgres=> EXPLAIN EXECUTE sel1('plan1') ; 
                           QUERY PLAN ---------------------------------------------------------------- 
 Seq Scan on plan1  (cost=0.00..17906.01 rows=1000001 width=12) 
   Filter: ((c2)::text = 'plan1'::text) 
(2 rows)

如果执行相同的 SQL 语句 5 次或更多次，则可能会缓存执行计划，即使下次更改参数，也可以使用缓存的执行计划（通用执行计划）。在下面的示例中，执行计划中的显示在执行第 6 个 EXPLAIN 语句时从文本值更改为 $1。

postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ;   --执行5次
                           QUERY PLAN 
---------------------------------------------------------------- 
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12) 
   Filter: ((c2)::text = 'plan1'::text) 
(2 rows) 
postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ;  --第5次
                           QUERY PLAN 
---------------------------------------------------------------- 
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12) 
   Filter: ((c2)::text = ($1)::text) 
(2 rows)

新添加的参数 plan_cache_mode 会更改此行为。将参数值设置为 “force_custom_plan” 会关闭执行计划缓存。另一方面，将参数值设置为 “force_generic_plan” 会立即启用执行计划缓存。

postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Repeat 5 times 
                           QUERY PLAN 
---------------------------------------------------------------- 
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12) 
   Filter: ((c2)::text = 'plan1'::text) 
(2 rows) 
postgres=> EXPLAIN ANALYZE EXECUTE sel1('plan1') ; -- Sixth (execution 
plan changed) 
                           QUERY PLAN 
---------------------------------------------------------------- 
 Seq Scan on plan1  (cost=0.00..23311.01 rows=1000001 width=12) 
   Filter: ((c2)::text = ($1)::text) 
(2 rows)

data_sync_retry

此参数确定在检查点期间发出的 fsync 系统调用失败时会发生什么情况。在以前的版本中，fsync 函数被重新执行（data_sync_retry = “on”），如果 fsync 系统调用失败，新版本的默认行为（data_sync_retry = “off”）会导致实例因 PANIC 而停止。此参数是从 PostgreSQL 11.2 开始添加的。

2.2.修改参数

更改了以下配置参数的设置范围和选项。

参数名称	描述
client_min_messages	It is no longer possible to set it to a higher level than ERROR.
dynamic_shared_memory_type	The setting value “none” has been deleted.
log_autovacuum_min_duration	Log output contents now change according to VACUUM execution status.
log_connections	Application_name information has been added to the log.
plpgsql.extra_warnings	The following parameter values have been added:
	-too_many_rows
	-strict_multi_assignme
trace_sort	The log output message has been changed.
wal_level	It is now checked for proper level at startup.
wal_sender_timeout	The context has been changed from sighup to user.
default_with_oids	It cannot be set to “on”.
recovery_target_timeline	“Current” has been added as a setting value, the default value has been changed to “latest”.
autovacuum_vacuum_cost_delay	Data type changed from integer to real.

不能从 pg_settings 目录中引用 default_with_oids 参数。

postgres=# show default_with_oids;
 default_with_oids 
-------------------
 off
(1 row)

postgres=# select * from pg_settings where name ~'default_with_oids'\gx
(0 rows)

postgres=# alter system set default_with_oids='on';
ERROR:  tables declared WITH OIDS are not supported

wal_sender_timeout
用户可以按会话更改此参数。这允许在流复制环境中基于每个连接更改从库的参数。

PostgreSQL 11

postgres=# set wal_sender_timeout to '2min';
ERROR:  parameter "wal_sender_timeout" cannot be changed now

PostgreSQL 12

postgres=# set wal_sender_timeout to '2min';
SET

$ grep primary_conninfo data/postgresql.conf 
primary_conninfo = 'host=host1 port=5432 user=postgres 
password=password options=''-c wal_sender_timeout=5000'''

log_connections

当此参数设置为 ‘on’ 时，application_name 参数的值将添加到输出的日志中。

Connection from psql command  
LOG:  connection authorized: user=postgres database=postgres 
application_name=psql 
 
Connection from pg_basebackup command 
LOG:  replication connection authorized: user=postgres 
application_name=pg_baseback 
 
Connection from Streaming Replication slave instance 
LOG:  replication connection authorized: user=postgres 
application_name=walreceiver

trace_sort

当此参数设置为 ‘on’ 时，输出日志格式已更改。

PostgreSQL 11

LOG:  -1 switching to external sort with 16 tapes: CPU: user: 0.00 
s, system: 0.00 s, elapsed: 0.00 s 
LOG:  -1 using 3951 KB of memory for read buffers among 15 input 
tapes 
LOG:  performsort of -1 done (except 15-way final merge): CPU: 
user: 0.15 s, system: 0.01 s, elapsed: 0.16 s

PostgreSQL 12

LOG:  performsort of worker -1 starting: CPU: user: 0.00 s, 
system: 0.00 s, elapsed: 0.00 s 
LOG:  internal sort of worker -1 ended, 25 KB used: CPU: user: 
0.00 s, system: 0.00 s, elapsed: 0.00 s

wal_level

使用复制槽时，需要检查 wal_level 参数在实例启动期间是否为适当的值。如果未设置需要的级别，实例启动将失败。
在 Logical Replication 环境中更改wal_level 为 “minimal”，并重新启动实例报错:

postgres=# ALTER SYSTEM SET wal_level=minimal ; 
ALTER SYSTEM 
postgres=# \q 
$ 
$ pg_ctl -D data restart 
waiting for server to shut down.... done 
server stopped 
waiting for server to start....2025-02-04 00:52:19.890 EDT [22682] 
FATAL:  WAL archival cannot be enabled when wal_level is "minimal" 
stopped waiting 
pg_ctl: could not start server 
Examine the log output. 
$

2.3.参数默认值修改

以下配置参数的默认值已更改。

参数名称	PostgreSQL 11	PostgreSQL 12
autovacuum_vacuum_cost_delay	20	2
extra_float_digits	0	1
jit	off	on
recovery_target_timeline	current	latest
server_version	11.22	12.22
server_version_num	110022	120020

参考

《PostgreSQL_12_GA_New_Features_en_20191011-1》

墨力计划 postgresql

最后修改时间：2025-02-27 12:31:46

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

PostgreSQL 笔记（共21篇）

学习笔记记录

PostgreSQL 12 新特性学习-SQL、配置参数调整

1.SQL调整

1.1.ALTER TABLE

1.2.ALTER TYPE ADD VALUE

1.3.COMMIT/ROLLBACK AND CHAIN

1.4.COPY

1.5.CREATE AGGREGATE

1.6.CREATE COLLATION

1.7.CREATE INDEX

1.8.CREATE STATISTICS

1.9.CREATE TABLE

1.10.EXPLAIN

1.11.REINDEX CONCURRENTLY

1.12.PL/pgSQL 附加检查

1.13.VACUUM / ANALYZE

1.14.WITH SELECT

1.15.Functions

2.配置参数

2.1.增加参数

2.2.修改参数

2.3.参数默认值修改

参考

文章被以下合辑收录

评论