Postgres schema search_path

/**在 TEST_DM库中新建 schema：test_dm 并授权*/

CREATE schema test_dm authorization uown_test_dm;

CREATE SCHEMA (模式名）AUTHORIZATION（用户名）中的用户名指的是将拥有该模式的用户名．如果省略，缺省为执行该命令的用户名． 只有超级用户才能创建不属于自己的模式．

另外，CREATE SCHEMA 可以包括在新模式中 创建对象的子命令．这些子命令和那些在创建完模式后发出的命令没有 任何区别，只不过是如果使用了 AUTHORIZATION 子句， 那么所有创建的对象都将被该用户拥有．

COMMENT ON schema "test_dm" IS 'DM层测试应用';

--授权

GRANT ALL ON SCHEMA test_dm TO uapp_test_dm;-- 能访问的用户

GRANT USAGE ON SCHEMA test_dm TO public; --能创建对象的用户，不建议这样，owner太多分不清

/** 添加 search_path */

-- 查新当前用户 search_path

show search_path;

-- 修改当前用户 search_path

set search_path = "$user", public, test_dm;

-- 修改指定数据库 search_path

alter database "test_DM" set search_path to public, test_dm

您可以在数据库级别设置默认的search_path：

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

或在用户或角色级别：

ALTER ROLE <role_name> SET search_path TO schema1,schema2;

在一个特定的数据库中设置用户的搜索路径。

ALTER ROLE <role_name> IN DATABASE <db_name> SET search_path TO schema1,schema2;

创建数据库后，默认情况下会从一个名为template1的隐藏"模板"数据库中创建该数据库，您可以更改该数据库以为将来创建的所有数据库指定新的默认搜索路径。您还可以创建另一个模板数据库，并使用CREATE DATABASE TEMPLATE 创建数据库。

背景

使用schema作为多租户隔离的技术, 当租户访问数据库时, 设置search_path. (业务不需要拼接sql nsp.object, 直接使用search_path来设置)

set search_path 和后续sql会不会分配到不同的后端连接, 导致set search_path无法达到改租户目标的目的? 这个问题不在本文讨论之列, 如果有问题, 可以考虑几个方案:

- 采用simple query, 把set search_path和业务sql封装到1个请求. 注意可能增加sql注入风险. 因为无法使用绑定变量.

- 使用事务方式, 每次发起请求时, 开启事务, 然后设置search_path, 性能会有问题.

- 使用函数发起请求, search_path放到函数里面.

本文测试大量set search_path的性能如何?

do language plpgsql $$

declare

begin

for i in 1..1000 loop

execute 'create schema sc'||i;

execute format('create table %I.abc(id int primary key, info text, crt_time timestamp);', 'sc'||i);

end loop;

end;

$$;

create or replace function upsert_multi_nsp(int, int) returns void as $$

declare

nsp name := 'sc'||$1;

begin

execute format('set search_path=%I', nsp);

execute format('insert into abc values (%s, random()::text, now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', $2);

reset search_path;

exception when others then

reset search_path;

end;

$$ language plpgsql strict;

vi test.sql

\set v1 random(1,1000)

\set v2 random(1,100000)

select upsert_multi_nsp(:v1,:v2);

```

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200

progress: 144.0 s, 189369.0 tps, lat 0.169 ms stddev 0.055

progress: 145.0 s, 188017.3 tps, lat 0.170 ms stddev 0.070

progress: 146.0 s, 183697.5 tps, lat 0.174 ms stddev 0.065

progress: 147.0 s, 185821.0 tps, lat 0.172 ms stddev 0.075

progress: 148.0 s, 187040.4 tps, lat 0.171 ms stddev 0.060

progress: 149.0 s, 186279.8 tps, lat 0.172 ms stddev 0.067

progress: 150.0 s, 187095.5 tps, lat 0.171 ms stddev 0.068

```

非set search_path的性能

create table abc(id int primary key, info text, crt_time timestamp);

create or replace function upsert_nsp(int) returns void as $$

declare

begin

execute format('insert into abc values (%s, random()::text, now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', $1);

end;

$$ language plpgsql strict;

vi test.sql

\set v1 random(1,100000000)

select upsert_nsp(:v1);

```

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 1200

progress: 56.0 s, 271966.4 tps, lat 0.117 ms stddev 0.159

progress: 57.0 s, 271754.7 tps, lat 0.118 ms stddev 0.161

progress: 58.0 s, 272925.0 tps, lat 0.117 ms stddev 0.151

progress: 59.0 s, 268870.2 tps, lat 0.118 ms stddev 0.163

progress: 60.0 s, 272693.3 tps, lat 0.118 ms stddev 0.167

progress: 61.0 s, 267019.3 tps, lat 0.120 ms stddev 0.151

progress: 62.0 s, 271654.5 tps, lat 0.118 ms stddev 0.137

progress: 63.0 s, 273257.4 tps, lat 0.117 ms stddev 0.139

progress: 64.0 s, 272775.8 tps, lat 0.117 ms stddev 0.127

progress: 65.0 s, 272574.6 tps, lat 0.117 ms stddev 0.137

progress: 66.0 s, 269902.3 tps, lat 0.118 ms stddev 0.146

```

1000个schema, 每次请求前设置search_path, 请求结束后reset search_path. qps 187095.

1个schema, qps 269902.

性能还是有一定差别, 但是依旧可以保持比较高的qps, 引入的rt约0.05毫秒, 对于大多数的sql来说0.05毫秒不算什么.

show search_path;

设置方法

SET search_path TO itsm;

ALTER database "数据库" SET search_path TO "模式";

实在不行

alter user “aaaa” set search_path TO "模式";

本文主要介绍当访问数据库中对象时（比如表）,非限定名访问时数据库系统搜索表的路径及顺序，精确定位所访问的表位于哪个模式下。

并附上检索表常见问题解决方案。

详细信息

说明

限定名方式访问表： 数据库.模式.表

数据库中的模式，相当于引用表时需要用到的限定名。如果把一个特定模式名拉到应用中（如 select ...from 模式名.表名）写起来很冗长。一般情况下我们需要访问时省略限定名（如select ... from 表名），因此数据库的每一个session都会有个默认的当前模式（限定名），但当一个数据库中有多个模式的时候，系统将沿着一条搜索路径来决定该名称指的是哪张表。

搜索路径是一个进行查看的模式列表，搜索路径中的第一个模式称为当前模式。

search_path参数是用来配置模式列表的，当使用非限定名引用指定表时，系统将按照search_path中配置的模式列表顺序依次检索该名称表，到第一次检索到为止。

1、search_path功能介绍

1.1 默认配置

1.1.1 介绍

在HighgoDB中，search_path的默认配置为“$user”,public

其中，$user代表与当前会话用户同名的模式，public为公共模式，create database时默认创建。

可以登录数据库后使用show search_path; 查看搜索路径

highgo=> show search_path;

search_path

-----------------

"$user", public

(1 row)

此处的模式列表值，与postgresql.conf文件中search_path一致。

使用非限定名创建对象，默认创建在当前模式下。

1.1.2 例子

（1）如当前会话用户是highgo,由于数据库中没有名为highgo的模式，因此默认当前模式为public。

highgo=# select current_user; --当前用户highgo

current_user

--------------

highgo

(1 row)

highgo=# \dn --数据库中的模式public

List of schemas

Name | Owner

-------- --------

public | highgo

(1 row)

highgo=# select current_schema; --默认当前模式public

current_schema

----------------

public

(1 row)

（2）如果数据库中有与当前用户同名的模式，则默认当前模式为用户同名模式。

highgo=# create schema highgo; --创建名为highgo的模式

CREATE SCHEMA

highgo=# select current_user; --当前用户highgo

current_user

--------------

highgo

(1 row)

highgo=# \dn --数据库中存在的模式highgo、public

List of schemas

Name | Owner

-------- --------

highgo | highgo

public | highgo

(2 rows)

highgo=# select current_schema; --默认当前模式highgo

current_schema

----------------

highgo

(1 row)

（3）如果修改search_path，使得public在"$user"前，则即使存在与当前用户同名的模式，默认当前模式为public。

highgo=# show search_path; --调整前搜索路径

search_path

-----------------

"$user", public

(1 row)

highgo=# set search_path to public,"$user"; --修改将public放置第一位

SET

highgo=# show search_path; --修改后搜索路径，注意模式列表顺序

search_path

-----------------

public, "$user"

(1 row)

highgo=# select current_user; --当前用户highgo

current_user

--------------

highgo

(1 row)

highgo=# \dn --数据库中存在的模式highgo、public

List of schemas

Name | Owner

-------- --------

highgo | highgo

public | highgo

(2 rows)

highgo=# select current_schema; --当前默认模式public

current_schema

----------------

public

(1 row)

1.1.3 总结

综上，当用户登录数据库，默认使用的当前模式为search_path中模式列表排在第一位的模式。

"$user"代表当前用户名。

如果要修改默认的当前模式，可以通过修改search_path改变模式列表位置以改变搜索顺序。

1.2 访问限制

1.2.1 介绍

对于数据库中存在多个模式，不同模式下的表是具有逻辑分离的。当search_path的值为默认值时，非限定名访问用户只能访问到与自己同名模式下及public模式下的表。如果要访问其他模式下的表有有如下方式：

方式一：使用限定名访问（模式名.表名）

方式二：切换当前模式（set search_path to ...）

方式三：修改搜索模式列表（search_path）

1.2.2 例子

准备：

添加模式myschema,并分别在模式public、highgo、myschema创建表（使用限定名方式创建）

highgo=# create schema myschema;

CREATE SCHEMA

highgo=# create table public.t_public(name varchar);

CREATE TABLE

highgo=# create table highgo.t_highgo(name varchar);

CREATE TABLE

highgo=# create table myschema.t_myschema(name varchar);

CREATE TABLE

使用\dt列出能检索到的表对象

highgo=# select current_user; --当前用户highgo

current_user

--------------

highgo

(1 row)

highgo=# show search_path; --search_path值

search_path

-----------------

"$user", public

(1 row)

highgo=# \dn --数据库中的模式

List of schemas

Name | Owner

---------- --------

highgo | highgo

myschema | highgo

public | highgo

(3 rows)

highgo=# select current_schema; --当前模式

current_schema

----------------

highgo

(1 row)

highgo=# \dt --检索到的表列表

List of relations

Schema | Name | Type | Owner

-------- ---------- ------- --------

highgo | t_highgo | table | highgo

public | t_public | table | highgo

(2 rows)

如上，检索不到模式myschema下的表。由于当前search_path的模式列表为"$user"、 public，使用非限定名访问模式myschema下表t_myschema时将报错：

highgo=# select * from t_myschema;

2020-06-02 16:16:45.940 HKT [12897] ERROR: relation "t_myschema" does not exist at character 15

2020-06-02 16:16:45.940 HKT [12897] STATEMENT: select * from t_myschema;

ERROR: relation "t_myschema" does not exist

LINE 1: select * from t_myschema;

上述错误可通过1.2.1中三种方式解决

（1）方式一：使用限定名访问（模式名.表名）

highgo=# select * from myschema.t_myschema;

name

------

(0 rows)

highgo=#

（2）方式二：切换当前模式（set search_path to ...）

highgo=# set search_path to myschema; -- 切换当前模式（需要多个可用“，”隔开）

SET

highgo=# show search_path; -- 搜索路径已改变

search_path

-------------

myschema

(1 row)

highgo=# select current_schema; -- 当前模式已变成myschema

current_schema

----------------

myschema

(1 row)

highgo=# select * from t_myschema; -- 再通过非限定名访问

name

------

(0 rows)

highgo=#

注意：set方式是临时方案，只在当前会话起作用。

highgo=# \c highgo highgo -- 新会话

You are now connected to database "highgo" as user "highgo".

highgo=# select current_user; -- 当前模式已变成highgo

current_user

--------------

highgo

(1 row)

highgo=# select * from t_myschema; --非限定名访问报错

2020-06-02 16:27:05.540 HKT [13516] ERROR: relation "t_myschema" does not exist at character 15

2020-06-02 16:27:05.540 HKT [13516] STATEMENT: select * from t_myschema;

ERROR: relation "t_myschema" does not exist

LINE 1: select * from t_myschema;

^

highgo=#

（3）方式三：修改搜索模式列表（search_path）

通过修改search_path参数值，将myschema添加到搜索路径的模式列表中，并使其永久生效。

highgo=# show search_path; -- 修改前search_path

search_path

-----------------

"$user", public

(1 row)

highgo=# alter system set search_path = "$user",public,myschema; -- 添加模式myschema到search_path

ALTER SYSTEM

highgo=# SELECT pg_reload_conf(); -- 重新加载配置文件，是修改的search_path生效

pg_reload_conf

----------------

t

(1 row)

highgo=# highgo=# show search_path; -- 修改后的search_path

search_path

---------------------------

"$user", public, myschema

(1 row)

^

highgo=# select * from t_myschema; --非限定名访问

name

------

(0 rows)

highgo=#

此方式是永久生效的，切换会话还是可以访问：

highgo=# \c highgo highgo

You are now connected to database "highgo" as user "highgo".

highgo=# select * from t_myschema;

name

------

(0 rows)

highgo=#

1.2.3 总结

限定名访问可以精确检索到数据库中的表，不受search_path的搜索路径访问控制。

非限定名访问默认只能访问search_path中模式列表中的表。

1.3 搜索路径

1.3.1 介绍

试想一下：如果多个模式下存在同名表，且在访问时使用非限定名访问，那么是否会报错呢？或者不报错会检索到哪张表？

下面我们通过上面的设想来验证一下。

1.3.2 例子

准备工作：

highgo=# create table public.t_test(name varchar);

CREATE TABLE

highgo=# create table highgo.t_test(name varchar);

CREATE TABLE

highgo=# create table myschema.t_test(name varchar);

CREATE TABLE

highgo=# \dt

List of relations

Schema | Name | Type | Owner

---------- ------------ ------- --------

highgo | t_highgo | table | highgo

highgo | t_test | table | highgo

myschema | t_myschema | table | highgo

public | t_public | table | highgo

(4 rows)

highgo=#

细心的同学可能会发现，我们在三个模式下都创建了表t_test，且当前是可以全部检索到三个模式下的对象的，但为什么t_test只检索到了模式highgo下的？

分别往三个模式中的t_test表插入数据：

highgo=# insert into public.t_test values('t_test of public');

INSERT 0 1

highgo=# insert into highgo.t_test values('t_test of highgo');

INSERT 0 1

highgo=# insert into myschema.t_test values('t_test of myschema');

INSERT 0 1

highgo=#

（1）模式highgo在前时

highgo=# show search_path;

search_path

---------------------------

"$user", public, myschema

(1 row)

highgo=# select current_schema;

current_user

--------------

highgo

(1 row)

highgo=# select * from t_test;

name

------------------

t_test of highgo

(1 row)

highgo=#

非限定名访问是，检索到的是模式highgo下的表t_test。

（2）模式public在前时

highgo=# set search_path to public,"$user",myschema;

SET

highgo=# show search_path;

search_path

---------------------------

public, "$user", myschema

(1 row)

highgo=#select current_schema;

current_schema

----------------

public

(1 row)

highgo=# select * from t_test;

name

------------------

t_test of public

(1 row)

highgo=#

非限定名访问是，检索到的是模式public下的表t_test。

（3）模式myschema在前时

highgo=# set search_path to myschema,"$user",public;

SET

highgo=# show search_path;

search_path

---------------------------

myschema, "$user", public

(1 row)

highgo=# select current_schema;

current_schema

----------------

myschema

(1 row)

highgo=# select * from t_test;

name

--------------------

t_test of myschema

(1 row)

highgo=#

非限定名访问是，检索到的是模式public下的表t_test。

如上得出结论：当多个模式下出现同名表，使用非限定名访问表时，数据库系统搜索路径根据search_path配置的模式列表顺序依次检索，匹配到指定名称的表为止。

再次验证上述结论：

保留public和myschema模式下的表t_test，并且search_path中"$user"一直排在第一位。

（4）当public在myschema前

highgo=# drop table highgo.t_test; -- 模式highgo中已无表t_test

DROP TABLE

highgo=# set search_path to "$user",public,myschema;

SET

highgo=# show search_path; -- 模式public排在myschema前

search_path

---------------------------

"$user", public, myschema

(1 row)

highgo=#select current_schema; -- 当前模式

current_schema

----------------

highgo

(1 row)

highgo=# select * from t_test; -- 非限定名访问表t_test

name

------------------

t_test of public

(1 row)

highgo=#

如上可以看到，当表t_test均存在模式public与myschema中时，模式public在search_path模式列表中的位置在myschema前，则非限定名检索时，数据库系统搜索到public下的表t_test。

（5）当myschema在public前

highgo=# set search_path to "$user",myschema,public;

SET

highgo=# show search_path; -- 模式public排在myschema前

search_path

---------------------------

"$user", myschema, public

(1 row)

highgo=# select current_schema; -- 当前模式

current_schema

----------------

highgo

(1 row)

highgo=# select * from t_test; -- 非限定名访问表t_test

name

--------------------

t_test of myschema

(1 row)

highgo=#

可以看到，当myschema在前时，则非限定名检索时，数据库系统搜索到myschema下的表t_test。

1.3.3 总结

限定名访问，数据库系统将根据指定的限定名（数据库名、模式名）检索指定数据库的指定模式下的表。

非限定名访问，对于表的搜索路径，当客户端请求访问某张表时，数据库系统会根据search_path配置的模式列表顺序依次搜索，直到第一次搜索到指定名称的表为止。

2、检索表常见问题解决

2.1 relation "XXX" does not exist

此类问题，如果确认数据库中存在XXX对象，但客户端缺访问不到，只需要排查一下对象XXX所在的模式是否在客户端查询的search_path模式列表中（通过show search_path查看）。

具体原因及解决参考1.2 访问限制。

2.2 permission denied for type_x XXX

此类问题，属于当前登录用户（login_user）没有权限访问该对象。

type_xxx代表数据库对象（比如模式、表、函数等），XXX代表对象名称。

对于此类问题，只需要将操作对象的相应权限赋予给该用户即可。

最简单的方式：

将该对象的owner用户（owner_user）的组权限赋予登录用户，使用owner用户执行：

grant owner_user to login_user。