search_path 是一个要查找的 schema 列表。search_path 中第一个匹配的表被认为是想要的表。如果 search_path 中没有匹配的表，就会报告错误，即使数据库中其他 schema 中存在匹配的表名。

在 search_path 中添加一个 schema，实际上是信任所有用户对该 schema 有 CREATE 权限。

命令行访问 AnalyticDB for PostgreSQL

psql 是 Greenplum 中比较常用的命令行工具，提供了丰富的命令，其二进制文件在 Greenplum 安装后的 BIN 目录下。

将客户端工具压缩包下载到本地后，需要进行解压并安装，以下内容以 CentOS 7 平台上安装 AnalyticDB PostgreSQL 6.0 版客户端工具为例，其使用方式如下：

  • 在客户端工具压缩包目录下，解压文件，命令如下：
tar -xzvf adbpg_client_package.el7.x86_64.tar.gz
  • 解压后切换到bin目录下，执行如下命令：
cd adbpg_client_package/bin

  • bin 目录下包括客户端工具 psql、pg_dump 等，按照各工具参考文档，执行命令行

  • 配置环境变量

export PATH=$PATH:/home/username/adbpg_client_package/bin
  • 通过指定参数的方式进行连接：
psql -h yourgpdbaddress.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount

-h：指定主机地址
-p：指定端口号
-d：指定数据库（默认的数据库是 postgres）
-U：指定连接的用户
  • 输入密码，进入 psql 的命令行页面
psql (9.4.24)
Type "help" for help.

postgres=>

注：连接前，需配置网络白名单。

参数描述

search_path 中的第一个 schema 被称为当前 schema。除了是第一个被搜索的 schema 之外，如果 CREATE TABLE 命令没有指定 schema 名称，创建新表的 schema 默认即 search_path 中的第一个 schema。

查看参数

使用如下命令，显示当前的 search_path：

testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

第一个值指定一个与当前用户同名的 schema 被搜索。如果不存在这样的 schema，该条目将被忽略。第二个元素指的是我们已经看到的 public schema。

search_path 中存在的第一个 schema 是创建新对象的默认位置。这就是默认情况下（当没有同名 schema 时），对象是在 public schema 中创建的原因。当对象在没有 schema 限定的情况下被引用时（表修改、数据修改或查询命令），search_path 被遍历，直到找到一个匹配的对象。因此，在默认配置中，任何未经限定的访问都只能引用 public schema。

设置参数

使用如下命令，可以将新的 schema 放在 search_path 中：

testadb=> set search_path to public, testsa;
SET

我们在这里省略了 "$user"，因为我们没有立即需要它，然后我们可以在没有 schema 限定的情况下访问 testsa 模式下的某张表。

-- 查看表结构

testadb=> \d dim_ann_poc;
            Table "testsa.dim_ann_poc"
   Column   |          Type           | Modifiers
------------+-------------------------+-----------
 vc_type    | character varying(1000) |
 vc_detaile | character varying(1000) |
 f_price    | numeric                 |
Distributed by: (vc_type)

另外，由于 testsa 是路径中的第一个元素，新的对象将默认在其中创建。

我们也可以这样写：

testadb=> set search_path to testsa;
SET

testadb=> show search_path;
 search_path
-------------
 testsa
(1 row)

在当前会话下，我们就不能够在没有明确限定的情况下访问 public schema 了。

注：

  • 当不同的 schema 中存在同名的对象，将使用在 search_path 中首先找到的对象
  • 系统目录模式 pg_catalog 始终被搜索，无论是否在路径中提及
  • 如果有 schema 存在大写字母，要用双引号，例如 "My_Schema"

search_path 持久性

针对会话修改

在连接中直接使用 set search_path to xxx 是可以针对本连接生效的，但是弊端就是仅对当前连接生效，重新连接数据库则被还原了。

  • 查看当前数据库的 search_path
testadb=> show search_path;
  search_path
----------------
 "$user",public
(1 row)
  • 针对会话修改
testadb=> set search_path to public, testsa;
SET
  • 查看修改后的 search_path
testadb=> show search_path;
  search_path
----------------
 public, testsa
(1 row)
  • 重新登录数据库，再次查看 search_path
testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

重新登录，针对会话修改的 search_path 失效。

针对数据库修改

  • 查看当前数据库的 search_path
testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
  • 针对数据库修改
testadb=> alter database testadb set search_path to public, testsa;
ALTER DATABASE
  • 查看修改后的 search_path（需重新登录数据库）
testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
  • 重新登录数据库，再次查看 search_path
testadb=> show search_path;
  search_path
----------------
 public, testsa
(1 row)

