ALTER TABLESPACE

Function

ALTER TABLESPACE modifies the attributes of a tablespace.

Precautions

• Only the tablespace owner or a user granted with the ALTER permission can run the ALTER TABLESPACE command. The system administrator has this permission by default. To modify a tablespace owner, you must be the tablespace owner or system administrator and a member of the new owner role.

• To change the owner, you must also be a direct or indirect member of the new owning role.

  NOTE: If new_owner is the same as old_owner, the current user will not be verified. A message indicating successful ALTER execution is displayed.

Syntax

• The syntax of renaming a tablespace is as follows:

  AlterTablespace ::= ALTER TABLESPACE tablespace_name
      RENAME TO new_tablespace_name;

• The syntax of setting the owner of a tablespace is as follows:

  AlterTablespace ::= ALTER TABLESPACE tablespace_name
      OWNER TO new_owner;

• The syntax of setting the attributes of a tablespace is as follows:

  AlterTablespace ::= ALTER TABLESPACE tablespace_name
      SET ( {tablespace_option = value} [, ... ] );

• The syntax of resetting the attributes of a tablespace is as follows:

  AlterTablespace ::= ALTER TABLESPACE tablespace_name
      RESET ( { tablespace_option } [, ...] );

• The syntax of setting the quota of a tablespace is as follows:

  AlterTablespace ::= ALTER TABLESPACE tablespace_name
      RESIZE MAXSIZE { UNLIMITED | 'space_size'};

Parameter Description

• tablespace_name

  Specifies the tablespace to be modified.

  Value range: an existing table name

• new_tablespace_name

  Specifies the new name of a tablespace.

  The new name cannot start with PG_.

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

• new_owner

  Specifies the new owner of the tablespace.

  Value range: an existing username

• tablespace_option

  Sets or resets the parameters of a tablespace.

  Value range:

  • seq_page_cost: sets the optimizer to calculate the cost of obtaining disk pages in sequence. The default value is 1.0.

  • random_page_cost: sets the optimizer to calculate the cost of obtaining disk pages in a non-sequential manner. The default value is 4.0.

    NOTE:

    • The value of random_page_cost is relative to that of seq_page_cost. It is meaningless when the value is equal to or less than the value of seq_page_cost.
    • The prerequisite for the default value 4.0 is that the optimizer uses indexes to scan table data and the hit ratio of table data in the cache is about 90%.
    • If the size of the table data space is smaller than that of the physical memory, decrease the value to a proper level. On the contrary, if the hit ratio of table data in the cache is lower than 90%, increase the value.
    • If random-access memory like SSD is adopted, the value can be decreased to a certain degree to reflect the cost of true random scan.

  Value range: a positive floating point number

• RESIZE MAXSIZE

  Resets the maximum size of tablespace.

  Value range:

  • UNLIMITED: No limit is set for this tablespace.

  • Determined by space_size. For details about the format, see CREATE TABLESPACE.

    NOTE:

    • If the modified value is lower than the actual one used by the current tablespace, the modification can be successful. Later, the users can continue to write data into the tablespace only when they set the value lower than the new limit.

    • You can also use the following statement to change the value of MAXSIZE:

    ALTER TABLESPACE tablespace_name RESIZE MAXSIZE
    { 'UNLIMITED' | 'space_size'};

Examples

See Examples in CREATE TABLESPACE.