本期内容如下：
- 函数的自增序号
- 版本兼容性案例
- 备库catchup快速追主
一、函数的自增序号
FROM子句后的函数使用with ordinality后缀修饰时，函数的每行输出将追加一列类型为bigint的自增序号。
例如常见unnest函数将数组展开为表：
postgres=# SELECT * FROM unnest(array(values('one'), ('two'))) WITH ORDINALITY AS t(f1,f2);
f1 | f2
-----+----
one | 1
two | 2
(2 rows)
复制不使用with ordinality，需要额外生成行号语句会复杂些
SELECT unnest(array(values('one'), ('two'))) AS f1,
generate_series(1, array_length(array(values('one'), ('two')), 1)) AS f2;
复制批量生成测试数据的generate_series函数也很合适使用with ordinality
postgres=# SELECT f2,f1 FROM generate_series(2_00, 1_00,-50) WITH ORDINALITY AS t(f1,f2);
f2 | f1
----+-----
1 | 200
2 | 150
3 | 100
(3 rows)
复制另外，对于xml和json类型的函数也能使用for ordinality
SELECT * FROM json_table(
'[{"a":10,"b":20},{"a":30,"b":40}]'::jsonb,
'$[*]'
COLUMNS (
id FOR ORDINALITY,
column_a int4 path '$.a',
column_b int4 path '$.b',
a int4,
b int4,
c text
)
);
复制二、两个有趣的版本兼容性案例
- 版本9.6到10使用insert语句与generate_series函数生成数据不一致问题
INSERT INTO test VALUES ( generate_series(1, 10) ,generate_series(1, 3) );复制
上面的插入语句里两个generate_series函数返回行数不一致，且一个行数不是另外一个的倍数时，在9.6和10分别有不一样的行为，测试截图如下：
第一条语句，即便返回行数不一致，只要其中一个是另一个的倍数，版本行为是一致的，插入的数据条数是第一个函数返回的行数10，第二个函数返回的行数2，能整除，值不断重复生成如下：
这是9.6版本的行为，而10版本插入的行数虽然是一致，但第二个函数只产生2行数据不会重复，其他数据库行的第二列值为NULL。
第二条语句，两个函数返回行数不一致，且10不能整除3，低版本9.6产生笛卡尔积，10版本以上，第二个函数同样不会重复生成数据:
大家可以实测一下
- 版本11到12浮点数精度显示不一致问题
之前第37期案例分享过这个问题，表结构如下：
create table t1(a varchar);
复制Java使用double类型插入数据主要代码如下：
String sql = "insert into t1 values(?)"; PreparedStatement ps = connection.prepareStatement(sql); ps.setObject(1, new Double(48)); ps.execute(); ps.setObject(1, new Double(48.1)); ps.execute(); ps.setObject(1, new Double(48.9)); ps.execute();复制
JDBC驱动层发送到服务端时会隐含设置extra_float_digits = 3，此时版本11和12入库的结果会有差异。
从12版本开始，浮点数输出显示的精度做了优化，extra_float_digits参数描述也说明了这一点，使用shortest-precise模式。
三、主备节点间的catchup功能
微信群友提到备份工具pg_probackup的catchup的功能，之前没注意到，后来了解到是2.5.2版本新增的功能，主要有两个场景：
- 快速添加新的standby节点
- 让落后的standby快速追赶主库
例如使用catchup命令快速搭建standby节点：
$ pg_probackup catchup --backup-mode=full \
--source-pgdata=/home/postgres/data_master \
--destination-pgdata=/home/postgres/data_standby3000 \
--pguser=backup --pgdatabase=postgres --pgport=2000 \
--remote-host=192.168.20.100 \
--remote-port=22 \
--remote-user=postgres \
--stream --threads=4
复制使用catchup命令增量追赶主库
$ pg_probackup catchup --backup-mode=delta \
--source-pgdata=/home/postgres/data_master \
--destination-pgdata=/home/postgres/data_standby3000 \
--pguser=backup --pgdatabase=postgres --pgport=2000 \
--remote-host=192.168.20.100 \
--remote-port=22 \
--remote-user=postgres \
--exclude-path=postgresql.conf \
--stream --threads=4
复制同时保留postgresql.conf配置文件，不同步。
其他配置文件不需要同步也可以继续添加，例如：
--exclude-path=postgresql.auto.conf \ --exclude-path=pg_hba.conf \ --exclude-path=pg_ident.conf \复制
注意增量追赶主库，需要先停standby节点的服务，才能操作。
推荐阅读
- 数据库微观案例第43期
- 数据库微观案例第42期
- 数据库微观案例第41期 ｜NULL值案例
- 数据库微观案例第40期
- PostgreSQL智慧碎片｜微观案例 ｜宏观收获
- PostgreSQL小案例集｜4月刊
与我联系
- 微信公众号：象楚之行
- 墨天轮：https://www.modb.pro/u/15675
- 微信：skypkmoon
勤耕细作，用心积微；静待花开，量变质成。
