PostgreSQL知识分享-第32期

本期整理的几个问题如下：

• search_path设置
• 默认权限设置
• 数据导出格式(包括json、excel等)
• 触发器执行顺序
• JDBC批量插入batchSize设置

一、search_path设置问题

PG数据库相比MySQL和Oracle多了一层逻辑的schema，很多开发人员经常会遇到找不到表对象，需要设置schema的问题。

在JDBC里可以设置currentSchema参数，参考如下：

jdbc:postgresql://ip:port/my_db?currentSchema=my_schema_name

更好的方式是在数据库服务端，对业务db或者user进行search_path设置。

alter database my_db set search_path = my_schema_name;

alter user my_user set search_path = my_schema_name;

alter user my_user in database my_db set search_path to my_schema_name;

可以使用上面三种方式中的任意一种，在db、user或者user与db的组合上进行设置。

如果使用psql，也有如下几种方式进行设置：

方式一：同时使用-c和-f

$ psql -c "set search_path to my_schema_name" -f script.sql

方式二：通过环境变量PGOPTIONS进行设置

$ env PGOPTIONS="-c search_path=my_schema_name" psql

方式三：参考libpq连接串变量语法，使用options进行设置

$ psql "dbname=postgres options=-csearch_path=my_schema_name"

二、默认权限设置问题

首先进行如下操作准备：

create user dba_admin superuser;
create user testuser createdb;
create database test owner testuser;

接下来，管理用户dba_admin登录test数据库：

$ psql -U dba_admin test

顺序执行下列语句：

CREATE TABLE public.t1(id int);

GRANT USAGE, CREATE ON SCHEMA public TO testuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO testuser;

CREATE TABLE public.t2(id int);

CREATE VIEW v_t1 as select * from t1 ;

最后使用testuser用户测试t1、t2表以及v_t1视图的访问权限，结果如下：

• 无法访问t1表
• 可以访问t2表和v_t1视图

v_t1视图可以被访问，有的人可能不太理解。视图类属于表，对table的默认权限设置也适用于view，因此上面的ALTER DEFAULT PRIVILEGES语句设置之后的t2表和v_t1视图都可以被访问。这也是使用视图非常方便的一个特性，可以减少视图里相关对象的权限设置而允许用户有部分访问权限。

当然我们也可以更安全的控制视图v_t1的权限，让其检测基表的权限，不过这需要在15及以上的版本。使用security_invoker属性即可，参考操作如下：

alter view v_t1 set (security_invoker = on);

设置security_invoker属性后，v_t1视图引用的t1没有访问权限，那v_t1视图也没有权限。

三、数据导出格式问题

pg里可以导出多种格式的数据，包括文本格式、csv标准格式、html网页格式，例如：

文本紧凑模式

$ psql -qtA  -c command

文本紧凑模式，使用多字符分隔符@@

$ psql -qtA -F '@@'  -c  "select * from foo;"

HTML格式

$ psql -qtH  -c command

CSV格式，逗号分隔

$ psql -qt --csv  -c command

JSON格式

方式一：使用子查询、row_to_json函数、copy命令

COPY (SELECT row_to_json(t) 
FROM (SELECT * FROM mytab) t) 
TO '/home/postgres/data.json';

方式二：使用row_to_json函数以及元命令\t、\a、\o
使用row_to_json函数，每行数据为一个json

\t\a\o data.json
SELECT row_to_json(r) FROM mytab AS r;

使用json_agg函数，导出表数据为json数组

\t\a\o data.json
select json_agg(t) FROM (SELECT * from mytab) t;

方式三：使用json_arrayagg聚合函数以及元命令\t、\a、\g
从版本16开始，可以直接使用json_arrayagg聚合函数返回JSON数组，而不需要先使用子查询返回给json_arrary。

元命令\t、\a简写为\t\a，使用json_arrayagg函数查询后，再使用元命令\g输出查询结果：

