SET_OBJECT_STORAGE_POLICY

Creates or changes the storage policy of a database object by assigning it a labeled storage location.

Creates or changes the storage policy of a database object by assigning it a labeled storage location. The Tuple Mover uses this location to store new and existing data for this object. If the object already has an active storage policy, calling SET_OBJECT_STORAGE_POLICY sets this object's default storage to the new labeled location. Existing data for the object is moved to the new location.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

SET_OBJECT_STORAGE_POLICY (
  '[[database.]schema.]object-name', 'location-label'
   [,'key-min', 'key-max'] [, 'enforce-storage-move' ] )

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

object-name
Identifies the database object assigned to a labeled storage location. The object-name can resolve to a database, schema, or table.
location-label
The label of object-name's storage location.
key-min
key-max
Valid only if object-name is a table, specifies the range of table partition key values to store at the labeled location.
enforce-storage-move
Specifies when the Tuple Mover moves all existing storage containers for object-name to the labeled storage location:

  • false (default): Move storage containers only after all pending mergeout tasks return.

  • true: Immediately move all storage containers to the new location.

Privileges

One of the following:

  • Superuser

  • Object owner and access to its storage location.

Examples

The following example changes the storage policy of table t1 from the communal storage location with label main to the communal storage location with the label s3.

To see where the projections for the table t1 are stored before the policy change, you can query the STORAGE_CONTAINERS system table:

=> SELECT node_name, schema_name, projection_name, location_label, shard_name FROM STORAGE_CONTAINERS WHERE projection_name = 't1_super' ORDER BY shard_name, sal_storage_id;
 node_name | schema_name | projection_name | location_label | shard_name  
-----------+-------------+-----------------+----------------+-------------
 e1        | public      | t1_super        | main           | segment0003
 initiator | public      | t1_super        | main           | segment0003
 e1        | public      | t1_super        | main           | segment0003
 initiator | public      | t1_super        | main           | segment0003
 e1        | public      | t1_super        | main           | segment0003
 initiator | public      | t1_super        | main           | segment0003
 e1        | public      | t1_super        | main           | segment0003
 initiator | public      | t1_super        | main           | segment0003
(8 rows)

Call SET_OBJECT_STORAGE_POLICY to change the storage policy for t1 to the communal storage location with the s3 label:

=> SELECT SET_OBJECT_STORAGE_POLICY('t1', 's3');
 SET_OBJECT_STORAGE_POLICY  
----------------------------
 Object storage policy set.
(1 row)

Query the STORAGE_CONTAINERS system table to confirm that the object is now stored in the communal storage location with the label s3:

=> SELECT node_name, schema_name, projection_name, location_label, shard_name FROM STORAGE_CONTAINERS WHERE projection_name = 't1_super' ORDER BY shard_name, sal_storage_id;
 node_name | schema_name | projection_name | location_label | shard_name  
-----------+-------------+-----------------+----------------+-------------
 e1        | public      | t1_super        | s3             | segment0003
 initiator | public      | t1_super        | s3             | segment0003
 e1        | public      | t1_super        | s3             | segment0003
 initiator | public      | t1_super        | s3             | segment0003
 e1        | public      | t1_super        | s3             | segment0003
 initiator | public      | t1_super        | s3             | segment0003
 e1        | public      | t1_super        | s3             | segment0003
 initiator | public      | t1_super        | s3             | segment0003
(8 rows)

See also