lightdb for postgresql 系列【search_path介绍】

postgres会按照search_path的路径去寻找数据库对象，默认current_user同名的schema下去寻找

postgres=# show search_path ;
       search_path       
-------------------------
 "$user", public, oracle
(1 row)

如下查看当前用户和schema

postgres=# select current_user;
 current_user 
--------------
 lightdb
(1 row)
postgres=# \dn+ lightdb 
                List of schemas
 Name | Owner | Access privileges | Description 
------+-------+-------------------+-------------
(0 rows)

当前为lightdb用户且没有lightdb为名的schema，所以不指定schema前提下创建数据库对象，则创建到public schema下

postgres=# create table test_search (id int);
CREATE TABLE
postgres=# \dt+ test_search;
                              List of relations
 Schema |    Name     | Type  |  Owner  | Persistence |  Size   | Description 
--------+-------------+-------+---------+-------------+---------+-------------
 public | test_search | table | lightdb | permanent   | 0 bytes | 
(1 row)

如果创建了lightdb schema我们再看下

postgres=# create schema lightdb;
CREATE SCHEMA
postgres=# create table test_search1 (id int);
CREATE TABLE
postgres=# \dt+ test_search1;
                               List of relations
 Schema  |     Name     | Type  |  Owner  | Persistence |  Size   | Description 
---------+--------------+-------+---------+-------------+---------+-------------
 lightdb | test_search1 | table | lightdb | permanent   | 0 bytes | 
(1 row)

同样的去查询数据库对象也是按照这个顺序去寻找

postgres=# create table public.test_s (id int);
CREATE TABLE
postgres=# create table lightdb.test_s(id int);
CREATE TABLE
postgres=# insert into public.test_s values(1);
INSERT 0 1
postgres=# insert into lightdb.test_s values(2);
INSERT 0 1
postgres=# select * from test_s;
 id 
----
  2
(1 row)
postgres=# drop table test_s;
DROP TABLE
postgres=# select * from test_s;
 id 
----
  1
(1 row)

通过上面的测试，可以理解search_path概念，为什么会有search_path的问题，原因还是postgres权限区别于Oracle MySQL所致。