1.配置参数
PostgreSQL 10 新特性学习-参数、实用程序、扩展调整
1.1.增加参数
| 参数名称 | 描述 | 默认值 |
|---|---|---|
| data_directory_mode | Database cluster protection mode (internal) | 0700 |
| enable_parallel_append | Enabled Parallel Append feature (user) | on |
| enable_parallel_hash | Enabled Parallel Hash feature (user) | on |
| enable_partition_pruning | Enabled partition pruning (user) | on |
| enable_partitionwise_aggregate | Enabled Partition-Wise Join feature (user) | off |
| enable_partitionwise_join | Enabled Partition-Wise Aggregate feature (user) | off |
| jit | Enabled JIT compilation feature (user) | on |
| jit_above_cost | The Cost to enable JIT compile function (user) | 100000 |
| jit_debugging_support | Enable debugger at JIT compilation (superuser-backend) | off |
| jit_dump_bitcode | Add LLVM bit code (superuser) | off |
| jit_expressions | Enable JIT expression (user) | on |
| jit_inline_above_cost | The cost to decide whether to make JIT inlining (user) | 500000 |
| jit_optimize_above_cost | The cost to decide whether to perform JIT optimization (user) | 500000 |
| jit_profiling_support | Enabled perf profiler (superuser-backend) | off |
| jit_provider | JIT provider name (postmaster) | llvmjit |
| jit_tuple_deforming | Enabled JIT tuple deforming (user) | on |
| max_parallel_maintenance_workers | Maximum number of parallel workers used in maintenance processing (user) | 2 |
| parallel_leader_participation | Change the behavior of the leader process (user) | on |
| ssl_passphrase_command | Command to get SSL connection passphrases (sighup) | ‘’ |
| ssl_passphrase_command_supports_reload | Whether to use ssl_passphrase_command when reloading (sighup) | off |
| vacuum_cleanup_index_scale_factor | The ratio of the number of INSERTs that perform index cleanup (user) | 0.1 |
执行并行查询时，协调整个查询的领导进程和工作进程相互协作。
--leader+2个worker 并行，可以看到 rows=3333333 loops=3
postgres=# set parallel_leader_participation to 'on';
SET
postgres=# explain analyze select count(1) from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=116778.55..116778.56 rows=1 width=8) (actual time=511.237..512.908 rows=1 loops=1)
-> Gather (cost=116778.33..116778.54 rows=2 width=8) (actual time=511.148..512.901 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=115778.33..115778.34 rows=1 width=8) (actual time=507.076..507.077 rows=1 loops=3)
-> Parallel Seq Scan on test (cost=0.00..105361.67 rows=4166667 width=0) (actual time=0.033..313.232 rows=3333333 loops=3)
Planning Time: 0.058 ms
Execution Time: 512.938 ms
(8 rows)
--只有2个worker 并行，可以看到 rows=5000000 loops=2
postgres=# set parallel_leader_participation to 'off';
SET
postgres=# explain analyze select count(1) from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=127195.21..127195.22 rows=1 width=8) (actual time=659.560..661.155 rows=1 loops=1)
-> Gather (cost=127195.00..127195.21 rows=2 width=8) (actual time=659.553..661.149 rows=2 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=126195.00..126195.01 rows=1 width=8) (actual time=653.486..653.487 rows=1 loops=2)
-> Parallel Seq Scan on test (cost=0.00..113695.00 rows=5000000 width=0) (actual time=0.076..403.044 rows=5000000 loops=2)
Planning Time: 0.059 ms
Execution Time: 661.186 ms
(8 rows)
1.2.调整参数
| 参数名称 | 调整 |
|---|---|
| log_parser_stats | Memory information will be added to the log when the setting value is changed to “on” |
| log_statement_stats | Memory information will be added to the log when the setting value is changed to “on” |
| log_planner_stats | Memory information will be added to the log when the setting value is changed to “on” |
| log_executor_stats | Memory information will be added to the log when the setting value is changed to “on” |
| wal_segment_size | UNIT of pg_settings catalog changed from 8kB to B |
当该值设置为 “on” 时，信息输出会增加。其他输出信息因操作系统而异。以下示例适用于 Linux。
- log_parser_stats参数
log_parser_stats 设置为on 日志中比之前多输出内存的消耗信息。
postgres=# set log_parser_stats to 'on';
SET
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# set client_min_messages =log;
SET
postgres=# select 1;
LOG: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000025 s system, 0.000023 s elapsed
! [0.002958 s user, 0.005589 s system total]
! 6192 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [0/1229] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [8/3] voluntary/involuntary context switches
LOG: PARSE ANALYSIS STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000009 s system, 0.000010 s elapsed
! [0.002958 s user, 0.005664 s system total]
! 6192 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [0/1229] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [8/3] voluntary/involuntary context switches
LOG: REWRITER STATISTICS
DETAIL: ! system usage stats:
! 0.000000 s user, 0.000004 s system, 0.000003 s elapsed
! [0.002958 s user, 0.005706 s system total]
! 6192 kB max resident size
! 0/0 [0/0] filesystem blocks in/out
! 0/0 [0/1229] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [8/3] voluntary/involuntary context switches
?column?
----------
1
(1 row)
1.3.参数默认值改变
| 参数名称 | PostgreSQL 10 | PostgreSQL 11 |
|---|---|---|
| server_version | 10.21 | 11.16 |
| server_version_num | 100021 | 110016 |
1.4.移除的参数
| 参数名称 | 替代 |
|---|---|
| replacement_sort_tuples | None |
1.5.认证参数变更
- 实例连接字符串
| 参数名称 | 默认值 | 描述 |
|---|---|---|
| replication | false | Specify whether to use replication protocol |
| true: Physical replication | ||
| database: logical replication |
sslcompression 参数的默认值已更改为 “off”。
2.实用程序
本节介绍实用程序命令的主要增强功能。
2.1.psql
psql 命令中添加了以下功能。
- \gdesc
添加了 \gdesc 命令，用于显示最近执行的查询的列名和数据类型。
postgres=# select 1 c1 \gdesc
Column | Type
--------±--------
c1 | integer
(1 row)
- Pager 规范
作为指定 psql 命令使用的pager的环境变量，PSQL_PAGER 比 PAGER 优先。
$ export PSQL_PAGER="less -SFX"
$ psql
postgres=# select * from pg_settings ;
- 查询状态
与 SQL 语句的执行状态相关的变量现在可用。结合 \if 命令，现在可以在 SQL 脚本中处理 SQL 语句执行结果。LAST_ERROR_MESSAGE 和 LAST_ERROR_SQLSTATE 变量在错误后即使另一个 SQL 语句成功，也会保留其内容。
| 变量 | 描述 |
|---|---|
| ERROR | True if the most recently executed SQL failed |
| LAST_ERROR_MESSAGE | The error message of the last error |
| LAST_ERROR_SQLSTATE | The error code of the last error |
| ROW_COUNT | Number of tuples processed by the most recently executed SQL statement |
| SQLSTATE | Execution status code of the most recently executed SQL statement |
postgres=# SELECT * FROM t8;
ERROR: relation "t8" does not exist
LINE 1: SELECT * FROM t8;
^
postgres=# \echo :ERROR
true
postgres=# \echo :SQLSTATE
42P01
postgres=# select 1;
?column?
----------
1
(1 row)
postgres=# \echo :ROW_COUNT
1
postgres=# \echo :LAST_ERROR_MESSAGE
relation "t8" does not exist
postgre
- exit and quit
“exit” 和 “quit” 命令现在可用于终止 psql 命令。以前，唯一的 \q 可用。
postgres@73a05a20a995:~$ psql
psql (11.16 (Debian 11.16-1.pgdg90+1))
Type "help" for help.
postgres=# quit
postgres@73a05a20a995:~$ psql
psql (11.16 (Debian 11.16-1.pgdg90+1))
Type "help" for help.
postgres=# exit
postgres@73a05a20a995:~$
- 定义参数检查
用于检查是否定义了变量的语法 “:{?variable_name}” 现在可用。
postgres=# \set TESTVAL 1
postgres=# \echo :{?TESTVAL}
TRUE
postgres=# \echo :TESTVAL
1
postgres=# \echo :{?TESTVAL1}
FALSE
2.2.initdb
对 initdb 命令实施了以下增强功能。
- --wal-segsize
添加了一个 “–wal-segsize” 选项来指定 WAL 文件的大小。此选项可以指定为 2 的倍数并且是 1~1024MB。默认值为16 MB。除此之外在安装时执行的 “configure” 命令的 “–with-wal-segsize” 选项已过时。
$ initdb --wal-segsize=128 data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
…
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
- --allow-group-access
添加了 --allow-group-access 选项（或 -g 选项），以允许组访问数据库集群访问模式。如果指定此选项，则组的读/执行权限将添加到数据库集群的目录保护模式中，数据库集群参数 data_directory_mode 更改为 0750。
$ initdb --allow-group-access data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.
…
pg_ctl -D data -l logfile start
$ ls -ld data
drwxr-x---. 19 postgres postgres 4096 May 25 13:32 data
2.3.pg_dump / pg_dumpall
pg_dump 和 pg_dumpall 命令中添加了以下选项。
- --load-via-partition-root
--load-via-partition-root 选项已添加到 pg_dump 和 pg_dumpall 命令中。指定此选项后，在加载数据时，将通过分区表的根表加载数据，而不是通过单个分区加载数据。
$ pg_dump -d postgres -t hashtab1 --load-via-partition-root -t hashtab1_p1 -t hashtab1_p2 -f hashtab3.sql
$ more hashtab3.sql
...
部分省略
...
COPY public.hashtab1 (c1, c2) FROM stdin;
4 a
7 a
\.
--
-- Data for Name: hashtab1_p2; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.hashtab1 (c1, c2) FROM stdin;
1 a
3 a
5 a
8 a
\.
...
部分省略
...
- --encoding
--encoding 选项（或 -E 参数）已添加到 pg_dumpall 命令中。与 pg_dump 命令一样，此选项指定输出数据的字符编码。
$ pg_dump -d postgres -t hashtab1_p2 -E utf8 > 1.sql
- --no-comments
pg_dump 命令中添加了 --no-comments 选项。如果指定了此选项，则不会加载 COMMENT。
$ pg_dump -t hashtab1 --no-comments
2.4.pg_receivewal
pg_receivewal 命令中添加了以下选项。
- --endpos
添加了 --endpos 选项（或 -E 选项）以指定命令将停止的 LSN。这与 PostgreSQL 10 中添加到 pg_recvlogical 命令的 --endpos 选项相同。
pg_receivewal --endpos=0/6C32A8E0 -D waldir
- --no-sync
添加了 --no-sync 选项。指定此选项后写入数据时不会执行同步系统调用。
$ pg_receivewal -D waldir --no-sync
2.5.pg_ctl
实现了从命令发送 SIGKILL 信号pg_ctl功能。
$ pg_ctl --help|grep -i kill
pg_ctl kill SIGNALNAME PID
Allowed signal names for kill:
ABRT HUP INT KILL QUIT TERM USR1 USR2
--重新加载配置
$ pg_ctl kill HUP 1006
2.6.pg_basebackup
为 pg_basebackup 命令实施了以下增强功能。
- --no-verify-checksum
pg_basebackup 命令现在检查备份块的校验和。 --no-verify-checksum 选项跳过校验和验证过程。如果校验和中发生错误，则 pg_basebackup 命令将以非零值退出。
- --create-slot
pg_basebackup 命令现在具有用于创建复制槽的 --create-slot 选项（或 -C 选项）。此选项与 --slot 选项一起使用。即使在 pg_basebackup 命令完成后，创建的复制槽也会保留。如果已存在同名的复制槽，则 pg_basebackup 命令将显示错误消息并退出。
$ pg_basebackup --create-slot --slot pgbashslot1 --no-verify-checksums -D data1
- 批处理模式下的操作更改
当为pg_basebackup命令指定 --progress 选项并从批处理模式执行它（从 shell 脚本重定向到文件）时，将输出换行代码 “\n” 而不是 “\r”。
$ pg_basebackup --progres -D data1 &> back.out
$ cat back.out
waiting for checkpoint
0/831591 kB (0%), 0/1 tablespace
84195/831591 kB (10%), 0/1 tablespace
706685/831591 kB (84%), 0/1 tablespace
820810/831591 kB (98%), 0/1 tablespace
950449/950449 kB (100%), 0/1 tablespace
1090833/1090833 kB (100%), 0/1 tablespace
1217610/1217610 kB (100%), 0/1 tablespace
1277642/1277642 kB (100%), 0/1 tablespace
1398218/1398218 kB (100%), 0/1 tablespace
1515082/1515082 kB (100%), 0/1 tablespace
1645163/1645163 kB (100%), 0/1 tablespace
1761182/1761182 kB (100%), 0/1 tablespace
1900990/1900990 kB (100%), 0/1 tablespace
2019736/2019736 kB (100%), 0/1 tablespace
2148408/2148408 kB (100%), 0/1 tablespace
2242520/2242520 kB (100%), 0/1 tablespace
2334968/2334968 kB (100%), 0/1 tablespace
2368472/2368472 kB (100%), 0/1 tablespace
2408120/2408120 kB (100%), 0/1 tablespace
2501307/2501307 kB (100%), 0/1 tablespace
2520030/2520030 kB (100%), 0/1 tablespace
2520030/2520030 kB (100%), 0/1 tablespace
2520030/2520030 kB (100%), 1/1 tablespace
- 排除 pg_internal.init 文件
pg_internal.init 文件已从 pg_basebackup 命令的备份目标中排除。
- 排除 UNLOGGED 表
UNLOGGED 和 TEMPORARY 表将从传输数据中排除。
2.7.pg_resetwal / pg_controldata
用于指定 WAL 文件大小的 --wal-segsize 选项已添加到 pg_resetwal 命令中。此外，pg_resetwal 和 pg_controldata 命令具有与现有短选项相对应的长名称选项。
pg_resetwal命令增加
| 短选项 | 增加选项 |
|---|---|
| -c | --commit-timestamp-ids |
| -D | --pgdata |
| -e | --epoch |
| -f | --force |
| -l | --next-wal-file |
| -m | --multixact-ids |
| -n | --dry-run |
| -o | --next-oid |
| -O | --multixact-offset |
| -x | --next-transaction-id |
| None | --wal-segsize |
pg_controldata 命令增加
| 短选项 | 增加选项 |
|---|---|
| -D | --pgdata |
2.8.configure
configure 命令中的以下选项已更改。
- --with-wal-segsize
选项 --with-wal-segsize 已过时。WAL 文件的大小可以通过 initdb 命令的 --wal-segsize 选项来指定。
- --with-llvm
添加了 --with-llvm 选项，用于为 LLVM 提供 JIT 编译功能。指定此选项时，llvm-config 命令和 clang 命令必须包含在命令搜索路径中（或设置环境变量 LLVM_CONFIG 和环境变量 CLANG）。
2.9.pg_verify_checksums
Pg_verify_checksums 已添加为从数据库外部进行校验和一致性检查的命令。实例运行时无法执行该命令。在下面的示例中，仅确认某些文件的校验和，这表明某些块与校验和不匹配。
$ pg_verify_checksums -D data -r 16410
pg_verify_checksums: checksum verification failed in file
"data/base/16385/16410", block 0: calculated checksum 42D6 but expected 84E0
Checksum scan completed
Data checksum version: 1
Files scanned: 1
Blocks scanned: 1
Bad checksums: 1
$
3.扩展
3.1.adminpack
之前版本此模块中包含的函数需要 SUPERUSER 权限。现在 GRANT 授权后一般用户也可以执行。
PostgreSQL 10
postgres=# create extension adminpack;
CREATE EXTENSION
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> select pg_logdir_ls();
ERROR: only superuser can list the log directory
PostgreSQL 11
postgres=# create extension adminpack;
CREATE EXTENSION
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> select pg_logdir_ls();
ERROR: permission denied for function pg_logdir_ls
postgres=>
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant execute on function pg_logdir_ls to u1;
GRANT
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> select pg_logdir_ls();
pg_logdir_ls
--------------------------------------------------------------
("2024-12-28 09:28:46",log/postgresql-2024-12-25_092846.log)
("2024-12-29 00:00:00",log/postgresql-2024-12-29_000000.log)
("2024-12-30 00:00:00",log/postgresql-2024-12-30_000000.log)
(3 rows)
3.2.amcheck
添加了一个新的 Contrib 模块 amcheck。可以检查唯一的 B 树索引。检查 HASH 索引或者分区父表的B树索引都回报错。
postgres# SELECT bt_index_check('part1v1_pkey') ;
bt_index_check
----------------
(1 row)
postgres=# SELECT bt_index_parent_check('part1v1_pkey') ;
bt_index_parent_check
-----------------------
(1 row)
| 函数名 | 描述 |
|---|---|
| bt_index_check | Consistency check for B-Tree index |
| bt_index_parent_check | Consistency check of B-Tree index including parentage relationship |
3.3.btree_gin
现在可以为 bool、bpchar 和 uuid 列创建 B 树 GIN 索引。
postgres=# CREATE EXTENSION btree_gin ;
CREATE EXTENSION
postgres=# CREATE TABLE gintbl1(c1 bool, c2 bpchar(10), c3 uuid) ;
CREATE TABLE
postgres=# CREATE INDEX idx1_gintbl1 ON gintbl1 USING gin(c1) ;
CREATE INDEX
postgres=# CREATE INDEX idx2_gintbl1 ON gintbl1 USING gin(c2) ;
CREATE INDEX
postgres=# CREATE INDEX idx3_gintbl1 ON gintbl1 USING gin(c3) ;
CREATE INDEX
postgres=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
postgres=# INSERT INTO gintbl1 (c1, c2, c3)
SELECT
(i % 2 = 0), -- c1: 布尔值，假设按偶数取 `true`，奇数取 `false`
'sample' || i::text, -- c2: BPCHAR 类型，生成字符串 'sample' + 数字
uuid_generate_v4() -- c3: 使用 `uuid_generate_v4()` 生成 UUID
FROM generate_series(1, 1000) AS s(i);
INSERT 0 1000
postgres=#
postgres=# explain SELECT * FROM gintbl1
WHERE c3 = 'd13b85b4-5654-4444-98c8-f8567dd537f0';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on gintbl1 (cost=8.01..12.02 rows=1 width=28)
Recheck Cond: (c3 = 'd13b85b4-5654-4444-98c8-f8567dd537f0'::uuid)
-> Bitmap Index Scan on idx3_gintbl1 (cost=0.00..8.01 rows=1 width=0)
Index Cond: (c3 = 'd13b85b4-5654-4444-98c8-f8567dd537f0'::uuid)
(4 rows)
3.4.citext
添加了索引的 Operator 类 citext_pattern_ops。运算符 ~<~、~<=~、~>~、~>=~ 可用于 citext 类型，类似于文本类型。传统上，它被转换为文本类型并执行。
postgres=# create extension citext;
CREATE EXTENSION
postgres=# CREATE TABLE citext1 (
postgres(# id SERIAL PRIMARY KEY,
postgres(# c2 CITEXT
postgres(# );
CREATE TABLE
postgres=# INSERT INTO citext1 (c2) VALUES
postgres-# ('apple'),
postgres-# ('banana'),
postgres-# ('Orange'),
postgres-# ('grape'),
postgres-# ('melon'),
postgres-# ('pear'),
postgres-# ('Apple');
INSERT 0 7
postgres=# -- 创建 citext 类型的 B-tree 索引，使用 citext_pattern_ops 操作符类
postgres=# CREATE INDEX idx_c2_pattern_ops ON citext1 USING btree (c2 collate pg_catalog."default" citext_pattern_ops);
CREATE INDEX
postgres=# explain analyze SELECT * FROM citext1 WHERE c2 ~>=~ 'banana';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_c2_pattern_ops on citext1 (cost=0.13..8.17 rows=2 width=36) (actual time=0.022..0.024 rows=5 loops=1)
Index Cond: (c2 ~>=~ 'banana'::citext)
Planning Time: 0.063 ms
Execution Time: 0.038 ms
(4 rows)
postgres=# SELECT * FROM citext1 WHERE c2 ~>=~ 'banana';
id | c2
----+--------
2 | banana
4 | grape
5 | melon
3 | Orange
6 | pear
(5 rows)
3.5.cube / seg
Index Only Scan 现在可以使用 GiST 索引执行。
postgres=# create extension cube;
CREATE EXTENSION
postgres=#
postgres=# CREATE TABLE cube_table (
id SERIAL PRIMARY KEY,
c1 cube
);
CREATE TABLE
postgres=# INSERT INTO cube_table (c1)
postgres-# VALUES
postgres-# ('(1,2,3)'),
postgres-# ('(4,5,6)'),
postgres-# ('(7,8,9)'),
postgres-# ('(10,11,12)');
INSERT 0 4
postgres=# CREATE INDEX idx_cube_c1 ON cube_table USING gist (c1);
CREATE INDEX
postgres=# EXPLAIN ANALYZE
postgres-# SELECT c1
postgres-# FROM cube_table
postgres-# WHERE c1 @> '(4,5,6)';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_cube_c1 on cube_table (cost=0.13..8.15 rows=1 width=32) (actual time=0.032..0.032 rows=1 loops=1)
Index Cond: (c1 @> '(4, 5, 6)'::cube)
Heap Fetches: 1
Planning Time: 0.223 ms
Execution Time: 0.074 ms
(5 rows)
postgres=# SELECT c1
postgres-# FROM cube_table
postgres-# WHERE c1 @> '(4,5,6)';
c1
-----------
(4, 5, 6)
(1 row)
3.6.jsonb_plpython
添加了一个新的 Contrib 模块 jsonb_plpython。必须在 configure 命令中指定 --with-python 选项才能安装。CREATE EXTENSION 语句中指定的模块名称为 “jsonb_plpythonu”、“jsonb_plpython2u” 或 “jsonb_plpython3u”。可以在 CREATE FUNCTION 语句的 TRANSFORM 子句中指定 Jsonb。
postgres=# CREATE EXTENSION jsonb_plpython3u;
CREATE EXTENSION
postgres=# CREATE OR REPLACE FUNCTION fpython(val jsonb)
RETURNS jsonb
TRANSFORM FOR TYPE jsonb
LANGUAGE plpython3u
AS $$
return (val) ;
$$ ;
CREATE FUNCTION
postgres=#
postgres=# SELECT fpython('{"1":1,"example": null}'::jsonb) ;
fpython
---------------------------
{"1": 1, "example": null}
(1 row)
3.7.jsonb_plperl
添加了一个新的 Contrib 模块 jsonb_plperl。必须在 “configure” 命令中指定 --with-perl 选项才能安装。要使用它，请在 CREATE FUNCTION 语句的 TRANSFORM 子句中指定 jsonb。
postgres=# CREATE EXTENSION jsonb_plperl;
CREATE EXTENSION
postgres=# CREATE OR REPLACE FUNCTION fperl(val jsonb)
postgres-# RETURNS jsonb
postgres-# TRANSFORM FOR TYPE jsonb
postgres-# LANGUAGE plperl
postgres-# AS $$
postgres$# return $_[0] ;
postgres$# $$ ;
CREATE FUNCTION
postgres=# SELECT fperl('{"1":1,"example": null}'::jsonb) ;
fperl
---------------------------
{"1": 1, "example": null}
(1 row)
3.8.pageinspect
last_cleanup_num_tuples 列已添加到 bt_metap 函数的输出中。
postgres=# SELECT * FROM bt_metap('part1v1_pkey')\gx
-[ RECORD 1 ]-----------+-------
magic | 340322
version | 3
root | 1
level | 0
fastroot | 1
fastlevel | 0
oldest_xact | 0
last_cleanup_num_tuples | -1
postgres=# delete from part1v1;
DELETE 1
postgres=# vacuum part1v1;
VACUUM
postgres=# SELECT * FROM bt_metap('part1v1_pkey')\gx
-[ RECORD 1 ]-----------+-------
magic | 340322
version | 3
root | 1
level | 0
fastroot | 1
fastlevel | 0
oldest_xact | 0
last_cleanup_num_tuples | 0
3.9.pg_prewarm
在实例启动时自动加载共享内存中存在的页面的功能。当参数 shared_preload_libraries 指定 “pg_prewarm” 并启动实例时，后台工作进程 “postgres： autoprewarm master” 将自动启动。autoprewarm 主进程在实例启动时将之前存储在共享内存中的块加载到共享内存中。之后，此过程会定期将共享内存上的块信息保存在文件中。如果参数 max_worker_processes 为 0，则后台工作进程不会启动。
- File
autoprewarm 进程将共享内存中加载的块信息保存到的文件是{PGDATA}/autoprewarm.blocks 的此文件为文本格式，保存数据库、表空间、文件节点、块等…
更新文件时，它会写入 {PGDATA}/autoprewarm.blocks.tmp 文件，并且文件名会更改。
- pg_prewarm.autoprewarm 参数
为此参数指定默认值 “on” 将启用自动预热功能。
- pg_prewarm.autoprewarm_interval 参数
指定定期保存共享内存上的块信息的最小间隔（以秒为单位）。默认值为 300 秒（5 分钟）。当此参数设置为 0 时，不执行定期共享内存保存处理。仅当实例停止时，才会执行此过程。
# postgresql.conf
shared_preload_libraries = 'pg_prewarm'
pg_prewarm.autoprewarm = true
pg_prewarm.autoprewarm_interval = 300s
3.10.pg_trgm
添加了 strict_word_similarity 函数。此函数类似于 word_similarity 函数，但它将范围边界与单词 boundary 匹配。
postgres=# create extension pg_trgm;
CREATE EXTENSION
postgres=# SELECT strict_word_similarity('word', 'two words'), similarity('word', 'words'),word_similarity('word', 'two words');
strict_word_similarity | similarity | word_similarity
------------------------+------------+-----------------
0.571429 | 0.571429 | 0.8
(1 row)
3.11.postgres_fdw
postgres_fdw 模块中添加了以下增强功能。
- 更新远程分区
即使分区是 FOREIGN TABLE，现在也可以通过 partition table 更新 Tuples。也可以通过 COPY 语句插入元组。
postgres=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Descript
---------------+----------+----------------------+---------------------+------+---------+-------------------------------------------------+---------
fs | postgres | file_fdw | postgres=U/postgres+| | | |
| | | u1=U/postgres | | | |
remote_server | postgres | postgres_fdw | | | | (host '172.17.0.2', port '5432', dbname 'mydb') |
(2 rows)
postgres=# \deu+
List of user mappings
Server | User name | FDW options
---------------+-----------+----------------------------------------
remote_server | public | ("user" 'postgres', password '123456')
(1 row)
postgres=# \d+ prange3
Table "public.prange3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
c1 | numeric | | | | main | |
c2 | character varying(10) | | | | extended | |
Partition key: RANGE (c1)
Partitions: prange3_p1 DEFAULT
postgres=# \d+ prange3_p1
Foreign table "public.prange3_p1"
Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+-------------+----------+--------------+-------------
c1 | numeric | | | | | main | |
c2 | character varying(10) | | | | | extended | |
Partition of: prange3 DEFAULT
No partition constraint
Server: remote_server
FDW options: (schema_name 'public', table_name 'p')
postgres=# insert into prange3 values(1,'a');
INSERT 0 1
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# select * from p;
c1 | c2
----+----
1 | a
(1 row)
- 更改superuser 的检查方法
现在，与 remote 实例的连接检查由 USER MAPPING 用户完成，而不是会话用户。
- 通过 UPDATE / DELETE 语句联接时的行为
现在可以将连接同一 FOREIGN SERVER 上的 FOREIGN TABLE 的 DELETE 或 UPDATE 语句向下推送到远程实例。
PostgreSQL 10
postgres=> explain verbose delete from remote_employees1 r1 using remote_employees1 r2 where r1.id=r2.id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Delete on public.remote_employees1 r1 (cost=221.35..423.10 rows=3216 width=284)
Remote SQL: DELETE FROM public.employees1 WHERE ctid = $1
-> Hash Join (cost=221.35..423.10 rows=3216 width=284)
Output: r1.ctid, r2.*
Hash Cond: (r1.id = r2.id)
-> Foreign Scan on public.remote_employees1 r1 (cost=100.00..182.27 rows=2409 width=10)
Output: r1.ctid, r1.id
Remote SQL: SELECT id, ctid FROM public.employees1 FOR UPDATE
-> Hash (cost=118.01..118.01 rows=267 width=282)
Output: r2.*, r2.id
-> Foreign Scan on public.remote_employees1 r2 (cost=100.00..118.01 rows=267 width=282)
Output: r2.*, r2.id
Remote SQL: SELECT id, name, salary FROM public.employees1
(13 rows)
PostgreSQL 11
postgres=# explain verbose delete from prange3 p1 using prange3 p2 where p1.c1=p2.c1 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Delete on public.prange3 p1 (cost=397.85..484.75 rows=5515 width=48)
Foreign Delete on public.prange3_p1 p1_1
Remote SQL: DELETE FROM public.p WHERE ctid = $1
-> Merge Join (cost=397.85..484.75 rows=5515 width=48)
Output: p1_1.ctid, p2.ctid, ((p2.*)::prange3), p2.tableoid
Merge Cond: (p2.c1 = p1_1.c1)
-> Sort (cost=179.75..181.83 rows=835 width=74)
Output: p2.ctid, ((p2.*)::prange3), p2.tableoid, p2.c1
Sort Key: p2.c1
-> Append (cost=100.00..139.22 rows=835 width=74)
-> Foreign Scan on public.prange3_p1 p2 (cost=100.00..135.05 rows=835 width=74)
Output: p2.ctid, p2.*, p2.tableoid, p2.c1
Remote SQL: SELECT c1, c2, ctid FROM public.p
-> Sort (cost=218.11..221.41 rows=1321 width=38)
Output: p1_1.ctid, p1_1.c1
Sort Key: p1_1.c1
-> Foreign Scan on public.prange3_p1 p1_1 (cost=100.00..149.63 rows=1321 width=38)
Output: p1_1.ctid, p1_1.c1
Remote SQL: SELECT c1, ctid FROM public.p FOR UPDATE
(19 rows)
3.12.pg_rewind
从PostgreSQL 11开始使用pg_rewind 工具可以不依赖superuser用户了，只需要授权给用户下面几个函数的权限：
- pg_ls_dir
- pg_read_file
- pg_read_binary_file
- pg_stat_file
参考
《PostgreSQL_11_New_Features_20180525》
《快速掌握PostgreSQL版本新特性》
最后修改时间：2025-01-06 15:52:22
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
