从Oracle到PG-PostgreSQL参数优先级别测试验证

开源软件联盟PostgreSQL分会 2019-12-10

752

Oracle的参数可以设置system和session级别，当设置了session级别的参数时，会覆盖值system级别。postgresql除了该两个级别外，还有database级别、user/role级别、user/role+database组合级别。

下面针对这几个级别的参数优先级问题，在postgresql-11.5进行测试验证，测试以参数client_min_messages为例子。

测试数据库用户：aken

测试database：akendb01

测试验证前，client_min_messages最初参数值：session默认继承当前system级别。

(postgres@[local]:5432)[postgres]#show client_min_messages ;
client_min_messages
---------------------
warning <<<当user=postgres且database=postgres时，session最初继承的system级别的参数值
(1 row)
(aken@[local]:5432)[akendb01]#select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | warning <<<当user=aken且database=akendb01时，session最初继承的system级别的参数值
(1 row)
(postgres@[local]:5432)[postgres]#

测试前，client_min_messages在user级别、database级别、user+database组合级别的参数值均未设置：

(postgres@[local]:5432)[postgres]#select usename,a.useconfig,c.setconfig from pg_user a, pg_db_role_setting c where a.usesysid=c.setrole and a.usename='aken';
usename | useconfig | setconfig
---------+-----------+-----------
(0 rows)
(postgres@[local]:5432)[postgres]#select datname,c.setconfig from pg_database b,pg_db_role_setting c where b.oid=c.setdatabase and b.datname like 'akendb%';
datname | setconfig
---------+-----------
(0 rows)
(postgres@[local]:5432)[postgres]#select usename,datname,a.useconfig,c.setconfig from pg_user a,pg_database b,pg_db_role_setting c where a.usesysid=c.setrole and b.oid=c.setdatabase and a.usename='aken' and b.datname like 'akendb%';
usename | datname | useconfig | setconfig
---------+---------+-----------+-----------
(0 rows)

测试一：设置user级别参数，测试对比user级别和system级别的优先级。

(aken@[local]:5432)[postgres]#select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | warning <<<设置前，当user=aken且database=postgres时，session最初继承的system级别的参数值
(1 row)
(postgres@192.168.1.108:5432)[postgres]#\c - postgres
Password for user postgres:
You are now connected to database "postgres" as user "postgres".
(postgres@[local]:5432)[postgres]#alter role aken set client_min_messages=ERROR;
ALTER ROLE
(postgres@[local]:5432)[postgres]#select usename,a.useconfig,c.setconfig from pg_user a, pg_db_role_setting c where a.usesysid=c.setrole and a.usename='aken';
usename | useconfig | setconfig
---------+-----------------------------+-----------------------------
aken | {client_min_messages=error} | {client_min_messages=error}
(1 row)
(postgres@192.168.1.108:5432)[postgres]#\c - aken
Password for user aken:
You are now connected to database "postgres" as user "aken".
(aken@192.168.1.108:5432)[postgres]>select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | error <<<设置后，当user=aken且database=postgres时，session继承user级别参数值，即user级别覆盖system级别
(1 row)
(aken@192.168.1.108:5432)[postgres]>

从上面的测试可以看出，用户aken登录akendb01的session参数client_min_messages继承了user级别的参数值：client_min_messages=error。

所以，user级别的优先于system级别。

测试二、设置database级别参数

对比database和system级别、database和user级别的参数优先级。

(aken@192.168.1.108:5432)[postgres]>\c - postgres
Password for user postgres:
You are now connected to database "postgres" as user "postgres".
(postgres@192.168.1.108:5432)[postgres]#
(postgres@[local]:5432)[postgres]#select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | warning <<<设置前，当user=postgres且database=postgres时，session最初继承的system级别参数值
(1 row)
(postgres@192.168.1.108:5432)[postgres]#alter database akendb01 set client_min_messages=log; <<<设置database级别
ALTER DATABASE
(postgres@192.168.1.108:5432)[postgres]#select datname,c.setconfig from pg_database b,pg_db_role_setting c where b.oid=c.setdatabase and b.datname like 'akendb%';
datname | setconfig
----------+---------------------------
akendb01 | {client_min_messages=log}
(1 row)
(postgres@[local]:5432)[postgres]#
(postgres@[local]:5432)[postgres]#\c akendb01
You are now connected to database "akendb01" as user "postgres".
(postgres@[local]:5432)[akendb01]#select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | log <<<设置后，当user=postgres并且database=akendb01时，session继承了database级别的参数，即database级别覆盖system级别。
(1 row)
(postgres@[local]:5432)[akendb01]#
(postgres@[local]:5432)[akendb01]#\c - aken
You are now connected to database "postgres" as user "aken".
(aken@[local]:5432)[akendb01]>select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | error <<<当user=aken且database=akendb01时，session继承了user级别的参数值，即user级别覆盖database级别
(1 row)
(aken@192.168.1.108:5432)[akendb01]>
(aken@[local]:5432)[akendb01]>

