暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL 11 新特性学习-架构调整、SQL、PL/pgSQL功能增强

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

原创 柚子身上依 2024-12-25
493

1.架构调整

PostgreSQL 10 新特性学习-架构

1.1.字典表修改

  • 字典中新增的列
字典名称 新增列 类型 描述
pg_aggregate aggfinalmodify char Whether the aggfinalfn function changes the value
aggmfinalmodify char Whether the aggmfinalfn function changes the value
pg_attribute atthasmissing bool Have a default value that has not updated the page
attmissingval anyarray Default value not updating page
pg_class relrewrite oid OID when the new relation is created during DDL execution
pg_constraint conparentid oid Parent partition constraint OID
pg_index indnkeyatts smallint Number of key columns
pg_partitioned_table partdefid oid OID of the default partition
pg_proc prokind char Object kind
f: function
p: procedure
a: aggregate function
w: window function
pg_publication pubtruncate boolean TRUNCATE propagation
pg_stat_wal_receiver sender_host text Connection destination hostname
sender_port integer Connection destination port number
information_schema.table_constraints enforced yes_or_no Reserved for future use 
postgres=# select aggfnoid,aggfinalmodify,aggmfinalmodify from pg_aggregate limit 1;  
    aggfnoid    | aggfinalmodify | aggmfinalmodify 
----------------+----------------+-----------------
 pg_catalog.avg | r              | r
(1 row)

postgres=# select relname,relrewrite from pg_class limit 1;
 relname | relrewrite 
---------+------------
 test    |          0
(1 row)

postgres=# select oid,conname,conparentid from pg_constraint where conname~'part1';
  oid  |   conname    | conparentid 
-------+--------------+-------------
 24742 | part1_pkey   |           0
 24747 | part1v1_pkey |       24742
 24752 | part1v2_pkey |       24742
(3 rows)

postgres=# select indexrelid::regclass,indrelid::regclass,indnkeyatts from pg_index where indrelid=24738;
 indexrelid | indrelid | indnkeyatts 
------------+----------+-------------
 part1_pkey | part1    |           1
(1 row)

postgres=# select partrelid::regclass,partdefid::regclass from pg_partitioned_table where partrelid='prange1'::regclass;
 partrelid | partdefid 
-----------+-----------
 prange1   | pranged
(1 row)

postgres=# select proname,prokind from pg_proc where pronamespace=2200 limit 10;
          proname           | prokind 
----------------------------+---------
 postgres_fdw_handler       | f
 postgres_fdw_validator     | f
 hash_page_type             | f
 pg_check_frozen            | f
 pg_truncate_visibility_map | f
 pg_visibility_map          | f
 pg_visibility              | f
 pg_visibility_map          | f
 pg_visibility              | f
 pg_visibility_map_summary  | f
(10 rows)

postgres=# select pubname,pubtruncate from pg_publication limit 4;
 pubname | pubtruncate 
---------+-------------
 pub1    | t
 pub2    | t
(2 rows)

postgres=# select distinct enforced from information_schema.table_constraints;
 enforced 
----------
 YES
(1 row)
  • 字典中删除的列
字典名称 删除列 描述
pg_class relhaspkey Have primary key
pg_proc proisagg Is aggregate function
proiswindow Is Window function
  • 值存储到information_schema schema的字典表表
字典名称 列名 描述
triggers action_order Trigger execution order
action_reference_new_table “NEW” transition table name
action_reference_old_table “OLD” transition table name
tables table_type FOREIGN stored for the foreign table (previously FOREIGN TABLE stored)
  • pg_stat_activity

Backend_type列和进程名称现在已同步。复制启动器进程的 backend_type 列的值为 PostgreSQL 10 中的background worker，但在PostgreSQL 11 是 logical replication launcher和logical replication worker
PostgreSQL 10

postgres=# SELECT pid,wait_event, backend_type FROM pg_stat_activity where wait_event ~'Logical';
 pid |     wait_event      |         backend_type         
-----+---------------------+------------------------------
  34 | LogicalLauncherMain | background worker
  3009 | LogicalApplyMain    | background worker
(2 rows)

PostgreSQL 11

postgres=# SELECT pid,wait_event, backend_type FROM pg_stat_activity where wait_event ~'Logical';
 pid |     wait_event      |         backend_type         
-----+---------------------+------------------------------
  32 | LogicalLauncherMain | logical replication launcher
  33 | LogicalApplyMain    | logical replication worker
(2 rows)
  • pg_attribute

在 PostgreSQL 11 中，具有 DEFAULT 值和 NOT NULL 约束的列现在可以为
添加时没有更新实际的 Tuples。与此同时，此功能的列已添加到 pg_attribute 目录中。

postgres=# alter table plist1 add COLUMN c3 INT NOT NULL DEFAULT 10 ;
ALTER TABLE
postgres=# select attrelid::regclass,atthasmissing,attmissingval from pg_attribute WHERE attname='c3' ;
 attrelid | atthasmissing | attmissingval 
----------+---------------+---------------
 plist1   | f             | 
 plist11  | t             | {10}
 plist12  | t             | {10}
 plist1d  | t             | {10}
 prange4  | f             | 
(5 rows)

1.2.增加角色

添加了以下角色。这些角色主要用于允许普通用户执行copy 语句和执行 file_fdw Contrib 模块。

角色名称 描述
pg_execute_server_program Execute programs on the server
pg_read_server_files Read files on the server
pg_write_server_files Write files on the server
  • pg_read_server_files