重新登录，针对数据库修改的 search_path 永久生效。

案例使用

存在与用户名同名的 schema

注：此处的用户名，指登录数据库时的用户名。

环境说明：

  • search_path 为 "$user", public
  • 连接用户为 testzw
  • 存在与用户名相同的 testzw schema
  • 创建表 t1

操作示例：

  • 查看当前用户
testadb=> select current_user;
 current_user
--------------
 testzw
(1 row)
  • 查看当前 search_path
testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
  • 创建与用户名同名的 schema
testadb=> create schema testzw;
CREATE SCHEMA
  • 查看当前数据库下的所有 schema
testadb=> \dn
      List of schemas
     Name      |   Owner
---------------+------------
 IMV_delta     | adbpgadmin
 adbpg_restore | adbpgadmin
 adbpg_toolkit | adbpgadmin
 gp_toolkit    | adbpgadmin
 public        | adbpgadmin
 testsa        | testzw
 testzw        | testzw
(7 rows)
  • 创建表（不指定模式）
testadb=> create table t1 (id int, name varchar(10)) distributed by (id);
CREATE TABLE

testadb=> \d t1;
             Table "testzw.t1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(10) |
Distributed by: (id)
  • 创建表（指定 public 模式）
testadb=> create table public.t1 (id int, name varchar(10), age varchar(10)) distributed by (id);
CREATE TABLE

testadb=> \d t1;
             Table "testzw.t1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(10) |
Distributed by: (id)

testadb=> \d public.t1;
             Table "public.t1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(10) |
 age    | character varying(10) |
Distributed by: (id)

结论：

  • 表 t1 的 schema 自动为 search_path 的第一选择 "$user"，即 testzw
  • 此时，对表 t1 的任何无前缀操作均在 testzw.t1 上
  • 使用 \d t1 显示表结构时，优先显示 testzw schema 中的表

不存在与用户名同名的 schema

环境说明：

  • search_path 为 "$user", public
  • 连接用户为 testzw
  • 不存在与用户名相同的 testzw schema
  • 创建表 t1

操作示例：

  • 查看当前用户
testadb=> select current_user;
 current_user
--------------
 testzw
(1 row)
  • 查看当前 search_path
testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
  • 删除与用户名同名的 schema
testadb=> drop schema testzw cascade;
  • 查看当前数据库下的所有 schema
testadb=> \dn;
      List of schemas
     Name      |   Owner
---------------+------------
 IMV_delta     | adbpgadmin
 adbpg_restore | adbpgadmin
 adbpg_toolkit | adbpgadmin
 gp_toolkit    | adbpgadmin
 public        | adbpgadmin
 testsa        | testzw
(6 rows)
  • 创建表（不指定模式）
testadb=> create table t2 (id int, name varchar(10)) distributed by (id);
CREATE TABLE

testadb=> \d t2;
             Table "public.t2"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(10) |
Distributed by: (id)

结论：

  • 表 t2 的 schema 自动为 search_path 的第二选择 public
  • 此时，对表 t2 的任何无前缀操作均在 public.t2 上

修改 search_path

环境说明：

  • 创建 schema myschema
  • 设置 search_path 为 myschema（会话级）
  • 无前缀创建表 t3
  • 无前缀查看表 t3

操作示例：

  • 查看当前用户
testadb=> select current_user;
 current_user
--------------
 testzw
(1 row)
  • 查看当前 search_path
testadb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)
  • 创建 schema
testadb=> create schema myschema;
CREATE SCHEMA
  • 查看当前数据库下的所有 schema
testadb=> \dn;
      List of schemas
     Name      |   Owner
---------------+------------
 IMV_delta     | adbpgadmin
 adbpg_restore | adbpgadmin
 adbpg_toolkit | adbpgadmin
 gp_toolkit    | adbpgadmin
 myschema      | testzw
 public        | adbpgadmin
 testsa        | testzw
(7 rows)
  • 修改 search_path
testadb=> set search_path to myschema;
SET

testadb=> show search_path;
 search_path
-------------
 myschema
(1 row)
  • 创建表（不指定模式）
testadb=> create table t3 (id int, name varchar(10)) distributed by (id);
CREATE TABLE

testadb=> \d t3;
            Table "myschema.t3"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(10) |
Distributed by: (id)

结论：

  • 根据 search_path 的设置，无前缀创建及查看的表 t3 的 schema 均为 myschema

参考资料

超详细 VMware 虚拟机下 CentOS 7 安装教程（图文详解）

云原生数据仓库 AnalyticDB PostgreSQL 版客户端连接

PostgreSQL 中 search_path 参数解密

原创文章，转载请注明出处：http://www.opcoder.cn/article/56/