Schemas

Daria Barysheva

Contents

Overview
View the list of schemas
Create a new schema
Schema search path
- Change the search_path parameter

Overview

Schema is a logical union of objects within a single database. In comparison with databases, schemas are not rigidly separated: users can access multiple schemas at a time (if they have the necessary privileges). You can use schemas for the following purposes:

To allow many users to access one database without conflicts.
To organize database objects into different logical groups to make them more manageable. It can be especially helpful while configuring access privileges.
To put the third-party applications into separate schemas so they do not collide with the names of other schemas objects.

TIP

Remember that schemas allow you to have more than one object (such as tables) with the same name in one database without conflicts — if these objects belong to different schemas.

The following table describes the system-level schemas that are added to the ADB database by default.

Default schemas
Name	Description
arenadata_toolkit	Stores ADB settings and information about the distribution of tables and operations performed
diskquota	It is used by the ADB extension Diskquota. This extension allows users to limit the disk space used by schemas and roles
gp_toolkit	It is an administrative schema that contains external tables, views, and functions that you can access with SQL commands — to view and query the system log files and other system metrics (including tables sizes, bloat, indexes, resource queues, spill files, etc.)
information_schema	Consists of a standardized set of views that contain information about the database objects. These views get system information from the system catalog tables in a standardized way (according to the ANSI SQL 2008 standard)
kadb	It is used in Kafka to ADB Connector
madlib	Stores the Apache Madlib library functions
pg_aoseg	Stores append-optimized table objects. This schema is used internally by the DBMS
pg_bitmapindex	Stores bitmap index objects such as lists of values. This schema is used internally by the DBMS
pg_catalog	Contains the full information about DB objects: the system catalog tables, built-in data types, functions, and operators
pg_toast	Stores large objects such as records that exceed the page size (TOAST, Oversized-Attribute Storage Technique). This schema is used internally by the DBMS
public	It is a default schema that is used for each new database object you create — unless you explicitly specify another schema (or change the default schema search path). All database roles (users) have the `CREATE` and `USAGE` privileges in the `public` schema

CAUTION

It is not recommended to drop or modify the schemas listed above.

View the list of schemas

To view the list of available schemas in the current database, do the following:

Сonnect to the ADB Master Server (via SSH, etc.) and login under the default user name gpadmin:
```
$ sudo su - gpadmin
```
Connect to the database via psql (or any other client program):
```
$ psql adb
```
The output is:
```
psql (9.4.24)
Type "help" for help.
```

Run one of the following commands:

Get information from the pg_catalog.pg_namespace table. It contains all database schemas.

SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;

The output is:

      nspname       | nspowner |              nspacl
--------------------+----------+----------------------------------
 arenadata_toolkit  |       10 | {gpadmin=UC/gpadmin}
 diskquota          |       10 |
 gp_toolkit         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 information_schema |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 kadb               |       10 |
 madlib             |       10 |
 pg_aoseg           |       10 |
 pg_bitmapindex     |       10 |
 pg_catalog         |       10 | {gpadmin=UC/gpadmin,=U/gpadmin}
 pg_toast           |       10 |
 public             |       10 | {gpadmin=UC/gpadmin,=UC/gpadmin}
(11 rows)

Get data from the information_schema.schemata table. Compare to pg_catalog.pg_namespace, this table stores information about the database schemas according to the ANSI SQL 2008 standard.

SELECT * FROM information_schema.schemata ORDER BY schema_name;

The output is:

 catalog_name |    schema_name     | schema_owner | default_character_set_catalog | default_character_set_schema | default_character_set_name | sql_path
--------------+--------------------+--------------+-------------------------------+------------------------------+----------------------------+----------
 adb          | arenadata_toolkit  | gpadmin      |                               |                              |                            |
 adb          | diskquota          | gpadmin      |                               |                              |                            |
 adb          | gp_toolkit         | gpadmin      |                               |                              |                            |
 adb          | information_schema | gpadmin      |                               |                              |                            |
 adb          | kadb               | gpadmin      |                               |                              |                            |
 adb          | madlib             | gpadmin      |                               |                              |                            |
 adb          | pg_aoseg           | gpadmin      |                               |                              |                            |
 adb          | pg_bitmapindex     | gpadmin      |                               |                              |                            |
 adb          | pg_catalog         | gpadmin      |                               |                              |                            |
 adb          | pg_toast           | gpadmin      |                               |                              |                            |
 adb          | public             | gpadmin      |                               |                              |                            |
(11 rows)

If you work in the psql client program, you can use the \dnS+ meta-command to show the list of schemas.

\dnS+

The output is:

                                                 List of schemas
        Name        |  Owner  | Access privileges  |                         Description