postgres=# create user u1;
CREATE ROLE
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> CREATE TABLE copy1(id int, name text);
CREATE TABLE
postgres=> COPY copy1 FROM '/var/lib/postgresql/copy1.txt' with (delimiter ' ');
ERROR:  must be superuser or a member of the pg_read_server_files role to COPY from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres=# grant pg_read_server_files  to u1;
GRANT ROLE
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> 
postgres=> CREATE TABLE copy1(id int, name text);
CREATE TABLE
postgres=> COPY copy1 FROM '/var/lib/postgresql/copy1.txt' with (delimiter ' ');
COPY 1
postgres=> select * from copy1;
 id | name 
----+------
  1 | a
(1 row)
  • pg_write_server_files
postgres=> COPY copy1 to 'copy11.txt';
ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres=# grant pg_write_server_files to u1; 
GRANT ROLE
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> 
postgres=> COPY copy1 to 'copy11.txt';
ERROR:  relative path not allowed for COPY to file
postgres=> 
postgres=> COPY copy1 to '/var/lib/postgresql/copy11.txt';
COPY 1
  • pg_execute_server_program
postgres=# \! gzip copy1.txt
postgres=# \! ls 
copy1.txt.gz  copy11.txt  data
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# GRANT USAGE ON FOREIGN SERVER fs TO u1;
GRANT
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> 
postgres=> CREATE FOREIGN TABLE tfile1 (id NUMERIC, val VARCHAR(10)) SERVER fs OPTIONS (program 'gzip -d copy1.txt.gz', delimiter ' ') ;
ERROR:  only superuser or a member of the pg_execute_server_program role may specify the program option of a file_fdw foreign table
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres=# grant pg_execute_server_program to u1;
GRANT ROLE
postgres=# \c - u1
You are now connected to database "postgres" as user "u1".
postgres=> 
postgres=> CREATE FOREIGN TABLE tfile1 (id NUMERIC, val VARCHAR(10)) SERVER fs OPTIONS (program 'gzip -d </var/lib/postgresql/copy1.txt.gz', delimiter ' ') ;
CREATE FOREIGN TABLE
postgres=> 
postgres=> select * from tfile1;
 id | val 
----+-----
  1 | a
(1 row)

1.3.LLVM 集成

PostgreSQL 11 支持使用 LLVM （https://llvm.org/） 进行 JIT 编译，以加快长时间运行的
处理器瓶颈导致的 SQL 语句。预先编译估计超过一定成本的 SQL 语句，然后执行。

  • 安装

为了使用 LLVM，必须在安装时指定 “configure” 命令的选项 --with-llvm。执行 “configure” 命令、“llvm-config” 命令和 “clang” 时
command 必须包含在命令执行路径中（或在环境变量中指定
LLVM_CONFIG和环境变量 CLANG）。

'--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-6.0' 'CLANG=/usr/bin/clang-6.0'
  • JIT 编译的行为

对于总执行成本超过参数 jit_above_cost（默认值 100000）的 SQL 语句，运行 LLVM 的 JIT 编译功能。如果该参数指定为 “-1” 或参数 jit 设置为 “off”，则 JIT 功能将被禁用。JIT 编译处理的行为通过内联（参数 jit_inline_above_cost）和优化（参数 jit_optimize_above_cost）来更改。

postgres=# show jit_above_cost ;
 jit_above_cost 
----------------
 100000
(1 row)

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

postgres=# show jit_inline_above_cost;
 jit_inline_above_cost 
-----------------------
 500000
(1 row)

postgres=# show jit_optimize_above_cost;
 jit_optimize_above_cost 
-------------------------
 500000
(1 row)
  • 执行计划

使用 JIT 编译功能对 SQL 语句执行 EXPLAIN 语句时，将显示从 “JIT：” 开始的信息。

postgres=# set jit to 'on';
SET
postgres=# set jit_above_cost to '10';
SET
postgres=# explain analyze select count(1) from test;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=116778.55..116778.56 rows=1 width=8) (actual time=21168.632..21238.615 rows=1 loops=1)
   ->  Gather  (cost=116778.33..116778.54 rows=2 width=8) (actual time=21168.210..21238.573 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=115778.33..115778.34 rows=1 width=8) (actual time=21106.963..21106.965 rows=1 loops=3)
               ->  Parallel Seq Scan on test  (cost=0.00..105361.67 rows=4166667 width=0) (actual time=12.662..20960.872 rows=3333333 loops=3)
 Planning Time: 82.801 ms
 JIT:
   Functions: 9
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.298 ms, Inlining 0.000 ms, Optimization 1.186 ms, Emission 17.758 ms, Total 20.242 ms
 Execution Time: 21239.204 ms
(12 rows)

1.4.GIN / GiST / HASH 索引的谓词锁定

谓词锁现在可用于 GIN 索引、GiST 索引、HASH 索引。此功能减小了锁范围，提高了具有多个会话的 SQL 语句的并发性。
以下示例是使用 HASH 索引的验证结果。在 PostgreSQL 10 中锁定范围是表，但在 PostgreSQL 11 中锁定范围是页面。
PostgreSQL 10

postgres=# SELECT locktype, relation::regclass, mode FROM pg_locks ;
   locktype    |  relation  |      mode       
---------------+------------+-----------------
 relation      | pg_locks   | AccessShareLock
 relation      | idx1_lock1 | AccessShareLock
 relation      | lock1      | RowShareLock
 virtualxid    |            | ExclusiveLock
 transactionid |            | ExclusiveLock
 tuple         | lock1      | SIReadLock
 relation      | idx1_lock1 | SIReadLock
(7 rows)

PostgreSQL 11


postgres=# SELECT locktype, relation::regclass, mode FROM pg_locks ;
   locktype    |  relation  |      mode       
---------------+------------+-----------------
 relation      | pg_locks   | AccessShareLock
 relation      | idx1_lock1 | AccessShareLock
 relation      | lock1      | RowShareLock
 virtualxid    |            | ExclusiveLock
 transactionid |            | ExclusiveLock
 tuple         | lock1      | SIReadLock
 page          | idx1_lock1 | SIReadLock
