SET_OBJECT_STORAGE_POLICY

Creates or changes an object storage policy by associating a database object with a labeled storage location.

Note: You cannot create a storage policy on a USER type storage location.

Syntax

SET_OBJECT_STORAGE_POLICY ( 'object_name', 'location_label' 
	[, 'key_min', 'key_max'] [, enforce_storage_move] )

Parameters

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 the storage location with which object_name is being associated. 

key_min, key_max

[Optional] Applicable only when object_name is a table, key_min and key_max specify the table partition key value range to be stored at the location. 

enforce_storage_move

[Optional] Specify this parameter as true to move all existing storage data to the target location within this function's transaction. By default, data move the next time the Tuple Mover runs on the affected data.

Privileges

Must be the object owner to set the storage policy and have access to the storage location.

New Storage Policy

If an object does not have a storage policy, this function creates a new policy. The labeled location is then used as the default storage location during TM operations, such as moveout and mergeout.

Existing Storage Policy

If the object already has an active storage policy, calling this function changes the default storage for the object to the new labeled location. Any existing data stored on the previous storage location is marked to move to the new location during the next TM moveout operations. To move the data immediately, use the enforce_storage_move argument.

Forcing Existing Data Storage to a New Storage Location

You can optionally use this function to move existing data storage to a new location as part of completing the current transaction. To do so, specify the last parameter as true. You might want to force a move, even though it means waiting for the operation to complete before continuing, if the data being moved is old. The Tuple Mover runs less frequently on older data.

To move existing data as part of the next TM moveout, either omit the parameter, or specify its value as false.

Note: Specifying the parameter as true performs a cluster-wide operation. If an error occurs on any node, the function displays a warning message and skips the node where the error occurs. It then continues executing the operation on the remaining nodes.

Examples

This example shows how to set a storage policy for the table test to use the storage labeled SSD as its default location:

=>  select set_object_storage_policy('test','ssd', true);
set_object_storage_policy
--------------------------------------------------
Object storage policy set.
Task: moving storages
(Table: public.test) (Projection: public.test_b0)
(Table: public.test) (Projection: public.test_b1)

(1 row)

See Also