作者是HPE日本公司的数据库专家、Oracle ACE Director： Noriyoshi Shinoda
pg 12新特性
2.1分析查询性能提升：
并行查询改进：当事务隔离级别是串行化时，并行查询可以使用
分区表提升：计算值可以作为分区键值，分区表可以用作外键约束
2.2可靠性提升：
pg_checksums命令：pg11时新加的pg_verify_checksums命令在pg12时改为pg_checksums
pg_checksums可以检查完整性，也可以改变checksums函数的enable/disable
2.3可维护性：
废弃recovery.conf
recovery.conf文件的参数被整合进postgresql.conf：
其中，recovery.conf文件中的standby_mode参数在postgresql.conf没有对应参数
recovery.conf文件的trigger_file参数对应postgresql.conf文件的promote_trigger_file参数
在恢复时（recovery），在pg cluster中建立一个recovery.signal文件并启动实例，当恢复完成时，该文件被删除。
在流复制环境中，在备实例中建立一个standby.file，当提升为主节点时，该文件被删除。
当recovery.conf文件存在时，pg cluster启动会失败。
reindex命令的提升：
reindex有了concurrently子句，当rebuilding索引时，锁定的范围很少
监控特性的提升：
catalog（系统视图）被引入，以便检查如下语句的执行状态：
cluster语句----pg_stat_progress_cluster
vaccum full语句---pg_stat_progress_cluster
create index语句---pg_stat_progress_create_index
新加入了等待事件（wait event）：从pg_stat_activity视图可以看到：
等待事件 描述 修改
BackendRandomLock Waiting to generate a random number 删除
GSSOpenServer Waiting for connect to GSSAPI sever 新增
CheckpointDone Waiting for a checkpoint to complete 新增
CheckpointStart Waiting for a checkpoint to start 新增
Promote Waiting for standby promotion 新增
WALSync Waiting for a WAL file to reach stable storage 新增
pg_promote函数：该函数用于执行备库提升为主库。
2.4为将来的新特性的准备：
多种存储引擎
注入zheap存储引擎将来被期望
64位 transaction-ID：
64 bit transaction id 是可用的，API "GetTopFullTransactionId" and "GetCurrentFullTransactionId" are provided，但是当前的heap表不使用这两个API
2.5不兼容性：
与postgresql 11相比，postgresql 12有下列特定的改变
recovery.conf 文件：该文件在pg12中被废弃
pg_checksums命令：pg11时新加的pg_verify_checksums命令在pg12时改为pg_checksums，同时，一些新特性也被加入。
to_timestamp/to_date函数
pg11时，注意下面的'空格YYYY/MM/DD'
postgres=# select to_date('2019/03/23',' YYYY/MM/DD');
to_date
------------
0019-03-23 ---->>>返回的是0019年。
(1 row)
pg12时，注意下面的'空格YYYY/MM/DD'
postgres=# select version();
version
-------------------------------------------------------------
PostgreSQL 12rc1, compiled by Visual C++ build 1900, 64-bit
(1 row)
postgres=# select to_date('2019/03/23',' YYYY/MM/DD');
to_date
------------
2019-03-23 ---->>>返回的是2019年。
(1 row)
postgres=#
with oids子句被删除：
create table 语句中的with oids子句被删除。含有oid的表不能被建立。
在系统视图中，oid列不再是隐藏列。
参数default_with_oids不能被改为on
pg_dump的--oids选项（-o option）被移除掉。
timetravel 插件被移除掉
被移除掉的数据类型：
abstime、reltime、tinterval被移除掉
系统视图pg_shadow中valutil列已经改为timestamp with time zone数据类型
分区表中的copy freeze
分区表中的copy freeze子句被禁止。
外键约束名字的改变
函数：
对于并行查询，current_schema和current_schemas被设置为不安全（unsafe）
3.新特性细节：
新加了一个information_schema视图：
column_column_usage:存储了一个表中生成列的信息
系统视图中加的列
第一：oid列已经添加到各个视图中
第二：pg_attribute视图的attgenerated列，char类型，s代表generated column
第三：pg_collation视图的collisdeterministic列，boolean类型，是否为collation deterministic
第四：pg_statistic视图的stacol[1-5],oid类型，collation information
第五：pg_statistic_ext视图的stxmcv列，pg_mcv_list数据类型，mcv statistics信息
第六：pg_stat_database视图的checksum_failures列，bigint类型，被检测到checksum error的块数
pg_stat_database视图的checksum_last_failure列,timestamp with time zone数据类型，被检测到checksum error的最后时间戳。
第七：pg_stat_replication视图的reply_time列，timestamp with time zone数据类型，存储了从备实例收到的最后reply messages的发送时间。该列对superuser或者pg_monitor的holder显示。
第八：pg_stat_ssl视图的client_serial列，numeric类型，客户端证书序列号
pg_stat_ssl视图的issuer_dn列,text类型，客户端证书issuer DN
系统视图中删除的列
第一：pg_attrdef视图的adsrc列，
第二：pg_class视图的relhassoids列，
第三：pg_constraint视图的consrc列，
系统视图中修改的列
第一：pg_shadow视图的valuntil列，数据类型改为timestamp with time zone列
第二：pg_stat_ssl视图的client_dn列，原来的列名是lientdn
pg_indexes:
pg 12中，该视图包括分区索引。pg_class视图的relkind列包括T类型的索引。
实例的启动日中，包括版本号信息
最大连接数：
用于replication的连接数从max_wal_senders参数获得，不在依赖max_connections参数。
max_wal_senders信息已经被加入到pg_controldata命令的输出中。
jsonb type和gin 索引
jsonb @@jsonpath 和 jsonb @?jsonpath这两个操作符被加入到 jsonb 类型的GIN index中。
GIN 索引的操作符类别可以用jsonb_ops或者json_path_ops.
ECPG
declare statement
bytea data typePluggable Storage Engine
默认存储引擎是由defaut_table_access_method 参数指定的，该参数默认值是heap
pg_am系统视图中新加入了一行，如下：
amname | amhandler | amtype
--------+----------------------+--------
heap | heap_tableam_handler | t
create table data1(c1 numeric,c2 varchar(10)) using heap;
create table data2 using heap as select *from data1;
create matrialized view mview1 using heap as select count(*) cnt from data1;
\d+ table_name 的输出如下：
postgres=# \d+ data1;
Table "public.data1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
c1 | numeric | | | | main | |
c2 | character varying(10) | | | | extended | |
Access method: heap
postgres=#
通过在psql中设置\set HIDE_TABLEAM ON 来禁用 Access method的显示，如下：
postgres=# \set HIDE_TABLEAM ON
postgres=# \d+ data1
Table "public.data1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
c1 | numeric | | | | main | |
c2 | character varying(10) | | | | extended | |
postgres=#
另外，\dA也能输出访问方法
postgres=# \dA
List of access methods
Name | Type
--------+-------
brin | Index
btree | Index
gin | Index
gist | Index
hash | Index
heap | Table
spgist | Index
(7 rows)
postgres=#
pg_hba.conf文件
clientcert 条目的新参数：verfy-full
this option also ensures that the certificate's cn(common name) matches the username or proper mapping
gssapi认证
Generic Security Standard Application Programming Interface (GSSAPI) is available for client
hostgssenc 和hostnogssenc
若是启用gssapi，请在configure时指定--with-gssapi选项。Text search
pg11有16种语言支持全文检索，pg12中有22中语言支持全文检索
postgres=# \dF
List of text search configurations
Schema | Name | Description
------------+------------+---------------------------------------
pg_catalog | arabic | configuration for arabic language --->>>pg 12新支持
pg_catalog | danish | configuration for danish language
pg_catalog | dutch | configuration for dutch language
pg_catalog | english | configuration for english language
pg_catalog | finnish | configuration for finnish language
pg_catalog | french | configuration for french language
pg_catalog | german | configuration for german language
pg_catalog | hungarian | configuration for hungarian language
pg_catalog | indonesian | configuration for indonesian language --->>>pg 12新支持
pg_catalog | irish | configuration for irish language --->>>pg 12新支持
pg_catalog | italian | configuration for italian language
pg_catalog | lithuanian | configuration for lithuanian language --->>>pg 12新支持
pg_catalog | nepali | configuration for nepali language
pg_catalog | norwegian | configuration for norwegian language --->>>pg 12新支持
pg_catalog | portuguese | configuration for portuguese language
pg_catalog | romanian | configuration for romanian language
pg_catalog | russian | configuration for russian language
pg_catalog | simple | simple configuration
pg_catalog | spanish | configuration for spanish language
pg_catalog | swedish | configuration for swedish language
pg_catalog | tamil | configuration for tamil language --->>>pg 12新支持
pg_catalog | turkish | configuration for turkish language
(22 rows)
postgres=#
Libpq API
新增下列三个函数：PQresultMemoryAize GetForeignDataWrapperExtended GetForeignServerExtended
客户端环境变量 PG_COLOR和PG_COLORS
alter table
通过allow_system_table_mods（默认值是off，该参数从pg 9.x就存在）来控制系统视图是否可以被alter
默认参数情况下，系统视图是不允许手工alter的。
postgres=# alter table pg_attribute set (autovacuum_vacuum_scale_factor=0);
ERROR: permission denied: "pg_attribute" is a system catalog
postgres=#
alter type add value
事务块内可以执行alter type t1 add value 'v3';但是，新增的值不能在事务块内使用
commit and chain;
rollback and chain;
copy命令
copy from：
copy from where条件;
\copy from where条件;
copy freeze：
不能在分区表上执行copy freeze
create or replace aggregate
create index
覆盖索引支持Gist索引
Gist索引的free page可以通过vacuum回收
建立gin索引时，wal日志的产生量显著减少
create statistics
create table stat1 （c1 numeric,c2 numeric,c3 varchar(10));
create statistics mcv_stat1(mcv) on c1,c2 from stat1;
create table
generated column:不能插入或者更新列值，只能这样写insert into gen1 values('ab','cd',default);
列值是实际存储表的记录，
此种类型的列，可以查询information_schema.column_column_usage视图获得
此种类型的列，在pg_attribute.attgenerated列中的列值为s
此种类型的列，不能作为分区键，另外，不能定义一个依赖于其他的generated column的generated column，
分区表定义语句中的tablespace子句可用。
分区表定义语句中的FOR Values子句：可以在for values中指定计算公式或者函数
分区表可以被作为外键引用：在pg11时，不允许这么做通过在create table语句的with子句中使用vacuum_index_cleanup=off，来指示vacuum操作不针对index。默认值是on。
postgres=# create table t_vaccum1 (c1 int,c2 varchar(10)) with (vacuum_index_cleanup=off);
CREATE TABLE
postgres=# \d+ t_vaccum1;
Table "public.t_vaccum1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
c1 | integer | | | | plain | |
c2 | character varying(10) | | | | extended | |
Options: vacuum_index_cleanup=off
postgres=#
通过在create table语句的with子句中使用vacuum_truncate=off，来指示vacuum操作不truncate 表中尾部的empty block。默认值是on。
postgres=# create table t_truncate_vacuum1 (c1 int,c2 varchar(10)) with (vacuum_truncate=off);
CREATE TABLE
postgres=#
postgres=# \d+ t_truncate_vacuum1;
Table "public.t_truncate_vacuum1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
c1 | integer | | | | plain | |
c2 | character varying(10) | | | | extended | |
Options: vacuum_truncate=off
postgres=#
explain
在explain中加入settings on来指示执行计划中非默认值的参数
explain (settings on) select * from data1 where c1=100;
reindex (verbose) table concurrently data1;
通过临时建立一个新索引（index_name_ccnew）,然后替换老索引实现。
reindexdb --dbname postgres --echo --concurrently
plpgsql 额外的检查：
可以为参数plpgsql.extra_warnings指定如下两个值，用来输出额外的warning信息：
strict_multi_assignment和too_many_rows
举例：set plpgsql.extra_warnings to 'too_many_rows';vacuum参数:
vacuum (skip_locked) tab_name;
举例说明：第一个session：
postgres=# begin;
BEGIN
postgres=#
postgres=# lock table data1 in exclusive mode;
LOCK TABLE
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
73076
(1 row)
postgres=#第二个session：
postgres=# vacuum (skip_locked) data1;
WARNING: skipping vacuum of "data1" --- lock not available
VACUUM
postgres=# \echo :sqlstate
:sqlstate
postgres=# \echo :SQLSTATE
00000
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
54780
(1 row)
postgres=#
vacuum和analyze语法：
vacuum (verbose true, full true,analyze false) data1;
vacuum (verbose true, index_cleanup off) data1;
vacuum (verbose true, truncate off) data1;
举例如下：
<暂时未列出>
在pg12中，with子句可以指定物化或者非物化，在pg12之前，在with子句中指定的cte都是物化的。
函数：
sql/json函数：下列函数被加入到pg12版本中：
jsonb_path_exists
jsonb_path_match
jsonb_path_query
jsonb_path_query_array
jsonb_path_query_first
pg_partition_tree:用于显示分区表的树结构，同时也支持层次分区结构。
select *from pg_partition_tree('分区表的表名，不是分区名');
pg_partition_root:返回指定分区的top-level 分区表的表名
select pg_partition_root('某个分区名');
pg_partition_ancestor：返回祖先，包括自身。
pg_promote:本函数将备实例提升为主实例。在pg12之前，需要执行pg_ctl promote来提升。
SELECT pg_promote(true, 90) --->>>true：等待；90:等待90秒
在指定时间内没有提升完成或者执行失败时，本函数返回false，否则返回true
pg_ls_tmpdir:返回临时文件的大小.select *from pg_ls_tmpdir();
pg_ls_archive_statusdir:获取归档文件（archive file）的状态。
本函数检查$PGDATA/pg_wal/archive_status目录，输出文件名、大小、修改时间。
本函数不输出archived WAL files的实际信息。
本函数需要superuser权限或者pg_monitor role。
date_trunc函数：本函数可以设置timezone
Hyperbolic函数：下列包括在SQL：standards 2016中的函数已经被添加：
log10
sinh
cosh
tanh
asinh
acosh
atanh
Copy replication slot：执行对现有replication slot的copy
提供pg_copy_physical_replication_slot函数和pg_copy_logical_replication_slot
配置参数方面的变化：
新增的参数：
参数名 描述 默认值
archive_cleanup_command
data_sync_retry
default_table_access_method
log_statement_sample_rate
log_transaction_sample_rate
plan_cache_mode
primary_conninfo
primary_slot_name
promote_trigger_file
recovery_end_command
recovery_min_apply_delay
recovery_target
recovery_target_action
recovery_target_inclusive
recovery_target_lsn
recovery_target_name
recovery_target_time
recovery_target_timeline
recovery_target_xid
restore_command
shared_memory_type
ssl_library 需要configure时启用--with-openssl
ssl_max_protocol_version
ssl_min_protocol_version
tcp_user_timeout
wal_init_zero
wal_recycle有变化的参数：
client_min_messages 不能设置为比ERROR更高级别
dynamic_shared_memory_type “none”值已经被删除
log_autovacuum_min_duration 根据vacuum执行状态来输出内容
log_connections application_name被添加到log中（当本参数设置为on时）
plpgsql.extra_warnings 两个参数被添加：too_many_rows和strict_multi_assignment
trace_sort log格式被改变（当本参数设置为on时）
wal_level 实例启动时会检测是否为合适的值
wal_sender_timeout 参数可以基于user或者sessison改变
default_with_oids 不能设置为on；从pg_settings视图不能引用本参数值。
recovery_target_timeline 默认值改为“lasest”，新加入了一个值“Current”
autovacuum_vacuum_cost_delay 数据类型从integer改为real默认值改变的参数：
参数名 pg11 pg12
autovacuum_vacuum_cost_delay 20 2
extra_float_digits 0 1
jit off on
recovery_target_timeline '' latest
server_version 11.3 12beta1
server_version_num 110003 120000
transaction_isolation default read committed
实用工具：
configure：
删除了configure时的--disable-strong-random选项
添加了configure时的--with-gssapi选项
initdb：
当环境变量TZ没有指定时，initdb参考/etc/localtime文件来确定pg cluster的time zone
oid2name：
短名 长名 描述
-f --filenode 指定file node
-i --indexes 包括index和sequence
-o --oid 指定oid
-q --quiet 忽略header
-s --tablespaces 显示tablespace的oid
-S --system-objects 包括system objects
-t --table 指定表名
-x --extended 输出额外的信息
-d --dbname 连接到的database
-h --host 连接到的hostname（-H选项已经被废弃）
-p --port 连接到的port号
-U --username database namepg_basebackup
当pg_basebackup使用-R参数时，会在-D对应的目录下生成一个standby.signal文件
另外，在postgresql.auto.conf文件中加入了primary_conninfo参数
pg_checksums：之前的名称是pg_verify_checksums
前提：必须干净的关闭pg cluster之后才能运行pg_checksums,否则，汇报如下错误：
pg_checksums: error: cluster must be shut down
pg_ctl
pg_ctl -D data logrotate
pg_dump
--on-conflict-do-nothing ：与--inserts或者--column-inserts配合使用
--extra-float-digits：取值范围是-15到3，使用方式是 set extra_float_digits=指定值
--rows-per-insert：与--inserts配合使用，取值范围是1到2147483647，就是一个insert语句对应多少个记录。
pg_dumpall
--extra-float-digits:取值范围是-15到3，使用方式是 set extra_float_digits=指定值
--exclude-database:可以排除某个或者某几个database，用法是--exclude-database='demodb[12]' -f alldump.sql
额外的注释：
alter user set 语句
pg_rewind语句
新加了--no-sync选项，作用是：不执行sync系统调用
pg_restore
当指定标准输出作为数据输出目的地时，请使用-f-
pg_upgrade
--socketdir选项（或者-s 选项）：指定了建立local socket的directory
--clone选项：使用reflink特性来执行fast cloning操作，该特性只适用于某些os和某些文件系统
psql
csv输出格式：
--csv
--\pset format csv
默认的列分隔符是逗号，可以使用\pset csv_fieldsep来修改
可以用\pset tuples_only on来禁止输出列名
\d可以明确输出分区表，之前的版本不输出分区键信息。
\z输出分区表信息
\conninfo 显示出tcpip信息
\set VERBOSITY sqlstate 显示报错码
\dP 和\dP+显示分区表
vacuumdb
--disable-page-skipping选项：可以在pg9.6及其之后的版本中使用
--skip-locked选项：可以在pg12及其之后的版本中使用
--min-mxid-age:
--min-xid-age:
vacuumlo
短名 长名 描述
-l --limit the upper limit of objects to delete
-n --dry-run 不实际运行
-v --verbose
-h --host
-p --port
-U --username
-w --no-password
-W --password
插件：auto_explain:
新加了auto_explain.log_level参数
postgres=# load 'auto_explain';
LOAD
postgres=# set auto_explain.log_level = notice;
SET
postgres=# set auto_explain.log_min_duration=0;
SET
postgres=# select * from data1 where c1=1000;
NOTICE: duration: 0.084 ms plan:
Query Text: select * from data1 where c1=1000;
Seq Scan on data1 (cost=0.00..20.38 rows=4 width=70)
Filter: (c1 = '1000'::numeric)
c1 | c2
----+----
(0 rows)
postgres=#
JIT编译信息已经加入到log中
citext：
新加了citext_hash_extended用来计算64bit的hash value
hstore
新加了hstore_hash_extended用来计算64bit的hash value
pg_stat_statement
pg_stat_statement_reset函数新加了一个参数用于限制统计信息的删除范围。
统计信息可以基于database id，user id，query id进行删除。
若是这些参数没有忽略（默认值是0），所有的统计信息会被丢弃。
postgres_fdw
新加了用于可以运行在remote session的guc参数
760
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