\t\a
select json_arrayagg (name ORDER BY name) FROM mytab \g data.json

或者用psql

$ psql -d postgres -qAt -c "select json_arrayagg (name ORDER BY name) FROM mytab;" -o data.json

excel格式

excel格式需要使用pgclimb工具导出为excel格式，例如

单表导入到excel

$ ./pgclimb --dbname=postgres --output=test.xlsx --query="select * from test" xlsx  

多张表分别导入excel不同sheet

$ ./pgclimb --dbname=postgres --output=data.xlsx --query="select * from employees" xlsx  --sheet emp

$ ./pgclimb --dbname=postgres --output=data.xlsx --query="select * from departments" xlsx  --sheet dep

$ ./pgclimb --dbname=postgres --output=data.xlsx --query="select * from jobs" xlsx  --sheet job

四、触发器执行顺序问题

PG数据库允许我们对同一张表创建多个触发器，多个触发器的执行顺序是什么规则呢？

结论如下：

1. 首先执行statement级别before触发器
2. 其次执行row级别before触发器
3. 接着执行row级别after触发器
4. 最后执行statement级别after触发器

如果同一类触发器有多个，则按触发器名称的字母顺序执行.

测试代码如下：

CREATE TABLE t_demo (id int);

CREATE FUNCTION trig_func() 
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'name of trigger: %', TG_NAME;
        RETURN NEW;
    END;
$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER c_trigger_bef 
    BEFORE INSERT
    ON t_demo
    FOR EACH ROW 
    EXECUTE PROCEDURE trig_func();
 
CREATE TRIGGER b_trigger_bef 
    BEFORE INSERT
    ON t_demo
    FOR EACH ROW
    EXECUTE PROCEDURE trig_func();
 
CREATE TRIGGER a_trigger_bef 
    BEFORE INSERT
    ON t_demo
    FOR EACH STATEMENT
    EXECUTE PROCEDURE trig_func();
 
CREATE TRIGGER c_trigger_aft 
    AFTER INSERT
    ON t_demo
    FOR EACH ROW 
    EXECUTE PROCEDURE trig_func();

CREATE TRIGGER d_trigger_aft 
    AFTER INSERT
    ON t_demo
    FOR EACH ROW 
    EXECUTE PROCEDURE trig_func();
 
CREATE TRIGGER b_trigger_aft 
    AFTER INSERT
    ON t_demo
    FOR EACH STATEMENT 
    EXECUTE PROCEDURE trig_func();

插入结果如下：

postgres=# INSERT INTO t_demo VALUES (1);
NOTICE:  name of trigger: a_trigger_bef
NOTICE:  name of trigger: b_trigger_bef
NOTICE:  name of trigger: c_trigger_bef
NOTICE:  name of trigger: c_trigger_aft
NOTICE:  name of trigger: d_trigger_aft
NOTICE:  name of trigger: b_trigger_aft
INSERT 0 1

参考链接：USING MULTIPLE TRIGGERS ON THE SAME TABLE IN POSTGRESQL

五、JDBC批量插入batchSize设置

在进行查询时，如果一次性返回一张表的所有数据，可能会造成客户端内存不足，此时需要设置
fetchSize参数，按批次获取数据。

进行批量更新和批量插入的时候，也可以设置batch size批次大小，相当于设置Buffer缓冲区。batch size设置越大，批量操作向数据库发送sql的次数越少，速度就越快。

insert插入操作还可以结合reWriteBatchedInserts参数，将多条insert合并为一条insert，使用insert…values(?),(?)…多values的形式。

但batch size的值最好设置为2的指数倍，利用pg_stat_statements插件，使用如下语句进行测试观察，可以得到最佳实践值。

select queryid,query,calls,rows from pg_stat_statements where query like '%test_tab%';

例如我的测试表有10个字段，插入1W条数据，如果设置100条数据一个batch，观察到产生300次调用，而设置64条数据一个batch，产生不到200次调用，后者明显效率更高。