上面的测试可以看出：

1.用户postgres在database=akendb01的session，参数client_min_messages继承了database级别的参数值，即client_min_messages=log。

2.用户aken登录akendb01的session，参数client_min_messages继承了user级别的参数值：client_min_messages=error。

所以，database级别优先于system级别，而user级别优先于database级别。

测试三、设置user+database级别参数

对比user级别、user+database级别的优先级。

这里说明一下，上面在设置user+database级别之前：

1.在database级别，数据库akendb01设置了client_min_messages=log;

2.在user/role级别，用户aken设置了client_min_messages=error;

3.在user=aken且database=akendb01的会话中，session继承了user级别的参数值，即user级别覆盖database和system级别。

(aken@[local]:5432)[akendb01]>select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | error 
(1 row)
(aken@192.168.1.108:5432)[akendb01]>

下面继续加入user+database组合的参数级别：

(aken@[local]:5432)[akendb01]>\c - postgres
You are now connected to database "akendb01" as user "postgres".
(postgres@[local]:5432)[akendb01]#
(postgres@[local]:5432)[akendb01]#alter role aken in database akendb01 set client_min_messages=notice;
LOG: statement: alter role aken in database akendb01 set client_min_messages=notice;
ALTER ROLE
(postgres@[local]:5432)[akendb01]#select usename,datname,a.useconfig,c.setconfig from pg_user a,pg_database b,pg_db_role_setting c where a.usesysid=c.setrole and b.oid=c.setdatabase and a.usename='aken' and b.datname like 'akendb%';
usename | datname | useconfig | setconfig
---------+----------+-----------------------------+------------------------------
aken | akendb01 | {client_min_messages=error} | {client_min_messages=notice}
(1 row)
(postgres@[local]:5432)[akendb01]#
(postgres@[local]:5432)[akendb01]#\c - aken
You are now connected to database "akendb01" as user "aken".
(aken@[local]:5432)[akendb01]>select name,context,setting from pg_settings where name like 'client_min%';
name | context | setting
---------------------+---------+---------
client_min_messages | user | notice <<< user=aken并且database=akendb01时，session继承了user+database级别的参数值，user+database级别覆盖system、database、user级别。
(1 row)
(aken@[local]:5432)[akendb01]>

上面的测试可以看出：

1.用户aken登录akendb01的session，参数client_min_messages继承了user+database组合级别的参数值：client_min_messages=notice。

所以，user+database组合级别优先于user级别。

最后，在user+database组合级别的session中直接set参数值：

(aken@[local]:5432)[akendb01]>set client_min_messages=warning;
SET
(aken@[local]:5432)[akendb01]>
(aken@[local]:5432)[akendb01]>show client_min_messages ;
client_min_messages
---------------------
warning <<< 直接set session级别参数值，覆盖前面的所有级别
(1 row)
(aken@[local]:5432)[akendb01]>

综上，postgres参数级别的优先顺序为：

1.默认情况下，session会继承system级别参数值；

2.在设置了database级别参数的情况下，database级别的参数值优先于system级别；

3.在设置了user/role级别参数的情况下，user级别的参数值优先于database级别；

4.在设置了user+database级别参数的情况下，该组合级别的参数值优先于user级别；

5.在当前session给会话直接set parameter=value的情况下，该参数值最优先。

欢迎投稿

中国开源软件推进联盟PostgreSQL分会，欢迎大家积极投稿，向PGer分享自己的实践经验、心得体会，共建PG中国生态。

投稿邮箱：

partner@postgresqlchina.com

oracle

最后修改时间：2019-12-11 09:25:24

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

从Oracle到PG-PostgreSQL参数优先级别测试验证

评论