--------------------+---------+--------------------+-------------------------------------------------------------
 arenadata_toolkit  | gpadmin | gpadmin=UC/gpadmin |
 diskquota          | gpadmin |                    |
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 information_schema | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 kadb               | gpadmin |                    |
 madlib             | gpadmin |                    |
 pg_aoseg           | gpadmin |                    | Reserved schema for Append Only segment list and eof tables
 pg_bitmapindex     | gpadmin |                    | Reserved schema for internal relations of bitmap indexes
 pg_catalog         | gpadmin | gpadmin=UC/gpadmin+| system catalog schema
                    |         | =U/gpadmin         |
 pg_toast           | gpadmin |                    | reserved schema for TOAST tables
 public             | gpadmin | gpadmin=UC/gpadmin+| standard public schema
                    |         | =UC/gpadmin        |
(11 rows)

Create a new schema

To create a new schema, it is necessary to use the CREATE SCHEMA command and then write the schema name.

The following example creates the new schema test_schema (in the current database adb).

CREATE SCHEMA test_schema;

The output should be similar to:

CREATE SCHEMA

Now, if you run any command to list the database schemas, you get the following:

adb=# \dnS+
                                                 List of schemas
        Name        |  Owner  | Access privileges  |                         Description
--------------------+---------+--------------------+-------------------------------------------------------------
 arenadata_toolkit  | gpadmin | gpadmin=UC/gpadmin |
 diskquota          | gpadmin |                    |
 gp_toolkit         | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 information_schema | gpadmin | gpadmin=UC/gpadmin+|
                    |         | =U/gpadmin         |
 kadb               | gpadmin |                    |
 madlib             | gpadmin |                    |
 pg_aoseg           | gpadmin |                    | Reserved schema for Append Only segment list and eof tables
 pg_bitmapindex     | gpadmin |                    | Reserved schema for internal relations of bitmap indexes
 pg_catalog         | gpadmin | gpadmin=UC/gpadmin+| system catalog schema
                    |         | =U/gpadmin         |
 pg_toast           | gpadmin |                    | reserved schema for TOAST tables
 public             | gpadmin | gpadmin=UC/gpadmin+| standard public schema
                    |         | =UC/gpadmin        |
 test_schema        | gpadmin |                    |
(12 rows)

Schema search path

To access database objects, you can use two main forms of object names: the ASCII standard database.schema.object or the shorter variant schema.object. The second form is preferable since you can not access more than one database at a time anyway. But there is also the third option that allows to omit even the schema names (and use only object names, e.g. table names). It is possible due to using the schema search path.

The schema search path is configured via the search_path parameter. This parameter specifies the order of searching the available schemas for objects. In fact, it contains the ordered comma-separated list of schemas. The requested object is searched in the first schema of the search path, in case of failure — in the second one, and so on. If there is no match in the search path, an error is reported, even if matching object names exist in other schemas in the database. The schema listed first in the search path becomes the default schema (it is also called the current schema). Aside from being the first schema searched, it is also the schema in which new objects are created if no schema name is explicitly defined.

To show the current schema search path, you can use the following command:

SHOW search_path;

In the default setup this command returns:

   search_path
-----------------
 "$user", public
(1 row)

The first element "$user" specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema. It explains why the public schema is used for new database objects by default. The next query returns the default (or current) schema:

SELECT current_schema();

In the default setup the result is:

 current_schema
----------------
 public
(1 row)

NOTE

The pg_catalog schema is always included into the schema search path, even if this schema is not explicitly named in the search path.

Change the search_path parameter

Changing the search_path parameter is possible at the database level (via the ALTER DATABASE command) and the role level (via the ALTER ROLE command). Let’s consider the first method using a simple example:

Set the search_path parameter at the database level.
```
ALTER DATABASE adb SET search_path TO test_schema,public;
```
The result should be similar to:
```
ALTER DATABASE
```
Logout from psql (or any other client program you use) to reopen the client session.
```
\q
```

Connect to the adb database again.

$ psql adb

The output is:

psql (9.4.24)
Type "help" for help.

Create any table without assigning a schema name.

CREATE TABLE test(id1 INT) DISTRIBUTED BY(id1);

The result is:

CREATE TABLE

Check the schema that is used for the test table.

\dt test

It should be equal to test_schema, not public.

               List of relations
   Schema    | Name | Type  |  Owner  | Storage
-------------+------+-------+---------+---------
 test_schema | test | table | gpadmin | heap
(1 row)

Return the previous value of the search_path parameter and restart the client session again.
```
adb=# ALTER DATABASE adb SET search_path TO "$user",public;
ALTER DATABASE
adb=# \q
```

NOTE

It is also possible to change the search_path parameter value at the session level — via the SET search_path TO <new_path_value>; command (where <new_path_value> is a new value of the parameter, e.g. test_schema,public). But it is strongly recommended to set the parameter at the database or role level, so that it does not reset on each session restart.

Found a mistake? Seleсt text and press Ctrl+Enter to report it