(7 rows)

1.5.增强的 LDAP 身份验证

“ldapsearchfilter”属性已添加到 LDAP 身份验证参数中，如pg_hba.conf 文件。LDAP 服务器的搜索比 “ldapsearchattribute” 属性更灵活。

# 启用 LDAP 认证
host    all         all         0.0.0.0/0          ldap    ldapserver=ldap.example.com ldapbinddn="cn=admin,dc=example,dc=com" ldapbindpasswd="password" ldapsearchfilter="(&(uid=%u)(memberOf=CN=admins,OU=groups,DC=example,DC=com))"

1.6.更改备份标签文件

时间线 ID 将添加到执行在线备份时创建的 backup_label 文件中。

postgres=# SELECT pg_start_backup(now()::text) ;
 pg_start_backup 
-----------------
 0/6A000028
(1 row)

postgres=# \! cat data/backup_label
START WAL LOCATION: 0/6A000028 (file 00000001000000000000006A)
CHECKPOINT LOCATION: 0/6A000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2024-12-19 08:25:42 UTC
LABEL: 2024-12-19 08:25:42.293213+00
START TIMELINE: 1

1.7.添加非空列不需要重新

PostgreSQL11之前，在给表加列时，如果不设置列的默认值，不需要rewrite table。但是若设置新增列的默认值，则必须rewrite table。PostgreSQL 11 解决了这个问题。
PostgreSQL 10

postgres=# create table testalter (id serial ,name text);
CREATE TABLE
postgres=# insert into testalter(name) select repeat('x',100);      
INSERT 0 1
postgres=# select relfilenode from pg_class where relname='testalter';
 relfilenode 
-------------
       66360
(1 row)
                                           ^
postgres=# alter table testalter add column age int default 1;
ALTER TABLE
postgres=# select relfilenode from pg_class where relname='testalter';
 relfilenode 
-------------
       66368
(1 row)

PostgreSQL 11

postgres=# create table testalter (id serial ,name text);
CREATE TABLE
postgres=# insert into testalter(name) select repeat('x',100);      
INSERT 0 1
postgres=# select relfilenode from pg_class where relname='testalter';
 relfilenode 
-------------
       57458
(1 row)

postgres=# alter table testalter add column age int default 1;
ALTER TABLE
postgres=# select relfilenode from pg_class where relname='testalter';
 relfilenode 
-------------
       57458
(1 row)

2.SQL功能增强

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

2.1.LOCK TABLE

在 PostgreSQL 11 中，现在可以在 LOCK TABLE 语句中指定视图。锁定视图时，相同的模式锁定将应用于视图定义中包含的表。
PostgreSQL 10

postgres=# LOCK TABLE view1 IN ACCESS EXCLUSIVE MODE ;
ERROR:  "view1" is not a table

PostgreSQL 11

postgres=# CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) ;
CREATE TABLE
postgres=# CREATE VIEW view1 AS SELECT * FROM data1 ;
CREATE VIEW
postgres=# BEGIN ;
BEGIN
postgres=# LOCK TABLE view1 IN ACCESS EXCLUSIVE MODE ;
LOCK TABLE
postgres=# SELECT locktype,relation::regclass, mode FROM pg_locks ;
   locktype    | relation |        mode         
---------------+----------+---------------------
 relation      | pg_locks | AccessShareLock
 virtualxid    |          | ExclusiveLock
 relation      | data1    | AccessExclusiveLock
 transactionid |          | ExclusiveLock
 relation      | view1    | AccessExclusiveLock
(5 rows)

不能锁定物化视图,物化视图包含在视图中也不会被锁定。

postgres=#  CREATE MATERIALIZED VIEW mview1 AS SELECT * FROM data1 ;
SELECT 0
postgres=#  BEGIN ;          
BEGIN
postgres=# LOCK TABLE mview1 IN ACCESS EXCLUSIVE MODE ;
ERROR:  "mview1" is not a table or a view
postgres=# ROLLBACK ; 
ROLLBACK
postgres=# CREATE VIEW view2 AS SELECT * FROM mview1 ;
CREATE VIEW
postgres=# BEGIN ;
BEGIN
postgres=# LOCK TABLE view2 IN ACCESS EXCLUSIVE MODE ;
LOCK TABLE
postgres=# SELECT locktype,relation::regclass, mode FROM pg_locks ;
   locktype    | relation |        mode         
---------------+----------+---------------------
 relation      | pg_locks | AccessShareLock
 virtualxid    |          | ExclusiveLock
 relation      | view2    | AccessExclusiveLock
 transactionid |          | ExclusiveLock
(4 rows)

2.2.函数索引的统计信息

现在可以为函数索引列指定 STATISTICS 值。

postgres=# create table stat1(c1 int,c2 int);
CREATE TABLE
postgres=# CREATE INDEX idx1_stat1 ON stat1 ((c1 + c2)) ;
CREATE INDEX
postgres=#  ALTER INDEX idx1_stat1 ALTER COLUMN 1 SET STATISTICS 1000 ;
ALTER INDEX
postgres=# \d+ idx1_stat1
                   Index "public.idx1_stat1"
 Column |  Type   | Key? | Definition | Storage | Stats target 
--------+---------+------+------------+---------+--------------
 expr   | integer | yes  | (c1 + c2)  | plain   | 1000
btree, for table "public.stat1"

2.3.vacuum和analyze

  • 同时指定多个表

VACUUM 和 ANALYZE 语句现在可以同时指定多个表。

postgres=# vacuum stat1,data1;
VACUUM
postgres=# analyze stat1,data1;
ANALYZE
  • 输出aggressively

执行 VACUUM （VERBOSE， FREEZE） 语句时，“aggressively” 被添加到输出中。
PostgreSQL 10

