本文分享如下10个小案例：

如何确定PostGIS依赖组件的版本
如何使用大小写不敏感的数据类型‍‍
表函数及数组元素动态引用
function、procedure、routine
Java如何映射bit类型
Java使用Double存储varchar精度问题
巧用分区键表达式导数据
ACL默认显示问题
Drop Table后空间未释放问题
如何追踪服务端错误堆栈

如何确定PostGIS依赖组件的版本

例如使用PG 16版本安装PostGIS，应该如何选择PostGIS的版本，同时PostGIS依赖的第三方组件Geos、PROJ、GDAL应该使用什么版本？

从PostGIS的wiki网站可以找到最佳搭配： https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

从上面的矩阵可以看出，在PG 16里PostGIS需要使用3.3～3.5版本，支持PostGIS的第三方组件版本也有相应的版本矩阵。

如何使用大小写不敏感的数据类型

一个迁移案例里Java程序提示找不到该扩展的控制文件，截图如下：

详细查看发现应用框架使用了citext扩展插件，在服务端手工编译这个插件即可正常。

使用citext插件可以创建citext数据类型，citext数据类型是一个不区分大小写的文本类型，允许在进行文本比较时忽略大小写，进行文本比较时更加方便和快速。据了解，这个插件在各大云厂商的服务里是标配。

关于citext的详细介绍请参考官网附录：额外的扩展插件。

表函数及数组元素动态引用

从Oracle迁移过来的table()函数，接收数组类型，参考SQL如下：

select * from table(array);

在PG里可以使用unnest函数替换

select unnest(array);

例如：先使用array + values列表或子查询来构建数组

postgres=# select array(values('one'), ('two') );   array   ----------- {one,two}(1 row)

再使用unnest函数转换

postgres=# select unnest(array(values('one'), ('two') )); unnest -------- one two(2 rows)

对数组元素的引用，PG里还可以使用[N:M]，尤其是使用[:M]，取前M个元素；或者使用[N:]，取后N个元素。

select (array_agg(pid))[:2] from pg_stat_activity; 
select (array_agg(pid))[2:] from pg_stat_activity;

这个小特性是从9.6版本开始支持。

function、procedure、routine

如下的授权语句在PG 11之前的版本执行时提示错误如下：

postgres=# grant execute on all routines in schema pg_catalog to admin;ERROR:  syntax error at or near "routines"...

从PG 11开始，才正式引入procedure存储过程，对函数及存储过程授权可以分开

grant execute on all functions in schema pg_catalog to admin;grant execute on all procedures in schema pg_catalog to admin;

PG 11开始也可以使用routines包含functions和procedures：

grant execute on all routines in schema pg_catalog to admin;

routine的概念在PG里包含的范围其实更广，可参考官网如下描述：

Java如何映射bit类型

数据库里使用bit比特类型时，Java里最合适的映射类型是String，如果是bit(1)一个比特位，可以使用boolean类型映射。下面的示例演示使用String及int类型来映射bit(3)。

首先进行表结构创建及数据插入

postgres=>  create table tab_bit(bt bit(3));CREATE TABLEpostgres=> insert into tab_bit values(B'001'),(B'010'),(B'100');INSERT 0 3

使用String类型接收测试截图如下：

使用int类型接收测试截图如下：

从测试结果看，使用int并不合适，会直接将比特串强转为int。

Java使用Double类型存储varchar精度丢失问题

首先进行表结构创建如下：

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，此时PG 11和PG 12入库的结果会有差异。

为什么12里使用varchar存储没有丢失精度呢，从官方文档的如下片段可以找到答案：

Now, whenever extra_float_digits is more than zero (as it now is by default), only the minimum number of digits required to preserve the exact binary value are output. The behavior is the same as before when extra_float_digits is set to zero or less.
https://www.postgresql.org/docs/12/release.html

从12版本开始，不管extra_float_digits参数如何设置，浮点数都使用准确的精度输出。

巧用分区键表达式导数据

在一个csv格式的文件里，有三个字段a,b,c，数据文件参考如下：

$ cat data.csv a,b,c1,aa1001,data2,bb1002,data

copy入库时可以截取b字段的前两位入到对应分区吗？是否需要使用触发器呢？ PG 10版本支持声明式分区，分区键还可以使用表达式：

CREATE TABLE tab(a varchar,b varchar,c varchar) PARTITION BY LIST(substr(b,1,2));

创建分区

CREATE TABLE tab_aa PARTITION OF tab FOR VALUES IN('aa');
CREATE TABLE tab_bb PARTITION OF tab FOR VALUES IN('bb');

使用copy入库

postgres=# \copy tab from data.csv with (format 'csv',header true)COPY 2

查询数据

postgres=# select * from tab; a |   b    |  c   ---+--------+------ 1 | aa1001 | data 2 | bb1002 | data(2 rows)
postgres=# select * from tab_aa; a |   b    |  c   ---+--------+------ 1 | aa1001 | data(1 row)
postgres=# select * from tab_bb; a |   b    |  c   ---+--------+------ 2 | bb1002 | data(1 row)

ACL默认显示问题

为什么表查看ACL（Access Control List）为空呢？

这其实并不奇怪，当没有做任何显式权限分配时，ACL为空是正常的，此时并不会显示owner的权限信息，因为owner比较特殊。当我们使用grant分配权限之后，就可以查看完整的ACL信息，此时会暴露出owner的权限信息。

对象的owner的权限信息默认不会展示，首次使用grant语句分配触发后即可暴露。

Drop Table后空间未释放问题

参考文章链接：

PostgreSQL中drop table后磁盘空间未及时释放问题
https:///db/1767100886471512064

使用12.3版本的时候遇到一个问题，会话1查询了一个大于1G的表之后未做其他操作且会话一直保持，会话2执行drop table后，磁盘空间一直未释放，直到会话1再执行其他操作或者结束会话，磁盘才会释放空间。在12.6的官网发布说明中有该问题的相关描述：

Previously, if the dropped relation spanned multiple 1GB segments, only the first segment was truncated immediately. Other segments were simply unlinked, which doesn't authorize the kernel to release the storage so long as any other backends still have the files open.
https://www.postgresql.org/docs/12/release-12-6.html

如果使用更老的版本，需要注意该问题。

如何追踪服务端错误堆栈

排查openGauss问题时可以通过服务端设置如下参数：

backtrace_min_messages='error'

该参数会打印函数的堆栈信息到服务器日志文件中，方便数据库研发人员准确定位分析问题。借助该参数很快定位到引起业务异常的原因，在当前版本的PG里并没有这样的功能参数。

不过当前PG里有个类似的参数backtrace_functions，需要知道内部函数名称，然后配置到该参数里，服务端就可以获取调用的函数堆栈信息，通过查看日志可以详细分析。好消息是在开发中的PG 17引入了backtrace_on_internal_error参数，大概是对其了这个功能，参考截图如下：

本文分享到这就结束了，再分享一句我喜欢的话：这些飘香的新特性值得我们学习！让我们通过这些案例在PG的花园里挖出更多收获之花。

期望各位小主们多关注支持～

ITPub博客

PostgreSQL小案例集｜4月刊