Help Center/ GaussDB/ Developer Guide(Distributed_8.x)/ SQL Reference/ SQL Syntax/ C/ CREATE TABLESPACE
Updated on 2024-08-20 GMT+08:00
View PDF
Share

CREATE TABLESPACE

Description

Creates a tablespace in a database.

Precautions

  • The system administrator or a user who inherits permissions of the built-in role gs_role_tablespace can create a tablespace.
  • Do not run CREATE TABLESPACE in a transaction block.
  • If running CREATE TABLESPACE fails but the internal directory (or file) has been created, the directory (or file) will remain. You need to manually clear it before creating the tablespace again. If there are residual files of soft links for the tablespace in the data directory, delete the residual files, and then perform O&M operations.
  • CREATE TABLESPACE cannot be used for two-phase transactions. If it fails on some nodes, the execution cannot be rolled back.
  • You are advised not to use user-defined tablespaces in the Huawei Cloud scenario.

    This is because user-defined tablespaces are usually used with storage media other than the main storage (storage device where the default tablespace is located, such as a disk) to isolate I/O resources that can be used by different services. Storage devices use standard configurations and do not have other available storage media in the Huawei Cloud scenario. If the user-defined tablespaces are not properly used, the system cannot run stably for a long time and the overall performance is affected. Therefore, you are advised to use the default tablespace.

Syntax

1
2
3
 
CREATE TABLESPACE tablespace_name
    [ OWNER user_name ] [ RELATIVE ] LOCATION 'directory' [ MAXSIZE 'space_size' ]
    [with_option_clause];

with_option_clause for creating a general tablespace is as follows:

1
 
WITH ({filesystem= { ' general ' | " general " | general } | address = { ' ip:port [, ... ] ' | " ip:port [, ... ] " } | cfgpath = { ' path ' | " path " } | storepath = { ' rootpath ' | " rootpath " } | random_page_cost = { ' value ' | " value " | value } | seq_page_cost = { ' value ' | " value " | value }}[, ... ])

Parameters

  • tablespace_name

    Specifies name of the tablespace to be created.

    The tablespace name must be distinct from the name of any existing tablespace in the database and cannot start with "pg", which are reserved for system catalogs.

    Value range: a string. It must comply with the naming convention.

  • OWNER user_name

    Specifies the name of the user who will own the tablespace. If omitted, the default owner is the current user.

    Only system administrators can create tablespaces, but they can use the OWNER clause to assign ownership of tablespaces to non-system-administrators.

    Value range: a string. It must be an existing user.

  • RELATIVE

    Specifies a relative path. The LOCATION directory is relative to each CN/DN data directory.

    Directory hierarchy: relative path of the CNs and DNs directory /pg_location/. A relative path contains a maximum of two levels.

    If this parameter is not specified, the absolute tablespace path is used. The LOCATION directory must be an absolute path.

  • LOCATION directory

    Specifies the directory for the table space. When creating an absolute tablespace path, ensure that the directory meets the following requirements:

    • The system user must have the read and write permissions on the directory, and the directory must be empty. If the directory does not exist, the system automatically creates it.
    • The directory must be an absolute path, and does not contain special characters, such as dollar sign ($) and greater-than sign (>).
    • The directory cannot be specified under the database data directory.
    • The directory must be a local path.

    Value range: a string. It must be a valid directory.

  • MAXSIZE 'space_size'

    Specifies the maximum size of a tablespace on a single DN.

    Value range: a string consisting of a positive integer and unit. The unit can be KB, MB, GB, TB, or PB currently. The unit of parsed value is KB and cannot exceed the range that can be expressed in 64 bits, which is 1 KB to 9007199254740991 KB.

  • filesystem

    Specifies the file system used by the tablespace.

    Value range:

    general: general file system.

    hdfs: Hadoop distributed file system. It is not supported in the current version.

    Default value: general

  • random_page_cost

    Specifies the time and resources required for randomly reading pages.

    Value range: 0 to 1.79769e+308

    Default value: value of the GUC parameter random_page_cost

  • seq_page_cost

    Specifies the time and resources required for reading pages in sequence.

    Value range: 0 to 1.79769e+308

    Default value: value of GUC parameter seq_page_cost

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
-- Create a tablespace.
gaussdb=# CREATE TABLESPACE tbs_location1 RELATIVE LOCATION 'test_tablespace/test_tablespace_1';

-- Create a tablespace and specify the maximum value.
gaussdb=# CREATE TABLESPACE tbs_location2 RELATIVE LOCATION 'test_tablespace/test_tablespace_2' MAXSIZE '10G';

-- Query tablespace information.
gaussdb=# SELECT * FROM pg_tablespace WHERE spcname = 'tbs_location2';
    spcname    | spcowner | spcacl | spcoptions | spcmaxsize | relative 
---------------+----------+--------+------------+------------+----------
 tbs_location2 |       10 |        |            | 10485760 K | t
(1 row)

-- Create user joe.
gaussdb=# CREATE ROLE joe IDENTIFIED BY '********';

-- Create a tablespace and set its owner to user joe.
gaussdb=# CREATE TABLESPACE tbs_location3 OWNER joe RELATIVE LOCATION 'test_tablespace/test_tablespace_3';

-- Delete tablespaces and users.
gaussdb=# DROP TABLESPACE tbs_location1;
gaussdb=# DROP TABLESPACE tbs_location2;
gaussdb=# DROP TABLESPACE tbs_location3;
gaussdb=# DROP ROLE joe;

Helpful Links

CREATE DATABASE, CREATE TABLE, CREATE INDEX, DROP TABLESPACE, and ALTER TABLESPACE

Suggestions

You are advised not to create tablespaces in a transaction using CREATE TABLESPACE.

Parent topic: C