postgres=# VACUUM (VERBOSE, FREEZE) data1 ; 
INFO:  vacuuming "public.data1"
INFO:  "data1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 11038
There were 0 unused item pointers.
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

PostgreSQL 11

postgres=# VACUUM (VERBOSE, FREEZE) data1 ; 
INFO:  aggressively vacuuming "public.data1"
INFO:  "data1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1025
There were 0 unused item pointers.
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

2.4.支持创建索引指定include

可以指定用于向索引添加列的 INCLUDE 子句。当将与唯一约束无关的列添加到唯一索引时这是有效的。在下面的示例中，UNIQUE CONSTRAINT 是在 c1 和 c2 列中创建的，但索引包括 c3 列。

  • INCLUDE
postgres=# alter table data2 add column c3 varchar;
ALTER TABLE
postgres=#  CREATE UNIQUE INDEX idx1_data2 ON data2 (c1, c2) INCLUDE (c3) ;
CREATE INDEX

INCLUDE 子句还可以作为 CREATE INDEX 语句用于 CREATE TABLE 语句的约束规范部分。

postgres=# CREATE TABLE data3 (c1 INT, c2 INT, c3 INT, c4 VARCHAR(10), 
CONSTRAINT data3_pkey PRIMARY KEY (c1, c2) INCLUDE (c3)) ;
CREATE TABLE
postgres=#
  • 阈值指数

现在可以在 CREATE INDEX 语句的 WITH 子句中指定 recheck_on_update 选项。默认值为 “on”。该参数指定是否通过 HOT 更新函数索引。

postgres=# CREATE INDEX idx1_data3 ON data3(upper(c4)) WITH (recheck_on_update = on) ;
CREATE INDEX
postgres=# \d data3
                      Table "public.data3"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 c1     | integer               |           | not null | 
 c2     | integer               |           | not null | 
 c3     | integer               |           |          | 
 c4     | character varying(10) |           |          | 
