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

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)