Schemas
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.
| Name | Description |
|---|---|
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 |
|
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 adbThe output is:
psql (9.4.24) Type "help" for help.
-
Run one of the following commands:
-
Get information from the
pg_catalog.pg_namespacetable. 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.schematatable. Compare topg_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_pathparameter 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
adbdatabase again.$ psql adbThe 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
testtable.\dt testIt should be equal to
test_schema, notpublic.List of relations Schema | Name | Type | Owner | Storage -------------+------+-------+---------+--------- test_schema | test | table | gpadmin | heap (1 row)
-
Return the previous value of the
search_pathparameter 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 |