Indexes:
    "data3_pkey" PRIMARY KEY, btree (c1, c2) INCLUDE (c3)
    "idx1_data3" btree (upper(c4::text)) WITH (recheck_on_update

2.5.新增建表参数

现在可以指定存储参数toast_tuple_target指示threshold 值。默认值与以前相同。

postgres=# CREATE TABLE toast1(c1 INT, c2 VARCHAR(10)) WITH (toast_tuple_target = 1024) ;
CREATE TABLE

2.6.WINDOW函数

现在可以在 WINDOWS 函数中指定窗口框架规范中的 GROUPS 子句和 EXCLUDE 子句。此外，float 4 类型、float 8 类型和 numeric 类型可用于 RANGE 子句;默认的exclude 是 exclude

  • order by score::int groups between 1 preceding and 6 following

表示相同的score值是一个分组,然后在通过 between 1 preceding and 6 following 前面取 1个分组,后面取 6个分组,比如行sid=3,sub=4这行,同一个分组时 65,前面一个分组时60,后面的5个分组只有一个 71,所以sum的值是 60+60+65+65+65+71+71=457


postgres=# select
postgres-# *,
postgres-# sum(score) over w1,
postgres-# round((avg(score) over w1)::decimal,2),
postgres-# first_value(score) over w1,
postgres-# last_value(score) over w1
postgres-# from t1223 where sub=4
postgres-# window w1 as (partition by sub order by score::int groups between 1 preceding and 6 following)
postgres-# order by sub, score;
 sid | sub |  des   | score | sum | round | first_value | last_value 
-----+-----+--------+-------+-----+-------+-------------+------------
   1 |   4 | 物理 |    60 | 457 | 65.29 |          60 |         71
   2 |   4 | 物理 |    60 | 457 | 65.29 |          60 |         71
   3 |   4 | 物理 |    65 | 457 | 65.29 |          60 |         71
   4 |   4 | 物理 |    65 | 457 | 65.29 |          60 |         71
   5 |   4 | 物理 |    65 | 457 | 65.29 |          60 |         71
   6 |   4 | 物理 |    71 | 337 | 67.40 |          65 |         71
   7 |   4 | 物理 |    71 | 337 | 67.40 |          65 |         71
(7 rows)
  • order by score::int groups between 1 preceding and 6 following

表示取本行、前一行、后5行，比如行sid=3,sub=4这行,前一行是 60，本行是65，后5行是 65、65、71、71，所以sum值是60+65+65+65+71+71=337

postgres=# select
*,
sum(score) over w1,
round((avg(score) over w1)::decimal,2),
first_value(score) over w1,
last_value(score) over w1
from t1223 where sub=4
window w1 as (partition by sub order by score::int rows between 1 preceding and 6 following)
order by sub, score;
 sid | sub |  des   | score | sum | round | first_value | last_value 
-----+-----+--------+-------+-----+-------+-------------+------------
   1 |   4 | 物理 |    60 | 457 | 65.29 |          60 |         71
   2 |   4 | 物理 |    60 | 457 | 65.29 |          60 |         71
   3 |   4 | 物理 |    65 | 397 | 66.17 |          60 |         71
   4 |   4 | 物理 |    65 | 337 | 67.40 |          65 |         71
   5 |   4 | 物理 |    65 | 272 | 68.00 |          65 |         71
   6 |   4 | 物理 |    71 | 207 | 69.00 |          65 |         71
   7 |   4 | 物理 |    71 | 142 | 71.00 |          71 |         71
(7 rows)
  • order by score::int range between 1 preceding and 6 following

是根据score的数值进行的窗口计算, 比如行sid=3,sub=4这行对应的score值是65,取值范围是 [64,71] ,所以sum值是 65+65+65+71+71=337

postgres=# select
*,
sum(score) over w1,
round((avg(score) over w1)::decimal,2),
first_value(score) over w1,
last_value(score) over w1
from t1223 where sub=4
window w1 as (partition by sub order by score::int range between 1 preceding and 6 following)
order by sub, score;
 sid | sub |  des   | score | sum | round | first_value | last_value 
-----+-----+--------+-------+-----+-------+-------------+------------
   1 |   4 | 物理 |    60 | 315 | 63.00 |          60 |         65
   2 |   4 | 物理 |    60 | 315 | 63.00 |          60 |         65
   3 |   4 | 物理 |    65 | 337 | 67.40 |          65 |         71
   4 |   4 | 物理 |    65 | 337 | 67.40 |          65 |         71
   5 |   4 | 物理 |    65 | 337 | 67.40 |          65 |         71
   6 |   4 | 物理 |    71 | 142 | 71.00 |          71 |         71
   7 |   4 | 物理 |    71 | 142 | 71.00 |          71 |         71
(7 rows)
  • order by score::int groups between 1 preceding and 6 following exclude current row

表示相同的score值是一个分组,前面取 1个分组,后面取 6个分组,比如行sid=3,sub=4这行,同一个分组是 65,前面一个分组时60,后面的5个分组只有一个 71,但是exclude current row表示不计算当前行,所以sum的值是 60+60+65+65+71+71=392

postgres=# select
*,
sum(score) over w1,
round((avg(score) over w1)::decimal,2),
first_value(score) over w1,
last_value(score) over w1
from t1223 where sub=4
window w1 as (partition by sub order by score::int groups between 1 preceding and 6 following exclude current row)
order by sub, score;
 sid | sub |  des   | score | sum | round | first_value | last_value 
-----+-----+--------+-------+-----+-------+-------------+------------
   1 |   4 | 物理 |    60 | 397 | 66.17 |          60 |         71
   2 |   4 | 物理 |    60 | 397 | 66.17 |          60 |         71
   3 |   4 | 物理 |    65 | 392 | 65.33 |          60 |         71
   4 |   4 | 物理 |    65 | 392 | 65.33 |          60 |         71
   5 |   4 | 物理 |    65 | 392 | 65.33 |          60 |         71
   6 |   4 | 物理 |    71 | 266 | 66.50 |          65 |         71
   7 |   4 | 物理 |    71 | 266 | 66.50 |          65 |         71
(7 rows)
  • order by score::int groups between 1 preceding and 6 following exclude group

表示相同的score值是一个分组,前面取 1个分组,后面取 6个分组,比如行sid=3,sub=4这行,同一个分组是 65,前面一个分组时60,后面的5个分组只有一个 71,但是exclude group 表示不计算当前组,所以sum的值是 60+60+71+71=262

postgres=# select
*,
sum(score) over w1,
round((avg(score) over w1)::decimal,2),
first_value(score) over w1,
last_value(score) over w1
from t1223 where sub=4
window w1 as (partition by sub order by score::int groups between 1 preceding and 6 following exclude group)
order by sub, score;
 sid | sub |  des   | score | sum | round | first_value | last_value 
-----+-----+--------+-------+-----+-------+-------------+------------
   1 |   4 | 物理 |    60 | 337 | 67.40 |          65 |         71
   2 |   4 | 物理 |    60 | 337 | 67.40 |          65 |         71
   3 |   4 | 物理 |    65 | 262 | 65.50 |          60 |         71
   4 |   4 | 物理 |    65 | 262 | 65.50 |          60 |         71
   5 |   4 | 物理 |    65 | 262 | 65.50 |          60 |         71
   6 |   4 | 物理 |    71 | 195 | 65.00 |          65 |         65
   7 |   4 | 物理 |    71 | 195 | 65.00 |          65 |         65
(7 rows)
  • order by score::int groups between 1 preceding and 6 following exclude ties

表示相同的score值是一个分组,前面取 1个分组,后面取 6个分组,比如行sid=3,sub=4这行,同一个分组是 65,前面一个分组时60,后面的5个分组只有一个 71,但是exclude ties 表示不计算当前组其他行(本行需要参与计算),所以sum的值是 60+60+65+71+71=327

postgres=# select
*,
sum(score) over w1,
round((avg(score) over w1)::decimal,2),
first_value(score) over w1,
last_value(score) over w1
from t1223 where sub=4
window w1 as (partition by sub order by score::int groups between 1 preceding and 6 following exclude ties)
order by sub, score;
 sid | sub |  des   | score | sum | round | first_value | last_value 
-----+-----+--------+-------+-----+-------+-------------+------------
   1 |   4 | 物理 |    60 | 397 | 66.17 |          60 |         71
   2 |   4 | 物理 |    60 | 397 | 66.17 |          60 |         71
   3 |   4 | 物理 |    65 | 327 | 65.40 |          60 |         71
   4 |   4 | 物理 |    65 | 327 | 65.40 |          60 |         71
   5 |   4 | 物理 |    65 | 327 | 65.40 |          60 |         71
   6 |   4 | 物理 |    71 | 266 | 66.50 |          65 |         71
   7 |   4 | 物理 |    71 | 266 | 66.50 |          65 |         71
(7 rows)

2.7.并行查询执行计划

在执行并行查询时会为每个worker程序显示有关排序的信息。

postgres=# explain analyze verbose select * from test order by 2;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=735401.36..1707691.54 rows=8333334 width=23) (actual time=54270.979..59634.301 rows=10000000 loops=1)
   Output: id, name, create_time
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=734401.34..744818.00 rows=4166667 width=23) (actual time=53991.851..54958.420 rows=3333333 loops=3)
         Output: id, name, create_time
         Sort Key: test.name
         Sort Method: external merge  Disk: 113008kB
         Worker 0:  Sort Method: external merge  Disk: 114384kB
         Worker 1:  Sort Method: external merge  Disk: 105496kB
         Worker 0: actual time=53875.473..54906.672 rows=3436102 loops=1
         Worker 1: actual time=53829.492..54698.250 rows=3169202 loops=1
         ->  Parallel Seq Scan on public.test  (cost=0.00..105361.67 rows=4166667 width=23) (actual time=14.068..49992.833 rows=3333333 loops=3)
               Output: id, name, create_time
               Worker 0: actual time=0.034..50645.540 rows=3436102 loops=1
               Worker 1: actual time=0.032..48428.740 rows=3169202 loops=1
 Planning Time: 56.577 ms
 Execution Time: 59941.938 ms
