postgreSQL的search"/>
关于postgreSQL的search
作者:瀚高PG实验室 (Highgo PG Lab)- 徐云鹤
如下是对search_path的一些测试:
查询当前search_path:
highgo=# show search_path;
search_path
----------------
"$user",public
(1 row)
创建一张表:
highgo=# create table one (int int);
CREATE TABLE
查询该表所属模式:
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
public | one | table | highgo
(1 row)
该表被创建到了public模式下。
那为什么没有被创建到"$user"下呢?
查询当前数据库中的模式:
highgo=# select catalog_name,schema_name,schema_owner from information_schema.schemata;
catalog_name | schema_name | schema_owner
--------------+--------------------+--------------
highgo | pg_toast | highgo
highgo | pg_temp_1 | highgo
highgo | pg_toast_temp_1 | highgo
highgo | pg_catalog | highgo
highgo | public | highgo
highgo | hgdb_catalog | highgo
highgo | oracle_catalog | highgo
highgo | information_schema | highgo
原来并没有与"$user"同名的highgo模式。
再创建一个模式:
highgo=# create schema first;
CREATE SCHEMA
创建表:
highgo=# create table two (int int);
CREATE TABLE
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
public | one | table | highgo
public | two | table | highgo
(2 rows)
highgo=# show search_path;
search_path
----------------
"$user",public
(1 row)
修改search_path,将public放到前边:
highgo=# set search_path=public,"$user";
SET
highgo=# create table three (int int);
CREATE TABLE
检查:
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
public | one | table | highgo
public | three | table | highgo
public | two | table | highgo
(3 rows)
看上去好像没什么影响。
再创建与用户同名模式:
highgo=# create schema highgo;
CREATE SCHEMA
修改search_path,将"$user"放到前边:
highgo=# set search_path="$user",public;
SET
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
public | one | table | highgo
public | three | table | highgo
public | two | table | highgo
(3 rows)
创建表:
highgo=# create table four (int int);
CREATE TABLE
查询:
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
highgo | four | table | highgo
public | one | table | highgo
public | three | table | highgo
public | two | table | highgo
(4 rows)
发现模式发生了改变。
再次修改search_path,修改第一个为不存在的模式。
highgo=# set search_path=asp,"$user",public;
SET
创建表:
highgo=# create table five (int int);
CREATE TABLE
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
highgo | five | table | highgo
highgo | four | table | highgo
public | one | table | highgo
public | three | table | highgo
public | two | table | highgo
(5 rows)
发现它跳过了不存在的模式,该表被创建到了第一个存在的模式。
创建asp模式。
highgo=# create schema asp;
CREATE SCHEMA
创建表:
highgo=# create table six (int int);
CREATE TABLE
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
asp | six | table | highgo
highgo | five | table | highgo
highgo | four | table | highgo
public | one | table | highgo
public | three | table | highgo
public | two | table | highgo
(6 rows)
预料之中。
显式指定模式名。
highgo=# create table public.seven(int int);
CREATE TABLE
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
asp | six | table | highgo
highgo | five | table | highgo
highgo | four | table | highgo
public | one | table | highgo
public | seven | table | highgo
public | three | table | highgo
public | two | table | highgo
(7 rows)
与预期的一样,创建到了指定的模式下。
修改search_path,去掉asp:
highgo=# set search_path="$user",public;
SET
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
highgo | five | table | highgo
highgo | four | table | highgo
public | one | table | highgo
public | seven | table | highgo
public | three | table | highgo
public | two | table | highgo
(6 rows)
此时已经看不到asp模式下的表。
尝试查询:
highgo=# select * from six;
ERROR: relation "six" does not exist at character 15
STATEMENT: select * from six;
ERROR: relation "six" does not exist
LINE 1: select * from six;
^
再次创建表six:
highgo=# create table six (int int);
CREATE TABLE
highgo=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+--------
highgo | five | table | highgo
highgo | four | table | highgo
highgo | six | table | highgo
public | one | table | highgo
public | seven | table | highgo
public | three | table | highgo
public | two | table | highgo
(7 rows)
此时存在six表在highgo模式下。
highgo=# insert into six values(1);
INSERT 0 1
highgo=# select * from six;
int
-----
1
(1 row)
修改search_path:
highgo=# set search_path=asp,"$user",public;
SET
highgo=# select * from six;
int
-----
(0 rows)
发现并没有数据。
原因是此时查询的是asp模式下的six表。
验证当前的schema:
highgo=# select current_schema();
current_schema
----------------
asp
(1 row)
查询数据库配置文件:
[highgo@db1 data]$ cat postgresql.conf|grep search_path
#search_path = '"$user",public' # schema names
默认是注释掉的。
现在尝试修改它。
search_path = 'php,"$user",public' # schema names
重载:
[highgo@db1 data]$ pg_ctl reload
在之前的会话查询:
highgo=# show search_path;
search_path
----------------------
asp, "$user", public
(1 row)
新开会话查询:
highgo=# show search_path;
search_path
--------------------
php,"$user",public
(1 row)
说明该参数在数据库层面设置后,不会影响现有会话。
实验过程确认在会话中设置该参数,仅会对此会话造成影响。
也可以对用户层面进行设置:
highgo=# alter user highgo set search_path=asp,php,"$user",public;
ALTER ROLE
在现有会话查询:
highgo=# show search_path;
search_path
--------------------
php,"$user",public
(1 row)
新开会话查询:
highgo=# show search_path;
search_path
---------------------------
asp, php, "$user", public
(1 row)
说明该参数在用户层面设置后,不会影响现有会话,并且用户层面做的设置的优先级高于数据库层面。
更多推荐
关于postgreSQL的search
发布评论