(18 rows)

2.8.新增hash函数

已添加/扩展以下功能。

  • 增加加密hash函数
函数名称 描述
sha224(bytea) Calculate the SHA-224 hash value
sha256(bytea) Calculate the SHA-256 hash value
sha384(bytea) Calculate the SHA-384 hash value
sha512(bytea) Calculate the SHA-512 hash value 
postgres=# select sha512('cgm') ;
                                                               sha512                                                               
------------------------------------------------------------------------------------------------------------------------------------
 \xfefc3467e7e956093be9ff1b2b0ef762cd2067aaee4a6075dd6b2edf26bc1a78d996c42db133d7bb794466044e8b427f579eb0097baa41ff07e086faa61aa2a5
(1 row)
  • json(b)_to_tsvector函数

添加了从 JSON 类型（或 JSONB 类型）转换为 tsvector 类型的 json(b)_to_tsvector 函数。

postgres=# SELECT json_to_tsvector('english', '{"a": "The Fat Rats","b": 123}'::json, '["string", "numeric"]') ;
    json_to_tsvector     
-------------------------
 '123':5 'fat':2 'rat':3
(1 row)
  • websearch_to_tsquery函数

添加了将 Web 搜索格式字符串转换为 tsquery 类型的 websearch_to_tsquery 函数。

postgres=# SELECT websearch_to_tsquery('english', '"fat rat" or rat') ;
  websearch_to_tsquery   
-------------------------
 'fat' <-> 'rat' | 'rat'
(1 row)

2.9.操作符

  • 字符串正向匹配搜索

添加了运算符 “^ @” 来搜索字符串的正向匹配项。它可以用于与 WHERE 子句中的 LIKE ‘character_string%’” 相同的目的。

postgres=# SELECT usename FROM pg_user WHERE usename ^@ 'po' ;
 usename  
----------
 postgres
(1 row)

与 like 子句不同，此运算符不使用 B-Tree 索引。但 SPGiST 索引是可用的。

postgres=# \d+ items
                                                Table "public.items"
 Column |  Type   | Collation | Nullable |              Default              | Storage  | Stats target | Description 
--------+---------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id     | integer |           | not null | nextval('items_id_seq'::regclass) | plain    |              | 
 name   | text    |           |          |                                   | extended |              | 
Indexes:
    "items_pkey" PRIMARY KEY, btree (id)
    "items_name_spgist" spgist (name)
postgres=# explain analyze select * from items where name ^@ 'ap';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using items_name_spgist on items  (cost=0.13..8.15 rows=1 width=36) (actual time=0.010..0.011 rows=4 loops=1)
   Index Cond: (name ^@ 'ap'::text)
 Planning Time: 0.119 ms
 Execution Time: 0.025 ms
(4 rows)

2.10.创建函数的with 语句取消

PostgreSQL 10

postgres=# CREATE FUNCTION func3()
RETURNS INTEGER AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql
WITH (isStrict);
CREATE FUNCTION

PostgreSQL 11

postgres=# CREATE FUNCTION func2()
RETURNS INTEGER AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql
WITH (isStrict);
ERROR:  syntax error at or near "WITH"
LINE 7: WITH (isStrict);
        ^

2.11.JSONB 类型强制转换

  • JSONB 类型强制转换

从JSONB 类型转换为 bool 类型和数值类型

postgres=# SELECT 'true'::jsonb::bool ;
 bool 
------
 t
(1 row)

postgres=# SELECT '1.0'::jsonb::float ;
 float8 
--------
      1
(1 row)

postgres=# SELECT '12345'::jsonb::int4 ;
 int4  
-------
 12345
(1 row)

postgres=# SELECT '12345'::jsonb::numeric ;
 numeric 
---------
   12345
(1 row)

2.12.SP-GiST 索引的增强

提供了可为 Polygon 类型创建的 poly_ops 运算符类。此外用户还可以定义压缩的方法。

postgres=# select opc.opcname,amop.amopopr::regoperator, amop.amopstrategy
from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
where 
opc.opcname ~'poly'
and opf.oid = opc.opcfamily
and am.oid = opf.opfmethod
and amop.amopfamily = opc.opcfamily
and am.amname = 'spgist'
and amop.amoplefttype = opc.opcintype;
 opcname  |       amopopr        | amopstrategy 
----------+----------------------+--------------
 poly_ops | <<(polygon,polygon)  |            1
 poly_ops | &<(polygon,polygon)  |            2
 poly_ops | &&(polygon,polygon)  |            3
 poly_ops | &>(polygon,polygon)  |            4
 poly_ops | >>(polygon,polygon)  |            5
 poly_ops | ~=(polygon,polygon)  |            6
 poly_ops | @>(polygon,polygon)  |            7
 poly_ops | <@(polygon,polygon)  |            8
 poly_ops | &<|(polygon,polygon) |            9
 poly_ops | <<|(polygon,polygon) |           10
 poly_ops | |>>(polygon,polygon) |           11
 poly_ops | |&>(polygon,polygon) |           12
(12 rows)

postgres=# CREATE TABLE geographic_regions (
postgres(#     id SERIAL PRIMARY KEY,
postgres(#     region_name TEXT,
postgres(#     boundary POLYGON
postgres(# );
CREATE TABLE
postgres=# INSERT INTO geographic_regions (region_name, boundary)
postgres-# VALUES 
postgres-# ('Region A', '((0,0), (1,0), (1,1), (0,1))'),
postgres-# ('Region B', '((2,2), (3,2), (3,3), (2,3))');
INSERT 0 2
postgres=# CREATE INDEX geographic_regions_boundary_spgist ON geographic_regions
postgres-# USING spgist (boundary poly_ops);
CREATE INDEX
postgres=# SELECT region_name
postgres-# FROM geographic_regions
postgres-# WHERE boundary @> point(0.5, 0.5);
 region_name 
-------------
 Region A
(1 row)

2.13.to_number函数行为变化

忽略转换模板中的逗号
PostgreSQL 10

postgres=# SELECT to_number('1234', '9,999') ;
 to_number 
-----------
       134
(1 row)

PostgreSQL 11


postgres=# SELECT to_number('1234', '9,999') ;
 to_number 
-----------
      1234
(1 row)

TO_DATE / TO_NUMBER / TO_TIMESTAM 多字节字符处理时跳过
PostgreSQL 10


postgres=# SELECT to_number('1234', '我999') ;
 to_number 
-----------
         4
(1 row)

postgres=# SELECT to_date('2024-12-31', 'yyyy-m我-dd') ;
  to_date   
------------
 2024-01-01
(1 row)

PostgreSQL 11

postgres=# SELECT to_number('1234', '我999') ;
 to_number 
-----------
       234
(1 row)

postgres=# SELECT to_date('2024-12-31', 'yyyy-m我-dd') ;
  to_date   
------------
 2024-01-31
(1 row)

2.14.表达式索引

如果过滤条件是表达式或者虚拟列，可以对于表达式或者虚拟列创建索引，从而提高执行效率。

postgres=# create table ftest(c1 int,c2 timestamp);
CREATE TABLE
postgres=# insert into ftest select c2,c1 from generate_series('2024-01-01'::timestamp,now(),'1 minutes') with ordinality as s(c1,c2);
INSERT 0 534664
postgres=# explain analyze select * from ftest where (c2+interval '180 days') <now();
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on ftest  (cost=0.00..12247.62 rows=178221 width=12) (actual time=0.015..88.472 rows=275465 loops=1)
   Filter: ((c2 + '180 days'::interval) < now())
   Rows Removed by Filter: 259199
 Planning Time: 0.040 ms
 Execution Time: 94.704 ms
(5 rows)

postgres=# explain analyze select * from ftest where (c2+interval '180 days') <now();
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on ftest  (cost=0.00..12247.62 rows=178221 width=12) (actual time=0.011..90.662 rows=275465 loops=1)
   Filter: ((c2 + '180 days'::interval) < now())
   Rows Removed by Filter: 259199
 Planning Time: 0.033 ms
 Execution Time: 97.164 ms
(5 rows)

postgres=# create index on ftest(c2);
CREATE INDEX
postgres=# explain analyze select * from ftest where (c2+interval '180 days') <now();
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Seq Scan on ftest  (cost=0.00..12247.62 rows=178221 width=12) (actual time=0.025..89.638 rows=275466 loops=1)
   Filter: ((c2 + '180 days'::interval) < now())
   Rows Removed by Filter: 259198
 Planning Time: 0.289 ms
 Execution Time: 95.967 ms
(5 rows)
                      
postgres=# create index on ftest((c2+interval '180 days'));
CREATE INDEX
postgres=# explain analyze select * from ftest where (c2+interval '180 days') <now();
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ftest  (cost=3341.64..9351.51 rows=178221 width=12) (actual time=26.305..44.463 rows=275466 loops=1)
   Recheck Cond: ((c2 + '180 days'::interval) < now())
   Heap Blocks: exact=1490
   ->  Bitmap Index Scan on ftest_expr_idx  (cost=0.00..3297.09 rows=178221 width=0) (actual time=26.177..26.178 rows=275466 loops=1)
         Index Cond: ((c2 + '180 days'::interval) < now())
 Planning Time: 0.622 ms
 Execution Time: 50.866 ms
(7 rows)

3.PL/pgSQL功能增强

PL/pgSQL 语言的增强以及使用 PL/pgSQL 的新对象。

3.1.支持存储过程

在 PostgreSQL 11 中，新增了 PROCEDURE 对象。PROCEDURE 与 FUNCTION 对象几乎相同，但是没有返回值。PROCEDURE 使用 CREATE PROCEDURE 语句创建。与 CREATE FUNCTION 语句不同，没有 RETURNS 子句、ROWS 子句、PARALLEL 子句、CALLED ON 子句等。

postgres=# CREATE PROCEDURE proc1(INOUT p1 TEXT) AS 
postgres-# $$ 
postgres$# BEGIN 
postgres$# RAISE NOTICE 'Parameter: %', p1 ; 
postgres$# END ; 
postgres$# $$ 
postgres-# LANGUAGE plpgsql ;
CREATE PROCEDURE

有关所创建过程的信息可以从 pg_proc 目录中。要执行 PROCEDURE，请使用 CALL 语句而不是 SELECT 语句。括号 （（）） 是必需的，无论参数是否存在。可以为 PROCEDURE 参数指定 INOUT。

postgres=# CALL proc1('test') ;
NOTICE:  Parameter: test
  p1  
------
 test
(1 row)

要显示从 psql 命令创建的 PROCEDURE 的定义，请使用与 FUNCTION 相同的 df 命令。PROCEDURE 将 Type 列显示为 “proc”。并且 FUNCTION 的 type column 的值被改为 “func”。

postgres=# \dfp
                       List of functions
 Schema | Name  | Result data type | Argument data types | Type 
--------+-------+------------------+---------------------+------
 public | proc1 |                  | INOUT p1 text       | proc
(1 row)

postgres=# select proname,prorettype,prokind  from pg_proc where prokind in('p','f') order by prokind desc limit 2;
 proname | prorettype | prokind 
---------+------------+---------
 proc1   |       2249 | p
 boolout |       2275 | f
(2 rows)

postgres=# SELECT p.proname,
       t.typname AS return_type
FROM pg_proc p
JOIN pg_type t ON p.prorettype = t.oid
WHERE p.proname = 'proc1';
 proname | return_type 
---------+-------------
 proc1   | record
(1 row)

postgres=# SELECT p.proname,
       t.typname AS return_type
FROM pg_proc p
JOIN pg_type t ON p.prorettype = t.oid
WHERE p.proname = 'boolout';
 proname | return_type 
---------+-------------
 boolout | cstring
(1 row)

即使从 psql 命令引用或更改 PROCEDURE 定义，也可以像 FUNCTION 一样执行它

postgres=# \sf proc1
CREATE OR REPLACE PROCEDURE public.proc1(INOUT p1 text)
 LANGUAGE plpgsql
AS $procedure$ 
BEGIN 
RAISE NOTICE 'Parameter: %', p1 ; 
END ; 
$procedure$
  • 事务控制

在程序中，可以控制事务。在 PL/pgSQL 过程中，可以使用 COMMIT 语句和 ROLLBACK 语句。现在可以控制游标循环中的事务。

postgres=# CREATE PROCEDURE transaction_test1() 
postgres-# LANGUAGE plpgsql 
postgres-# AS $$ 
postgres$# BEGIN 
postgres$#     FOR i IN 0..9 LOOP 
postgres$#         INSERT INTO test1 (a) VALUES (i) ; 
postgres$#         IF i % 2 = 0 THEN 
postgres$#             COMMIT ; 
postgres$#         ELSE 
postgres$#             ROLLBACK ; 
postgres$#         END IF ; 
postgres$#     END LOOP ; 
postgres$# END ; 
postgres$# $$ ;
CREATE PROCEDURE
postgres=# \d test1
Did not find any relation named "test1".
postgres=# 
postgres=# create table test1 (a int);
CREATE TABLE
postgres=# call transaction_test1(); 
CALL
postgres=# select * from test1;
 a 
---
 0
 2
 4
 6
 8
(5 rows)

事务控制的语法也以 PL/pgSQL 以外的语言提供。

PL/Python 
	plpy.commit() / plpy.rollback() 
PL/Perl 
	spi_commit() / spi_rollback() 
PL/Tcl 
	commit / rollback

3.2.变量定义增强

  • CONSTANT 变量

CONSTANT 子句声明的常量不赋值默认为null。如果已经赋值，多次赋值时报错，并且PostgreSQL 11和之前报错有点区别。

PostgreSQL 10

postgres=# do $$ 
postgres$# DECLARE 
postgres$# cons1 CONSTANT NUMERIC := 1 ; 
postgres$# BEGIN 
postgres$# cons1 := 2 ; 
postgres$# END ; 
postgres$# $$ ;
ERROR:  "cons1" is declared CONSTANT
LINE 5: cons1 := 2 ; 
        ^

PostgreSQL 11

postgres=# do $$ 
postgres$# DECLARE 
postgres$# cons1 CONSTANT NUMERIC := 1 ; 
postgres$# BEGIN 
postgres$# cons1 := 2 ; 
postgres$# END ; 
postgres$# $$ ;
ERROR:  variable "cons1" is declared CONSTANT
LINE 5: cons1 := 2 ; 
        ^
  • NOT NULL变量

可以在变量上指定 NOT NULL 约束。声明时需要初始值。如果未指定初始值，则会发生错误。并且分配 NULL 值将导致错误。

postgres=# do $$ 
    DECLARE 
       nn1 NUMERIC NOT NULL ; 
    BEGIN 
       nn1 :=1;RAISE NOTICE 'nn1 = %', nn1 ; 
    END ; 
    $$ ;
ERROR:  variable "nn1" must have a default value, since it's declared NOT NULL
LINE 3:        nn1 NUMERIC NOT NULL ; 
                           ^
postgres=# do $$ 
    DECLARE 
       nn1 NUMERIC NOT NULL :=1; 
    BEGIN 
       nn1 :=null;RAISE NOTICE 'nn1 = %', nn1 ; 
    END ; 
    $$ ;
ERROR:  null value cannot be assigned to variable "nn1" declared NOT NULL
CONTEXT:  PL/pgSQL function inline_code_block line 5 at assignment

--改正后
postgres=# do $$ 
    DECLARE 
       nn1 NUMERIC NOT NULL :=1; 
    BEGIN 
       RAISE NOTICE 'nn1 = %', nn1 ; 
    END ; 
    $$ ;
NOTICE:  nn1 = 1
DO
  • SET TRANSACTION

在 PostgreSQL 11 中，可以在 PL/pgSQL 块中执行 SET TRANSACTION 语句。

postgres=# DO LANGUAGE plpgsql $$ 
postgres$#   BEGIN 
postgres$#       PERFORM 1 ; 
postgres$#       COMMIT ; 
postgres$#       SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ; 
postgres$#       PERFORM 1 ; 
postgres$#       RAISE INFO '%', current_setting('transaction_isolation') ; 
postgres$#       COMMIT ; 
postgres$#   END ; 
postgres$#   $$ ;
INFO:  repeatable read
DO

参考

《PostgreSQL_10_New_Features_en_20170522-1》

墨力计划
最后修改时间：2025-01-06 15:15:22
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

PostgreSQL 笔记（共21篇）
学习笔记记录

评论